Ora-28113 With A VPD Policy On Partitioned Tables (Doc ID 1353800.1)

Last updated on NOVEMBER 04, 2015

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

The queries executed against a partitioned table which has special characters in the names of the partitions are failing after defining a RLS policy:



create user testrlspart identified by testrlspart;
grant connect, resource to testrlspart;


CREATE TABLE testrlspart.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) TABLESPACE "USERS" ,
PARTITION "2" VALUES LESS THAN (2) TABLESPACE "USERS" ,
PARTITION "3" VALUES LESS THAN (3) TABLESPACE "USERS" )
/


CREATE OR REPLACE FUNCTION TEST_FGA (object_schema IN VARCHAR2, object_name
VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN '1=1';
END;
/


exec DBMS_RLS.ADD_POLICY ( 'testrlspart', 'TESTONFGAC', 'test_privacy',
'testrlspart', 'TEST_FGA');

alter system set events '10730 trace name context forever, level 3';


conn testrlspart/testrlspart

DELETE FROM testrlspart.TESTONFGAC PARTITION ("3") WHERE ID=3;
ERROR at line 1:
ORA-28113: policy predicate has error


The trace obtained after setting event 10730 shows that the internal RLS view is "losing" the double quotes around the partition name:


RLS view :
SELECT "ID","PARTITIONKEY","COMENT" FROM "TESTRLSPART"."TESTONFGAC"
PARTITION (3)
"TESTONFGAC" WHERE (1=1)
ORA-14108: illegal partition-extended table name syntax



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms