- Posted by Gavin Soorma
- On January 30, 2014
- 0 Comments
- 12c, advanced security, data masking, Data Redaction, dbms_redact
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 :
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 firstname.lastname@example.org email@example.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 22.214.171.124.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 126.96.36.199.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!