Dependent Objects Gets Invalidated When Policy Is Added Or Dropped (Doc ID 1090749.1)

Last updated on OCTOBER 27, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

Symptoms

If applying the VPD policy on a synonym (or deleting it, possibly other operations trigger the same problem), the plsql depending on the same synonyms on the base table is invalidated. 
This does not occur when the VPD policy is applied directly on the base table. 
The problem tends to become even more complex when the dependency chain is deep. Otherwise, it is benign and hardly noticeable.
This can be experimented as below:

 

conn / as sysdba
drop user test cascade;
create user test identified by test;
grant dba to test;
create table test.test_table ( emp_name varchar2(240), org_id number);
--inserted some records in this table
insert into test.test_table values ('testlash', 381);
insert into test.test_table values ('alok', 382);
insert into test.test_table values ('arun', 383);
insert into test.test_table values ('anandam', 384);
insert into test.test_table values ('prasad', 385);
commit;
--create table synonym
create synonym test.test_syn FOR test.test_table;
--create a depending object on the depending synonym.
CREATE OR REPLACE PACKAGE test.test_pol_pkg
AS
   FUNCTION test_fn RETURN varchar2;
END test_pol_pkg;
/
CREATE OR REPLACE PACKAGE BODY test.test_pol_pkg
 AS
   FUNCTION  test_fn
    RETURN varchar2
    IS
            l_name varchar2(1000):='TEST';
    BEGIN
         select emp_name into l_name from test_syn where rownum<2;
         return l_name;
    END;
END test_pol_pkg;
/
--add the VPD policy
BEGIN
   DBMS_RLS.ADD_POLICY(
           object_schema => 'test',
           object_name => 'test_syn',
           policy_name => 'test_table_policy',
           function_schema => 'test',
           policy_function => 'test_table_POLICY.test_predicate',
           statement_types => 'select, insert, update, delete',
           update_check => FALSE,
           enable => TRUE,
           static_policy => FALSE);
END;
/
--check the depending package - it is invalid!
select owner, object_type, object_name, status from dba_objects where
object_name = 'TEST_POL_PKG';
OWNER                          OBJECT_TYPE         OBJECT_NAME               STATUS
------------------------------ ------------------------------------------------- -------
TEST                           PACKAGE             TEST_POL_PKG              VALID
TEST                           PACKAGE BODY        TEST_POL_PKG              INVALID
--Run the invalid package:
select test.test_pol_pkg.test_fn from dual;
--The package is validated automatically - no down time!
select owner, object_type, object_name, status from dba_objects where
object_name = 'TEST_POL_PKG';
OWNER                          OBJECT_TYPE         OBJECT_NAME               STATUS
------------------------------ ------------------------------------------------- -------
TEST                           PACKAGE             TEST_POL_PKG              VALID
TEST                           PACKAGE BODY        TEST_POL_PKG              VALID
--drop the policy:
BEGIN
   DBMS_RLS.DROP_POLICY(
           object_schema => 'test',
           object_name => 'test_syn',
           policy_name => 'test_table_policy'
         );
END;
/
--recheck the package - it's again invalid
select owner, object_type, object_name, status from dba_objects where
object_name = 'TEST_POL_PKG';
  1* select owner, object_type, object_name, status from dba_objects where
object_name = 'TEST_POL_PKG'
OWNER                          OBJECT_TYPE         OBJECT_NAME               STATUS
------------------------------ ------------------------------------------------- -------
TEST                           PACKAGE             TEST_POL_PKG              VALID
TEST                           PACKAGE BODY        TEST_POL_PKG              INVALID

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