I want to understand the restrictions that apply to VPD functions when used for column masking, compared with their use for Row-Level Security.
According to the Oracle Database Security Guide (11g Release 1)
Column-masking conditions generated by the policy function must be simple Boolean expressions, unlike regular Oracle Virtual Private Database predicates.
I have long understood the above as implying that column-masking conditions should not contain sub-queries (i.e. inner selects).
However, we tested using a condition with a select inside another select (2-level nesting) and yet it worked. We were on 11g Release 2, by the way.
So, I wonder, does anyone have experience with using sub-queries in column-masking conditions? Or, alternatively, does anyone have more information on what Oracle means with "regular VPD predicates" and "simple Boolean expressions" (of course, in the context of VPD)?
SEC_RELEVANT_COLS_OPT = DBMS_RLS.ALL_ROWS is used with SEC_RELEVANT_COLS to display all rows but with the sensitive columns appearing as NULL. This feat is accomplished internally by rewriting the SQL in a manner that replaces the column with a CASE statement within which the predicate must be evaluated:
CASE WHEN ( <predicate> ) THEN "<COLLUMN>" ELSE NULL
The reason for the comment you found is that your <predicate> must be palpable inside the above construct,
Harm ten Napel