Thursday, April 3, 2014

Data Redaction in Oracle


In one of my previous post on column masking ( http://mfzahirdba.blogspot.com/2011/05/column-masking.html) , I was explaining   about using VPD to mask the sensitive columns for compliance and regulatory requirements and so on. 

With Oracle 12c , Oracle provides package to set up policies to do the same . For some columns such as ssn , birthdate , there are some preset  masking patterns available such as DBMS_REDACT.REDACT_US_SSN_ENTIRE , DBMS_REDACT.REDACT_US_SSN_F5 , DBMS_REDACT.REDACT_NUM_ZIP_CODE, ...) 

This is one another complimentary feature that could be used with VPD , OLS , and so on. 

Here are the examples. 

SQL> set linesize 1000
SQL> set pagesize 90

SQL> create table employee as
  2  select lpad(object_id * object_id, 9, 5)  ssn , object_name ename  ,
  3      object_id * 2 as empid
  4      from all_objects
  5      where object_id > 70000  and rownum <= 10 ;

Table created.

SQL> col ename format a30
SQL> Select * from employee;

SSN       ENAME                               EMPID
--------- ------------------------------ ----------
490014000 /135a6a83_CacheCustomizerError     140002
490028000 /7b729baa_CacheCustomizerError     140004
490042000 /5e0f0b23_CacheCustomizerError     140006
490056001 /f55b0251_CacheCustomizerError     140008
490070002 /f1dfa39b_CacheCustomizerError     140010
490084003 /a92f901c_CacheCustomizerError     140012
490098004 /e56e9991_CacheCustomizerError     140014
490112006 /72700b70_CacheCustomizerError     140016
490126008 /c00b177b_CacheCustomizerError     140018
490140010 /791bbe73_CacheCustomizerError     140020

10 rows selected.

Now , let us create redaction policy on the SSN column as below. In this example , I am using "FULL redaction .  If the "client_info" is set to "PAYROLL" , I will see the value of 'SSN' with no redaction . Otherwise , it returns 0 , as it is a Full redaction. 

( Note : The user needs to be granted EXECUTE privileges on DBMS_REDACT by a user with SYSDBA privilege ) .

SQL> BEGIN
  2     DBMS_REDACT.ADD_POLICY(
  3       object_schema        => 'zahir',
  4       object_name          => 'employee',
  5       column_name          => 'ssn',
  6       policy_name          => 'hide_emp_ssn',
  7       function_type        => DBMS_REDACT.FULL,
  8        function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,
  9       expression           => 'SYS_CONTEXT(''USERENV'',''CLIENT_INFO'') != ''PAYROLL''');
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> exec dbms_application_info.set_client_info('PAYROLL') ;

PL/SQL procedure successfully completed.

SQL> Select SYS_CONTEXT('USERENV','CLIENT_INFO') from dual ;

SYS_CONTEXT('USERENV','CLIENT_INFO')
---------------------------------------------------------------
PAYROLL

Let us run query the table again . As you can see , there is no change 

SQL> Select * from employee;

SSN       ENAME                               EMPID
--------- ------------------------------ ----------
490014000 /135a6a83_CacheCustomizerError     140002
490028000 /7b729baa_CacheCustomizerError     140004
490042000 /5e0f0b23_CacheCustomizerError     140006
490056001 /f55b0251_CacheCustomizerError     140008
490070002 /f1dfa39b_CacheCustomizerError     140010
490084003 /a92f901c_CacheCustomizerError     140012
490098004 /e56e9991_CacheCustomizerError     140014
490112006 /72700b70_CacheCustomizerError     140016
490126008 /c00b177b_CacheCustomizerError     140018
490140010 /791bbe73_CacheCustomizerError     140020

10 rows selected.


Let us  set the client_info to other than 'PAYROLL' ( ex: MIS) and run query the table again . As you can see , the value of SSN is redacted. 

SQL> exec dbms_application_info.set_client_info('MIS') ;

PL/SQL procedure successfully completed.

SQL> Select SYS_CONTEXT('USERENV','CLIENT_INFO') from dual ;

SYS_CONTEXT('USERENV','CLIENT_INFO')
---------------------------------------------------------------------
MIS




SQL> Select * from employee;

SSN       ENAME                               EMPID
--------- ------------------------------ ----------
          /135a6a83_CacheCustomizerError     140002
          /7b729baa_CacheCustomizerError     140004
          /5e0f0b23_CacheCustomizerError     140006
          /f55b0251_CacheCustomizerError     140008
          /f1dfa39b_CacheCustomizerError     140010
          /a92f901c_CacheCustomizerError     140012
          /e56e9991_CacheCustomizerError     140014
          /72700b70_CacheCustomizerError     140016
          /c00b177b_CacheCustomizerError     140018
          /791bbe73_CacheCustomizerError     140020

10 rows selected.

There can be at most only one redaction policy per table / view. The ADD_POLICY does not accommodate more than one column . To add a column to the policy / alter the characteristics of the policy we need to use ALTER_POLICY 

In the example below , we will redact the value of empid to anyone other than 'PAYROLL' identifier 

SQL> BEGIN
  2     DBMS_REDACT.ALTER_POLICY(
  3       object_schema        => 'zahir',
  4       object_name          =>  'employee',
  5       column_name          => 'empid',
  6       policy_name          => 'hide_emp_ssn',
  7       action              => DBMS_REDACT.ADD_COLUMN,
  8       function_type        => DBMS_REDACT.FULL,
  9       expression           => 'SYS_CONTEXT(''USERENV'',''CLIENT_INFO'') != ''PAYROLL''');
 10  END;
 11  /

PL/SQL procedure successfully completed.


SQL>  Select * from employee;

SSN       ENAME                               EMPID
--------- ------------------------------ ----------
          /135a6a83_CacheCustomizerError          0
          /7b729baa_CacheCustomizerError          0
          /5e0f0b23_CacheCustomizerError          0
          /f55b0251_CacheCustomizerError          0
          /f1dfa39b_CacheCustomizerError          0
          /a92f901c_CacheCustomizerError          0
          /e56e9991_CacheCustomizerError          0
          /72700b70_CacheCustomizerError          0
          /c00b177b_CacheCustomizerError          0
          /791bbe73_CacheCustomizerError          0

10 rows selected.



Personally , I feel , it would have better , if we 'ADD_POLICY' accommodates more than one column , if they match redaction characteristics / column datatype , so on.

One another caveat is that you cannot perform CTAS on the redacted table . 

SQL> Create table t as select * from employee;
Create table t as select * from employee
                       *
ERROR at line 1:
ORA-28081: Insufficient privileges - the command references a redacted object.


Comments welcome.


No comments:

Post a Comment