Oracle Data Redaction

Oracle Data Redaction  is the feature which enables to mask data at run time that is returned from queries issued by applications. During the time that the data is being masked or redacted, all of the data processing is performed normally, and the back-end referential integrity constraints are preserved. It is ideal for situations in which you must redact specific characters out of the result set of queries of Personally Identifiable Information (PII) returned to certain application users.

Redaction is suited for call center applications and other applications that are read-only. You have to be careful for applications that perform updates back to the database as redacted data can be written back to this database.

--------------------------------------------------------------
CREATE TABLE FARHAT.TEST_REDAC(ID NUMBER,NAME VARCHAR2(20),CREDIT_CARD_NUM VARCHAR2(10),ADDRESS VARCHAR2(10))


SET DEFINE OFF;
Insert into FARHAT.TEST_REDAC    (ID, NAME, CREDIT_CARD_NUM, ADDRESS)  Values    (1, 'JOHN', '113-456-78', 'LONDON');
Insert into FARHAT.TEST_REDAC    (ID, NAME, CREDIT_CARD_NUM, ADDRESS)  Values    (2, 'DAVID', '321-654-98', 'RIYADH');
Insert into FARHAT.TEST_REDAC    (ID, NAME, CREDIT_CARD_NUM, ADDRESS)  Values    (3, 'MIKE', '654-213-89', 'JEDDAH');
COMMIT;
/
   

Full Redaction (Masks full column data)

Create Policy
   
BEGIN
  DBMS_REDACT.ADD_POLICY(
  object_schema   => 'FARHAT',
  object_name     => 'TEST_REDAC',
  column_name     => 'ID',
  policy_name     => 'redact_test',
  function_type   => DBMS_REDACT.FULL,
   expression      => 'SYS_CONTEXT(''USERENV'',''CLIENT_INFO'') = ''FARIF-PC''');
END;

Check environment CLIENT_INFO is set or not

SQL> SELECT SYS_CONTEXT('USER_ENV','CLIENT_INFO') FROM DUAL; 

You can create a logon trigger to set CLIENT_INFO environment veriable automatically
 
CREATE OR REPLACE TRIGGER
LOGON_SET_POLICY
AFTER LOGON ON DATABASE
DECLARE
V_INFO VARCHAR2(250) := SYS_CONTEXT('USERENV','TERMINAL'); 
BEGIN
dbms_application_info.set_client_info(V_INFO);
END;

-------------------------------------------------------------------------------
Check environment CLIENT_INFO is set or not

SQL> SELECT sys_context('USERENV', 'CLIENT_INFO') FROM DUAL;

SYS_CONTEXT('USERENV','CLIENT_INFO')
--------------------------------------------------------------------------------
FARIF-PC

--------------------------------------------------------------------------------

SQL> select * from FARHAT.TEST_REDAC;

        ID NAME                 CREDIT_CAR ADDRESS
---------- -------------------- ---------- ----------
         0 JOHN               113-456-78   LONDON
         0 DAVID              321-654-98   RIYADH
         0 MIKE                654-213-89  JEDDAH

Partial Redaction (Masks few digits of column data e.g. 5 digits in below example)

BEGIN
        DBMS_REDACT.ALTER_POLICY(
          object_schema       => 'FARHAT',
          object_name         => 'TEST_REDAC',
          column_name         => 'CREDIT_CARD_NUM',
          policy_name         => 'redact_test',
          function_type       => DBMS_REDACT.PARTIAL,
          function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,
          expression          => 'SYS_CONTEXT(''USERENV'',''CLIENT_INFO'') = ''FARIF-PC''',
          policy_description  => 'Partially redacts 1st 5 digits',
          column_description  => 'Credit Card Number'
          );
       END;
    /

Test Partial Redaction

SQL> select * from FARHAT.TEST_REDAC;

        ID NAME                 CREDIT_CAR ADDRESS
---------- -------------------- ---------- ----------
         0 JOHN               XXX-XX--78 LONDON
         0 DAVID              XXX-XX--98 RIYADH
         0 MIKE                XXX-XX--89 JEDDAH


#############################################################
Policy Maintainance Tasks
#############################################################

Disable Policy


BEGIN
  DBMS_REDACT.DISABLE_POLICY (
    object_schema  => 'FARHAT',
    object_name    => 'TEST_REDAC',
    policy_name    => 'redact_test');
END;

Enable Policy

BEGIN
  DBMS_REDACT.ENABLE_POLICY (
    object_schema  => 'FARHAT',
    object_name    => 'TEST_REDAC',
    policy_name    => 'redact_test');
END;

Dropping Policy

BEGIN
  DBMS_REDACT.DROP_POLICY (
    object_schema  => 'FARHAT',
    object_name    => 'TEST_REDAC',
    policy_name    => 'redact_test');
END;