Oracle 12c Data Redaction (Full, Partial, Random, Regular Expression) Implementation Examples

_____________________________________________________________________________________________________________________

Let’s first create a sample test table 'TEST_DATA' under REDACT schema.

SQL> grant EXECUTE ON sys.dbms_redact TO REDACT;

Grant succeeded.

SQL> grant connect, resource to REDACT;

Grant succeeded.

Sample table created with following data

SQL> col CARD_NO for 9999999999999999

   CUST_ID           CARD_NO CUS_EMAIL                      CARD_EXP_

---------- ----------------- ------------------------------ ---------

     10001  2345234523452345 xxxxxxx@abc.com                20-OCT-24

     10002  7800780078007800 xxxxxxx@xyz.com                24-JAN-22

     10003  6543654365436543 xxxxxxx@hyx.com                30-APR-19

Different types of redactions in tabular format

Type	Stored Data	Redacted Form
FULL	10/12/2014	1/1/2010
PARTIAL	345-4321-5462	XXX-XXXX-5462
REGULAR EXP	First.Last@xyz.com	[HIDE]@xyz.com
RANDOM	301101601201	100110210310

1.     FULL REDACTION

In full redaction, the card number modified to 0 after applying the policy. Fuction_type parameter is highlighted.

BEGIN

   DBMS_REDACT.ADD_POLICY(

     object_schema        => 'REDACT',

     object_name          => 'TEST_DATA',

     column_name          => 'CARD_NO',

     policy_name          => 'test_card_no',

     function_type        => DBMS_REDACT.FULL,

     expression           => '5=5');

END;

/

SQL> SELECT cust_id, card_no, cus_email FROM TEST_DATA;

   CUST_ID           CARD_NO CUS_EMAIL

---------- ----------------- ------------------------------

     10001                 0 scott@abc.com

     10002                 0 kester@xyz.com

     10003                 0 peter@hyx.com

2.     PARTIAL REDACTION

Example 1: Partial Redaction on number values

In Partial Redaction, the first 12 digits of the credit card numbers are masked to number 4. We can mention the function_parameters to achieve this results.

BEGIN

   DBMS_REDACT.ALTER_POLICY(

     object_schema        => 'REDACT',

     object_name          => 'TEST_DATA',

     column_name          => 'CARD_NO',

     policy_name          => 'test_card_no',

     action               => DBMS_REDACT.modify_column,

     function_type        => DBMS_REDACT.PARTIAL,

     function_parameters  => '4,1,12');

END;

/

PL/SQL procedure successfully completed.

SQL> COLUMN card_no FORMAT 9999999999999999

SQL> SELECT cust_id, card_no, cus_email FROM TEST_DATA;

   CUST_ID           CARD_NO CUS_EMAIL

---------- ----------------- ------------------------------

     10001  4444444444442345 scott@abc.com

     10002  4444444444447800 kester@xyz.com

     10003  4444444444446543 peter@hyx.com

Example 2 – Partial Redaction to date data types

In this example the card expiry dates are modified to 15th Dec along with actual year of expiry.

BEGIN

   DBMS_REDACT.ALTER_POLICY(

     object_schema        => 'REDACT',

     object_name          => 'TEST_DATA',

     column_name          => 'CARD_EXP_DT',

     policy_name          => 'test_card_no',

     action               => DBMS_REDACT.add_column,

     function_type        => DBMS_REDACT.PARTIAL,

     function_parameters  => 'm12d15Y');

END;

/

PL/SQL procedure successfully completed.

SQL> select * from test_data;

   CUST_ID           CARD_NO CUS_EMAIL                      CARD_EXP_

---------- ----------------- ------------------------------ ---------

     10001  4444444444442345 scott@abc.com                  15-DEC-24

     10002  4444444444447800 kester@xyz.com                 15-DEC-22

     10003  4444444444446543 peter@hyx.com                  15-DEC-19

3.     RANDOM REDACTION

In Random Redaction the card numbers are transformed to entirely different card numbers as random number.

BEGIN

   DBMS_REDACT.ALTER_POLICY(

     object_schema        => 'REDACT',

     object_name          => 'TEST_DATA',

     column_name          => 'CARD_NO',

     policy_name          => 'test_card_no',

     action               => DBMS_REDACT.modify_column,

     function_type        => DBMS_REDACT.random);

END;

/

PL/SQL procedure successfully completed.

SQL>

SQL> select * from test_data;

   CUST_ID           CARD_NO CUS_EMAIL                      CARD_EXP_

---------- ----------------- ------------------------------ ---------

     10001   387894244881085 scott@abc.com                  15-DEC-24

     10002  2331653517751192 kester@xyz.com                 15-DEC-22

     10003  3856235911291399 peter@hyx.com                  15-DEC-19

4.     REGULAR EXPRESSION

In this example the customer email address is getting transformed to xxxxxx@domain.com using the regular expression redaction.

BEGIN

    DBMS_REDACT.ADD_POLICY (

           object_schema          => 'REDACT',

           object_name            => 'TEST_DATA',

           policy_name            => 'test_card_no',

           column_name            => 'CUS_EMAIL',

           function_type          => DBMS_REDACT.REGEXP,

           expression             => '1=1',

           enable                 => TRUE,

           regexp_pattern         => '(.){2,}\@([[:alnum:]])',

           regexp_replace_string  => 'xxxxxxx@\2',

           regexp_position        => '1',

           regexp_occurrence      => '0',

           regexp_match_parameter => 'i'

   );

END;

/

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TEST_DATA;

SQL> COL CUS_EMAIL FOR A30

SQL> /

   CUST_ID           CARD_NO CUS_EMAIL                      CARD_EXP_

---------- ----------------- ------------------------------ ---------

     10001  2345234523452345 xxxxxxx@abc.com                20-OCT-24

     10002  7800780078007800 xxxxxxx@xyz.com                24-JAN-22

     10003  6543654365436543 xxxxxxx@hyx.com                30-APR-19

_____________________________________________________________________________________________________________________