Re: Auditing DML of selected users

From: Teresa Redmond <XKEAAGIPVIEZ_at_spammotel.co>
Date: Mon, 03 May 2004 16:23:09 GMT
Message-ID: <e4001fe6a43b0d5eef7126cb64c77fd3@news.teranews.com>

On Mon, 3 May 2004 01:06:14 -0700, in comp.databases.oracle.server, "George Mansoor" <ghm_at_4link.net> wrote:

>Pete - Thanks for the information. I just don't get how to audit
>insert/update/delete on all tables for just a few users and get the DML
>statement for that insert/update/delete. Is this possible w/ auditing, or do
>I need to install triggers on every table?

George,

This is the trigger I use for capturing updates/deletes. I had to make a trigger for each table, and had to use "if deleting" and "if updating" rather than getting the actual SQL used because I couldn't find the correlation between the user's information and the SQL the user executed. When I did use code for getting the SQL statement, it always gave me the trigger code, not the update or delete I had performed on the test table. Please also keep in mind that I'm a newbie to this also, this is what I was able to get together after much lurking and question asking. :-)

<code>
BEGIN
  if updating then
    INSERT INTO schema.audit_table
    SELECT 'table_name', USER, SYSDATE,

           s.osuser, s.program, 'UPDATING', s.terminal     FROM dual d, v$session s, v$sql sq
    WHERE (S.SQL_ADDRESS = sq.ADDRESS
    AND S.osuser IS NOT NULL
    and s.osuser not like '%SYSTEM%');
  else
    if deleting then

      INSERT INTO schema.audit_table
      SELECT 'table_name', USER, SYSDATE,
             s.osuser, s.program, 'DELETING', s.terminal
      FROM dual d, 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 if;
  end if;
END audit_trigger;
</code>

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

Received on Mon May 03 2004 - 11:23:09 CDT