Configure Data Redaction on Oracle

In previous article we provide the brief introduction about Data Redaction on Oracle. Now in this post, we are going to configure different type of Data Redaction in Oracle 12c.

Full Data Redaction
Partial Data Redaction
Random Data Redaction
Data Redaction with REGEXP

For the demonstration on this I have a table that holds credit card information and I want to redact the card number in that table.

OBJECT_SCHEMA: REDACT_USER
OBJECT_TABLE: credit_card_detail
COLUMN_NAME: card_no

Now we are going to create user, table and load some data on it.

SQL> alter session set container =PDB1;

Session altered.

SQL> create user redact_user identified by redact_user;

User created.

Now Connect with ‘redact_user‘ user to create the table

[oracle@DBtest opc]$ sqlplus redact_user/redact_user@pdb1

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 10 16:42:26 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> sho user
USER is "REDACT_USER"

SQL> create table credit_card_detail
(
customer_id number(10) GENERATED ALWAYS AS IDENTITY START WITH 1000,
start_date date NOT NULL,
card_no number(16) NOT NULL,
exp_date date NOT NULL
);

SQL> insert into credit_card_detail(start_date,card_no,exp_date) values (sysdate,1285145836589848,TRUNC(ADD_MONTHS(SYSDATE,36)));

SQL> insert into credit_card_detail(start_date,card_no,exp_date) values (sysdate,7844896487984154,TRUNC(ADD_MONTHS(SYSDATE,36)));

SQL> insert into credit_card_detail(start_date,card_no,exp_date) values (sysdate,8554884663181228,TRUNC(ADD_MONTHS(SYSDATE,36)));

SQL> insert into credit_card_detail(start_date,card_no,exp_date) values (sysdate,9487545796548985,TRUNC(ADD_MONTHS(SYSDATE,36)));

SQL> commit;

SQL> set lines 200
SQL> col card_no for 9999999999999999
SQL> select * from credit_card_detail;

CUSTOMER_ID  START_DAT CARD_NO           EXP_DATE
----------- --------- ----------------- ---------
1000        10-AUG-17 1285145836589848  10-AUG-20
1001        10-AUG-17 7844896487984154  10-AUG-20
1002        10-AUG-17 8554884663181228  10-AUG-20
1003        10-AUG-17 9487545796548985  10-AUG-20

Full Data Redaction

Mow we are going to add a data redaction policy to redact the CARD_NO column of CREDIT_CARD_DETAIL table.
I will make use of the FUNCTION_TYPE called DBMS_REDACT.
FULL to redact CARD_NO values to a static value, every time it is being queried using the data redact event EXPRESSION ‘1=1’.

SQL> sho user
USER is "SYS"
SQL> BEGIN
DBMS_REDACT.add_policy(
object_schema => 'REDACT_USER',
object_name => 'credit_card_detail',
column_name => 'card_no',
policy_name => 'redact_card_no',
function_type => DBMS_REDACT.full,
expression => '1=1'
);
END;
/
PL/SQL procedure successfully completed.

Now connect with ‘redact_user‘ user to check the table.

[oracle@DBtest ~]$ sqlplus redact_user/redact_user@pdb1

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 10 16:50:10 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Thu Aug 10 2017 16:49:05 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from credit_card_detail;

CUSTOMER_ID START_DAT CARD_NO    EXP_DATE
----------- --------- ---------- ---------
1000        10-AUG-17  0         10-AUG-20
1001        10-AUG-17  0         10-AUG-20
1002        10-AUG-17  0         10-AUG-20
1003        10-AUG-17  0         10-AUG-20

In FULL data redact policy, the column data gets redacted to a static value (by default 0).We can view default static values for FULL redaction policy by querying REDACTION_VALUES_FOR_TYPE_FULL view.

SQL> select NUMBER_VALUE from REDACTION_VALUES_FOR_TYPE_FULL;

NUMBER_VALUE
------------
0

Partial Data Redaction

Lets say I want to mask the first 9 characters of the 16 digit card number instead of completely making the data. We can opt for PARTIAL data redact policy for this purpose.

