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:

  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');

Now lets see if it works:
hacking 1

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:

 sal_txt varchar2(1024);
 FOR LCounter IN 1..99999 LOOP
   SELECT FIRST_NAME||' '||LAST_NAME||' salary is '||LCounter into sal_txt
   WHERE SALARY=LCounter and rownum<2; 

   DBMS_OUTPUT.PUT_LINE(sal_txt) ;

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:

employee salary bypassing redaction

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.

Oded Raz
Oracle ACE Director

4 replies
  1. Ananth
    Ananth says:

    Excellent feature . Thanks for sharing with us. May i know this feature is available from which version of oracle.

    • Zohar Elkayam
      Zohar Elkayam says:

      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, the Oracle data redaction feature was introduced which allows a greater level of control and protection over sensitive data using the DBMS_REDACT package.

  2. eMarcel
    eMarcel says:

    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!

  3. Onkar
    Onkar says:

    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.


Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.