Data Redaction in Oracle Database 12c flaws or security gaps?

I’ve been working on proof of concept project for Data Redaction in Oracle Database 12c. Hard to say but POC has proven that data redaction has couple of flaws or according to Oracle “constraints”. Therefore before we could continue with implementation we would have to find solutions to below findings. Any feedback from the readers would be much appreciated.

Test scenario: Database user schema is “APEX_ZION”. A table “DEMO_CUSTOMERS” has data redaction enabled on CUST_POSTAL_CODE column, masking data using randomly generated characters. Here is how to enable data redaction. Another user schema “TEST_USER” has granted SELECT privileges on DEMO_CUSTOMERS table. The goal is to mask sensitive data for test_user only. So when we login to a database as “APEX_ZION” and we run an SQL query we can see true data:

SQL> SELECT CUST_POSTAL_CODE FROM APEX_ZION.DEMO_CUSTOMERS;

CUST_POSTAL_CODE
----------------
20166     
30320     
02128     
60666     
11371     
63145     
06096     

 7 rows selected

Next we run the same SQL query as “test_user” and we can see masked data only:

SQL> SELECT CUST_POSTAL_CODE FROM APEX_ZION.DEMO_CUSTOMERS;

CUST_POSTAL_CODE
----------------
]2x#( 
_UJX/ 
\Bzy# 
z:*Qr 
L`!<I 
oBE&5 
N"2G] 

 7 rows selected 

So far so good. Here comes the funny part…

Using Oracle SQL Developer we are logged as “test_user” (1), We run SQL query (2) and as a result we can see masked data. Next, in the same session navigate to Other Users (3) -> APEX_ZION (4) -> Tables (5) -> DEMO_CUSTOMERS (6) and click on Data tab (7) to view table’s content. Surprise, the data is not being redacted anymore.

In addition please read David Litchfield’s white paper “Oracle Data Redaction is Broken” here (PDF). I checked all three methods in my labs in September 2016 and in result we see that using “RETURNING INTO” and “XMLQUERY()” methods appear to be fixed. However one gap still persist in a Database 12c version 12.1.0.2 – “an iterative inference attack”. It is possible to be executed disclosing redacted data even to a regular test_user, with “create procedure” and “select” privileges on a target table! According to Oracle documentation it is a constraint in data redaction.

Below test result based on example 3 from David Litchfield’s white paper:

select cc from APEX_ZION.REDACTIONTEST;
CC 
-------
XXXXXXXXXXXXXXXX

--an iterative inference attack
exec p_undoredaction;
PL/SQL procedure successfully completed.
CC: 4111222233334444

All in all Oracle Data Redaction seems to be very handy feature next to well known Oracle Data Masking option. However facing above flaws it is a bit pointless to implement it in any of the environments. And the sad part of it is that David Litchfield has reported his findings to Oracle in 2013 and Oracle did not “fix” it yet (2016). I’m not sure whether bug from SQL Developer has been already reported to Oracle, this is something that I’ve discovered today. Please leave your comments if you know more similar flaws or how to tackle existing ones.

EDIT: Even though Oracle Data Redaction is not designed to prevent data exposure to database users who run ad hoc queries directly against the database, it can provide an additional layer to reduce the chances of accidental data exposure. Because such users may have rights to change data, alter the database schema, and circumvent the SQL query interface entirely, it is possible for a malicious user to bypass Data Redaction policies in certain circumstances. Source: Oracle Documentation

More can be read here Security Considerations for Using Oracle Data Redaction:

Oracle Data Redaction is not intended to protect against users who run ad hoc SQL queries that attempt to determine the actual values by inference.

Now I’m asking myself, what’s the point of using data redaction whatsoever? It’s like asking my boy not to watch certain TV channels securing them by default security pin 0000. Do you know any use cases of using data redaction? Please leave your comments down below.

Cheers!!