0

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?

2
  • You can create a logon trigger that enables tracing whenever the user logs in – Andrew Brennan Nov 11 '16 at 14:36
  • I hadn't thought of that, works a treat many thanks! – Huskie69 Nov 15 '16 at 13:37
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.