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
[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

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google