Problems Using FGA with Ansi Joins (Doc ID 726332.1)

Last updated on AUGUST 10, 2015

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

 

When using Ansi SQL, the Fine Grained Auditing is not working everytime as it is expected.
There are 2 known scenarios:
In the first scenario, when applying a FGA policy on a column which is referred by the ANSI join, the data does not reach the audit trail:
create user test identified by test

create table t1 (a number, b number, c char(1)); 
create table t2 (b number, d char(1));
    
begin
DBMS_FGA.ADD_POLICY(
object_schema => 'test',
object_name   => 't1',
policy_name   => 'chk_t1',
audit_column => 'c',
statement_types => 'select');
end;
/
    
select t1.c from t1 join t2 on (t1.b = t2.b);

select * from dba_fga_audit_trail; 
no rows selected  

A similar query, just that written with classic SQL, is audited:

select t1.c from t1,t2 where (t1.b = t2.b);

SQL>  select sql_text from dba_fga_audit_trail;
SQL_TEXT
------------------------------------------------------------------------------ 
select t1.c from t1,t2 where (t1.b = t2.b);

In the second scenario, unnecessary audit entries are produced, despite the fact that the audited column is not referred in the Ansi SQL. This situation has been noticed to appear so far when trying a workaround for the first scenarion, ie set: audit_condition=>'1=1' or when using a view in the Ansi SQL.
--as test:
create table t1 (a number, b char(1));
create table t2 (b char(1), c number);
  
begin
dbms_fga.add_policy (
object_schema=>'test',
object_name=>'t2',
policy_name=>'CHK_t2',
audit_column=>'c',
audit_condition=>'1=1',
statement_types=>'SELECT'
);
end;
/   

The classic SQL works as expected, ie the following query does not create an audit entry:
select t1.a from t1,t2 where (t1.b = t2.b);

SQL> select sql_text from dba_fga_audit_trail;
no rows selected

However, the similar ANSI SQL does create, erroneously, an entry in the audit trail:
select t1.a from t1 join t2 on (t1.b = t2.b);
SQL>  select sql_text from dba_fga_audit_trail; 

SQL_TEXT 
--------------------------------------------------------------------------
select t1.a from t1 join t2 on (t1.b = t2.b); 

The second variant, using the intermediate view, does not need audit_condition=>'1=1'. The behavior is similar, but the problems are quite different:
--as test:
create table t1 (a number, b char(1));
create table t2 (b char(1), c number);
create view t2v as select b  , c from t2;

begin
 dbms_fga.add_policy (
 object_schema=>'test',
 object_name=>'t2',
 policy_name=>'chk_t2',
 audit_column=>'c',
 statement_types=>'SELECT'
 );
end;

select t1.a from t1,t2 where t1.b=t2.b;
SQL>  select sql_text from dba_fga_audit_trail;   
no rows selected

select t1.a from t1 join t2v on (t1.b = t2v.b);
SQL>  select sql_text from dba_fga_audit_trail;  

SQL_TEXT  
---------------------------------------------------------------------------
select t1.a from t1 join t2v on (t1.b = t2v.b); 




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