My Oracle Support Banner

Problems Using FGA with Ansi Joins (Doc ID 726332.1)

Last updated on FEBRUARY 18, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.