Virtual Private Database (Oracle VPD) was introduced by Oracle in release 8i. It’s a security feature that provides access restriction at row/column level for privacy and regulatory compliance. It allows sophisticated logic to be applied through the use of custom functions in which rules can be written for fine-grained data access. If you want to know more about how it works check the official documentation.
The point of this post is that the column-level security of Oracle VPD can be easily tricked! It all started when I decided to check the new Redaction feature released with Oracle 12c. This is somehow similar to VPD but it works at a different level. I came across a very interesting article from David Litchfield that in his article demonstrates how this feature is broken and not safe at all. Well, starting from there I made my own similar investigations on VPD and got some really surprising results.
Let’s see what I’ve discovered by connecting to the database with SCOTT and creating our usual test table

The table has a “customer id” column, a “security” flag that we’ll use to identify secret data and a “credit card number” column. I’ve also inserted 5 rows just for the purpose of this demo.
Now I want the “cc_number” values to be invisible to the user SCOTT whenever the flag “secret” is equal to Y. To do that we can use Oracle VPD and first need to create a function containing the logic for hiding that column

The code is very simple: if the user is SCOTT the condition secret = ‘N’ is added to the query hiding the sensitive data. All other users will see all without any filter.
In order to make this rule active we need to create a “policy” that calls the function we just compiled

This policy is defined at column level so it’s intended to hide values from the column CC_NUMBER leaving all remaining ones visible.
If we connect to the database with another user, let’s say MARK in my test, and query the table we are able to see all data

Now let’s go back and connect with SCOTT. When running the same query we get a different result because of the VPD policy

As you can see, for all rows that have secret equal to Y, the column CC_NUMBER is not visible. It seems solid, doesn’t it? I’m afraid it’s not.
Do you remember that way of returning column values in a delete or update DML by using the RETURNING INTO clause?
Well, look at this

We just printed the value we should not have access to! By simply updating a row whose CC number we should not see and by using the RETURNING INTO clause we managed to easily (and I underline it, EASILY) access the hidden data. I’ve tested this on releases, and with same exact results.
It seems Oracle forgot that data can be retrieved this way. I don’t have much more to add, just don’t rely on Oracle VPD column-level security if you really want to keep your sensitive data safe.
At least not until Oracle releases a patch for this quite big flaw.

Share this post on Share on Facebook
Tweet about this on Twitter
Share on LinkedIn
Email this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *