Re: Audit command help

On 9 Mar 2004 04:40:33 -0800, in comp.databases.oracle.server, nospam_at_joekaz.net (Joe) scribbled:

>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.

The above is what I saw, after having run "noaudit all;".

>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!

Thanks for that info, that sure was fast! And yes, I did want to delete everything, but you're right, you do have to be careful.

Thanks!

-- 
~teresa~
 AFH Barwench

  ^..^  "Never try to outstubborn a cat."  Robert A. Heinlein  ^..^
  http://pixelmeow.com/  http://www.heinleinsociety.org/
     http://pixelmeow.com/Book_Exchange/index.htm
           http://pixelmeow.com/forum/
      aim: pixelmeow msn: pixelmeow_at_passport.com