In
one of my previous post on column masking
( http://mfzahirdba.blogspot.com/2011/05/column-masking.html) , I was
explaining about using VPD to mask the sensitive columns for compliance
and regulatory requirements and so on.
With
Oracle 12c , Oracle provides package to set up policies to do the same . For
some columns such as ssn , birthdate , there are some preset masking
patterns available such as DBMS_REDACT.REDACT_US_SSN_ENTIRE
, DBMS_REDACT.REDACT_US_SSN_F5 , DBMS_REDACT.REDACT_NUM_ZIP_CODE,
...) .
This
is one another complimentary feature that could be used with VPD , OLS , and so
on.
Here
are the examples.
SQL> set linesize 1000
SQL> set pagesize 90
SQL> create table employee as
2 select lpad(object_id * object_id, 9, 5)
ssn , object_name ename ,
3 object_id * 2 as empid
4 from all_objects
5 where object_id > 70000
and rownum <= 10 ;
Table created.
SQL> col ename format a30
SQL> Select * from employee;
SSN ENAME
EMPID
--------- ------------------------------ ----------
490014000 /135a6a83_CacheCustomizerError
140002
490028000 /7b729baa_CacheCustomizerError
140004
490042000 /5e0f0b23_CacheCustomizerError
140006
490056001 /f55b0251_CacheCustomizerError
140008
490070002 /f1dfa39b_CacheCustomizerError
140010
490084003 /a92f901c_CacheCustomizerError
140012
490098004 /e56e9991_CacheCustomizerError
140014
490112006 /72700b70_CacheCustomizerError 140016
490126008 /c00b177b_CacheCustomizerError
140018
490140010 /791bbe73_CacheCustomizerError
140020
10 rows selected.
Now
, let us create redaction policy on the SSN column as below. In this example ,
I am using "FULL redaction . If the "client_info" is set
to "PAYROLL" , I will see the value of 'SSN' with no redaction .
Otherwise , it returns 0 , as it is a Full redaction.
(
Note : The user needs to be granted EXECUTE privileges on DBMS_REDACT by a
user with SYSDBA privilege ) .
SQL> BEGIN
2 DBMS_REDACT.ADD_POLICY(
3 object_schema
=> 'zahir',
4 object_name
=> 'employee',
5 column_name
=> 'ssn',
6 policy_name
=> 'hide_emp_ssn',
7 function_type
=> DBMS_REDACT.FULL,
8 function_parameters
=> DBMS_REDACT.REDACT_US_SSN_F5,
9 expression
=> 'SYS_CONTEXT(''USERENV'',''CLIENT_INFO'') !=
''PAYROLL''');
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> exec
dbms_application_info.set_client_info('PAYROLL') ;
PL/SQL procedure successfully completed.
SQL> Select SYS_CONTEXT('USERENV','CLIENT_INFO') from
dual ;
SYS_CONTEXT('USERENV','CLIENT_INFO')
---------------------------------------------------------------
PAYROLL
Let us run query the table again .
As you can see , there is no change
SQL> Select * from employee;
SSN ENAME
EMPID
--------- ------------------------------ ----------
490014000 /135a6a83_CacheCustomizerError
140002
490028000 /7b729baa_CacheCustomizerError
140004
490042000 /5e0f0b23_CacheCustomizerError
140006
490056001 /f55b0251_CacheCustomizerError
140008
490070002 /f1dfa39b_CacheCustomizerError
140010
490084003 /a92f901c_CacheCustomizerError
140012
490098004 /e56e9991_CacheCustomizerError
140014
490112006 /72700b70_CacheCustomizerError
140016
490126008 /c00b177b_CacheCustomizerError
140018
490140010 /791bbe73_CacheCustomizerError
140020
10 rows selected.
Let us set the client_info to
other than 'PAYROLL' ( ex: MIS) and run query the table again . As you can see
, the value of SSN is redacted.
SQL> exec
dbms_application_info.set_client_info('MIS') ;
PL/SQL procedure successfully completed.
SQL> Select
SYS_CONTEXT('USERENV','CLIENT_INFO') from dual ;
SYS_CONTEXT('USERENV','CLIENT_INFO')
---------------------------------------------------------------------
MIS
SQL> Select * from employee;
SSN ENAME
EMPID
--------- ------------------------------
----------
/135a6a83_CacheCustomizerError 140002
/7b729baa_CacheCustomizerError 140004
/5e0f0b23_CacheCustomizerError 140006
/f55b0251_CacheCustomizerError 140008
/f1dfa39b_CacheCustomizerError 140010
/a92f901c_CacheCustomizerError 140012
/e56e9991_CacheCustomizerError 140014
/72700b70_CacheCustomizerError 140016
/c00b177b_CacheCustomizerError 140018
/791bbe73_CacheCustomizerError 140020
10 rows selected.
There can be at most only one redaction policy per table / view.
The ADD_POLICY does not accommodate more than one column . To add a column to
the policy / alter the characteristics of the policy we need to use
ALTER_POLICY
In the example below , we will redact the value of empid to anyone
other than 'PAYROLL' identifier .
SQL> BEGIN
2 DBMS_REDACT.ALTER_POLICY(
3 object_schema
=> 'zahir',
4 object_name
=> 'employee',
5 column_name
=> 'empid',
6 policy_name
=> 'hide_emp_ssn',
7 action
=> DBMS_REDACT.ADD_COLUMN,
8 function_type
=> DBMS_REDACT.FULL,
9 expression
=> 'SYS_CONTEXT(''USERENV'',''CLIENT_INFO'') !=
''PAYROLL''');
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> Select * from employee;
SSN ENAME
EMPID
--------- ------------------------------
----------
/135a6a83_CacheCustomizerError 0
/7b729baa_CacheCustomizerError 0
/5e0f0b23_CacheCustomizerError 0
/f55b0251_CacheCustomizerError 0
/f1dfa39b_CacheCustomizerError 0
/a92f901c_CacheCustomizerError 0
/e56e9991_CacheCustomizerError 0
/72700b70_CacheCustomizerError 0
/c00b177b_CacheCustomizerError 0
/791bbe73_CacheCustomizerError 0
10 rows selected.
Personally , I feel , it would have better , if we 'ADD_POLICY'
accommodates more than one column , if they match redaction characteristics /
column datatype , so on.
One another caveat is that you cannot perform CTAS on the redacted
table .
SQL> Create table t as select * from
employee;
Create table t as select * from employee
*
ERROR at line 1:
ORA-28081: Insufficient privileges - the command
references a redacted object.
Comments welcome.
