We need to audit when a table is accessed (either select, update or delete) and I know Oracles Fine Grained Auditing can handle that. The issue we are having is that all of our users use the same oracle account to log in and we need to audit WHO, using the users username on the application, did the action on this table. What I was thinking is that we can just pass the users id in as part of the select statement ( select 'bob', col1, col2...) and that could possibly be accessed with Oracles Fine Grained Auditing.

Does anyone have any experience in doing something like this or if it is even possible. If not, any ideas on how we can accomplish this?


Forgot to mention that this is in Oracle 10G

  • Do the users login using a dedicated connection, or is there connection pooling involved? The normal way of doing this kind of thing is to use DBMS_APPLICATION_INFO.set_module/set_action to set the text you want to capture (in this case username), then log it from v$session.module/action.
    – Philᵀᴹ
    May 4, 2012 at 13:14
  • I'd also like to add that business requirements like this come up all the time, and often the auditing requirements are very far-fetched and not actually practical, needed or sensible. What kind of application is it? Could the application itself log to an audit table of some kind? Buying 10Tb of disk just for FGA is no fun.
    – Philᵀᴹ
    May 4, 2012 at 13:17
  • We use connection pooling for accessing the DB. Will I be able to capture what information the user is accessing in that select statement via DBMS_APPLICATION_INFO?
    – jworrin
    May 4, 2012 at 13:20
  • It is a financial application and we need to keep this info for PCI purposes ( who views credit card numbers ect. ) so we need to capture when ever the data is accessed, not just when the application accesses the data
    – jworrin
    May 4, 2012 at 13:21
  • Just knocked a test case up for you below.
    – Philᵀᴹ
    May 4, 2012 at 13:38

1 Answer 1


Get the client to call dbms_session.set_identifier('PHILTEST');. This will then be set in the CLIENT_ID audit field. Obviously you'l need to call it first from the client whenever a new connection is pulled from the pool.

For example:

PHIL@PHILL11G2 > conn / as sysdba
Loading glogin.sql

Session altered.

SYS@PHILL11G2 AS SYSDBA> audit all by phil by access;

Audit succeeded.


Audit succeeded.

SYS@PHILL11G2 AS SYSDBA> truncate table aud$;

Table truncated.


Table truncated.

SYS@PHILL11G2 AS SYSDBA> conn phil/phil

PHIL@PHILL11G2 > exec dbms_session.set_identifier('PHILTEST');

PL/SQL procedure successfully completed.

PHIL@PHILL11G2 > select count(*) from fgatest;


PHIL@PHILL11G2 > conn / as sysdba

SYS@PHILL11G2 AS SYSDBA> select CLIENT_ID from dba_audit_trail;


2 rows selected.

  • If the range of times when a user is logged in are recorded and only one user can use the OS at a time, then the already populated OS_USER column may be sufficient. If these criteria cannot be met then filling Client_ID is the right thing to do. (+1) May 4, 2012 at 14:39
  • In my table that I am wanting to audit, I have the credit card information and I want to track exactly what information was looked at, not just the fact that someone looked at the table. I would want to store the credit card number (obfuscated) and the expiration date, along with the time and the client_id (application users id after using dbms_session.set_identifier('blah'). Can this also be accomplished?
    – jworrin
    May 8, 2012 at 19:31
  • If you're doing PCI certification, wouldn't you be better off just logging the pk of the credit card table along with the client_id (for later lookup, if investigation warrants). Just a thought, as I know how stringent IT security people can be.
    – Philᵀᴹ
    May 8, 2012 at 20:01
  • @Philᵀᴹ can you show example how to use this information in FGA auditing? In DBMS_FGA.add_policy, parameter audit_condition relies on columns in audited table, e.g. audit_condition => 'DEPARTMENT_ID = 50', . would that work for checking sys_context('USERENV','CLIENT_IDENTIFIER') ?
    – Jakub P
    May 25, 2021 at 8:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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