Oracle 11g R2 – Auditing Specific User Activity

auditoracle-11g-r2

I want to be able audit user 'SCOTT' on 'SELECT, INSERT, UPDATE, DELETE' dml operations across all objects in the database SCOTT has access to.

I'm familiar with dbms_fga but I can't achieve what I'd like to using the add_policy arguments (it wants to be more fine-grained than I need it to be).

I've also tried switching on the audit_trail parameter: 

alter system set audit_trail=os scope=spfile;
bounce
alter system set audit_file_dest='/u01/app/oracle/auditing';
audit session by SCOTT;

but all this seems to do is create files which log login/logoff activity (and doesn't seem to be restricted to SCOTT either).

Any ideas if it's possible to audit a specific user on all activity that takes place in their session?

Best Answer

Thanks to Andrew Brennan, I created a trigger that enables tracing when the user logs on:

CREATE OR REPLACE TRIGGER USER_TRACE_TRG
AFTER LOGON ON DATABASE
BEGIN
    IF USER = 'SCOTT'
  THEN
    execute immediate 'alter session set events ''10046 trace name context forever, level 1''';
  END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
Related Question