Configure Data Redaction on Oracle

    Aug 18, 2017 5:43:09 AM by Skant Gupta

    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: Oracle

    Skant Gupta

    Written by 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.