Wednesday, June 26, 2013

Oracle 12c New Features - Data Redaction via DBMS_REDACT

Oracle 12c has added functionality to redact data in specific columns depending on a policy configured by the DBMS_REDACT package.  

First create table EMPLOYEE in schema TEST and grant select to GOD and NOBODY.

SQL> connect test/test@T12P1
Connected.
SQL> create table employee (emp_id integer primary key, emp_name varchar2(10), salary number);

Table created.

SQL> grant select on employee to god;

Grant succeeded.

SQL> grant select on employee to nobody;

Grant succeeded.

SQL> insert into employee (emp_id, emp_name, salary) values (1,'John',100000);

1 row created.

SQL> insert into employee (emp_id, emp_name, salary) values (2,'Ben',80000);

1 row created.

SQL> commit;

Commit complete.

Now we create the data redaction policy using DBMS_REDACT.ADD_POLICY.  Only someone logged in as GOD should be allowed to see the values stored in TEST.EMPLOYEE.SALARY.  In this case we are using function type FULL which obscures all data, but other options include PARTIAL, RANDOM, REGEXP.

SQL> connect system/password@T12P1
Connected.
SQL> BEGIN
  2  DBMS_REDACT.add_policy(object_schema => 'TEST'
  3  ,object_name => 'EMPLOYEE'
  4  ,policy_name => 'Salary Redaction'
  5  ,expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''GOD'' OR SYS_CONTEXT(''USERENV'',''SESSION_USER'') IS NULL'
  6  ,column_name => 'SALARY'
  7  ,function_type => dbms_redact.FULL
  8  );
  9  END;
 10  /

PL/SQL procedure successfully completed.

When we are logged in as nobody the salary column is redacted.

SQL> connect nobody/nobody@T12P1
Connected.
SQL> select * from test.employee;

              EMP_ID EMP_NAME                 SALARY
-------------------- ---------- --------------------
                   1 John                          0
                   2 Ben                           0

But when we are logged in as god we can see the data.

SQL> connect god/god@T12P1
Connected.
SQL> select * from test.employee;

              EMP_ID EMP_NAME                 SALARY
-------------------- ---------- --------------------
                   1 John                     100000
                   2 Ben                       80000

Note that redaction policies do NOT apply to users with the EXEMPT REDACTION POLICY system privilege, which by default is granted via EXP_FULL_DATABASE and DBA roles.  This means that by default DBAs will still have access to redacted data.

SQL> connect system/password@T12P1
Connected.
SQL> select * from test.employee;

              EMP_ID EMP_NAME                 SALARY
-------------------- ---------- --------------------
                   1 John                     100000
                   2 Ben                       80000

SQL> select grantee, privilege from dba_sys_privs where privilege = 'EXEMPT REDACTION POLICY';

GRANTEE
-----------------------------------------------------------------
EXP_FULL_DATABASE

SQL> select grantee from dba_role_privs where granted_role = 'EXP_FULL_DATABASE';

GRANTEE
-----------------------------------------------------------------
SYS
DATAPUMP_EXP_FULL_DATABASE
DBA
DATAPUMP_IMP_FULL_DATABASE