ORA-42804 In 12C DB for INSERT...RETURNING Command With VPD Column Masking (Doc ID 2374405.1)

Last updated on OCTOBER 10, 2022

Applies to:

Symptoms

ORA-42804 error occurs in 12.1.0.2 DB for INSERT...RETURNING command with VPD Column-masking policy defined on that table ( VPD policy with
'sec_relevant_cols_opt => dbms_rls.all_rows' and SELECT statement_type).

But the same VPD policy and INSERT...RETURNING command works in 11.2.0.4 DB

Test case:
==========
sqlplus / as sysdba
CREATE USER USER1  IDENTIFIED BY **** DEFAULT TABLESPACE USERS;
grant connect, resource, unlimited tablespace to USER1;
ALTER USER USER1 QUOTA UNLIMITED ON USERS;

sqlplus USER1
create table VPD_TEST_TABLE2
(COL1 NUMBER, COL2 VARCHAR2(4000));

CREATE OR REPLACE PACKAGE USER1.PKG1 AUTHID DEFINER
IS
FUNCTION IsVisible(
i_object_schema in varchar2
,i_object_name in varchar2
)
RETURN varchar2;
END PKG1;
/

CREATE OR REPLACE PACKAGE BODY USER1.PKG1
AS
FUNCTION IsVisible(
i_object_schema in varchar2
,i_object_name in varchar2
)
RETURN varchar2
IS
BEGIN
IF dbms_session.is_role_enabled('ROLE1')
THEN
RETURN '1=1';
ELSE
RETURN '1=2';
END IF;
END IsVisible;
END;
/

sqlplus / as sysdba
BEGIN
SYS.DBMS_RLS.ADD_POLICY (
object_schema => 'USER1'
,object_name => 'VPD_TEST_TABLE2'
,policy_name => 'VPD_TEST_TABLE2'
,function_schema => 'USER1'
,policy_function => 'PKG1.ISVISIBLE'
,statement_types => 'SELECT'
,policy_type => dbms_rls.dynamic
,long_predicate => FALSE
,sec_relevant_cols => 'COL2'
,sec_relevant_cols_opt => dbms_rls.all_rows
,update_check => FALSE
,static_policy => FALSE
,enable => TRUE );
END;
/

sqlplus USER1
declare  l_results VARCHAR2(4000);
lv_col2 VARCHAR2(4000);
lv_col1 NUMBER;
begin
l_results := RPAD('',4000,'X');
insert into VPD_TEST_TABLE2
(COL1,COL2)
values
(1, l_results)
RETURNING COL2, COL1 INTO lv_col2, lv_col1;
END;
/
declare
*
ERROR at line 1:
ORA-42804: The query could disclose rows protected by select VPD policy.
ORA-06512: at line 7

Changes

 Upgrade from 11.2.0.4 to 12.1.0.2 DB

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.