Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Thanks!

Forgot to mention that this is in Oracle 10G

share|improve this question
    
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 '12 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 '12 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 '12 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 '12 at 13:21
    
Just knocked a test case up for you below. –  Phil May 4 '12 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
Connected.
Loading glogin.sql

Session altered.

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

Audit succeeded.

SYS@PHILL11G2 AS SYSDBA> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY ACCESS;

Audit succeeded.

SYS@PHILL11G2 AS SYSDBA> truncate table aud$;

Table truncated.

SYS@PHILL11G2 AS SYSDBA> TRUNCATE TABLE fga_log$;

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;

  COUNT(*)
----------
     1

PHIL@PHILL11G2 > conn / as sysdba

SYS@PHILL11G2 AS SYSDBA> select CLIENT_ID from dba_audit_trail;

CLIENT_ID
----------------------------------------------------------------
PHILTEST
PHILTEST

2 rows selected.

SYS@PHILL11G2 AS SYSDBA> 
share|improve this answer
    
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) –  Leigh Riffel May 4 '12 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 '12 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 '12 at 20:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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