Lets alter the existing data redact policy for CARD_NO using ALTER_POLICY procedure of DBMS_REDACT package from FULL to PARTIAL.

SQL> sho user
USER is "SYS"
SQL> BEGIN
DBMS_REDACT.alter_policy(
object_schema => 'REDACT_USER',
object_name => 'credit_card_detail',
column_name => 'card_no',
policy_name => 'redact_card_no',
action => DBMS_REDACT.modify_column,
function_type => DBMS_REDACT.partial,
function_parameters => '9,1,9'
);
END;
/

Now connect with ‘redact_user‘ user to check the partial redaction.

[oracle@DBtest ~]$ sqlplus redact_user/redact_user@pdb1

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 10 16:50:10 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Thu Aug 10 2017 16:49:05 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from credit_card_detail;

CUSTOMER_ID START_DAT CARD_NO            EXP_DATE
----------- --------- ------------------ ---------
1000        10-AUG-17 9999999996589848   10-AUG-20
1001        10-AUG-17 9999999997984154   10-AUG-20
1002        10-AUG-17 9999999993181228   10-AUG-20
1003        10-AUG-17 9999999996548985   10-AUG-20

As expected, in this partial redaction the first 9 digits in the CARD_NO are masked with the number 9.

Random Data Redaction

In RANDOM data redact policy, the column data would be redacted to a random value each time it is being queried. Lets alter our existing data redact policy from PARTIAL to RANDOM using ALTER_POLICY procedure of DBMS_REDACT package.

BEGIN
DBMS_REDACT.alter_policy(
object_schema => 'REDACT_USER',
object_name => 'credit_card_detail',
column_name => 'card_no',
policy_name => 'redact_card_no',
action => DBMS_REDACT.modify_column,
function_type => DBMS_REDACT.random
);
END;
/

Now connect with ‘redact_user‘ user to check the random redaction.

[oracle@DBtest ~]$ sqlplus redact_user/redact_user@pdb1

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 10 16:50:10 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Thu Aug 10 2017 16:49:05 +00:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from credit_card_detail;

CUSTOMER_ID START_DAT CARD_NO            EXP_DATE
----------- --------- ------------------ ---------
1000        10-AUG-17 1103216213352204   10-AUG-20
1001        10-AUG-17 4815184172022361   10-AUG-20
1002        10-AUG-17 1527880276976492   10-AUG-20
1003        10-AUG-17 4777571877322035   10-AUG-20

Now we apply the redaction on other users only.

In this case we can exclude the redact_user from the policy.

