Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, CT.

Asked: April 30, 2016 - 11:16 pm UTC

Last updated: November 09, 2022 - 2:12 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hi,
Greetings.
I would like to apply data redaction policy on a table to accomplish the following business requirement.
The table contains PII filed SSN. The security(Data Redaction Policy) should be defined as below.

1. Person with Manager Role can see all the parts of SSN.( No redaction).
2. Person with Supervisor Role can see the last 4 digits SSN.( partial redaction).
3. Person with call center representative role can see nothing.( Full redaction).

From looking through the documentation and examples out in the web world, I understand we can apply redaction policy like a switch on for particualr set of roles and off for the rest of the roles.

Any suggestions and Guidance on how we can implement the above security in place would be highly appreciated.

Thank you so much for your time.

Sincerely
CT

and Connor said...

Probably the best way - look at upgrading to 12c, where data refaction facilities are part of the database engine itself.

https://docs.oracle.com/database/121/ASOAG/redaction.htm

In 11g, I'd look at

a) create a context variable that is set at login to what the person's role is
b) create a view which uses that variable to decide on the redaction level
c) disable direct table access from queries via standard Oracle security

So your view would look something like (pseudo-code):

select
  col1,
  col2,
  ...
  case 
    when sys_context('MY_CONTEXT','MY_ROLE') = 'Manager' then col_ssn
    when sys_context('MY_CONTEXT','MY_ROLE') = 'Supervisor' then '****'||substr(col_ssn,length(col_ssn)-4)
    else
       null
    end redacted_ssn
from ...



Hope this helps.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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
Connor McDonald
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.
Connor McDonald
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.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.