Column Masking Testcase

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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.