Home » RDBMS Server » Security » How can I audit ALL activity for a specific Client Id? (Oracle 11.2.0.4, 3 node RAC, Linux Rel 5.7)
How can I audit ALL activity for a specific Client Id? [message #617803] Thu, 03 July 2014 13:43 Go to next message
schenk
Messages: 2
Registered: July 2014
Location: United States
Junior Member
We are running Oracle 11.2.0.4 / Web Application / Connection Pooling / Logging in with a single Database User and setting the Client ID for each application user.
I would like to audit ALL activity for a specific Client Id. It is easy to audit a client id for a database object (below), but what about all activity?

BEGIN
DBMS_FGA.ADD_POLICY(
OBJECT_SCHEMA => 'ABC',
OBJECT_NAME => 'EMPLOYEE',
POLICY_NAME => 'TRACK_EMPL_CLIENTID',
AUDIT_CONDITION => 'SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER'') = ''aduser''',
HANDLER_SCHEMA => NULL,
HANDLER_MODULE => NULL,
ENABLE => true,
STATEMENT_TYPES => 'INSERT,UPDATE,DELETE,SELECT',
AUDIT_TRAIL => DBMS_FGA.DB + DBMS_FGA.EXTENDED,
AUDIT_COLUMN => NULL);
END;
/

I read a post that if you leave the OBJECT_NAME = NULL, it will audit all activity, which is exactly what I would like to do. However, this does not work. I get an ORA-01405 Fetched column value is NULL error.

BEGIN
DBMS_FGA.ADD_POLICY(
OBJECT_SCHEMA => 'ABC',
OBJECT_NAME => NULL,
POLICY_NAME => 'TRACK_CLIENTID',
AUDIT_CONDITION => 'SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER'') = ''aduser''',
HANDLER_SCHEMA => NULL,
HANDLER_MODULE => NULL,
ENABLE => true,
STATEMENT_TYPES => 'INSERT,UPDATE,DELETE,SELECT',
AUDIT_TRAIL => DBMS_FGA.DB + DBMS_FGA.EXTENDED,
AUDIT_COLUMN => NULL);
END;
/

Does anyone know if there is a way to audit all activity for a specific Client ID?
(DB After Login trigger does not work because the Connection Pooling maintains a connection.)
Any ideas are greatly appreciated.

Thanks in advance,
MF
Re: How can I audit ALL activity for a specific Client Id? [message #617820 is a reply to message #617803] Thu, 03 July 2014 14:49 Go to previous messageGo to next message
John Watson
Messages: 7933
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

You have a real problem here, and one of which Uncle Oracle is fully aware: it is fixed in release 12.x. In release 11.x, I do not know of any good solution. One possibility is to enable tracing with dbms_monitor.client_id_trace_enable. Would that be any use to you?
Re: How can I audit ALL activity for a specific Client Id? [message #618147 is a reply to message #617820] Tue, 08 July 2014 09:05 Go to previous message
schenk
Messages: 2
Registered: July 2014
Location: United States
Junior Member
Thank you for your response. I appreciate it.
That confirms my suspicions.

Until we move to 12, I will use tracing if we need to track a specific Client.
(I'm glad this is getting addressed in 12.)

Thanks again.
MF

Previous Topic: User Getting Locked
Next Topic: Key Points on Database security best practices
Goto Forum:
  


Current Time: Mon Jun 17 18:23:07 CDT 2019