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 126.96.36.199 – “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.
The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.