I'm hoping someone can offer some assistance with my quandary:
I'm running a 10.2.0.4 database with auditing enabled:
SQL> show parameter audit_trail;
NAME TYPE VALUE
------------------------------------ -------- -------------
audit_trail string DB, EXTENDED
I have auditing enabled for create session:
SQL> select audit_option, success, failure from dba_stmt_audit_opts;
AUDIT_OPTION SUCCESS FAILURE
---------------------------------------- ---------- ----------
CREATE SESSION BY ACCESS BY ACCESS
My problem is that when I run a report against DBA_AUDIT_SESSION, the CLIENT_ID column is never populated, it's always blank. I've tried running a trigger to populate the client_identifier variable:
create or replace procedure capture_module
as
begin
dbms_session.set_identifier(sys_context('userenv','module'));
end;
/
create or replace trigger module_logon
after logon
on database
begin
capture_module;
end;
/
And it works on the client_identifier when I check dual:
select sys_context('userenv','client_identifier') from dual;
And if I put an access trigger on a table and create an audit event, the CLIENT_ID column from DBA_AUDIT_TRAIL and DBA_COMMON_AUDIT_TRAIL both show the updated value (module), but no matter what, I never get the CLIENT_ID column in DBA_AUDIT_SESSION to show anything - it's always null.
What am I doing wrong? At the end of the day, I want to run a report against DBA_AUDIT_SESSION that will tell me who logged in, when they logged in and out, where they logged in from and the one thing I can't get - what module they were running (SQLplus, Toad, etc). Why can't I get the CLIENT_ID column to take the value I'm setting with the logon trigger?
Thanks in advance.
] 
Blog
Search
Help
Members
Register
Login
Home
