Re: object privilege granted to public a sox problem? (and others)

Douglas,

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. I suspect a bug in the AppDetective descriptions?

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.

cheers

Pete

Douglas Cowles wrote:
> I appreciate everyone's responses to the extproc problem I had yesterday.
> I have a further question since many of you seem to know something about
> sox recommendations. I don't know whether the appdetective application
> is flagging just SOX recommendations or not but some of them seem quite
> daunting to implement and seem contrary to Oracle's own database
> philosophy. This isn't to say they're wrong I'm just looking for some
> advice.
>
> For example.. it flags "Object privilege granted to public" - This flags
> over TWO thousand violations - everything from
> Execute on OWA_COOKIE to
> select on ALL_TABLES, ALL_CONSTRAINTS.. standard vanilla stuff etc., I
> mean select on all_tables is a big security violation? I mean I guess so
> but how well are my patches and upgrades going to go if I revoke all 2000
> object grants to public? I'd post the whole list but it would just be
> annoyingly long.
>
> Is this a SOX requirement? Should this be risk accepted instead? In
> which case, does anyone have a good way to put that?
>
> Again, another one is "System privilege granted to public" 128 violations
> - this includes stuff like "CREATE PROCEDURE" granted to perfstat, or
> "EXECUTE ANY PROCEDURE" granted to OUTLN. I mean I guess I can see some
> of this but other stuff seems like I could be in a corner if I revoke it
> all.
>
> Most of this stuff is Oracle standard - maybe the idea is it's too loose.
>
> Any thoughts?
>
>
> Doug Cowles
>
>

-- 

Pete Finnigan
Director
PeteFinnigan.com Limited

Specialists in database security.

If you need help to audit or secure an Oracle database, please ask for
details of our courses and consulting services

Phone: +44 (0)1904 791188
Fax  : +44 (0)1904 791188
Mob  : +44 (0)7742 114223
email: pete_at_petefinnigan.com
site : http://www.petefinnigan.com

Registered Office: 9 Beech Grove, Acomb, York, YO26 5LD, United Kingdom
Company No       : 4664901
VAT No.          : 940 6681 14

Please note that this email communication is intended only for the
addressee and may contain confidential or privileged information. The
contents of this email may be circulated internally within your
organisation only and may not be communicated to third parties without
the prior written permission of PeteFinnigan.com Limited.  This email is
not intended nor should it be taken to create any legal relations,
contractual or otherwise.

--
http://www.freelists.org/webpage/oracle-l