Data Redaction in Oracle

In one of my previous post on column masking , 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.

Originally published at https://zahirmohideen.blogspot.com/2014/04/data-redaction-in-oracle.html