Oracle auditing on specific user activity

Question

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?

• Andrew Brennan: You can create a logon trigger that enables tracing whenever the user logs in
• Huskie69: I hadn't thought of that, works a treat many thanks!

Answer 1

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;
/