Foreign Key Constraint Check While Inserting Fires The Select VPD Policy On the Parent Table When OLS is installed

(Doc ID 467464.1)

Last updated on OCTOBER 25, 2017

Applies to:

Oracle Server - Enterprise Edition - Version 9.2.0.1 to 10.2.0.4 [Release 9.2 to 10.2]
Information in this document applies to any platform.

Symptoms

On systems where OLS is installed the constraint check fires the SELECT VPD policy defined on the parent table, this may result in unexpected error ORA-28117 "integrity constraint violated - parent record not found" .

 Create the policy function and the tables :

CREATE OR REPLACE PACKAGE VPD_TEST.pSecPackage
IS
  PROCEDURE mSetUserID( pinUserID IN PLS_INTEGER );
  FUNCTION mGetUserID RETURN PLS_INTEGER;

END;
/

CREATE OR REPLACE PACKAGE BODY VPD_TEST.pSecPackage
IS

PROCEDURE mSetUserID(
pinUserID IN PLS_INTEGER
) IS
BEGIN
DBMS_SESSION.SET_CONTEXT(
'VPD'
, 'NOWNERID'
, pinUserID
);
END;

FUNCTION mGetUserID RETURN PLS_INTEGER IS
BEGIN
RETURN SYS_CONTEXT('VPD', 'NOWNERID');
END;
END;
/



