Creating Partial Redaction Policy – Data Redaction Part-IV

Published by Jignesh Jethwa
Oracle 12c Logo

Here we are going to discuss Partial Redaction Policy with following points where as in my earlier article we have discussed Full Data Redaction Policy.

  1. Create Partial Redaction Policy
  2. Syntax
  3. Hands-on on Partial Redaction Policy using fixed character short-cut.
  4. Hands-on on Partial Redaction Policy using character data type.
  5. Hands-on on Partial Redaction Policy using number data type.

Lets consider one by one as below:

Create Partial Redaction Policy

Partial Redaction Policy is about Portion of the data would be redacted. For Ex: In your bank monthly statement email, Your account number might redacted in order to read only last 4 digit and rest might replace with ‘*’ or Big DOT.

Note: We can only redact column with character, number, or date-time data types.

Syntax of Creating Partial Redaction Policy

 DBMS_REDACT.ADD_POLICY (
 object_schema IN VARCHAR2 := NULL,
 object_name IN VARCHAR2,
 column_name IN VARCHAR2 := NULL,
 policy_name IN VARCHAR2,
 function_type IN BINARY_INTEGER := NULL,
 function_parameters IN VARCHAR2 := NULL,
 expression IN VARCHAR2,
 enable IN BOOLEAN := TRUE);

function_type: In order to create Partial Redaction, we use DBMS_REDACT.PARTIAL function type.

Hands-on on Partial Redaction Policy using fixed character short-cuts

We can create partial redaction policy with the help of DBMS_REDACT.ADD_POLICY with predefined fixed character short-cut function parameters.

Some function_parameters parameter shortcuts are as below:

DBMS_REDACT.REDACT_US_SSN_F5: Redact first 5 numbers of social security number. Datatype: VARCHAR2. Ex: 546-76-3245 becomes XXX-XX-3245.
DBMS_REDACT.REDACT_US_SSN_L4: Redact last 4 numbers of social security number. Datatype: VARCHAR2. Ex: 546-76-3245 becomes 546-76-XXXX.
DBMS_REDACT.REDACT_US_SSN_ENTIRE: Redact entire social security number. Datatype: VARCHAR2. Ex: 546-76-3245 becomes XXX-XX-XXXX.
DBMS_REDACT.REDACT_NUM_US_SSN_F5: Redact first 5 numbers of social security number. Datatype: NUMBER. Ex: 546763245 becomes XXXXX3245.
DBMS_REDACT.REDACT_NUM_US_SSN_L4: Redact last 4 numbers of social security number. Datatype: NUMBER. Ex: 54676XXXX.
DBMS_REDACT.REDACT_NUM_US_SSN_ENTIRE: Redact entire social security number. Datatype: NUMBER. Ex: 546763245 becomes XXXXXXXXX.
DBMS_REDACT.REDACT_ZIP_CODE: Redact entire 5 digit postal code. Datatype: VARCHAR2. Ex: 96745 becomes XXXXX.
DBMS_REDACT.REDACT_NUM_ZIP_CODE: Redact entire 5 digit postal code. Datatype: NUMBER. Ex: 96745 becomes XXXXX.
DBMS_REDACT.REDACT_DATE_MILLENNIUM: Redacts dates.(DD-MON-YY) becomes 01-JAN-00 (January 1, 2000)
DBMS_REDACT.REDACT_DATE_EPOCH: Redacts all dates to 01-JAN-70.
DBMS_REDACT.REDACT_CCN16_F12: Redact first 12 digit of 16 digit credit card number. 1234-5678-9101-1213 becomes ****-****-****-1213.

To simulate scenario, consider following hands-on on redaction of US social security number upto first 5 number with the help of fixed character short-cuts.

Create table EMPSSN with id,name and ssn column as character datatype as below:

SQL> create table EMPSSN(
 id number(3),
 name varchar2(10),
 ssn varchar2(11));

Insert values accordingly:

 SQL> insert into EMPSSN values(1,'xyz','456-54-3456');
 SQL> insert into EMPSSN values(2,'pqr','955-23-3600');
 SQL> insert into EMPSSN values(3,'abc','652-52-4958');
 SQL> commit;

Query SSN column from EMPSSN table:

 SQL> select * from EMPSSN;
 ID         NAME       SSN
 ---------- ---------- -----------
 1          xyz        456-54-3456
 2          pqr        955-23-3600
 3          abc        652-52-4958

Create partial redacted policy with DBMS_REDACT.REDACT_US_SSN_F5 character short-cut as below:

 BEGIN
 DBMS_REDACT.ADD_POLICY(
 object_schema => 'C##SCOTT',
 object_name => 'EMPSSN',
 column_name => 'ssn',
 policy_name => 'scott_empssn_ssn',
 function_type => DBMS_REDACT.PARTIAL,
 function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,
 expression => '1=1');
 END;
 /

