Demonstration of Oracle 12c Data Redaction

Tuesday, December 8, 2015

Demonstration of Oracle 12c Data Redaction

Introduction

In this post I'd like to demonstrate the new Oracle 12c security feature - Data Redaction.

Although it was introduced in Oracle 12c, a similar feature named "column masking" has been introduced back in 2003 with Oracle version 10g. Data Redaction allows you to protect confidential data by masking the sensitive information from unauthorized users. The way for Oracle to know if the user is authorized to see the real data is by using the EXEMPT REDACTION POLICY system privilege. If the user doesn't have the EXEMPT REDACTION POLICY system privilege it means that he is unauthorized and therefore if a redaction policy is defined for the data the the user attempts to query, the output will be redacted rather than the real data.

Data Redaction Policies

Data Redaction policy for a table or view defines which column/s will be redacted and in which manner.

The management of redaction policies is done via the DBMS_REDACT package.

For example, you can create a new policy DBMS_REDACT.ADD_POLICY procedure, modify an existing policy using the DBMS_REDACT.ALTER_POLICY, disable a policy using DBMS_REDACT.DISABLE_POLICY and drop a policy using DBMS_REDACT.DROP_POLICY.

Demonstration

First, let's create a table and insert a 2 records to the table.

SQL> create table EMPLOYEE (id number, name varchar2(20), join_date timestamp);
Table created.

SQL> insert into EMPLOYEE values (1, 'PINI',sysdate);
1 row created.

SQL> insert into EMPLOYEE values (2, 'DAVID',sysdate);
1 row created.
SQL> commit;
Commit complete.

SQL> select * from EMPLOYREE;

        ID NAME                 JOIN_DATE
---------- -------------------- ------------------------------
         1 PINI                 08-DEC-15 03.33.27.000000 PM
         2 DAVID                08-DEC-15 03.33.28.000000 PM

Let's start by creating a new redaction policy named "REDACT_EMPLOYEE" for the "Name" column

SQL> BEGIN
  2     DBMS_REDACT.add_policy (object_name     => 'EMPLOYEE',
  3                             column_name     => 'ID',
  4                             policy_name     => 'REDACT_EMPLOYEE',
  5                             function_type   =>  DBMS_REDACT.full,
  6                             expression      => '1=1');
  7  END;
  8  /
PL/SQL procedure successfully completed.

SQL> select * from EMPLOYEE;

        ID NAME                 JOIN_DATE
---------- -------------------- ------------------------------
         0 PINI                 08-DEC-15 03.33.27.000000 PM
         0 DAVID                08-DEC-15 03.33.28.000000 PM

As we can see, the value of the ID column has been changed to 0. You may ask yourself, why 0? How Oracle decides which values to use for the masking? We'll discuss about it in a minute. Now, let's redact the rest of the columns (Name and JOIN_DATE) using the ALTER_POLICY procedure. In order to do that, we'll set the value of "action" parameter to be the DBMS_REDACT.ADD_COLUMN constant, as follows:

SQL> BEGIN
  2     DBMS_REDACT.alter_policy (object_name     => 'EMPLOYEE',
  3                             column_name     => 'NAME',
  4                             policy_name     => 'REDACT_EMPLOYEE',
  5                             function_type   => DBMS_REDACT.full,
  6                             expression      => '1=1',
  7                             action          => DBMS_REDACT.ADD_COLUMN);
  8  END;
  9  /
PL/SQL procedure successfully completed.

SQL> BEGIN
  2     DBMS_REDACT.alter_policy (object_name     => 'EMPLOYEE',
  3                             column_name     => 'JOIN_DATE',
  4                             policy_name     => 'REDACT_EMPLOYEE',
  5                             function_type   => DBMS_REDACT.full,
  6                             expression      => '1=1',
  7                             action          => DBMS_REDACT.ADD_COLUMN);
  8  END;
  9  /
PL/SQL procedure successfully completed.

SQL> select * from EMPLOYEE;

        ID NAME                 JOIN_DATE
---------- -------------------- ------------------------------
         0                      01-JAN-01 01.00.00.000000 AM
         0                      01-JAN-01 01.00.00.000000 AM

As we can see, Oracle changed all values of the EMPLOYEE table with fixed values and that's because we set the function_type parameter to be DBMS_REDACT.FULL which is by the way, the default behaviour so if you will omit the function_type parameter, it will use a FULL redaction, i.e. it will use fixed values for the data redaction.
The default redaction values for the FULL redaction can be obtained via the REDACTION_VALUES_FOR_TYPE_FULL dictionary view.

As you can see, the default value for NUMBER is 0, for characther data types it's a single space and for date-time data types is the first day of January, 2001, which appears as 01-JAN-01.
In order to change the default masking valuess when using a FULL redaction, you can use the UPDATE_FULL_REDACTION_VALUES Procedure. In addition to the default FULL redaction type, you can also choose to work with other function types:

NONE - No redaction
PARTIAL - Partial redaction, redact a portion of the column data
RANDOM - Random redaction, each query results in a different random value
REGEXP - Regular expression based redaction

For example, you can change the function_type of all the columns to be RANDOM as follows:

SQL> BEGIN
  2     DBMS_REDACT.alter_policy (object_name     => 'EMPLOYEE',
  3                               column_name     => 'ID',
  4                               policy_name     => 'REDACT_EMPLOYEE',
  5                               function_type   => DBMS_REDACT.RANDOM,
  6                               action          => DBMS_REDACT.MODIFY_COLUMN);
  7  END;
  8  /
PL/SQL procedure successfully completed.

SQL> BEGIN
  2     DBMS_REDACT.alter_policy (object_name     => 'EMPLOYEE',
  3                               column_name     => 'NAME',
  4                               policy_name     => 'REDACT_EMPLOYEE',
  5                               function_type   => DBMS_REDACT.RANDOM,
  6                               action          => DBMS_REDACT.MODIFY_COLUMN);
  7  END;
  8  /
PL/SQL procedure successfully completed.

SQL> BEGIN
  2     DBMS_REDACT.alter_policy (object_name     => 'EMPLOYEE',
  3                               column_name     => 'JOIN_DATE',
  4                               policy_name     => 'REDACT_EMPLOYEE',
  5                               function_type   => DBMS_REDACT.RANDOM,
  6                               action          => DBMS_REDACT.MODIFY_COLUMN);
  7  END;
  8  /
PL/SQL procedure successfully completed.

SQL> select * from EMPLOYEE;

        ID NAME                 JOIN_DATE
---------- -------------------- ------------------------------
         8 N(#i                 23-AUG-80 05.25.47.000000 AM
         5 SG*cK                11-DEC-90 04.19.26.000000 AM

As I've mentioned in the beginning of this post, once a user has the EXEMPT REDACTION POLICY system privilege it means that he is authotized to view the original data. Let's connect with user SYS and grant EXEMPT REDACTION POLICY to user "pini":

SQL> grant EXEMPT REDACTION POLICY to pini;
Grant succeeded.

SQL> connect pini/pini@//isrvmrh541:1521/pinidb
Connected.
SQL> select * from EMPLOYEE;

        ID NAME                 JOIN_DATE
---------- -------------------- ------------------------------
         1 PINI                 08-DEC-15 03.33.27.000000 PM
         2 DAVID                08-DEC-15 03.33.28.000000 PM

As you can see, now user "pini" is able to view the original "confidential" data because he has the EXEMPT REDACTION POLICY privilege.

License

The data redaction feature is available as part of the "Advanced Security" option. The "Advanced Security" option is an extra cost option to the Enterprise Edition.