Ora-28113 With A VPD Policy On Partitioned Tables
(Doc ID 1353800.1)
Last updated on MARCH 22, 2019
Applies to:Oracle Database - Enterprise Edition - Version 188.8.131.52 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
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 user1 identified by xxxxx;
grant connect, resource to user1;
CREATE TABLE user1.TESTONFGAC
( ID NUMBER(5,0) NOT NULL ENABLE,
PartitionKey NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,
) 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
exec DBMS_RLS.ADD_POLICY ( 'user1', 'TESTONFGAC', 'test_privacy',
alter system set events '10730 trace name context forever, level 3';
DELETE FROM user1.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:
SELECT "ID","PARTITIONKEY","COMENT" FROM "user1"."TESTONFGAC"
"TESTONFGAC" WHERE (1=1)
ORA-14108: illegal partition-extended table name syntax
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document