Can this be accomplished with Oracles Fine Grained Auditing

auditoracleoracle-10g

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

Best Answer

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>

Related Solutions

Enabling/disabling/changing Oracle auditing without a shutdown

AUDIT_TRAIL is an initialisation parameters and needs a database restart. Anyway by default AUDIT_TRAIL is set to DB. This could be enough.

With this AUDIT_TRAIL you have just to issue:

AUDIT SELECT TABLE

to start the audit on any table for any select. As you stated this activity has an overhead from the database point of view. you have to pay attention to you SYSAUX tablespace growth where by default resides the sys.aud$ table. To move sys.aud$ you have to do:

SQL> BEGIN
 DBMS_AUDIT_MGMT.set_audit_trail_location(
 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
 audit_trail_location_value => 'AUDIT_TBS');
END;
/

FGA is much more than AUDIT and lets you specify the conditions necessary for an audit record to be generated. Furthermore FGA policies are programatically bound to the object (table, view).

Apart from this you should consider a different solution like an third party tool for DAM (Direct Access Monitoring). Oracle has its own solution today called Oracle Firewall. Otherwise you can check for other products like IBM Guardium, DBProtect, etc.

Fatal NI connect errors

In addition to Phil suggestion, I'll activate tracing on sql net.

Just add:

TRACE_LEVEL_SERVER=USER

to: $ORACLE_HOME/network/admin/sqlnet.ora (create it if don't exist)

As for the listener, you can increase the tracing level using ADMIN or SUPPORT options instead of USER. When you found the client who is causing it, you can enable client sql net traces as well.

Fatal NI connect errors are hard to determinate. But in my experience, most of them was caused by a client who left a session opened in TOAD, and then the computer goes to sleep mode.

note* If you have MOS, you should check note [1116960.1] and [1121357.1]

Best Answer

Related Solutions

Enabling/disabling/changing Oracle auditing without a shutdown

Fatal NI connect errors

Related Question