Re: Audit command help

From: Joe <nospam_at_joekaz.net>
Date: 9 Mar 2004 04:40:33 -0800
Message-ID: <b9c56449.0403090440.79efa21f@posting.google.com>

Teresa Redmond <NJZLIRWUWYGI_at_spammotel.com> wrote in message news:<a1ebc36a3d1f262f0be466b6b9b47bbb_at_news.teranews.com>...
> Hello again, (oracle 8.1.5 on win2kPro)
>
> In testing the auditing I set up last week, I've worked up a few more
> questions. Is it possible to audit objects by who does something to
> them? I've been reading the Audit Statement in
> http://download-east.oracle.com/docs/cd/F49540_01/DOC/index.htm and
> this doc show two audit statements: for object, and for sql
> statements. For what I want to do, seeing who deletes or updates what
> and what statement is used (among a few other things), should I use
> the audit sql statement syntax?
>
> As in: "audit delete table, update table, by user1, user2, user3;".
>
> Also, I finished putting in the audit statements for the tables last
> week, but have seen thousands and thousands of results put in sys.aud$
> and sys.dba_audit_object on the "select" statement, which I did not
> include in my audit statement. I entered "audit delete, update on
> schema.table;" for every table in the schema. I'm unsure why I would
> see results on a "select", but I am trying to learn this so am not
> surprised that I don't know why. :-)
>
> I just ran "noaudit all;" and am trying to clear out the almost
> 700,000 rows that appeared since last week. Delete from sys.aud$ is
> *very* slow...
>
> Thanks again for your help!

If you are finding a lot of "select" statements in the audit trail, you may have select auditing on by default. You can check:

SQL> select * from ALL_DEF_AUDIT_OPTS;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE --- --- --- --- --- --- --- --- --- --- --- --- --- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-

^^^^^^^^ that shows that no default options are on.  If you see:

SQL> select * from ALL_DEF_AUDIT_OPTS;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE --- --- --- --- --- --- --- --- --- --- --- --- --- -/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/-

^^^^^^^  then the S/S under 'SEL' means that every new table created

gets select auditing on by default. You can stop this behavior with "noaudit all on default" which is not the same as "noaudit all". As this only affect the defaults applied to new objects, you also need to turn off that option for any existing object which has it enabled.  This should show you any object which already has select auditing enabled:

select * from DBA_OBJ_AUDIT_OPTS where SEL != '-/-';

You can then do a "noaudit select on schema.obj" for each one. Or "noaudit all on schema.obj".

Regarding slow deletes, if you are just testing, and don't want to clear the whole audit trail, "truncate sys.aud$" will be much quicker than delete. Be sure that you really want to delete everything before doing this!

-- 
Joe
http://www.cafeshops.com/joekaz
http://www.joekaz.net

Received on Tue Mar 09 2004 - 06:40:33 CST