Re: Audit command help

On Mon, 08 Mar 2004 15:23:43 -0800, in comp.databases.oracle.server, Daniel Morgan <damorgan_at_x.washington.edu> scribbled:

>Teresa Redmond wrote:
>
>> 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!
>
>Look up DDL triggers at: http://www.psoug.org/reference/library.html

Okay, I've been fiddling with this and here is what I've come up with. I based it on your example:
//
CREATE OR REPLACE TRIGGER bcs_trigger
BEFORE CREATE
ON SCHEMA DECLARE
  oper ddl_log.operation%TYPE;

BEGIN
  INSERT INTO ddl_log
  SELECT ora_sysevent, ora_dict_obj_owner,   ora_dict_obj_name, NULL, USER, SYSDATE   FROM dual;
END bcs_trigger;
//
but I changed mine to:
//
CREATE OR REPLACE TRIGGER test_audit_trigger AFTER UPDATE OR DELETE
ON schema.atest

BEGIN
  INSERT INTO schema.audit_table
  SELECT dictionary_obj_name, USER, SYSDATE,

         s.osuser, s.program, sq.sql_text, s.terminal   FROM v$session s, v$sql sq
  WHERE (S.SQL_ADDRESS = sq.ADDRESS
  AND S.osuser IS NOT NULL
  and s.osuser not like '%SYSTEM%');
END test_audit_trigger;
//

So far, this works. This puts a record into audit_table after I change a value in the atest table. Problem is, the SQL text it puts in the record *is the SQL text from the trigger body*!!! I wanted the SQL text that changed the value in the atest table! GRRRR!!!

I just found something on Pete Finnegan's site that says runtime SQL can't be captured. Is that what I'm trying to do, and it can't be done? I also am not getting any value for dictionary_obj_name (which is what it's called in my db). I would like to have the name of the table that got affected entered into the record along with the SQL that affected it.

Thanks again for all your help!

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