Pete Finnigan's Oracle Security Weblog

Doug posted an interesting question on the Oracle-l list yesterday titled "object privilege granted to public a sox problem? (and others)". This is an interesting problem and often one that is worth solving to make the database more secure BUT often hard to do. This is my response:

There seems to be some confusion in some of what you report below. The
first is that you say that AppDetective reports 2000 issues of grants
against objects to PUBLIC. In 11g, the number is

SQL> select count(*) from dba_tab_privs
2 where grantee='PUBLIC';

COUNT(*)
----------
27467

SQL>

In 10gR2 its around 21.5K and in 91R2 its around 12K. This would suggest
that AppDetective is picking out a large subset of objects, some key
things like packages that manipulate web content such as OWA_COOKIE and
probably all views with the ALL_% in the name BUT certainly not
reporting all grants to PUBLIC. Most views with ALL_% have some issue
for security. The problem is that you make the mistake that just because
a user/schema has access to objects of the type exposed by a view (one
poster talked about ALL_TABLES) that its OK for the "real person" who
accesses that user/schema to see what privilege the user/schema has.
This is not always the case that its OK, Imagine that an application
schema has access to ALL_TABLES, it does by defailt via PUBLIC, it can
see all the application schema, lost of useful data for instance for
someone who wants to steal; say credit cards, it makes it easier to find
them. The problem in this example is that if the application is
breakable then anyone gaining access also has access to these views. So
its best to revoke them. The issue is that because this privileges are
granted to PUBLIC its world-wide across the database. I agree that some
should be revoked (ALL_USERS for instance is a very good example to
prevent enumeration of usernames) from PUBLIC, then if the access is
genuinely required by a user/schema, consider it carefully and grant it
back again to just that user/schema and no more.

The issue with revoking from PUBLIC are that:

1) any upgrade/patch may break if it relies on access to a particular view
2) The upgrade/ patch often puts the PUBLIC privilege back again.
3) Running catproc can also put some PUBLIC privileges back again
4) finding which users/schemas need access to the ALL_% views is
tedious. If everything is static you can check for invalid objects,
revoke the privilege, check again for invalid objects, grant the
privilege to the users/schemas who own the objects, recompile.
5) The above works for static code, if there is embedded dymamic code
that used the view/package it doesnt work. If there is external code
that uses it; again it doesnt work.

Revoking 2000 public grants is unrealistic BUT it can be done with a lot
of careful work, full understanding of the schemas installed and
application code. I do know customers of mine who have revoked quite a
lot of public privileges. Keeping them revoked is a big job also that
must be automated.

In your second point you talk about "System privilege granted to public"
128 violations BUT the examples are not grants to PUBLIC? also:

SQL> select count(*) from dba_sys_privs
2 where grantee = 'PUBLIC';

COUNT(*)
----------
0

SQL>

There have never been system privileges granted to public by default and the descriptions reported are not in fact system privileges granted to PUBLIC. I
suspect a bug in the AppDetective descriptions or perhaps your interpretation in this question?

So in summary, I can see the benefits of revoking key PUBLIC privileges
BUT you must understand the process completely, TEST and also make sure
tha changes remain in place.