Oracle 12c Data Redaction Flaw

There is a nice new feature in Oracle 12c that allows you to mask data during query parse time and does not need to have virtual private database, label security, or database vault configured. All it takes is a simple policy based on the named account as I will demonstrate using version 12.1.0.2.  However, there is a flaw in this feature that I will show at the end.

1)    I created test users – SUPERVISOR and WORKER
2)    I created a policy to restrict the contents of the SALARY column in the HR.EMPLOYEES table to anyone who is not the SUPERVISOR with this code:

set serveroutput on
BEGIN
DBMS_REDACT.ADD_POLICY(OBJECT_SCHEMA=>'HR',object_name=>'EMPLOYEES',policy_name=>'POLITICA_TESTE',expression=>'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SUPERVISOR''');
DBMS_REDACT.ALTER_POLICY(OBJECT_SCHEMA=>'HR',object_name=>'EMPLOYEES',policy_name=>'POLITICA_TESTE',action=>DBMS_REDACT.ADD_COLUMN,column_name=>'"SALARY"',function_type=>DBMS_REDACT.FULL);
END;
/

3)     I then queried the HR.EMPLOYEES table as SUPERVISOR

SQL> select employee_id, last_name, first_name, salary
from hr.employees
where salary between 10000 and 15000
order by last_name;
2    3    4
EMPLOYEE_ID LAST_NAME                 FIRST_NAME               SALARY
----------- ------------------------- -------------------- ----------
174 Abel                      Ellen                     11000
204 Baer                      Hermann                   10000
169 Bloom                     Harrison                  10000
148 Cambrault                 Gerald                    11000
147 Errazuriz                 Alberto                   12000
108 Greenberg                 Nancy                     12008
201 Hartstein                 Michael                   13000
205 Higgins                   Shelley                   12008
156 King                      Janette                   10000
168 Ozer                      Lisa                      11500
146 Partners                  Karen                     13500
114 Raphaely                  Den                       11000
145 Russell                   John                      14000
150 Tucker                    Peter                     10000
162 Vishney                   Clara                     10500
149 Zlotkey                   Eleni                     10500

16 rows selected.

4)    Now as WORKER:

SQL> select employee_id, last_name, first_name, salary
from hr.employees
where salary between 10000 and 15000
order by last_name;
2    3    4
EMPLOYEE_ID LAST_NAME                 FIRST_NAME               SALARY
----------- ------------------------- -------------------- ----------
174 Abel                      Ellen                         0
204 Baer                      Hermann                       0
169 Bloom                     Harrison                      0
148 Cambrault                 Gerald                        0
147 Errazuriz                 Alberto                       0
108 Greenberg                 Nancy                         0
201 Hartstein                 Michael                       0
205 Higgins                   Shelley                       0
156 King                      Janette                       0
168 Ozer                      Lisa                          0
146 Partners                  Karen                         0
114 Raphaely                  Den                           0
145 Russell                   John                          0
150 Tucker                    Peter                         0
162 Vishney                   Clara                         0
149 Zlotkey                   Eleni                         0

16 rows selected.

Notice anything interesting?  Even though I cannot see the actual values of the salary column, I can still narrow the search via comparison operators to get the same results.  This is the flaw.  You can use comparison and equivalence operators to not only get values in the ballpark but even get the exact value:

SQL> SQL> connect supervisor/supervisor
Connected.
SQL> select employee_id, last_name, first_name, salary
from hr.employees
where salary = 14000
order by last_name;

2    3    4
EMPLOYEE_ID LAST_NAME                 FIRST_NAME               SALARY
----------- ------------------------- -------------------- ----------
145 Russell                   John                      14000

SQL> SQL> connect worker/worker
Connected.
SQL> select employee_id, last_name, first_name, salary
from hr.employees
where salary = 14000
order by last_name;

2    3    4
EMPLOYEE_ID LAST_NAME                 FIRST_NAME               SALARY
----------- ------------------------- -------------------- ----------
145 Russell                   John                          0

This feature may be a good fit for other situations, but it will not stop a user with a little SQL knowledge and the ability to adhoc query the database.

More information on this feature is here:

http://www.oracle.com/technetwork/articles/database/data-redaction-odb12c-2331480.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s