Re: How to audit object grants to PUBLIC

From: Thomas Gaines <Thomas.Gaines_at_noaa.gov>
Date: Fri, 10 May 2002 11:27:06 -0600
Message-ID: <3CDC02EA.25813214@noaa.gov>

CS -

Sorry. Jim and I couldn't resist...

Anyway, I ran a little test on my 8.1.7 test database (sorry, I don't have an 8.1.6 version available here at work), and all worked quite well. As the system user, I'm unable to see that the particular user (in my test case, scott) granted select privilege, but I can see that he did grant some object privilege. Would that be sufficient for your needs?

In any case, here are the notes that I wrote as I did this little test:

=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
First, I turn on auditing in my init.ora on my test1 database: audit_trail = db

Restart the database.

Next, as user system, enable auditing of the grant action: audit grant on scott.emp by access;

Next, connect as scott and issue the following: grant select on scott.emp to public;

To confirm that auditing is indeed on, do the following query as user system:
SQL> SELECT * FROM sys.dba_obj_audit_opts 2 WHERE owner = 'SCOTT' AND object_name LIKE 'EMP%';

OWNER                          OBJECT_NAME                    OBJECT_TY

ALT AUD

------------------------------ ------------------------------ ---------

--- ---
COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI --- --- --- --- --- --- --- --- --- --- --- --- --- ---

SCOTT                          EMP                            TABLE

-/- -/-
-/- -/- A/A -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-

Now, check the actual audit trail, but choose only particular columns to limit the output:
SQL> select OS_USERNAME, username, obj_name, action_name, 2 grantee from dba_audit_trail;

OS_USERNAME     USERNAME                       OBJ_NAME
--------------- ------------------------------ ------------------
ACTION_NAME                 GRANTEE
--------------------------- ------------------------------
SEG\tgaines     SCOTT                          EMP
GRANT OBJECT                PUBLIC

=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+

From here, everything looks okay. Try duplicating what I did on your 8.1.6 database. If there's still a problem, then maybe we've stumbled upon yet another Oracle bug. Drag.

Chris Stamper wrote:

> I can't get my 8.1.6 db to audit basic object priv grants if the
> grantor owns the object.  EX.  foo says:
>
> grant select on foo.bar to public;
>
> It doesn't show up in dba_audit_trail.
>
> Can anyone tell me how to audit such actions? I've already combed
> through sql reference, dba guide, concepts, metalink, etc etc.
> Auditting ALL by <owner> doesn't do it, auditting GRANT TABLE by
> <owner> doesn't do it.  What does?  Does anything?
>
> TIA.
> CS

Received on Fri May 10 2002 - 12:27:06 CDT