Populating CLIENT_ID column in DBA_AUDIT_SESSION [message #504937] |
Thu, 28 April 2011 08:19 |
WJKovacs
Messages: 9 Registered: April 2010
|
Junior Member |
|
|
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.
|
|
|
|
|
|
Re: Populating CLIENT_ID column in DBA_AUDIT_SESSION [message #504981 is a reply to message #504980] |
Thu, 28 April 2011 11:27 |
WJKovacs
Messages: 9 Registered: April 2010
|
Junior Member |
|
|
Testing has been done with another account, I just tried on another server, same results. See below:
SQL> create or replace procedure capture_module
as
begin
dbms_session.set_identifier(sys_context('userenv','module'));
end;
/
Procedure created.
SQL> create or replace trigger module_logon
after logon
on database
begin
capture_module;
end;
/
Trigger created.
SQL> select sys_context('userenv','client_identifier') from dual;
SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')
------------------------------------------------
SQL> select count(*) from dba_audit_session;
COUNT(*)
----------
143603
SQL> connect / as sysdba
Connected.
SQL> truncate table sys.aud$;
Table truncated.
SQL> select count(*) from dba_audit_session;
COUNT(*)
----------
0
Then I connected as a non-sys/system user from sqlplus on my local machine and checked to see if the audit event occurred:
SQL> /
COUNT(*)
----------
2
SQL> select client_id from dba_audit_session;
CLIENT_ID
----------------------------------------------------------------
But when I check to see if the CLIENT_IDENTIFIER from dual as updated by the trigger, it shows it has:
SQL> select sys_context('userenv','client_identifier') from dual;
SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')
----------------------------------------------
sqlplus@ushdba1 (TNS V1-V3)
SQL>
[Updated on: Thu, 28 April 2011 11:41] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
Re: Populating CLIENT_ID column in DBA_AUDIT_SESSION [message #505116 is a reply to message #505114] |
Fri, 29 April 2011 08:55 |
WJKovacs
Messages: 9 Registered: April 2010
|
Junior Member |
|
|
Okay, so if that's the case then my after logon trigger will never populate the client_id column in the DBA_AUDIT_SESSION table. Bearing that in mind, is there any way I can capture the module that is being used by the connection and view it through audit reports? Perhaps a link between views? Any advice would be appreciated.
|
|
|
|
|
Re: Populating CLIENT_ID column in DBA_AUDIT_SESSION [message #505140 is a reply to message #505120] |
Fri, 29 April 2011 11:32 |
WJKovacs
Messages: 9 Registered: April 2010
|
Junior Member |
|
|
Well at least it answered the question of why the trigger wasn't populating the client_id column of dba_audit_session so that's something - thank you.
But, I still need to find a way to capture the module information for each session created for the audit report. I'll have to find another way. Thanks again for the help.
|
|
|