It is better to start with minor note then to wait for long without any. Thus my first post in this blog would be about very specific problem.
I have spent some time investigating it before get to the bottom of this miscommunication between SQL statement improving and Fine-Grain Access Control (FGAC). Thus I believe it can be interesting for audience too. Below is the test case that allows to easily reproduce the issue.
Create partitioned table with non-standard partition names.
CREATE TABLE TESTONFGAC ( ID NUMBER(5,0) NOT NULL ENABLE, PartitionKey NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE, COMENT VARCHAR2(255) ) TABLESPACE "USERS" PARTITION BY RANGE (PartitionKey) (PARTITION "1" VALUES LESS THAN (1) SEGMENT CREATION IMMEDIATE TABLESPACE "USERS" , PARTITION "2" VALUES LESS THAN (2) SEGMENT CREATION IMMEDIATE TABLESPACE "USERS" , PARTITION "3" VALUES LESS THAN (3) SEGMENT CREATION IMMEDIATE TABLESPACE "USERS" ) /
Create FGAC policy function. For our case it does not matter how complicate would it be, thus create just dummy one.
CREATE OR REPLACE FUNCTION TEST_FGA (object_schema IN VARCHAR2, object_name VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN '1=1'; END; /
Create the policy on the newly created table using the function
exec DBMS_RLS.ADD_POLICY ( 'ETERPANI', 'TESTONFGAC', 'test_privacy', 'ETERPANI', 'TEST_FGA');
Everything before was just preparations. Now we are running the relatively new (since Oracle 10g) syntaxes.
DELETE FROM ETERPANI.TESTONFGAC PARTITION ("3") WHERE ID=3;
This reasonable simple statement fails with error “ORA-28113: policy predicate has error”. The further diagnostic can be found on oracle trace file.
*** 2011-06-25 01:23:27.188 ------------------------------------------------------------- Error information for ORA-28113: Logon user : ETERPANI Table/View : ETERPANI.TESTONFGAC Policy name : TEST_PRIVACY Policy function: ETERPANI.TEST_FGA RLS view : SELECT "ID","PARTITIONKEY","COMENT" FROM "ETERPANI"."TESTONFGAC" PARTITION (3) "TESTONFGAC" WHERE (1=1) ORA-14108: illegal partition-extended table name syntax -------------------------------------------------------------
As we see double quotes around partition name magically disappears.
In case of STANDARD partition naming (e.g. FIRST , SECOND e.t.c) it does not matter but if you choose lowercase naming or names starting from numbers you can get the ORA-28113.
Fancy automatic database schema generation tools frequently create objects in lower-case in this case it can be fatal.
The expected conclusion “little wit in the head makes much work for the feet” – keep your solution standard and simple (if you can).