Re: Auditing: Trigger or Audit_Trail?

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Mon, 1 Mar 2004 21:05:55 +0100
Message-ID: <40439702$0$22374$626a14ce@news.free.fr>

"Teresa Redmond" <NJZLIRWUWYGI_at_spammotel.com> a écrit dans le message de news:bf8a96e8f85fdf98a5cd3fa61bf74075_at_news.teranews.com...
> On Mon, 1 Mar 2004 13:46:47 -0500, in comp.databases.oracle.server,
> "Mark C. Stock" <mcstockX_at_Xenquery .com> scribbled:
> >
> >"Teresa Redmond" <NJZLIRWUWYGI_at_spammotel.com> wrote in message
> >news:9cc7db721501316b936bdeca1a0bae88_at_news.teranews.com...
> >| On Mon, 1 Mar 2004 19:08:28 +0100, in comp.databases.oracle.server,
> >| "Michel Cadot" <micadot{at}altern{dot}org> scribbled:
> >|
> >| >"Teresa Redmond" <NJZLIRWUWYGI_at_spammotel.com> a écrit dans le message de
> >| >news:afd4289902170a172b40a4a2f9e36762_at_news.teranews.com...
> >| >>
> >| >> My question is this: if I have one schema that I would like to audit
> >| >> (131 tables and 196000 records, and not a fast growing db), and what I
> >| >> want to audit is DML (delete and update only) and who does it, when,
> >| >> and from where, am I better off using the Audit Trail or using
> >| >> triggers? If I use the Audit Trail can I change it so that it will do
> >| >> just what I want, or is this "not good to do"?
> >| >
> >| >In your case, you can use:
> >| >audit delete, update on <your objects>
> >| >and then do something like the following to report:
> >| >
> >| >Column username FORMAT A11 HEADING User
> >| >Column terminal FORMAT A11 HEADING Terminal
> >| >Column owner FORMAT A10 HEADING Owner
> >| >Column obj_name FORMAT A15 HEADING Object
> >| >Column action_name FORMAT A14 HEADING Action TRUNC
> >| >Column returncode FORMAT 99990 HEADING RC
> >| >Column time_ FORMAT A17 HEADING Time
> >| >Select username, terminal, owner, obj_name, action_name, returncode,
> >| > to_char(timestamp,'DD/MM/YY HH24:MI:SS') time_
> >| >from dba_audit_object
> >| >order by timestamp asc
> >| >/
> >|
> >| Thank you *so* much, I will be trying this immediately!
> >
> >teresa,
> >
> >just verifying that you don't need data value audit, just actions? if so,
> >this suggestion is right on the money as it only records when UPDATE and
> >DELETE statements have been executed against the tables

>

> Yes, I just need to know that User1 performed Delete or Update on
> table Schema.Table from Machine at Date. We may go further with data
> value audits, but it's not been an issue yet. Unfortunately, after
> performing 'audit delete, update on schema.table' for all tables,
> adding some records to "schema.atest", then updating and deleting from
> atest, I got "no rows selected" when running the above script.

You have to set AUDIT_TRAIL init parameter to DB. This is a static parameter, so you have to restart the instance.

>

> I neglected to write before that I'm using 8i 8.1.5 on Win2kPro, does
> this make a difference? I got no error messages, but then, I didn't
> use the "show errors" directive.

>

> Thank you both so much.

>

> --
> Teresa Redmond
> Programmer/Analyst III
> Anteon Corporation
> tredmond at anteon dot com

Regards
Michel Cadot Received on Mon Mar 01 2004 - 14:05:55 CST