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.

Column Masking

One of the issues that we are facing is identity theft.
We store lot of sensitive information in the database  and expect ( or pray ) for it to be secure .

The data  can be stored in encrypted tablespace .
TDE works at the tablespace level  , ie , if someone steals the datafile ,
they can't see the data in the raw datafile  if it is encrypted .

But from sqlplus ( any other app )  , the columns will be visible .
Here is one way to mask the sensitive columns .


SQL> show user
USER is "ZAHIR"

SQL> create table employee as select object_id ssn , object_name ename from all_objects ;

Table created.

SQL> desc employee
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SSN                                       NOT NULL NUMBER
 ENAME                                     NOT NULL VARCHAR2(30)

SQL> CREATE OR REPLACE
PACKAGE sec_data_pkg
AS
FUNCTION mask_ssn(
    p_owner IN VARCHAR2,
    p_name  IN VARCHAR2 )
  RETURN VARCHAR2;
END sec_data_pkg;
/ 

Package created.

SQL>
CREATE OR REPLACE
  2  PACKAGE BODY sec_data_pkg
  3  AS
  4  FUNCTION mask_ssn(
  5      p_owner IN VARCHAR2,
  6      p_name  IN VARCHAR2 )
  7    RETURN VARCHAR2
  8  AS
  9  BEGIN
 10    IF sys_context( 'userenv', 'session_user' ) = 'ZAHIR' THEN
 11      RETURN NULL;
 12    ELSE
 13      RETURN '1=0';
 14    END IF;
 15  END;
 16    END sec_data_pkg;
 17
 18  / 

Package body created.

SQL>   BEGIN
  2           DBMS_RLS.ADD_POLICY(object_schema=>user, object_name=>'EMPLOYEE',
  3                               policy_name=>'SECURE_SSN',
  4                               function_schema=>user,
  5                               policy_function=>'sec_data_pkg.mask_ssn',
  6                               sec_relevant_cols=>'ssn',
  7                               sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
  8     END;
  9     /
                            
PL/SQL procedure successfully completed.

SQL>  create  synonym empl for employee;

Synonym created.

SQL> grant select on empl to public;

Grant succeeded.

SQL> show user
USER is "ZAHIR"

SQL>  select * from zahir.empl where rownum <= 10;

       SSN ENAME
---------- ------------------------------
       100 ORA$BASE
       116 DUAL
       117 DUAL
       280 MAP_OBJECT
       365 SYSTEM_PRIVILEGE_MAP
       367 SYSTEM_PRIVILEGE_MAP
       368 TABLE_PRIVILEGE_MAP
       370 TABLE_PRIVILEGE_MAP
       371 STMT_AUDIT_OPTION_MAP
       373 STMT_AUDIT_OPTION_MAP

10 rows selected.


Let us see , if any other signs on to the database and issues the same SELECT .
The values in the column SSN will not be visible , as per our policy (  sec_relevant_cols=>'ssn')


SQL> conn scott/tiger
Connected.

SQL> select * from zahir.empl where rownum <= 10;

       SSN ENAME
---------- ------------------------------
           ORA$BASE
           DUAL
           DUAL
           MAP_OBJECT
           SYSTEM_PRIVILEGE_MAP
           SYSTEM_PRIVILEGE_MAP
           TABLE_PRIVILEGE_MAP
           TABLE_PRIVILEGE_MAP
           STMT_AUDIT_OPTION_MAP
           STMT_AUDIT_OPTION_MAP

10 rows selected.

Please send your comments / suggestions .

Update :

I have updated this post for this question 

Column Masking was interesting, however how do we show only the last 4 digit of SSN if we want to. Can you please brief something on PCI compliance

We would use a view to display  the last 4 digits of SSN.

SQL> show user
USER is "ZAHIR"

SQL> create or replace view emp_v as
select ssn , 'xxx-xxx-'||substr(ssn,-4) formatted_ssn ,
ename from employee ; 

View created.

SQL> select * from emp_v where rownum < 5;

       SSN SSN_FORMATTE ENAME
---------- ------------ ------------------------------
 123456889 xxx-xxx-6889 ORA$BASE
 123456905 xxx-xxx-6905 DUAL
 123456906 xxx-xxx-6906 DUAL
 123457069 xxx-xxx-7069 MAP_OBJECT


SQL> exec DBMS_RLS.DROP_POLICY(object_schema=>user,object_name=>'EMPLOYEE' ,policy_name=>'SECURE_SSN') ;

PL/SQL procedure successfully completed.

SQL>
BEGIN
             DBMS_RLS.ADD_POLICY(object_schema=>user, object_name=>'EMP_V',
                                 policy_name=>'SECURE_SSN',
                                 function_schema=>user,
                                 policy_function=>'sec_data_pkg.mask_ssn',
                                 sec_relevant_cols=>'ssn',
                                 sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
       END;
       /
SQL> grant select on emp_v to scott;

Grant succeeded.


SQL> BEGIN
  2               DBMS_RLS.ADD_POLICY(object_schema=>user, object_name=>'EMP_V',
  3                                   policy_name=>'SECURE_SSN',
  4                                   function_schema=>user,
  5                                   policy_function=>'sec_data_pkg.mask_ssn',
  6                                   sec_relevant_cols=>'ssn',
  7                                   sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
  8         END;
  9         /

PL/SQL procedure successfully completed.

SQL> select * from zahir.emp_v where rownum < 5;

       SSN SSN_FORMATTE ENAME
---------- ------------ ------------------------------
 123456889 xxx-xxx-6889 ORA$BASE
 123456905 xxx-xxx-6905 DUAL
 123456906 xxx-xxx-6906 DUAL
 123457069 xxx-xxx-7069 MAP_OBJECT

SQL> conn scott/tiger
Connected.
SQL>  select * from zahir.emp_v where rownum < 5;

       SSN SSN_FORMATTE ENAME
---------- ------------ ------------------------------
           xxx-xxx-6889 ORA$BASE
           xxx-xxx-6905 DUAL
           xxx-xxx-6906 DUAL
           xxx-xxx-7069 MAP_OBJECT
 
SQL> select COALESCE (to_char(ssn) , formatted_ssn  ) ssn ,ename from zahir.emp_v where rownum < 5;

SSN                                      ENAME
---------------------------------------- ------------------------------
xxx-xxx-6889                             ORA$BASE
xxx-xxx-6905                             DUAL
xxx-xxx-6906                             DUAL
xxx-xxx-7069                             MAP_OBJECT