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>
Hi Rajesh,
ReplyDeleteCan we create a script to create the policy for first table-first colmn and then alter policy for first table-second column?
Like
if exists ( select object_name from redaction_policies where object_owner='RAJESH' and object_name=upper('&1'))
select distinct 'BEGIN DBMS_REDACT.ALTER_POLICY(......)
else select distinct 'BEGIN DBMS_REDACT.ADD_POLICY (....)
Thanks!
@Anonymous:
ReplyDeleteAre you looking for something like this.
rajesh@ORA11G> create table emp(empno number,salary number,email_id varchar2(10),hire_date date);
Table created.
rajesh@ORA11G>
rajesh@ORA11G> begin
2 dbms_redact.add_policy
3 (object_schema=>user,
4 object_name=>'EMP',
5 policy_name=>'EMP_PLCY',
6 column_name=>'SALARY',
7 function_type=>dbms_redact.RANDOM,
8 expression=> q'| sys_context('userenv','current_user') != 'RAJESH'|' );
9 end;
10 /
PL/SQL procedure successfully completed.
rajesh@ORA11G> select object_owner,object_name,
2 column_name, function_type
3 from redaction_columns ;
OBJECT_OWN OBJECT_NAM COLUMN_NAM FUNCTION_TYPE
---------- ---------- ---------- ---------------------------
RAJESH EMP SALARY RANDOM REDACTION
1 row selected.
rajesh@ORA11G> declare
2 l_policy_exists exception;
3 l_policy_exists_on_column exception;
4 pragma exception_init(l_policy_exists,-28069);
5 pragma exception_init(l_policy_exists_on_column,-28060);
6 begin
7 for x in ( select * from user_tab_columns
8 where table_name ='EMP'
9 and column_name in ('SALARY','EMAIL_ID','HIRE_DATE')
10 order by column_ID )
11 loop
12 begin
13 dbms_redact.add_policy
14 (object_schema=>user,
15 object_name=>'EMP',
16 policy_name=>'EMP_PLCY',
17 column_name=> x.column_name,
18 function_type=>dbms_redact.RANDOM,
19 expression=> q'| sys_context('userenv','current_user') != 'RAJESH'|' );
20
21 exception
22 when l_policy_exists_on_column then
23 null ;
24 when l_policy_exists then
25 begin
26 dbms_redact.alter_policy
27 ( object_schema=>user,
28 object_name=>'EMP',
29 policy_name=>'EMP_PLCY',
30 action=>dbms_redact.add_column,
31 column_name=> x.column_name,
32 function_type=>dbms_redact.RANDOM,
33 expression=> q'| sys_context('userenv','current_user') != 'RAJESH'|' );
34 exception
35 when l_policy_exists_on_column then null ;
36 end;
37
38 end;
39 end loop;
40 end;
41 /
PL/SQL procedure successfully completed.
rajesh@ORA11G> select object_owner,object_name,
2 column_name, function_type
3 from redaction_columns;
OBJECT_OWN OBJECT_NAM COLUMN_NAM FUNCTION_TYPE
---------- ---------- ---------- ---------------------------
RAJESH EMP HIRE_DATE RANDOM REDACTION
RAJESH EMP SALARY RANDOM REDACTION
RAJESH EMP EMAIL_ID RANDOM REDACTION
3 rows selected.
rajesh@ORA11G>
Hi Rajesh,
ReplyDeleteCan we create a data redaction policy on a column if a specific value match specific condition? for example:
table stock has (stock name, stock number , department)
I need to create a data redaction on column (stock number) if the (department) is IT only.
Is there anyway to do it?
Regards,
Bhushan
@Anonymous:
DeletePolicy to be applied for the entire column, not to the specific sets.
rajesh@ORA11G> select * from t;
STOCK_NUM STOCK_NAME DEPT_NAME
---------- ------------------------------ ----------
1 ICOL$ IT
2 I_USER1 I_USER1
3 CON$ CON$
4 UNDO$ UNDO$
5 C_COBJ# C_COBJ#
rajesh@ORA11G> begin
2 dbms_redact.add_policy
3 (object_schema=>user,
4 object_name=>'T',
5 policy_name=>'T_PLCY1',
6 policy_description=>'Redact_stock_num',
7 column_name=>'STOCK_NUM',
8 expression=>q'#dept_name<>'IT'#' );
9 end;
10 /
begin
*
ERROR at line 1:
ORA-28075: The policy expression has unsupported functions.
ORA-06512: at "SYS.DBMS_REDACT_INT", line 3
ORA-06512: at "SYS.DBMS_REDACT", line 40
ORA-06512: at line 2
Hi Rajesh,
ReplyDeleteCan we create a redaction policy to redact all digits except last two digits on a variable length field.
I am trying to create a policy on bank account number, each bank have different length restriction.
@Anonymous - Please check if this helps.
Deleterajesh@ORA11G> set feedback off
rajesh@ORA11G> drop table t purge;
rajesh@ORA11G> create table t(bank_acc varchar2(25));
rajesh@ORA11G> insert into t
2 select dbms_random.string('x',20)
3 from dual
4 connect by level <= 5;
rajesh@ORA11G> commit;
rajesh@ORA11G> set feedback on
rajesh@ORA11G> select * from t;
BANK_ACC
-------------------------
FM9PQ4IW58DA8PDD747K
411SN5CT9MWN2R2YJUX6
7WS2W6OZAK08JL73RMJZ
QEJ70F8SJAJF0ZM08QRX
PD1T73QTDLYA8HD7B1UF
5 rows selected.
rajesh@ORA11G> begin
2 dbms_redact.add_policy
3 (object_schema=>user,
4 object_name=>'T',
5 column_name=>'BANK_ACC',
6 policy_name=>'BANK_ACC_CHK',
7 function_type=>DBMS_REDACT.REGEXP,
8 expression=> q'| sys_context('userenv','current_user') != 'RAJESH'|' ,
9 regexp_pattern=>'^(.*)(..)$',
10 regexp_replace_string=>'XXXX-XXXX-XXX-\2') ;
11 end;
12 /
PL/SQL procedure successfully completed.
rajesh@ORA11G> select * from t;
BANK_ACC
--------------------------------
FM9PQ4IW58DA8PDD747K
411SN5CT9MWN2R2YJUX6
7WS2W6OZAK08JL73RMJZ
QEJ70F8SJAJF0ZM08QRX
PD1T73QTDLYA8HD7B1UF
5 rows selected.
rajesh@ORA11G> grant select on t to scott;
Grant succeeded.
rajesh@ORA11G> conn scott/tiger@ora11g
Connected.
scott@ORA11G> set timing off
scott@ORA11G> select * from rajesh.t;
BANK_ACC
--------------------------------------
XXXX-XXXX-XXX-7K
XXXX-XXXX-XXX-X6
XXXX-XXXX-XXX-JZ
XXXX-XXXX-XXX-RX
XXXX-XXXX-XXX-UF
5 rows selected.
scott@ORA11G>
Hi Rajesh great article! I need some help can you give an example using OS USER. I can't seem to get it to work.
ReplyDeleteCheers
David
@ David - you mean "Externally identified" users?
Deleteare you looking something like this ?
rajesh@ORA11G> show parameter os_aut
NAME TYPE VALUE
--------------------------------------------- ----------- --------------
os_authent_prefix string OPS$
remote_os_authent boolean FALSE
rajesh@ORA11G>
rajesh@ORA11G> create user "OPS$CTS\179818" identified externally;
User created.
rajesh@ORA11G> grant create session to "OPS$CTS\179818" ;
Grant succeeded.
rajesh@ORA11G> CONN /@ora11g
Connected.
ops$cts\179818@ORA11G>
ops$cts\179818@ORA11G>
Hi Rajesh,
ReplyDeleteI have below requirement can it be done via VPD or someother mechanism.
ACCT_TYPE ACCT_ID
-------------------- ------------------------
EE 100001702143
EE 100000091326
SBA 005010100117295
SBA 31641350321
CC 4893771231610001
CC 4511230001711401
we want to mask ACCT_ID when ACCT_TYPE is CC
Below is the Desired masking ouput
ACCT_TYPE ACCT_ID
-------------------- ------------------------
EE 100001703143
EE 100000091326
SBA 005010100117295
SBA 31641350321
CC 4893XXXXXXX
CC 4511XXXXXXX
Regards,
Manoj
@Manoj - VPD / Data redaction applies at the entire set, not on the specific set like this. can this be possible ?
Delete1) create a view (v1) as where acct_type ='CC' and define VPD on it. (to mask account_id)
2) create a view (v2) as where acct_type <> 'CC'
3) create a view (v3) as V1 union all V2 - and expose this view to end users.
Thanks Rajesh,
DeleteWell application team does not want views , as the code base is large and they do know the impact.
@Manoj,
Deletea) create a view that select out acct_id as substr(acct_id,1,4)||'XXXXX' when acc_type='CC' else acct_id as new_acc_ids.
b) Defind VPD on this view "new_acc_ids" columns.
c) the users should select this view as nvl(acct_id,new_acc_ids);
rajesh@ORA10G> set feedback off
rajesh@ORA10G> drop table t purge;
rajesh@ORA10G> create table t(acc_type varchar2(3), acct_id varchar2(20));
rajesh@ORA10G> insert into t values('EE',100001702143);
rajesh@ORA10G> insert into t values('EE',100000091326);
rajesh@ORA10G> insert into t values('SBA',005010100117295);
rajesh@ORA10G> insert into t values('SBA',31641350321);
rajesh@ORA10G> insert into t values('CC',4893771231610001);
rajesh@ORA10G> insert into t values('CC',4511230001711401);
rajesh@ORA10G> commit;
rajesh@ORA10G> select * from t;
ACC ACCT_ID
--- --------------------
EE 100001702143
EE 100000091326
SBA 5010100117295
SBA 31641350321
CC 4893771231610001
CC 4511230001711401
rajesh@ORA10G> create or replace view v
2 as
3 select acc_type,acct_id,
4 decode(acc_type,'CC',
5 substr(acct_id,1,4)||'XXXXXX',
6 acct_id) fn_acct_id
7 from t;
rajesh@ORA10G> select * from v;
ACC ACCT_ID FN_ACCT_ID
--- -------------------- --------------------
EE 100001702143 100001702143
EE 100000091326 100000091326
SBA 5010100117295 5010100117295
SBA 31641350321 31641350321
CC 4893771231610001 4893XXXXXX
CC 4511230001711401 4511XXXXXX
rajesh@ORA10G> create or replace function foo(p_owner varchar2,p_name varchar2)
2 return varchar2 as
3 begin
4 if user ='RAJESH' then
5 return '1=1' ;
6 else
7 return '1=0';
8 end if;
9 end;
10 /
rajesh@ORA10G>
rajesh@ORA10G> begin
2 dbms_rls.add_policy(object_schema=>user,
3 object_name=>'V',
4 policy_name=>'V_PLCY',
5 function_schema=>user,
6 policy_function=>'FOO',
7 sec_relevant_cols=>'ACCT_ID',
8 sec_relevant_cols_opt=>dbms_rls.all_rows);
9 end;
10 /
rajesh@ORA10G>
rajesh@ORA10G> select * from v;
ACC ACCT_ID FN_ACCT_ID
--- -------------------- --------------------
EE 100001702143 100001702143
EE 100000091326 100000091326
SBA 5010100117295 5010100117295
SBA 31641350321 31641350321
CC 4893771231610001 4893XXXXXX
CC 4511230001711401 4511XXXXXX
rajesh@ORA10G> grant select on v to scott;
rajesh@ORA10G> conn scott/tiger@ora10g
Connected.
scott@ORA10G> select * from rajesh.v;
ACC A FN_ACCT_ID
--- - --------------------
EE 100001702143
EE 100000091326
SBA 5010100117295
SBA 31641350321
CC 4893XXXXXX
CC 4511XXXXXX
6 rows selected.
scott@ORA10G> select acc_type, nvl(acct_id,fn_acct_id) as new_acc_ids
2 from rajesh.v;
ACC NEW_ACC_IDS
--- --------------------
EE 100001702143
EE 100000091326
SBA 5010100117295
SBA 31641350321
CC 4893XXXXXX
CC 4511XXXXXX
6 rows selected.
scott@ORA10G>
Thanks for sharing this great information I am impressed by the information that you have on this blog. Same as your blog i found another one Oracle Fusion Financials , Oracle Project Portfolio Management (PPM) Cloud Tutorial and Oracle Fusion Procurement . Actually I was looking for the same information on internet for Oracle Fusion Manufacturing , Oracle APEX , Oracle Financials Cloud and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can check more aboutOracle Fusion Supply Chain Management Cloud , Oracle HCM Cloud , Oracle Project Portfolio Management (PPM) Cloud , Oracle Cloud Applications to better understand
ReplyDelete