FGAC and new partition features

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).

About these ads

2 thoughts on “FGAC and new partition features

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s