Re: object privilege granted to public a sox problem? (and others)
- From: Pete Finnigan <pete@xxxxxxxxxxxxxxxx>
- To: dcowles@xxxxxxxxxx
- Date: Sun, 16 Nov 2008 19:18:55 +0000
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@xxxxxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
