Oracle Data Redaction provides functionality to mask (redact) data that is returned from user SELECT queries. The masking takes place in real time. The Data Redaction policy applies to the querying user, depending on this user's SYS_CONTEXT values. Oracle Database redacts only the data for the rows specified by the user's query, not the data for the entire column. The redaction takes place immediately before the data is returned to the querying user or application.
To set up a demonstration, uses the setup.sql script, then uses the code shown in below listing to set up a redaction policy on the EMP table.
Setup.sql
create table emp
( empno number,
ename varchar2(20),
salary number,
hire_date date,
email_id varchar2(30) ) ;
insert into emp(empno,ename,salary,hire_date,email_id)
values(1,'John',1013,sysdate,'John@example.com');
insert into emp(empno,ename,salary,hire_date,email_id)
values(2,'Miller',1578,sysdate-2,'miller@example.com');
insert into emp(empno,ename,salary,hire_date,email_id)
values(3,'Kim',1398,sysdate-3,'Kim@example.com');
commit;
rajesh@PDB1> grant select on emp to scott;
Grant succeeded.
rajesh@PDB1> begin
2 dbms_redact.add_policy
3 ( object_schema=>user,
4 object_name=>'EMP',
5 policy_name=>'EMP_REDACT',
6 column_name=>'SALARY',
7 function_type=>dbms_redact.random,
8 expression=> q'|sys_context('userenv','current_user')!= 'RAJESH'|'
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
rajesh@PDB1>
rajesh@PDB1> begin
2 dbms_redact.alter_policy
3 ( object_schema=> user,
4 object_name=>'EMP',
5 policy_name=>'EMP_REDACT',
6 action=>dbms_redact.add_column,
7 column_name=>'HIRE_DATE',
8 function_type=>dbms_redact.partial,
9 function_parameters=>'MDy1990' );
10 end;
11 /
PL/SQL procedure successfully completed.
rajesh@PDB1> begin
2 dbms_redact.alter_policy
3 ( object_schema=> user,
4 object_name=>'EMP',
5 policy_name=>'EMP_REDACT',
6 action=>dbms_redact.add_column,
7 column_name=>'EMAIL_ID',
8 function_type=>dbms_redact.regexp,
9 regexp_pattern=>dbms_redact.RE_PATTERN_EMAIL_ADDRESS,
10 regexp_replace_string=>dbms_redact.RE_REDACT_EMAIL_NAME,
11 regexp_match_parameter=>dbms_redact.RE_CASE_INSENSITIVE );
12 end;
13 /
PL/SQL procedure successfully completed.
rajesh@PDB1>
rajesh@PDB1> select * from emp ;
EMPNO ENAME SALARY HIRE_DATE EMAIL_ID
---------- ---------- ---------- ----------------------- --------------------
1 John 1013 28-DEC-2013 04:10:34 PM John@example.com
2 Miller 1578 26-DEC-2013 04:10:34 PM miller@example.com
3 Kim 1398 25-DEC-2013 04:10:34 PM Kim@example.com
3 rows selected.
rajesh@PDB1>
When connected from Scott schema the redacted data look like this.
To set up a demonstration, uses the setup.sql script, then uses the code shown in below listing to set up a redaction policy on the EMP table.
Setup.sql
create table emp
( empno number,
ename varchar2(20),
salary number,
hire_date date,
email_id varchar2(30) ) ;
insert into emp(empno,ename,salary,hire_date,email_id)
values(1,'John',1013,sysdate,'John@example.com');
insert into emp(empno,ename,salary,hire_date,email_id)
values(2,'Miller',1578,sysdate-2,'miller@example.com');
insert into emp(empno,ename,salary,hire_date,email_id)
values(3,'Kim',1398,sysdate-3,'Kim@example.com');
commit;
rajesh@PDB1> select * from emp;
EMPNO ENAME SALARY HIRE_DATE EMAIL_ID
---------- -------------------- ---------- ----------------------- ----------------------
1 John 1013 28-DEC-2013 04:10:34 PM John@example.com
2 Miller 1578 26-DEC-2013 04:10:34 PM miller@example.com
3 Kim 1398 25-DEC-2013 04:10:34 PM Kim@example.com
3 rows selected.
rajesh@PDB1>
When the owner of the table "EMP" selects data, the results displays the values intact - but when any other user selects data from the table, the data must be masked.
|
Column
|
Description of redaction
|
|
Salary
|
replace with some random numbers and don't show actual values
|
|
Hire_date
|
show only the day and month year should be replaced with 1990
|
|
Email_id
|
Keep the domain name and rest others should be masked
|
rajesh@PDB1> grant select on emp to scott;
Grant succeeded.
rajesh@PDB1> begin
2 dbms_redact.add_policy
3 ( object_schema=>user,
4 object_name=>'EMP',
5 policy_name=>'EMP_REDACT',
6 column_name=>'SALARY',
7 function_type=>dbms_redact.random,
8 expression=> q'|sys_context('userenv','current_user')!= 'RAJESH'|'
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
rajesh@PDB1>
rajesh@PDB1> begin
2 dbms_redact.alter_policy
3 ( object_schema=> user,
4 object_name=>'EMP',
5 policy_name=>'EMP_REDACT',
6 action=>dbms_redact.add_column,
7 column_name=>'HIRE_DATE',
8 function_type=>dbms_redact.partial,
9 function_parameters=>'MDy1990' );
10 end;
11 /
PL/SQL procedure successfully completed.
rajesh@PDB1> begin
2 dbms_redact.alter_policy
3 ( object_schema=> user,
4 object_name=>'EMP',
5 policy_name=>'EMP_REDACT',
6 action=>dbms_redact.add_column,
7 column_name=>'EMAIL_ID',
8 function_type=>dbms_redact.regexp,
9 regexp_pattern=>dbms_redact.RE_PATTERN_EMAIL_ADDRESS,
10 regexp_replace_string=>dbms_redact.RE_REDACT_EMAIL_NAME,
11 regexp_match_parameter=>dbms_redact.RE_CASE_INSENSITIVE );
12 end;
13 /
PL/SQL procedure successfully completed.
rajesh@PDB1>
rajesh@PDB1> select * from emp ;
EMPNO ENAME SALARY HIRE_DATE EMAIL_ID
---------- ---------- ---------- ----------------------- --------------------
1 John 1013 28-DEC-2013 04:10:34 PM John@example.com
2 Miller 1578 26-DEC-2013 04:10:34 PM miller@example.com
3 Kim 1398 25-DEC-2013 04:10:34 PM Kim@example.com
3 rows selected.
rajesh@PDB1>
scott@PDB1> select * from rajesh.emp ;
EMPNO ENAME SALARY HIRE_DATE EMAIL_ID
---------- -------------------- ---------- ----------------------- -------------------
1 John 796 28-DEC-1990 04:10:34 PM xxxx@example.com
2 Miller 694 26-DEC-1990 04:10:34 PM xxxx@example.com
3 Kim 79 25-DEC-1990 04:10:34 PM xxxx@example.com
3 rows selected.
scott@PDB1>
Information's on redaction policies and policy columns are maintained in REDACTION_POLICIES and REDACTION_COLUMNS data dictionary.
rajesh@PDB1> @printtbl ' select * from redaction_policies '
OBJECT_OWNER : "RAJESH"
OBJECT_NAME : "EMP"
POLICY_NAME : "EMP_REDACT"
EXPRESSION : "sys_context('userenv','current_user')!= 'RAJESH'"
ENABLE : "YES "
POLICY_DESCRIPTION : ""
-----------------
PL/SQL procedure successfully completed.
rajesh@PDB1>
rajesh@PDB1>
rajesh@PDB1>
rajesh@PDB1> @printtbl ' select * from redaction_columns '
OBJECT_OWNER : "RAJESH"
OBJECT_NAME : "EMP"
COLUMN_NAME : "SALARY"
FUNCTION_TYPE : "RANDOM REDACTION"
FUNCTION_PARAMETERS : ""
REGEXP_PATTERN : ""
REGEXP_REPLACE_STRING : ""
REGEXP_POSITION : "0"
REGEXP_OCCURRENCE : "0"
REGEXP_MATCH_PARAMETER : ""
COLUMN_DESCRIPTION : ""
-----------------
OBJECT_OWNER : "RAJESH"
OBJECT_NAME : "EMP"
COLUMN_NAME : "EMAIL_ID"
FUNCTION_TYPE : "REGEXP REDACTION"
FUNCTION_PARAMETERS : ""
REGEXP_PATTERN : "([A-Za-z0-9._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})"
REGEXP_REPLACE_STRING : "xxxx@\2"
REGEXP_POSITION : "1"
REGEXP_OCCURRENCE : "0"
REGEXP_MATCH_PARAMETER : "i"
COLUMN_DESCRIPTION : ""
-----------------
OBJECT_OWNER : "RAJESH"
OBJECT_NAME : "EMP"
COLUMN_NAME : "HIRE_DATE"
FUNCTION_TYPE : "PARTIAL REDACTION"
FUNCTION_PARAMETERS : "MDy1990"
REGEXP_PATTERN : ""
REGEXP_REPLACE_STRING : ""
REGEXP_POSITION : "0"
REGEXP_OCCURRENCE : "0"
REGEXP_MATCH_PARAMETER : ""
COLUMN_DESCRIPTION : ""
-----------------
PL/SQL procedure successfully completed.
rajesh@PDB1>
However If the querying user has the EXEMPT REDACTION POLICY system privilege, redaction will not be performed.
rajesh@PDB1> connect sys/oracle@pdb1 as sysdba
Connected.
sys@PDB1> grant exempt redaction policy to scott;
Grant succeeded.
sys@PDB1> set timing off
sys@PDB1>
sys@PDB1>
sys@PDB1> grant exempt redaction policy to scott;
Grant succeeded.
sys@PDB1> connect scott/tiger@pdb1
Connected.
scott@PDB1>
scott@PDB1> select * from session_privs ;
PRIVILEGE
----------------------------------------
CREATE SESSION
EXEMPT REDACTION POLICY
2 rows selected.
scott@PDB1>
scott@PDB1> select * from rajesh.emp ;
EMPNO ENAME SALARY HIRE_DATE EMAIL_ID
---------- -------------------- ---------- ----------------------- -------------------
1 John 1013 28-DEC-2013 04:10:34 PM John@example.com
2 Miller 1578 26-DEC-2013 04:10:34 PM miller@example.com
3 Kim 1398 25-DEC-2013 04:10:34 PM Kim@example.com
3 rows selected.
scott@PDB1>
