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

advertisements

 

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

_____________________________________________________________________________________________________________________

Website Stats