Toad World

Configure Data Redaction on Oracle

Avatar photo
Post author:Written By

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

 
Tags:

About the Author

Skant Gupta

Skant Gupta is an Oracle Certified Cloud Professional in Oracle Database 12c, an Oracle Certified Expert in Oracle Real Application Clusters (Oracle RAC) in Oracle Database 11g and 12c, and an Oracle Exadata Certified and an Oracle Certified Professional in Oracle Database 10g, 11g, and 12c. He works at Vodafone Technology in the UK and formerly worked as a senior DBA at Etisalat in Dubai. He has six years of experience with various Oracle technologies, focusing mainly on Cloud, database, and high availability solutions, Oracle WebLogic Suite, Oracle Exadata and Oracle GoldenGate. He has presented at several Oracle user groups worldwide, most recently in the US, the United Arab Emirates, and the India. He is also Technical Writer on http://www.oracle.com/technetwork/es/articles/index.html and http://www.oracle.com/technetwork/pt/articles/index.html with more than 30 article published in Oracle Technical Network.

Start the discussion at forums.toadworld.com