Redaction is backported 11g
CT, May 01, 2016 - 1:11 pm UTC
Hi Connor,
Thank you so much for taking time to answer the question. The answer is interesting, perfect and will work for the scenario.
Data redaction is available in 11g as it is back ported. I am wondering can we use the redaction policy to achieve this. I am able to do only one type of expression meaning I can switch on redaction for certain roles and switch off for the rest.
Thanks again
CT
May 02, 2016 - 3:04 am UTC
(Assuming you're on 11.2.0.4 *and* have the advanced security license) you could use DBMS_REDACT.
The way you achieve multiple redaction policies is to create views on the table, and add the policies to those.
Hope this helps.
Column level VPD
Rajeshwaran Jeyabal, May 02, 2016 - 9:58 am UTC
Is that a column level VPD doesn't help? something like this.
rajesh@ORA11G> create table t as
2 select owner,object_type,object_id,
3 to_char(object_id,'fm0000000000') as SSN
4 from all_objects
5 where rownum <=3;
Table created.
rajesh@ORA11G> create or replace view t_vw as
2 select owner,object_type,object_id,
3 case when sys_context('MYCTX','role_name')='MANAGER' then
4 ssn
5 when sys_context('MYCTX','role_name')='SUPERVISOR' then
6 'XXX-XXXX-'||substr(ssn,-3)
7 else null end ssn
8 from t;
View created.
rajesh@ORA11G> select * from t ;
OWNER OBJECT_TYP OBJECT_ID SSN
-------- ---------- ---------- -----------
SYS TABLE 20 0000000020
SYS INDEX 46 0000000046
SYS TABLE 28 0000000028
3 rows selected.
rajesh@ORA11G> select * from t_vw;
OWNER OBJECT_TYP OBJECT_ID SSN
-------- ---------- ---------- ------------
SYS TABLE 20
SYS INDEX 46
SYS TABLE 28
3 rows selected.
rajesh@ORA11G> create or replace function foo(p_schema_name in varchar2,
2 p_object_name in varchar2)
3 return varchar2 as
4 begin
5 if p_schema_name = user then
6 return '1=1';
7 else
8 return '1=0' ;
9 end if;
10 end;
11 /
Function created.
rajesh@ORA11G> begin
2 dbms_rls.add_policy
3 (object_schema=>user,
4 object_name=>'T_VW',
5 policy_name=>'SSN_HIDE',
6 function_schema=>user,
7 policy_function=>'FOO',
8 statement_types=>'SELECT',
9 sec_relevant_cols=>'SSN',
10 sec_relevant_cols_opt=>dbms_rls.all_rows);
11 end;
12 /
PL/SQL procedure successfully completed.
rajesh@ORA11G> create or replace context MYCTX using p;
Context created.
rajesh@ORA11G> create or replace procedure p(x varchar2)
2 as
3 begin
4 dbms_session.set_context('MYCTX','role_name',x);
5 end;
6 /
Procedure created.
rajesh@ORA11G>
rajesh@ORA11G> exec p('MANAGER');
PL/SQL procedure successfully completed.
rajesh@ORA11G> select * from t_vw;
OWNER OBJECT_TYP OBJECT_ID SSN
-------- ---------- ---------- ------------
SYS TABLE 20 0000000020
SYS INDEX 46 0000000046
SYS TABLE 28 0000000028
3 rows selected.
rajesh@ORA11G> exec p('SUPERVISOR');
PL/SQL procedure successfully completed.
rajesh@ORA11G> select * from t_vw;
OWNER OBJECT_TYP OBJECT_ID SSN
-------- ---------- ---------- ------------
SYS TABLE 20 XXX-XXXX-020
SYS INDEX 46 XXX-XXXX-046
SYS TABLE 28 XXX-XXXX-028
3 rows selected.
rajesh@ORA11G> exec p(null);
PL/SQL procedure successfully completed.
rajesh@ORA11G> select * from t_vw;
OWNER OBJECT_TYP OBJECT_ID SSN
-------- ---------- ---------- ------------
SYS TABLE 20
SYS INDEX 46
SYS TABLE 28
3 rows selected.
rajesh@ORA11G>
Thank you
CT, May 02, 2016 - 2:40 pm UTC
Thank you Connor and Rajeshwaran. I think applying data redaction through tables and view combination would be the way to go.
Thank you both for taking time to answer.
Cheers
CT
generate xml using redact column
Rahul Saxena, November 04, 2022 - 10:00 am UTC
Hi All,
I have applied dbms_redact on ename column. But i have to generate xml based on ename column as per following queries however its giving error. Please help me how to resolve.(version Oracle 12 c 2)
SELECT XMLElement("emp", e.ename ) FROM emp e;
Error code: ORA-28093
Description: operation on column string is not supported by data redaction
Thank you in advance.
November 09, 2022 - 2:12 am UTC
Unfortunately that is "by design" but there is an outstanding enhancement request to remove this limitation.
I don't know of workaround without doing the redaction yourself directly in the SQL.
Sorry.