I got ” ORA-28112: failed to execute policy function ” error in Oracle database.
ORA-28112: failed to execute policy function
Details of error are as follows.
ORA-28112: failed to execute policy function Cause: The policy function has one or more error during execution. Action: Check the trace file and correct the errors.
failed to execute policy function
This ORA-28112 error is related with the policy function has one or more error during execution.
After enabling the FGA policy , commands and failing with following errors.
SQL> select count(*) from Test; * Error at line 1 ORA-28112: failed to execute policy function
10046 trace is showing the following errors:
*** 2016-12-22 15:53:11.435 WAIT #140593380206992: nam='SQL*Net message from client' ela= 15724506 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=280234746918 CLOSE #140593380206992:c=0,e=11,dep=0,type=1,tim=280234747010 FGA: Query parsing returned with error# 936 FGA: Current Query is SELECT "QUEUE_ENTRY_ID","QUEUE_NAME","QUEUE_ENTRY","SDS_INSTANCE_ID","DATE_ENTERED","REDELIVER_DATE","REDELIVER_COUNT" FROM "SRTEST"."NC_JMSQUEUE" "NC_JMSQUEUE" WHERE SYS_AUDIT((CASE WHEN(INSERT,UPDATE,DELETE) THEN 1 ELSE NULL END), 'SRTEST','NC_JMSQUEUE', 'COGNOS_POLICY2',3 ) IS NULL FGA: Actual Query is SELECT "QUEUE_ENTRY_ID","QUEUE_NAME","QUEUE_ENTRY","SDS_INSTANCE_ID","DATE_ENTERED","REDELIVER_DATE","REDELIVER_COUNT" FROM "SRTEST"."NC_JMSQUEUE" "NC_JMSQUEUE" WHERE SYS_AUDIT((CASE WHEN(INSERT,UPDATE,DELETE) THEN 1 ELSE NULL END), 'SRTEST','NC_JMSQUEUE', 'COGNOS_POLICY2',3 ) IS NULL ===================== PARSE ERROR #140593380441064:len=33 dep=0 uid=89 oct=3 lid=89 tim=280234747270 err=28112 select count(*) from NC_JMSQUEUE WAIT #140593380441064: nam='SQL*Net break/reset to client' ela= 4 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=280234747340 WAIT #140593380441064: nam='SQL*Net break/reset to client' ela= 132 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=280234747478 WAIT #140593380441064: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=280234747503
DBMS_FGA_POLICY had the wrong data for the audit_condition
audit_condition should have the VALID condition to check , we can’t pass the DML’s in the audit_condition.
We can pass the DML’s only in the statement types NOT in the audit_condition.
Wrong Policy ========== begin DBMS_FGA.ADD_POLICY ( object_schema => 'HR', object_name => 'Test', policy_name => 'Test_Policy', audit_condition => 'INSERT,UPDATE,DELETE', <<<<<<<<<<<<<<<<<<<<<<<<<< This is NOT correct. audit_column => NULL); end; Correct Policy =========== SQL> begin DBMS_FGA.ADD_POLICY ( object_schema => 'HR', object_name => 'Test', policy_name => 'Test_Policy', statement_types => 'INSERT,UPDATE,DELETE', <<<<<<<<<<<<<<<<<<<< It is STATMENT_TYPE audit_column => NULL); end;
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )