Home » RDBMS Server » Security » Populating CLIENT_ID column in DBA_AUDIT_SESSION (Oracle 10.2.0.4, UNIX/Windows)
Populating CLIENT_ID column in DBA_AUDIT_SESSION [message #504937] Thu, 28 April 2011 08:19 Go to next message
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 #504953 is a reply to message #504937] Thu, 28 April 2011 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
Have you ever actually connected from a real REMOTE client system; not DB server itself?
Just asking.
Re: Populating CLIENT_ID column in DBA_AUDIT_SESSION [message #504979 is a reply to message #504953] Thu, 28 April 2011 11:14 Go to previous messageGo to next message
WJKovacs
Messages: 9
Registered: April 2010
Junior Member
I've been doing most of the testing on my local machine and connecting through SQLPlus and Toad. I'll enable the triggers on one of our staging databases and update to see if there are any changes in behavior.
Re: Populating CLIENT_ID column in DBA_AUDIT_SESSION [message #504980 is a reply to message #504979] Thu, 28 April 2011 11:19 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
Also test using schema other than SYS & SYSTEM.
Re: Populating CLIENT_ID column in DBA_AUDIT_SESSION [message #504981 is a reply to message #504980] Thu, 28 April 2011 11:27 Go to previous messageGo to next message
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 #504985 is a reply to message #504981] Thu, 28 April 2011 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what are the 2 rows you have in the audit view?

By the way you don't need (and must not) post a 256 character line just to show a null value. Truncate the line.

Regards
Michel
Re: Populating CLIENT_ID column in DBA_AUDIT_SESSION [message #505104 is a reply to message #504985] Fri, 29 April 2011 07:31 Go to previous messageGo to next message
WJKovacs
Messages: 9
Registered: April 2010
Junior Member
Sorry about the 256 character, I didn't realize it. Won't do again.

The line would look like this:

OS_USERNAME     USERNAME        USERHOST             CLIENT_ID                      TIMESTAMP              LOGOFF_TIME
marcosc       MARCOSC        CHI\MARCOSC-EHRO                                   2011-04-28-14:11:01
marcosc       MARCOSC        CHI\MARCOSC-EHRO                                   2011-04-28-14:11:15
Re: Populating CLIENT_ID column in DBA_AUDIT_SESSION [message #505111 is a reply to message #505104] Fri, 29 April 2011 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The line would look like this

And the action names are?
I bet these are "LOGON" ones.
Records about LOGON are genrated BEFORE the trigger is executed (it is an AFTER LOGN trigger).

Regards
Michel

[Updated on: Fri, 29 April 2011 08:05]

Report message to a moderator

Re: Populating CLIENT_ID column in DBA_AUDIT_SESSION [message #505113 is a reply to message #505111] Fri, 29 April 2011 08:13 Go to previous messageGo to next message
WJKovacs
Messages: 9
Registered: April 2010
Junior Member
Ok, so the trigger I have created activates AFTER the logon, and the audit information is captured BEFORE the trigger fires and therefore the updated client_id doesn't show in DBA_AUDIT_SESSION. Am I understanding that correctly?

[Updated on: Fri, 29 April 2011 08:14]

Report message to a moderator

Re: Populating CLIENT_ID column in DBA_AUDIT_SESSION [message #505114 is a reply to message #505113] Fri, 29 April 2011 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what I meant.

Regards
Michel
Re: Populating CLIENT_ID column in DBA_AUDIT_SESSION [message #505116 is a reply to message #505114] Fri, 29 April 2011 08:55 Go to previous messageGo to next message
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 #505118 is a reply to message #505116] Fri, 29 April 2011 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
is application 3-tier as below

EndUser<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer

Re: Populating CLIENT_ID column in DBA_AUDIT_SESSION [message #505120 is a reply to message #505116] Fri, 29 April 2011 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unless it is an Oracle product like SQL*Plus that uses internal access or a fair one that gives this information on OCI connection properties you cannot get the module in a logon trigger.

Regards
Michel
Re: Populating CLIENT_ID column in DBA_AUDIT_SESSION [message #505140 is a reply to message #505120] Fri, 29 April 2011 11:32 Go to previous message
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.
Previous Topic: Use Oracle Database on AIX with Active Directory
Next Topic: Oracle Label Security - Insert Operation
Goto Forum:
  


Current Time: Tue Oct 21 11:15:35 CDT 2014

Total time taken to generate the page: 0.08583 seconds