Re: Auditing: Trigger or Audit

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Mon, 1 Mar 2004 14:48:14 -0500
Message-ID: <v8GdnQ1kB-QZDt7dRVn-uA@comcast.com>

"Teresa Redmond" <NJZLIRWUWYGI_at_spammotel.com> wrote in message 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.

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

did you set the INIT.ORA parameter:

AUDIT_TRAIL=TRUE ;-{ mcs Received on Mon Mar 01 2004 - 13:48:14 CST

Re: Auditing: Trigger or Audit_Trail?