Multiple Records Generated In Dba_fga_audit_trail When Only One Query Is Issued. (Doc ID 742843.1)

Last updated on MARCH 10, 2009

Applies to:

Oracle Server Enterprise Edition - Version: 9.2.0.1 to 11.1.0.8
This problem can occur on any platform.
Oracle Server - Enterprise Edition - Version: 10.2.0.3

Symptoms

A parallel query executed against a table audited via DBMS_FGA will generate more entries in the FGA audit trail table. The number of generated rows will be equal to the number of PQ slaves spawned by the query coordinator. This behaviour can be seen when running a query against a partitioned table.

create table test
(id number,
name varchar2(20))
partition by range(id)
(partition p1 values less than (100),
partition p2 values less than (200),
partition px values less than (maxvalue))
parallel 3;

begin
for i in 1..400 loop
insert into test values(i,i);
commit;
end loop;
end;
/

begin
  dbms_fga.add_policy  (
        object_schema => 'TEST',
        object_name => 'TEST',
        policy_name => 'TEST_FGA',
        statement_types => 'SELECT'
        );
end;

SELECT * FROM TEST;

select session_id, ext_name, sql_text, statementid, entryid from dba_fga_audit_trail;

SESSION_ID  EXT_NAME  SQL_TEXT             STATEMENTID   ENTRYID
---------   --------  ------------------  ------------   --------
400331      oracle    SELECT * FROM TEST             7          2
400331                SELECT * FROM TEST                        1
400331                SELECT * FROM TEST                        1
400331                SELECT * FROM TEST                        1

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms