Re: Auditing: Trigger or Audit_Trail?

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.

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