Oracle Fine Grained Auditing Issue in Oracle 9i / 10g

Name Oracle Fine Grained Auditing Issue
Systems Affected Oracle Database 9i / 10g
Severity Medium Risk
Category Auditing disabled
Vendor URL http://www.oracle.com
Author Alexander Kornbrust (ak at red-database-security.com)
Date 02 May 2005 (V 1.00)

Details
Fine grained audit (FGA) does not work if the user SYS runs a SELECT statements. There are 2 issues. The SELECT statement issued by SYS is not audited.
Once SYS selects the table all subsequent SELECTs by other users are NOT audited.
Workaround
Do not run SQL for FGA objects as user SYS.
Flush the shared pool to activate auditing again.

Test case:

1. Create user and objects and add policy.
   connect /as sysdba
   grant dba to fga identified by fga;
   conn fga/fga
   create table emp as select * from scott.emp;
   execute dbms_fga.add_policy(object_schema=>'FGA',
   object_name=>'EMP',policy_name=>'FGA_POLICY');

2. Run SQL from fga user
   conn fga/fga
   select count(*) from fga.emp;
   select sql_text,to_char(timestamp,'HH24:MI:SS') time
   from sys.dba_fga_audit_trail;

–> It will return 1 row.

3. Run SQL from SYS user
   conn /as sysdba
   select count(*) from fga.emp;
   select sql_text,to_char(timestamp,'HH24:MI:SS') time
   from sys.dba_fga_audit_trail;

–> It will return 1 row which was obtained by step 2.

A new row was not inserted.

4. Run SQL from fga user again

conn fga/fga
select count(*) from fga.emp;
select sql_text,to_char(timestamp,'HH24:MI:SS') time
from sys.dba_fga_audit_trail;

–> It will return 1 row which was obtained by step 2.

A new row was not inserted again.

Patch Information
Apply patchset 10.1.0.4. for Oracle 10g.
This bug is not fixed in 9.2.0.6 with the latest patches from Oracle Critical Patch Update April 2005 applied.

History
17-feb-2004 Oracle logged and published this bug in Metalink (Bugid: 3450991)
28-mar-2005 Oracle release patchset 10.1.0.4 (for Linux)