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.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. 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. 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. 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. 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>
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. 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. 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> 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;
/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> 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 article, please feel free to leave me some feedback or to suggest any future topics.
I'll be looking forward to hearing from you – Swadhin Ray (Sloba)
For more information about me, please check out my Experts Exchange Profile page.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)