SQL> BEGIN
DBMS_REDACT.alter_policy(
object_schema => 'REDACT_USER',
object_name => 'credit_card_detail',
column_name => 'card_no',
policy_name => 'redact_card_no',
action => DBMS_REDACT.modify_expression,
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''REDACT_USER'''
);
END;
/

Now connect with ‘redact_user‘ user to check the random redaction.

[oracle@DBtest ~]$ sqlplus redact_user/redact_user@pdb1

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 10 16:50:10 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Thu Aug 10 2017 16:49:05 +00:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from credit_card_detail;

CUSTOMER_ID START_DAT CARD_NO               EXP_DATE
----------- --------- --------------------- ---------
1000        10-AUG-17 1285145836589848      10-AUG-20
1001        10-AUG-17 7844896487984154      10-AUG-20
1002        10-AUG-17 8554884663181228      10-AUG-20
1003        10-AUG-17 9487545796548985      10-AUG-20

Now connect with ‘hr’ user to check the random redaction.

[oracle@DBtest ~]$ sqlplus hr/hr@pdb1

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 10 16:50:10 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Thu Aug 10 2017 16:49:05 +00:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from REDACT_USER.credit_card_detail;

CUSTOMER_ID START_DAT CARD_NO            EXP_DATE
----------- --------- ------------------ ---------
1000        10-AUG-17 15862467982140     10-AUG-20
1001        10-AUG-17 58495063936495     10-AUG-20
1002        10-AUG-17 4544064994617337   10-AUG-20
1003        10-AUG-17 7095389847886435   10-AUG-20

Add new column in Redaction Policy

BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'REDACT_USER',
object_name => 'credit_card_detail',
policy_name => 'redact_card_no',
action => DBMS_REDACT.add_column,
column_name => 'exp_Date',
function_type => DBMS_REDACT.partial,
function_parameters => 'm1d1Y'
);
END;
/

In above situation , data is redacted on exp_date column for other users.

Now connect with ‘redact_user‘ user to check the random redaction.

[oracle@DBtest ~]$ sqlplus redact_user/redact_user@pdb1

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 10 16:50:10 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Thu Aug 10 2017 16:49:05 +00:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from credit_card_detail;

CUSTOMER_ID START_DAT CARD_NO               EXP_DATE
----------- --------- --------------------- ---------
1000        10-AUG-17 1285145836589848      10-AUG-20
1001        10-AUG-17 7844896487984154      10-AUG-20
1002        10-AUG-17 8554884663181228      10-AUG-20
1003        10-AUG-17 9487545796548985      10-AUG-20

Now connect with ‘hr’ user to check the random redaction.

[oracle@DBtest ~]$ sqlplus hr/hr@pdb1

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 10 16:50:10 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Thu Aug 10 2017 16:49:05 +00:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from REDACT_USER.credit_card_detail;

CUSTOMER_ID START_DAT CARD_NO               EXP_DATE
----------- --------- --------------------- ---------
1000        10-AUG-17 869906673046315       01-JAN-20
1001        10-AUG-17 7519630252957998      01-JAN-20
1002        10-AUG-17 7208975687465749      01-JAN-20
1003        10-AUG-17 474910076820871       01-JAN-20

Checking data redaction

SQL> SELECT object_owner,object_name,function_type,function_parameters FROM redaction_columns;

OBJECT_OWNER  OBJECT_NAME         FUNCTION_TYPE     FUNCTION_PARAMETERS
------------- ------------------- ----------------- --------------------
REDACT_USER   CREDIT_CARD_DETAIL  RANDOM REDACTION
REDACT_USER   CREDIT_CARD_DETAIL  PARTIAL REDACTION m1d1Y

SQL> SELECT object_owner,object_name,policy_name,expression,enable FROM redaction_policies;

OBJECT_OWNER OBJECT_NAME        POLICY_NAME    EXPRESSION                ENA
------------ ------------------ -------------- -------------------------- 
REDACT_USER  CREDIT_CARD_DETAIL redact_card_no SYS_CONTEXT('USERENV','SESSION_USER') != 'REDACT_USER' YES

Remove Data Redaction

BEGIN
DBMS_REDACT.drop_policy (
object_schema => 'REDACT_USER',
object_name => 'credit_card_detail',
policy_name => 'redact_card_no'
);
END;
/

Source

Comments


Manjunath September 7, 2017 at 2:31 am

Does Redaction come with enterprise edition license or will it require extra licensing on top of enterprise edition?
1. Reply
  
  Skant Gupta Article Author September 7, 2017 at 2:46 am
  
  Please go through the below link
  
  http://www.oracle.com/technetwork/database/options/data-masking-subsetting/learnmore/faq-security-asdr-external-3215961.pdf

Configure Data Redaction on Oracle

Full Data Redaction

Partial Data Redaction

Random Data Redaction

Like this:

Comments

Leave a Reply Cancel reply

Oracle Help

Follow Us

Full Data Redaction

Partial Data Redaction

Random Data Redaction

Share this:

Like this:

Related Articles

ASMCMD-8102: no connection to ASM; command requires ASM to run

CHM (Cluster Health Monitor)

LREG New BG Process In 12c

ora-02097 parameter cannot be modified because specified value is invalid

Downgrade Oracle Database from 12c to 11gR2

Connecting PDB in Oracle 12c

Comments

Leave a Reply Cancel reply

Tags

Oracle Help

Follow Us