Oracle Database 12c New Feature – Data Redaction

Data Redaction is one of the new Advanced Security features introduced in Oracle Database 12c.

It basically shields sensitive data from the application end users and this is done on the fly without any modification being done to the application.

This is different to Oracle Data Masking where data is transformed using masking formats and this updated masked data is stored in new data blocks.

We can create redaction policies which basically govern what condition needs to be satisfied before the data gets redacted, what columns in the table we are going to shield or apply redaction to and how are we going to perform the data redaction.

When the application issues a SQL statement, data is retrieved from the database and the redaction policy is then applied.

Let us look at a few examples of Data Redaction using the 12c Cloud Control and a 12c Container Database.

Note that we can do the same from the command line using the DBMS_REDACT.ADD_POLICY and DBMS_REDACT.ALTER_POLICY APIs.

In this example we have installed the plug-in for 12c Database management via Cloud Control and we can see the container database (CONDB2) and the pluggable database (SALES) as managed targets.

We can work with Data Redaction in Cloud Control and this is available from the Administration/Security menu

Let us now create a new Data Redaction policy.

Click on the Create button

In this example we will be creating a redaction policy called TEST_REDACTION and this will be applied to the EMP table in the SCOTT schema.

Click on the pencil icon which will launch the Policy Expression Builder.

The criteria for enforcing this policy is that the database user should be a non-DBA.
We can see that a redaction policy expression has been created :

SYS_CONTEXT(‘USERENV’,’ISDBA’)=’FALSE’

We can have a look at the DBMS_REDACT.ADD_POLICY command which has been issued in the background.

We will now specify what columns in the table we are going to redact and what kind of redaction policy we are going to apply.

We are going to hide the data contained in the SAL column of the table from any non-DBA database user account and we are not using a pre-defined template but will create our own Custom policy.

The redaction methods available are Full, Partial, Random and Regular expression.

In Full redaction columns are redacted to a constant value depending on the data type of the redacted column – like say a 0 for a NUMBER column.

In Partial the user can specify what positions in the data which will be replaced by user specified characters.

In Regular Expression a match and replace is performed based on some parameters.
 

Redaction Method	Original Data 		Redacted Data
 
Full    		100000			0
Partial 		543-46-2457 		xxx-xx-2457                    
Regular Expression 	gavin@oracle.com	xxx@oracle.com
Random			123456			321654

 
In the first example we specify FULL

Have a look at the DBMS_REDACT.ALTER_POLICY statement which has been issued

We can see that the TEST_REDACTION policy has been created and there is now one redacted column in the EMP table.

Let us now edit the TEST_REDACTION policy and add another column to the redacted columns in the table.

Similarly if the user is non-DBA, we want to hide the data in the HIREDATE column and we are using the PARTIAL Redaction Function this time.

The redaction format we use – m01d01y2001, will transform the data in the HIREDATE column when the query is run on the EMP table querying the HIREDATE column and will return a value of ’01-JAN-2001′ instead of the actual HIREDATE column value.

We now see that there are two redacted columns in the EMP table.

Let us now test the redaction policy we have just created.

In the first instance we connect as SCOTT which does not have the DBA role granted to it and query the EMP table.

Notice the redaction policy in action and how the actual data is being shielded from the user.

[oracle@orasql-001-dev ~]$ sqlplus scott/tiger@localhost:1525/sales

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jan 29 13:49:50 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Sat May 25 2013 04:26:41 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select ename,sal from emp;

ENAME             SAL
---------- ----------
SMITH               0
ALLEN               0
WARD                0
JONES               0
MARTIN              0
....
....

SQL> select ename,hiredate from scott.emp;

ENAME      HIREDATE
---------- ---------
SMITH      01-JAN-01
ALLEN      01-JAN-01
WARD       01-JAN-01
JONES      01-JAN-01
MARTIN     01-JAN-01
....
....

Let us connect as SYS and see the difference.

All the data is being returned because the use is a DBA user account unlike SCOTT.

SQL> conn / as sysdba
Connected.
SQL> alter session set container=sales;

Session altered.

SQL> select ename,sal from scott.emp;

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
.....
....

SQL> select ename,hiredate from scott.emp;

ENAME      HIREDATE
---------- ---------
SMITH      17-DEC-80
ALLEN      20-FEB-81
WARD       22-FEB-81
....
....

We have successfully shielded or masked data we consider to be sensitive from certain end users without any modification being required to be made to the application and have done the same with minimal effort using 12c Cloud Control!