After creating above policy, query SSN column from EMPSSN, you will find redacted Social security number with last 4 digit visible.

 SQL> select * from EMPSSN;
 ID         NAME       SSN
 ---------- ---------- -----------
 1          xyz        XXX-XX-3456
 2          pqr        XXX-XX-3600
 3          abc        XXX-XX-4958

This is about Partial Redaction Policy using fixed character short-cuts

 

Hands-on on Partial Redaction Policy using character data type

To simulate scenario create EMPSSN_CHAR table with id, name and ssn column with datatype: varchar2.

 SQL> create table EMPSSN_CHAR(
 id number(3),
 name varchar2(10),
 ssn varchar2(11));

Insert values accordingly:

 SQL> insert into EMPSSN_CHAR values(1,'xyz','456-54-3456');
 SQL> insert into EMPSSN_CHAR values(2,'pqr','955-23-3600');
 SQL> insert into EMPSSN_CHAR values(3,'abc','652-52-4958');
 SQL> commit;

Query SSN column from EMPSSN_CHAR as below:

 SQL> select * from EMPSSN_CHAR;
 ID         NAME       SSN
 ---------- ---------- -----------
 1          xyz        456-54-3456
 2          pqr        955-23-3600
 3          abc        652-52-4958

Now, create partial redaction policy function_type: DBMS_REDACT.PARTIAL

 BEGIN
 DBMS_REDACT.ADD_POLICY(
 object_schema => 'C##SCOTT',
 object_name => 'EMPSSN_CHAR',
 column_name => 'ssn',
 policy_name => 'c##scott_EMPSSN_CHAR_ssn',
 function_type => DBMS_REDACT.PARTIAL,
 function_parameters => 'VVVFVVFVVVV,VVV-VV-VVVV,*,1,5',
 expression => '1=1');
 END;
 /

Function parameter gives idea about formatting of redaction output.
1st Parameter: Character ‘V’ can be potentially redacted. And character ‘F’ can be use for formatting character like hyphens or blank spaces.
2nd Parameter: asterisk(*) use to mask character, specifies the character to be used for the redaction.
3rd Parameter: Starting digit position: In our case: 1
4th Parameter: Ending digit position: In our case: 5

After creating above policy, query SSN column from EMPSSN_CHAR table, you will find Social Security Number redacted upto first 5 characters and visible last 4 characters, as below:

SQL> select * from EMPSSN_CHAR;
 ID         NAME       SSN
 ---------- ---------- -----------
 1          xyz        ***-**-3456
 2          pqr        ***-**-3600
 3          abc        ***-**-4958

This is about Partial Redaction Policy using character data type.

 

Hands-on on Partial Redaction Policy using number data type

To simulate scenario create EMPSSN_NUM table with id, name and ssn column with datatype: number as below:

 SQL> create table EMPSSN_NUM(
 id number(3),
 name varchar2(10),
 ssn number(9));

Query SSN column from EMPSSN_NUM table as below:

 SQL> select * from EMPSSN_NUM;
 ID NAME SSN
 ---------- ---------- ----------
 1 xyz 456543456
 2 pqr 955233600
 3 abc 652524958

Insert values accordingly to above created table:

 SQL> insert into EMPSSN_NUM values(1,'xyz',456543456);
 SQL> insert into EMPSSN_NUM values(2,'pqr',955233600);
 SQL> insert into EMPSSN_NUM values(3,'abc',652524958);
 SQL> commit;

Now, create partial redaction policy function_type: DBMS_REDACT.PARTIAL

 BEGIN
 DBMS_REDACT.ADD_POLICY(
 object_schema => 'C##SCOTT',
 object_name => 'EMPSSN_NUM',
 column_name => 'ssn',
 policy_name => 'C##SCOTT_EMPSSN_NUM_ssn',
 function_type => DBMS_REDACT.PARTIAL,
 function_parameters => '0,1,5',
 expression => '1=1');
 END;
 /

Function parameter gives idea about formatting of redaction output.
1st parameter: Mask character.
2nd parameter: Starting digit position.
3rd parameter: Ending digit position.

Query SSN column from EMPSSN_NUM table as below, you will find first 5 character of SSN column has been redacted with zero ‘0’.

SQL> select * from EMPSSN_NUM;
 ID         NAME       SSN
 ---------- ---------- ----------
 1          xyz             3456
 2          pqr             3600
 3          abc             4958

This is about Partial Redaction Policy using number data type.

Stay Tune. 🙂

Leave a Reply

%d bloggers like this: