Fine grained auditing (FGA) : Use AND,OR operators in the audit condition using funtions and avoid ORA-28138

The issue here is that we want to use the "and" operator  as the auditing condition to compare two values  of the columns with fine grained auditing or FGA. But If  I do something like the following.I get error ORA-28138.

begin
 DBMS_FGA.ADD_POLICY (
       object_schema => 'hr',
       object_name => 'fg_test',
       policy_name => 'test_users_fga_policy',
       audit_column => 'C1, C2', 
       statement_types => 'SELECT,INSERT,UPDATE,DELETE',
       audit_condition => 'C1=35 AND C2= 45',
       audit_column_opts => DBMS_FGA.ANY_COLUMNS,
       audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED );
end;
/

 

SQL> insert into fg_test values('35','45');

insert into fg_test values('35','45')

            *

ERROR at line 1:

ORA-28138: Error in Policy Predicate

As per the oracle documentation you cannot use a complex condition in the FGA policies. See the documentation:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_fga.htm#CDEIECAG

"The audit_condition must be a Boolean expression that can be evaluated using the values in the row being inserted, updated, or deleted. The expression can also use functions, such as the USER or SYS_CONTEXT functions.

The expression must not combine conditions using operators such as AND and OR. audit_condition can be NULL (or omitted), which is interpreted as TRUE, but it cannot contain the following elements:"

So to fix the problem we need to create a function that returns a Boolean value .See the following test case.

   Environment:OEL 5 64 bit,DB 11.2.0.1 
1) Create Table

SQL> create table hr.fg_test(c1 number,c2 number);

Table created.

2) Create function

create or replace function hr.cond_fg(col1 number ,col2 number ) return number is
begin
if (col1 =35 and col2=45)
then
return 1;
else
return 0;
end if;
end;
/


3) Add Policy
exec DBMS_FGA.drop_policy ( -
       object_schema => 'hr', -
       object_name => 'fg_test', -
       policy_name => 'test_users_fga_policy');

begin
 DBMS_FGA.ADD_POLICY (
       object_schema => 'hr',
       object_name => 'fg_test',
       policy_name => 'test_users_fga_policy',
       audit_column => 'C1, C2', 
       statement_types => 'SELECT,INSERT,UPDATE,DELETE',
       audit_condition => 'hr.cond_fg(C1,C2)=1',
       audit_column_opts => DBMS_FGA.ANY_COLUMNS,
       audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED );
end;
/


4)Enable Policy

 begin
   DBMS_FGA.ENABLE_POLICY (
   object_schema    =>  'HR',
   object_name      =>  'FG_TEST',
   policy_name      =>  'test_users_fga_policy',
   enable           =>   TRUE);
   end;
   /


SQL> select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, POLICY_TEXT,
POLICY_COLUMN, ENABLED, SEL, INS, UPD, DEL
from DBA_AUDIT_POLICIES ;  2    3

OBJECT_SCHEMA                  OBJECT_NAME
------------------------------ ------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN                  ENA SEL INS UPD DEL
------------------------------ --- --- --- --- ---
HR                             FG_TEST
TEST_USERS_FGA_POLICY
hr.cond_fg(C1,C2)=1
C1                             YES YES YES YES YES

5) Run the test

As HR User
===========================================
SQL>  insert into fg_test values('&a','&b');
Enter value for a: 35
Enter value for b: 45
old   1:  insert into fg_test values('&a','&b')
new   1:  insert into fg_test values('35','45')

1 row created.

SQL> commit;

Commit complete.


As SYS User
==========================================================================================
SQL> select db_user,sql_text from dba_fga_audit_trail;

DB_USER
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
HR
 insert into fg_test values('35','45')
 



It seems to be working but as per the guidelines the function should not be too complex to avoid the performance issues.