I put together a quick testcase to show myself how to use DBMS_RLS on 11.2.0.4 to mask a column. If you would like to run it or see the output it is here: test case zip.
I ran the test case as SYSTEM with a tnsnames.ora entry named ORCL1124.
I based the test on the Oracle 11.2.0.4 Security manual section titled: “Using Column Masking to Display Sensitive Columns as NULL Values”
The most interesting thing was that I did a 10053 trace like I did on an earlier post: earlier VPD post with trace.
Tracing the column masking I found that Oracle sticks a CASE statement into the select clause:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TABLE_LIST"."OWNER" "OWNER","TABLE_LIST"."TABLE_NAME" "TABLE_NAME",CASE WHEN USER@!<>'CANNOTSEE' THEN "TABLE_LIST"."BLOCKS" ELSE NULL END "BLOCKS" FROM "TEST"."TABLE_LIST" "TABLE_LIST"
The predicate that I setup just checked for
user<>'CANNOTSEE'So, Oracle’s 11.2.0.4 DBMS_RLS package for VPD features just adds a CASE statement that returns a NULL for the column when the predicate is true and returns the real column otherwise.
Not earth shattering but I did not want to forget this.
Bobby
