My Oracle Support Banner

Possible Causes of ORA-42804 When VPD Column Masking is Used (Doc ID 2680419.1)

Last updated on APRIL 17, 2023

Applies to:

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

Symptoms

When a column is specified in sec_relevant_cols of a VPD policy and that column is used in the "returning" clause of a procedure,ORA-42804 is raised.

This is an expected behavior and a new security feature starting from 12c.

There are cases when the error is seen even when the masked column is not in "returning" clause.Those issues have been discussed in this note.

For better understanding,a simple test case to reproduce this behavior can be seen in the following note:

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

 

 1. Oracle Database 19c - sec_relevant_cols is used anywhere in DML and even if there is no returning into clause in DML.

 2. Oracle Database 19c - There is a trigger on VPD protected table and that VPD policy has sec_relevant_cols

 3. Oracle Database 12c - In a logical standby DB,the SQL Apply stops due to ORA-42804 even when a DML without returning into clause is executed on primary.

 

Cause

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
Symptoms
  1. Oracle Database 19c - sec_relevant_cols is used anywhere in DML and even if there is no returning into clause in DML.
  2. Oracle Database 19c - There is a trigger on VPD protected table and that VPD policy has sec_relevant_cols
  3. Oracle Database 12c - In a logical standby DB,the SQL Apply stops due to ORA-42804 even when a DML without returning into clause is executed on primary.
Cause
  1. Oracle Database 19c - sec_relevant_cols is used anywhere in DML and even if there is no returning into clause in DML.
  2. Oracle Database 19c - There is a trigger on VPD protected table and that VPD policy has sec_relevant_cols
  3. Oracle Database 12c - In a logical standby DB,the SQL Apply stops due to ORA-42804 even when a DML without returning into clause is executed on primary.
Solution
 1. Oracle Database 19c - sec_relevant_cols is used anywhere in DML and even if there is no returning into clause in DML.
 2. Oracle Database 19c - There is a trigger on VPD protected table and that VPD policy has sec_relevant_cols
 3. Oracle Database 12c - In a logical standby DB,the SQL Apply stops due to ORA-42804 even when a DML without returning into clause is executed on primary.

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