Hacking Oracle Data Redaction
Last month Oracle ACE Director Oded Raz published an article about Data Redaction. This month, Oded will explain some of the vulnerabilities of data redaction and how to “hack it”. I would like to thank Oded for his contribution and invite him to publish more things in the future.
On my last article, I have introduced you the new oracle security feature – Data Redaction, selective, on-the-fly redaction of sensitive data in SQL query results prior to display by applications so that unauthorized users cannot view the sensitive data. Although I welcome this feature and think it’s a much-needed addition to Oracle database security features it has some limitations that you need to be aware of before using it to protect sensitive data.
Adding the data reduction
To make my point let’s think about a simple scenario where we want to let some analysts access the employees table but we don’t want them to see the employees’ salaries.
First, we define the proper redaction policy:
BEGIN DBMS_REDACT.ADD_POLICY( object_schema => 'HR', object_name => 'EMPLOYEES', column_name => 'SALARY', column_description => 'emp_sal_comm shows employee salary and commission', policy_name => 'redact_emp_sal_comm', policy_description => 'Partially redacts the emp_sal_comm column', function_type => DBMS_REDACT.PARTIAL, function_parameters => '9,1,4', expression => '1=1'); END; /
Now lets see if it works:
As you can see if salary column is being access by any user, including HR original salary data is redacted and replaced with 9999. One of the analysts need to query the database directly using SQLPLUS, the DBA grant him access because the SALARY column is redacted,
Our analyst is very resourceful and curious and decided he must see employees’ salaries, and he try to run the following anonymous block against employees table:
DECLARE sal_txt varchar2(1024); BEGIN FOR LCounter IN 1..99999 LOOP BEGIN SELECT FIRST_NAME||' '||LAST_NAME||' salary is '||LCounter into sal_txt FROM HR.EMPLOYEES WHERE SALARY=LCounter and rownum<2; DBMS_OUTPUT.PUT_LINE(sal_txt) ; EXCEPTION WHEN NO_DATA_FOUND THEN CONTINUE; END; END LOOP; END; /
If you take a closer look at this code snippet you will that out analyst is not retrieving the salary at all for each loop iteration he is retrieving first name and last name of the employees with salary equals to the current iteration resulting with the following output:
As you can see although REDACT user (representing our analyst) can see actual salaries when queried directly, when using Oracle data redaction, The original data is not changed and when using a redacted column in a “where clause” the original data is being evaluated and not the redacted one. As you can see Oracle data redaction has its limitations, and we should know when and how to use it, in our little example the problem was that we have granted a direct access to the database to the analyst allowing him to “override” redaction.
Don’t get me wrong, Oracle data redaction is a great feature for preventing access to sanative data on production environments but we must understand its limitations and make sure we use it correctly.
So up until next time, be smart be careful and don’t be afraid to use Oracle database security features.
Oracle ACE Director
Excellent feature . Thanks for sharing with us. May i know this feature is available from which version of oracle.
Oracle 10g gave us to ability to perform column masking to prevent sensitive data from being displayed by applications but it was very limited with its uses. In Oracle 12c (and it was also back-ported to 220.127.116.11), the Oracle data redaction feature was introduced which allows a greater level of control and protection over sensitive data using the DBMS_REDACT package.
I like the way you call it: Limitations…. I call it serious security vulnerabilities and flaws which Oracle is familiar with and doesn’t fix it since 2013! Cheers!
Hi Oded Raz,
Thanks for the information.
Is there any where to mitigate this risk i.e. block/restrict the usage of redacted column in WHERE, RETURNING INTO clause.
We want to use the data redaction but mitigating the risk of it’s limitations.