Data Redaction was introduced in Oracle Database 12c Release 1 (read a note on this)
where we could hide or mask sensitive data in tables from non-privileged users. The data was ‘redacted’ at query run time and was stored in the database in its normal non-redacted form. The redaction could be full (no original data displayed) or partial (some portion of the original data is returned). We could also randomly redact data or use regular expressions to look for patterns of data to redact.
A number of new features related to Data Redaction have been introduced in Oracle 12.2.0.1.
We can now redact data based on different run time conditions as well as centrally manage and control data redaction policy expressions stored in the database. If we make a change to a data redaction policy expression, the change will cascade to all tables and columns which use the associated data redaction policy. Also new in 12.2, is the ability to use NULL as the redacted value.
Let us have a look at some examples demonstrating these new data redaction features.
We have two HR users in the database – HR_ADMIN and HR_MGR. Our security requirement is to hide certain sensitive information like salaries and date of birth from non-privileged users like HR_ADMIN, but privileged users like HR_MGR should be able to see even this type of sensitive data.
Currently with no data redaction in place (which is the default), both users can see all the data in the EMP_PAYROLL as well as the EMP_DETAILS tables even though those tables contain data which we consider to be sensitive.
SQL> conn hr_admin/hr_admin
Connected.
SQL> select * from hr.emp_payroll;
EMP_ID EMP_NAME EMP_SAL PAYROLL_MONTH
---------- ---------- ---------- ------------------------------
1001 JOHN SMITH 10000 MAY-17
1002 SARAH LEE 12000 MAY-17
1003 JACK JONES 15000 MAY-17
SQL> conn hr_mgr/hr_mgr
Connected.
SQL> select * from hr.emp_payroll;
EMP_ID EMP_NAME EMP_SAL PAYROLL_MONTH
---------- ---------- ---------- ------------------------------
1001 JOHN SMITH 10000 MAY-17
1002 SARAH LEE 12000 MAY-17
1003 JACK JONES 15000 MAY-17
SQL> conn hr_admin/hr_admin
Connected.
SQL> select * from hr.emp_details;
EMP_ID EMP_NAME DOB DOJ ADDRESS
---------- ------------ --------- --------- --------------------
1001 JOHN SMITH 01-JAN-76 22-MAR-01 5 The Vale, Horsham
1002 SARAH LEE 23-JUN-80 15-DEC-14 1 Pall Mall London
1003 JACK JONES 12-JAN-76 17-MAY-01 23 Hill Ave, Hull
SQL> conn hr_mgr/hr_mgr
Connected.
SQL> /
EMP_ID EMP_NAME DOB DOJ ADDRESS
---------- ------------ --------- --------- --------------------
1001 JOHN SMITH 01-JAN-76 22-MAR-01 5 The Vale, Horsham
1002 SARAH LEE 23-JUN-80 15-DEC-14 1 Pall Mall London
1003 JACK JONES 12-JAN-76 17-MAY-01 23 Hill Ave, Hull
We create a data redaction policy on the EMP_SAL column in the EMP_PAYROLL tablein the HR schema. The data redaction type is FULL which means that the entire data in the column will be disguised or not displayed.
But now the data in the EMP_PAYROLL table is redacted for both the HR_ADMIN as well as HR_MGR user. This was the normal Oracle 12c Release 1 behaviour.
SQL> conn /as sysdba
Connected.
SQL> BEGIN
2 DBMS_REDACT.ADD_POLICY( object_schema=>'HR',object_name=>'EMP_PAYROLL',column_name=>'EMP_SAL',policy_name=>'redact_emp_sal',function_type=>DBMS_REDACT.FULL,expression=>'1=1');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> conn hr_admin/hr_admin
Connected.
SQL> select * from hr.emp_payroll;
EMP_ID EMP_NAME EMP_SAL PAYROLL_MONTH
---------- ---------- ---------- ------------------------------
1001 JOHN SMITH 0 MAY-17
1002 SARAH LEE 0 MAY-17
1003 JACK JONES 0 MAY-17
SQL> conn hr_mgr/hr_mgr
Connected.
SQL> select * from hr.emp_payroll;
EMP_ID EMP_NAME EMP_SAL PAYROLL_MONTH
---------- ---------- ---------- ------------------------------
1001 JOHN SMITH 0 MAY-17
1002 SARAH LEE 0 MAY-17
1003 JACK JONES 0 MAY-17
We now create a data redaction policy expression which excludes the user HR_MGR (data will not be redacted for this user but for all other users) and apply the policy expression to the EMP_SAL column in the EMP_PAYROLL table.
SQL> conn / as sysdba
Connected.
SQL> BEGIN
2 DBMS_REDACT.CREATE_POLICY_EXPRESSION (
3 policy_expression_name=>'redact_hr_mgr_pol_expr',expression=>'SYS_CONTEXT(''USERENV'',''SESSION_USER'') !=''HR_MGR''');
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL (
3 object_schema=>'HR',object_name=>'EMP_PAYROLL',column_name=>'EMP_SAL',policy_expression_name=>'redact_hr_mgr_pol_expr');
4 END;
5 /
PL/SQL procedure successfully completed.
Now if we connect as HR_MGR we can see the data in the EMP_SAL column – but other users like HR_ADMIN still cannot see the data in that column.
SQL> conn hr_mgr/hr_mgr
Connected.
SQL> select * from hr.emp_payroll;
EMP_ID EMP_NAME EMP_SAL PAYROLL_MONTH
---------- ---------- ---------- ------------------------------
1001 JOHN SMITH 10000 MAY-17
1002 SARAH LEE 12000 MAY-17
1003 JACK JONES 15000 MAY-17
SQL> conn hr_admin/hr_admin
Connected.
SQL> select * from hr.emp_payroll;
EMP_ID EMP_NAME EMP_SAL PAYROLL_MONTH
---------- ---------- ---------- ------------------------------
1001 JOHN SMITH 0 MAY-17
1002 SARAH LEE 0 MAY-17
1003 JACK JONES 0 MAY-17
We next create another data redaction policy for the EMP_DETAILS table on the DOB column. Partial redaction is being used – data is being displayed but all the rows for the column DOB have the same value “01-JAN-00”. This is true for both the HR_ADMIN as well as HR_MGR users.
SQL> conn /as sysdba
Connected.
SQL> BEGIN
DBMS_REDACT.ADD_POLICY( object_schema=>'HR',object_name=>'EMP_DETAILS',column_name=>'DOB',policy_name=>'redact_emp_dob',function_type=>DBMS_REDACT.PARTIAL,function_parameters=>'m1d1y2000',expression=>'1=1');
END;
/ 2 3 4
PL/SQL procedure successfully completed.
SQL> conn hr_admin/hr_admin
Connected.
SQL> select * from hr.emp_details;
EMP_ID EMP_NAME DOB DOJ ADDRESS
---------- ------------ --------- --------- --------------------
1001 JOHN SMITH 01-JAN-00 22-MAR-01 5 The Vale, Horsham
1002 SARAH LEE 01-JAN-00 15-DEC-14 1 Pall Mall London
1003 JACK JONES 01-JAN-00 17-MAY-01 23 Hill Ave, Hull
SQL> conn hr_mgr/hr_mgr
Connected.
SQL> select * from hr.emp_details;
EMP_ID EMP_NAME DOB DOJ ADDRESS
---------- ------------ --------- --------- --------------------
1001 JOHN SMITH 01-JAN-00 22-MAR-01 5 The Vale, Horsham
1002 SARAH LEE 01-JAN-00 15-DEC-14 1 Pall Mall London
1003 JACK JONES 01-JAN-00 17-MAY-01 23 Hill Ave, Hull
Let us now apply the policy expression we had created earlier which excludes redaction for the HR_MGR user to the EMP_DETAILS table.
After applying the policy expression we see that the HR_MGR user cam see the data in the DOB column because data redaction has been excluded for this user.
SQL> conn / as sysdba
Connected.
SQL> BEGIN
DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL (
object_schema=>'HR',object_name=>'EMP_DETAILS',column_name=>'DOB',policy_expression_name=>'redact_hr_mgr_pol_expr');
END;
/
PL/SQL procedure successfully completed.
SQL> conn hr_mgr/hr_mgr
Connected.
SQL> select * from hr.emp_details;
EMP_ID EMP_NAME DOB DOJ ADDRESS
---------- ------------ --------- --------- --------------------
1001 JOHN SMITH 01-JAN-76 22-MAR-01 5 The Vale, Horsham
1002 SARAH LEE 23-JUN-80 15-DEC-14 1 Pall Mall London
1003 JACK JONES 12-JAN-76 17-MAY-01 23 Hill Ave, Hull
We can alter the data redaction policy type to the new 12.2 feature which is to display NULL values for the data in the redacted columns. Note that no data at all is displayed for the DOB column as compared to earlier.
SQL> begin
dbms_redact.alter_policy (object_schema => 'HR',object_name => 'EMP_DETAILS',column_name => 'DOB',policy_name => 'redact_emp_dob',action=>DBMS_REDACT.MODIFY_COLUMN,function_type => DBMS_REDACT.NULLIFY,expression => '1=1' );
end;
/ 2 3 4
PL/SQL procedure successfully completed.
SQL> conn hr_admin/hr_admin
Connected.
SQL> /
EMP_ID EMP_NAME DOB DOJ ADDRESS
---------- ------------ --------- --------- --------------------
1001 JOHN SMITH 22-MAR-01 5 The Vale, Horsham
1002 SARAH LEE 15-DEC-14 1 Pall Mall London
1003 JACK JONES 17-MAY-01 23 Hill Ave, Hull
If we drop the data redaction policy currently enforced for the EMP_PAYROLL table, now even the non-privileged HR_ADMIN user can see all the data in the EMP_SAL column.
SQL> BEGIN
DBMS_REDACT.DROP_POLICY (
object_schema => 'HR',
object_name => 'EMP_PAYROLL',
policy_name => 'redact_emp_sal');
END;
/
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> conn hr_admin/hr_admin
Connected.
SQL> select * from hr.emp_payroll;
EMP_ID EMP_NAME EMP_SAL PAYROLL_MONTH
---------- ---------- ---------- ------------------------------
1001 JOHN SMITH 10000 MAY-17
1002 SARAH LEE 12000 MAY-17
1003 JACK JONES 15000 MAY-17
Gavin, knowledge is power…I read lot of things in your blog and really admired.
Now whenever i choose anything,It is asking me to register so that i would get an access.Sometimes it is frustrating too.
You are a Ace Director…Do you really think your blog will make you a billionarie.
Apologize if it it is harsh..
Thanks,
Hey Gavin, during this tough time, I thought to utilize my time in learning new things. I really appreciate your content but a little disappointed that every time I try to read something interesting, it asks me to login and I am not a paid member. Can you make your content free at least for this month?