CREATE OR REPLACE PACKAGE VPD_TEST.pPolicyPackage
IS
FUNCTION mModifyPolicy( p_schema IN VARCHAR2 DEFAULT NULL,                                    p_object IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2;

FUNCTION mSelectPolicy( p_schema IN VARCHAR2 DEFAULT NULL, 
                        p_object IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY VPD_TEST.pPolicyPackage
IS
FUNCTION mModifyPolicy( p_schema IN VARCHAR2 DEFAULT NULL,
                        p_object IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2 IS
BEGIN
IF pSecPackage.mGetUserID != 1 THEN
RETURN '1=0';
ELSE
RETURN 'nOwnerID=SYS_CONTEXT(''VPD'', ''NOWNERID'',5)';
END IF;
END;

FUNCTION mSelectPolicy( p_schema IN VARCHAR2 DEFAULT NULL,
                        p_object IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2 IS

BEGIN
IF pSecPackage.mGetUserID = 1 THEN
RETURN 'nOwnerID=SYS_CONTEXT(''VPD'', ''NOWNERID'',5)';
ELSE
RETURN '1=0';
END IF;
END;
END;
/



CREATE TABLE VPD_TEST.T1( nPKID NUMBER(5,0), nOwnerID NUMBER(5,0) DEFAULT SYS_CONTEXT('VPD', 'NOWNERID',5));
ALTER TABLE VPD_TEST.T1 ADD CONSTRAINT cT1PK PRIMARY KEY (nPKID);



CREATE TABLE VPD_TEST.T2(nPKID NUMBER(5,0), nFKID NUMBER(5,0), nOwnerID NUMBER(5,0) DEFAULT SYS_CONTEXT('VPD', 'NOWNERID',5));
ALTER TABLE VPD_TEST.T2 ADD CONSTRAINT cT2PK PRIMARY KEY (nPKID);
ALTER TABLE VPD_TEST.T2 ADD CONSTRAINT cT2FK FOREIGN KEY (nFKID) REFERENCES VPD_TEST.T1(nPKID);



BEGIN

/*
T1 table policies
*/

DBMS_RLS.add_policy(
object_schema => 'VPD_TEST'
, object_name => 'T1'
, policy_name => 'MODPOLICY'
, policy_function => 'PPOLICYPACKAGE.MMODIFYPOLICY'
, function_schema => 'VPD_TEST'
, statement_types => 'INSERT,UPDATE,DELETE'
, update_check => TRUE
, policy_type => 4 -- SHARED_CONTEXT_SENSITIVE
);

DBMS_RLS.add_policy(
object_schema => 'VPD_TEST'
, object_name => 'T1'
, policy_name => 'SELPOLICY'
, policy_function => 'PPOLICYPACKAGE.MSELECTPOLICY'
, function_schema => 'VPD_TEST'
, statement_types => 'SELECT'
, policy_type => 4 -- SHARED_CONTEXT_SENSITIVE
);

/*
T2 table policies
*/

DBMS_RLS.add_policy(
object_schema => 'VPD_TEST'
, object_name => 'T2'
, policy_name => 'MODPOLICY'
, policy_function => 'PPOLICYPACKAGE.MMODIFYPOLICY'
, function_schema => 'VPD_TEST'
, statement_types => 'INSERT,UPDATE,DELETE'
, update_check => TRUE
, policy_type => 4 -- SHARED_CONTEXT_SENSITIVE
);

DBMS_RLS.add_policy(
object_schema => 'VPD_TEST'
, object_name => 'T2'
, policy_name => 'SELPOLICY'
, policy_function => 'PPOLICYPACKAGE.MSELECTPOLICY'
, function_schema => 'VPD_TEST'
, statement_types => 'SELECT'
, policy_type => 4 -- SHARED_CONTEXT_SENSITIVE
);

END;
/


 

Insert data into the tables. Before insert, we set the user ID to the right context so that the policy function will fire as expected:

 

BEGIN
pSecPackage.mSetUserID(1);
insert into t1(npkid) values (1000);
insert into t1_bis(npkid) values (1000);
insert into t2(npkid, nfkid) values (1000, 1000);
END;
/

 

Check the cached policies:

SELECT policy, policy_function_owner as policy_schema, sql_hash, predicate FROM v$VPD_POLICY WHERE OBJECT_OWNER = 'VPD_TEST'
/


POLICY      POLICY_SCHEMA     PREDICATE
-------     --------------    ----------------------------
SELPOLICY   VPD_TEST          1=1 AND "NFKID" ...
MODPOLICY   VPD_TEST          nOwnerID=SYS_CONTEXT..
MODPOLICY   VPD_TEST          nOwnerID=SYS_CONTEXT..

 

After searching within v$SQLAREA with SQL_ID, following SQLs were found: 
 
b6hgzwfghbprf = INSERT INTO T1(NPKID) VALUES (1000) 
cmfwhx1r07g8r = INSERT INTO T2(NPKID, NFKID) VALUES (2000, 1000) 
 
There is no select so it seems that constraint check really fires both select policy (SELPOLICY) and modifying policy (MODPOLICY). 


We will demonstrate the effect without constraint :

 

ALTER TABLE T2 DISABLE CONSTRAINT cT2FK;

BEGIN

insert into t1(npkid) values (1001);
insert into t2(npkid, nfkid) values (2002, 1001);

END;
/

 

 Check the cached policies once again :

 

SELECT policy, policy_function_owner as policy_schema, sql_id, predicate FROM v$VPD_POLICY WHERE OBJECT_OWNER = 'VPD_TEST'



POLICY    POLICY_SCHEMA   SQL_ID     PREDICATE
------    --------------  ------     ------------------------------------
MODPOLICY VPD_TEST    02b8139tjanr9 nOwnerID=SYS_CONTEXT('VPD', 'NOWNERID',5) 
MODPOLICY VPD_TEST    9k2u3b033rb0z nOwnerID=SYS_CONTEXT('VPD', 'NOWNERID',5)


After searching within v$SQLAREA with SQL_ID, following SQLs were found: 

02b8139tjanr9 = INSERT INTO T1(NPKID) VALUES (1001) 
9k2u3b033rb0z = INSERT INTO T2(NPKID, NFKID) VALUES (2002, 1001) 

 

Changes

 Differences in behaviouir are typically seen between systems where VPD policies exist on table with FK constraints and only some have OLS installed.

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