Partial Data Redaction in Oracle Redaction

Partial Data Redaction

Partial data redaction required fixed width of data. You can define position of data which is redacted.
Example first 5 digit of credit card is redacted.

Syntax of Partial Data Redaction

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 is value as “DBMS_REDACT.PARTIAL”
Defined Value of FUNCTION Parameters:
DBMS_REDACT.REDACT_US_SSN_F5 First 5 number redacted of Social Security Number(datatype is varchar2). E.g 123-45-6789 to XXX-XX-6789.
DBMS_REDACT.REDACT_US_SSN_L4Redacts the last 4 numbers of Social Security Number(datatype is varchar2). E.g 123-45-6789 to 123-45-XXXX.
DBMS_REDACT.REDACT_US_SSN_ENTIRE Redact the entire Social Security Number (datatype is varchar2). E.g 123-45-6789 to XXX-XX-XXXX.
DBMS_REDACT.REDACT_NUM_US_SSN_F5 Redact first 5 of social security (datatype is number) e.g 987654321 to xxxxx4321.
DBMS_REDACT.REDACT_NUM_US_SSN_L4 Redacts last 4 numbers of Social Security (datatype is NUMBER) E.g 987654320 to 98765XXXX.
DBMS_REDACT.REDACT_NUM_US_SSN_ENTIRE Redacts the entire Social Security number(datatype is NUMBER). E.g 987654320 to XXXXXXXXX.
DBMS_REDACT.REDACT_ZIP_CODE Redacts a 5-digit postal code (datatype is VARCHAR2) e.g 95476 to XXXXX.
DBMS_REDACT.REDACT_NUM_ZIP_CODE Redacts a 5-digit postal code (datatype is NUMBER) e.g. 95476 becomes XXXXX.
DBMS_REDACT.REDACT_DATE_MILLENNIUM Redacts dates that are in the DD-MON-YY format to 01-JAN-00 (January 1, 2000).
DBMS_REDACT.REDACT_DATE_EPOCH Redacts all dates to 01-JAN-70.
DBMS_REDACT.REDACT_CCN16_F12 Redacts a 16-digit credit card e.g 5105 1051 0510 5100 becomes ****-****-****-5100.

Example of adding the Partial Reduction

BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'HR',
object_name => 'employees',
column_name => 'SSN',
policy_name => 'redact_emp_ssns3',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,
expression => '1=1',
policy_description => 'Partially redacts 1st 5 digits in SS numbers',
column_description => 'ssn contains Social Security numbers');
END;
/

SELECT ssn FROM HR.employees;
SSN
-------
XXX-XX-1234
XXX-XX-7894

Define the masking manually with following format:

Partial Masking on Varchar Datatype

function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12',

Parameters:
1. Input: V used for each character with can be redacted. F is used as formatting character same as its value.
2. Output: V for each character to be potential and replace F with displayed format like hypen or space.
3. Mask Character: Single character used as redaction like *, #.
4. Starting digit position: starting V digit position for the redaction in parameter one.
5. Ending digit position: ending V digit position for the redaction(Do not include the F positions)

Example:

BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'HR',
object_name => 'employees',
column_name => 'ssn',
policy_name => 'redact_emp_ssns2',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => 'VVVFVVFVVVV,VVV-VV-VVVV,*,1,5',
expression => '1=1',
policy_description => 'Partially redacts Social Security numbers',
column_description => 'ssn contains character Social Security numbers');
END;
/

-- Check the SSN
SELECT ssn FROM HR.EMPLOYEES;
SSN
-----------
***-**-4320
***-**-4323
***-**-4325
***-**-4329

Partial Redaction Policies Using Number Data Types:
1 Mask character: Number from 0 to 9.
2 Starting digit position: starting digit position for the redaction.
3 Ending digit position: ending digit position for the redaction.

Example:

BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'HR',
object_name => 'employees',
column_name => 'ssn',
policy_name => 'redact_emp_ssns1',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => '7,1,5',
expression => '1=1',
policy_description => 'Partially redacts Social Security numbers',
column_description => 'ssn contains numeric Social Security numbers');
END;
/

SELECT ssn FROM hr.employees;
SSN
---------
777774320
777774323
777774325
777774329

Partial Redaction Policies Using Date-Time Data Types

Redaction done for Date time data types.

Following is the format used for define the policy on data time data type partial reduction on Data.
Note: To omit redaction, enter an uppercase like m for month to Uppercase M or for s seconds to S uppercase.
1. m: For the month. redact month with 1–12 to lowercase m. E.g. m5 represent as MAY.
2. d: For the day. redact a day with 1–31 to a lowercase d. E.g. d7 represent as 07. If you specify 31 for Month February then the last day of the month(28 or 29) is displayed.
3. y: For the year. Redact year, append 1–9999 to a lowercase y. E.g. y1984 displays as 84.
4. h: the hour. redact an hour, 0–23 to a lowercase h. E.g. h20 displays as 20.
5. m: the minute. redact a minute, 0–59 to a lowercase m. E.g. m30 displays as 30.
6. s: the second. redact a second, 0–59 to a lowercase s. E.g s45 displays as 45.

Examples
mdy2015HMS only birth year is replace with 2015 and HMS is large letter it is skipped.
You also change 010101 as m1d1y2001HMS format ’01-JAN-01 10:20:20.00000 AM’


BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'HR',
object_name => 'employees',
column_name => 'birth_date',
policy_name => 'redact_emp_bdate',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => 'mdy2015HMS',
expression => '1=1',
policy_description => 'Replaces birth year with 2015',
column_description => 'birth_date contains customer's birthdate');
END;
/

SELECT birth_date FROM HR.employees;
BIRTH_DATE
-----------
01-DEC-15 09.45.40.000000 AM
06-DEC-15 04.23.29.000000 AM

Advertisements

3 thoughts on “Partial Data Redaction in Oracle Redaction

  1. Pingback: Oracle Redaction | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.