12c: 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.

There are number of methods available for redaction ie;

• Full redaction: Redact all of the contents of the column data. The redacted value returned to the querying application user depends on the data type of the column. For example, columns of the NUMBER data type are redacted with a zero (0), and character data types are redacted with a single space.
• Partial redaction: You redact a portion of the column data eg; credit card number
• Regular expressions: Designed for use with character data only, you use regular expressions to look for patterns of data to redact eg; to redact email addresses
• Random: The redacted data presented to the querying application user appears as randomly generated values each time it is displayed.

Example:

1- Create the test table and populate with data

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


SET DEFINE OFF;
Insert into RD.TEST_REDAC    (ID, NAME, CREDIT_CARD_NUM, ADDRESS)  Values    (1, 'ABUZAR', '113-456-78', 'LAHORE');
Insert into RD.TEST_REDAC    (ID, NAME, CREDIT_CARD_NUM, ADDRESS)  Values    (2, 'ZEESHAN', '321-654-98', 'RIYADH');
Insert into RD.TEST_REDAC    (ID, NAME, CREDIT_CARD_NUM, ADDRESS)  Values    (3, 'USMAN', '654-213-89', 'KARACHI');
COMMIT;

2- Next you have to create redaction policy which defines, what kind of redaction to perform, how the redaction should occur, and when the redaction takes place. You can define the policy with a policy expression which allows for different application users to be presented with either redacted data or actual data, based on whether the policy expression returns TRUE or FALSE.

For security reasons, the functions and operators that can be used in the policy expression are limited to SYS_CONTEXT and a few others. User-created functions are not allowed.

To create a Data Redaction policy, you must use the DBMS_REDACT.ADD_POLICY procedure.

C:\app\ibukhary\product\12.1.0\dbhome_1\BIN>sqlplus /@pdb2 as sysdba

SQL> BEGIN
  2   DBMS_REDACT.ADD_POLICY(
  3     object_schema   => 'RD',
  4     object_name     => 'TEST_REDAC',
  5     column_name     => 'ID',
  6     policy_name     => 'redact_test',
  7     function_type   => DBMS_REDACT.FULL,
  8     expression      => 'SYS_CONTEXT(''USERENV'',''CLIENT_INFO'') = ''RED''');
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> select * from RD.TEST_REDAC;

        ID NAME                 CREDIT_CAR ADDRESS
---------- -------------------- ---------- ----------
         1 ABUZAR               113-456-78 LAHORE
         2 ZEESHAN              321-654-98 RIYADH

         3 USMAN                654-213-89 KARACHI


3- Now set the client info to test redaction

SQL> conn rd/rd@pdb2

Connected.

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

PL/SQL procedure successfully completed.

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

SYS_CONTEXT('USERENV','CLIENT_INFO')

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

RED

SQL> select * from RD.TEST_REDAC;

        ID NAME                 CREDIT_CAR ADDRESS

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

         0 ABUZAR               113-456-78 LAHORE

         0 ZEESHAN              321-654-98 RIYADH

         0 USMAN                654-213-89 KARACHI

4- Test with partial redaction

SQL> conn /@pdb2 as sysdba

Connected.

SQL> BEGIN
  2       DBMS_REDACT.ALTER_POLICY(
  3         object_schema       => 'RD',
  4         object_name         => 'TEST_REDAC',
  5         column_name         => 'CREDIT_CARD_NUM',
  6         policy_name         => 'redact_test',
  7         function_type       => DBMS_REDACT.PARTIAL,
  8         function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,
  9         expression          => 'SYS_CONTEXT(''USERENV'',''CLIENT_INFO'') = ''RED''',
 10         policy_description  => 'Partially redacts 1st 5 digits',
 11         column_description  => 'Credit Card Number'
 12         );
 13      END;
 14   /

PL/SQL procedure successfully completed. 

SQL> conn rd/rd@pdb2
Connected.

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

PL/SQL procedure successfully completed.

SQL> select * from RD.TEST_REDAC;

        ID NAME                 CREDIT_CAR ADDRESS
---------- -------------------- ---------- ----------
         0 ABUZAR               XXX-XX--78 LAHORE
         0 ZEESHAN              XXX-XX--98 RIYADH
         0 USMAN                XXX-XX--89 KARACHI


5-  Test other policy maintenance tasks

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


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

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