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; /
Does Redaction come with enterprise edition license or will it require extra licensing on top of enterprise edition?
Please go through the below link
http://www.oracle.com/technetwork/database/options/data-masking-subsetting/learnmore/faq-security-asdr-external-3215961.pdf