You Asked
i have to create a oracle FGA policy having multiple values for a particular column eg. temp table and it has stat column, data is ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o') and i want exclude stat = 'a' or 'b' from auditing the policy gets created but when i update it gives "ora-28138 error in policy predicate"
begin
DBMS_FGA.ADD_POLICY(
object_schema => 'scott',
object_name => 'temp',
policy_name => 'salary_change',
audit_column => 'stat',
audit_condition => 'stat<>''a'' or stat <>''b''',
enable => TRUE,
statement_types => 'UPDATE',
audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED );
end;
cannot update below as error "ora-28138 error in policy predicate"
update temp set sal = 5500 where stat = 'a';
update temp set sal = 5500 where stat = 'b';
update temp set sal = 5500 where stat = 'c';
please guide thanks.
and Connor said...
From the documentation:
http://docs.oracle.com/database/121/ARPLS/d_fga.htm#ARPLS015 ------------------------------------
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:
Subqueries or sequences
The following attributes of the USERENV namespace when accessed using the SYS_CONTEXT function:
CURRENT_SQL
CURRENT_SQL_LENGTH
CURRENT_BIND
Any use of the pseudo columns LEVEL, PRIOR, or ROWNUM.
Specifying an audit condition of "1=1" to force auditing of all specified statements ("statement_types") affecting the specified column ("audit_column") is no longer needed to achieve this purpose. A NULL value for audit_condition causes audit to happen even if no rows are processed, so that all actions on a table with this policy are audited.
------------------------------------
So you need to come up with alternatives for your expression, eg
SQL> create table t as
2 select e.*,'a' stat
3 from scott.emp e
4 union all
5 select e.*,'b' stat
6 from scott.emp e
7 union all
8 select e.*,'c' stat
9 from scott.emp e
10 /
Table created.
SQL> grant all on t to scott;
Grant succeeded.
SQL> begin
2 DBMS_FGA.ADD_POLICY(
3 object_schema => user,
4 object_name => 'T',
5 policy_name => 'SALARY_CHANGE',
6 audit_column => 'STAT',
7 audit_condition => q'{regexp_count(STAT,'(a|b)')=0}',
8 enable => TRUE,
9 statement_types => 'UPDATE',
10 audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED );
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> conn scott/tiger
Connected.
SQL> update mcdonac.t set sal = 5500 where stat = 'a';
14 rows updated.