Call: +44 (0)1904 557620 Call

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "Podcast with Pete Finnigan on the subject of virtual patching"] [Next entry: "A new exploit to bypass Oracle Database Vault has been released"]

The question of revoking PUBLIC grants

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';



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';



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.

There has been 4 Comments posted on this article

November 18th, 2008 at 07:48 am

Pete Finnigan says:

MetaLink does have a cautionary note -- Note#247093.1 -- on this issue.

November 18th, 2008 at 12:49 pm

Pete Finnigan says:

Thanks for the heads up on the metalink note Hemant.



November 18th, 2008 at 02:33 pm

Pete Finnigan says:

My perspective about most grants to Public can be found here:

November 18th, 2008 at 03:52 pm

Pete Finnigan says:

Hi Kirk,

Thanks for the link. I agree with you, which is what i hope came across in my post. If it can be shown that some key views should be revoked then thats fine as long as you can ensure that 1)it doesnt break the application and 2) it stays set where you think it is, i..e Oracle tends to undo revokes for you. This whole area is complex because each system is different.