Using full redaction for masking the column values in 12c

This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle’s Advanced Security.

 

Why would we need to mask a column’s data? One of the most common uses is to enable us to hide sensitive data from being shown on the screen of any application.

There are multiple ways to do this and one of the ways is to use DBMS_RLS policy. Here is one nice article published by Surya.  In this tutorial, however, we will explore how we can make use of full redaction option from DBMS_REDACT to mask the column values. The actual reference of masking can be taken from Oracle documentation from here.

In this article I am going to explain only one part i.e. full redaction, but there are other features which can also be used based on our business requirement. such as:

Partial redaction:  

Using this option we can mask only certain portions of the column. For example, say if we want to mask all digits except the last 4 numbers from our credit card or our SSN number then we can use partial redaction.

Regular expressions:

This type of option can be used to mask characters based on patterns search.

Random redaction:

We can dynamically generate any dummy number that can be displayed on an application’s screen based on the data type of the column.

No redaction:

This type of option is only used to test the redaction policy. To test the code and verify the application before moving the actual making policy to any production environment.

To use full redaction type from DBMS_REDACT we need to first grant the schema, in my case I am going to take the example from SCOTT schema.  In 10g onward we used “DBMS_RLS.ADD_POLICY” to use to mask the columns now let us take a look at how we can use dbms_redact object in a 12c environment.

First, let’s give the “GRANT” to the schema.

oracle@dev-VirtualBox ~ $ sql 
SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 1 14:28:18 2018 
Copyright (c) 1982, 2014, Oracle. All rights reserved. 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 

SQL> GRANT EXECUTE ON sys.dbms_redact TO SCOTT; 
Grant succeeded. 
SQL> 

 
Now let us select the column which we want to mask , in this example I am using existing schema on oracle i.e. SCOTT and object name is EMP table. 

 

SQL> select ename, sal from emp; 
ENAME SAL
---------- ---------- 
SMITH 800 
ALLEN 1600 
WARD 1250 
JONES 2975 
MARTIN 1250 
BLAKE 2850 
CLARK 2450 
SCOTT 3000 
KING 5000 
TURNER 1500 
ADAMS 1100 
JAMES 950 
FORD 3000 
MILLER 1300 

14 rows selected.

Open in new window

Now, let's add the policy to mask the SAL column from the EMP table. To add the policy we can use something like the example given below:
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 
SQL> 
BEGIN DBMS_REDACT.add_policy( object_schema => 'scott', object_name => 'EMP', column_name => 'SAL', policy_name => 'redact_sal', function_type => DBMS_REDACT.full, expression => '1=1' ); END;
/ 


PL/SQL procedure successfully completed. 

Open in new window


Let's verify our table now.
SQL> select ename, sal from emp; 
ENAME SAL ---------- ---------- 
SMITH 0 
ALLEN 0 
WARD 0 
JONES 0 
MARTIN 0 
BLAKE 0 
CLARK 0 
SCOTT 0 
KING 0 
TURNER 0 
ADAMS 0 
JAMES 0 
FORD 0 
MILLER 0 

14 rows selected. 

Open in new window


So we can now see the masking is successfully implemented.

Now, let’s see how we can use “SYS_CONTEXT” with the DBMS_REDACT package.

At the start of this article, I shared a link to where we can use “DBMS_RLS” but there, the only part we were using is to create a standalone function inside the DBMS_RLS package, but here we will see how we can use SYS_CONTEXT while adding the policy.  To demonstrate this I am going to create some sample tables that were used in the same blog.

So let’s first create the table:

SQL> CREATE TABLE XX_TEMP (EMP_SSN NUMBER , ENAME VARCHAR2(100), HIREDATE DATE); 

Table XX_TEMP created. 

Select allOpen in new window

Now, let’s add some sample data:

 
SQL> begin 
insert into xx_temp values (1234, 'VINAY', SYSDATE) ; 
insert into xx_temp values (1111, 'VENKET', SYSDATE) ; 
insert into xx_temp values (1234, 'SURYA', SYSDATE) ; 
commit; 
end; 

PL/SQL procedure successfully completed. 

Select allOpen in new window

So now our table will look something like this:

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

SQL> conn scott 
Enter password: 
Connected. 
SQL> select * from XX_TEMP; 

EMP_SSN ENAME HIREDATE
 ---------- ---------------------------------------- 
1234             VINAY          02-FEB-18 
1111              VENKET       02-FEB-18 
1234             SURYA          02-FEB-18 

SQL> 

Open in new window

 

Now, let’s add the policy using SYS_CONTEXT:

 
BEGIN DBMS_REDACT.add_policy ( 
object_schema => 'SCOTT', 
object_name => 'XX_TEMP', 
column_name => 'EMP_SSN', 
policy_name => 'redact_syscontext', 
function_type => DBMS_REDACT.full, 
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SCOTT''' ); 
END;
/ 

PL/SQL procedure successfully completed. 

Open in new window

Now let’s grant the same table to other schema and see how the table will look:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 
SQL> conn scott 
Enter password: 
Connected. 

SQL>GRANT SELECT ON SCOTT.XX_TEMP TO sloba; 

Grant succeeded.  

Open in new window

Now, let’s login with sloba and verify the table.

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 2 09:51:19 2018 
Copyright (c) 1982, 2014, Oracle. All rights reserved. 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 

SQL> conn sloba 
Enter password: 
Connected. 
SQL> select * from scott.xx_temp; 

EMP_SSN ENAME HIREDATE 
---------- --------------------- --------- 
0 VINAY 02-FEB-18 
0 VENKET 02-FEB-18 
0 SURYA 02-FEB-18 

SQL> conn scott 
Enter password: 
Connected. 

SQL> select * from xx_temp; 

EMP_SSN ENAME HIREDATE 
-------------------------------- --------- 
1234 VINAY 02-FEB-18 
1111 VENKET 02-FEB-18 
1234 SURYA 02-FEB-18 

SQL> 

Open in new window

From the above, we can see that the policy is applied for other users, other than scott user.

If we don’t want to use the policy we can drop it by using the following command:

SQL> 
BEGIN 
DBMS_REDACT.drop_policy ( 
object_schema => 'SCOTT', 
object_name => 'XX_TEMP', 
policy_name => 'redact_syscontext' );
END;
 / 

Open in new window

Now, let’s verify from sloba user after dropping the policy:

 
SQL> conn sloba 
Enter password: 
Connected. 

SQL> select * from scott.xx_temp; 

EMP_SSN ENAME HIREDATE 
-------------------------------- --------- 
1234 VINAY 02-FEB-18 
1111 VENKET 02-FEB-18 
1234 SURYA 02-FEB-18 
SQL> 

Open in new window

So now you can see that after dropping the policy we can view all the data from the object that we masked earlier.

Thank you for reading my blog , the reference is taken from one of my article that was published by Experts-Exchange.

Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s