Creating a Partial Redaction Policy

3.8.1 About Creating Partial Redaction Policies

In partial data redaction, only a portion of the data, such as the first five digits of an identification number, are redacted.

For example, you can redact most of a credit card number with asterisks (*), except for the last 4 digits. You can create policies for columns that use character, number, or date-time data types. For policies that redact character data types, you can use fixed character redaction formats. If you have the Enterprise Manager for Oracle Database 12.1.0.7 plug-in deployed on your system, then you can also create and save custom redaction formats.

Note:

In previous releases, the term shortcut was used for the term format.

3.8.2 Syntax for Creating a Partial Redaction Policy

The DBMS_REDACT.ADD_POLICY statement enables you to create policies that redact specific parts of the data returned to the application.

The DBMS_REDACT.ADD_POLICY fields for creating a partial redaction policy are as follows:

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);

In this specification:

object_schema, object_name, column_name, policy_name, expression, enable: See General Syntax of the DBMS_REDACT.ADD_POLICY Procedure
function_type: Specifies the function used to set the type of redaction. Enter DBMS_REDACT.PARTIAL.
function_parameters: The parameters that you set here depend on the data type of the column specified for the column_name parameter. See the following sections for details:

3.8.3 Creating Partial Redaction Policies Using Fixed Character Formats

The DBMS_REDACT.ADD_POLICY function_parameters parameter can be configured to support redaction of fixed character data types.

3.8.3.1 Settings for Fixed Character Formats

Oracle Data Redaction provides special predefined formats to configure policies that use fixed characters.

Table 3-9 describes DBMS_REDACT.ADD_POLICY function_parameters parameter formats that you can use for commonly redacted identity numbers (such as Social Security Numbers or Canadian Social Insurance Numbers), postal codes, and credit card numbers that use either the VARCHAR2 or NUMBER data types for their columns.

Table 3-9 Partial Fixed Character Redaction Formats

Format	Description
`DBMS_REDACT.REDACT_US_SSN_F5`	Redacts the first 5 numbers of Social Security Numbers when the column is a `VARCHAR2` data type. For example, the number `987-65-4320` becomes `XXX-XX-4320`.
`DBMS_REDACT.REDACT_US_SSN_L4`	Redacts the last 4 numbers of Social Security Numbers when the column is a `VARCHAR2` data type. For example, the number `987-65-4320` becomes `987-65-XXXX`.
`DBMS_REDACT.REDACT_US_SSN_ENTIRE`	Redacts the entire Social Security Number when the column is a `VARCHAR2` data type. For example, the number `987-65-4320` becomes `XXX-XX-XXXX`.
`DBMS_REDACT.REDACT_NUM_US_SSN_F5`	Redacts the first 5 numbers of Social Security Numbers when the column is a `NUMBER` data type. For example, the number `987654320` becomes `XXXXX4320`.
`DBMS_REDACT.REDACT_NUM_US_SSN_L4`	Redacts the last 4 numbers of Social Security Numbers when the column is a `NUMBER` data type. For example, the number `987654320` becomes `98765XXXX`.
`DBMS_REDACT.REDACT_NUM_US_SSN_ENTIRE`	Redacts the entire Social Security Number when the column is a `NUMBER` data type. For example, the number `987654320` becomes `XXXXXXXXX`.
`DBMS_REDACT.REDACT_SIN_NUMBER`	Redacts the Canadian Social Insurance number by replacing the first 6 digits by `9` (number). For example, `123456789` is redacted to `999999789`.
`DBMS_REDACT.REDACT_SIN_UNFORMATTED`	Redacts the Canadian Social Insurance number by replacing the first 6 digits by `X` (string). For example, `123456789` is redacted to `XXXXXX789`.
`DBMS_REDACT.REDACT_SIN_FORMATTED`	Redacts the Canadian Social Insurance Number by replacing the first 6 digits by `X` (string). For example, `123-456-789` is redacted to `XXX-XXX-789`.
`DBMS_REDACT.REDACT_UK_NIN_FORMATTED`	Redacts the UK National Insurance number by replacing the first 6 digits by `X` (string) but leaving the alphabetic characters as is. For example, `ET 27 02 23 D` is redacted to `ET XX XX XX D`.
`DBMS_REDACT.REDACT_UK_NIN_UNFORMATTED`	Redacts the UK National Insurance number by replacing the first 6 digits by `X` (string) and leaving the alphabetic characters as is. For example, `ET270223D` is redacted to `ETXXXXXXD`.
`DBMS_REDACT.REDACT_CCN_FORMATTED`	Redacts the credit card number (other than American Express) by replacing everything but the last 4 digits by ``. For example, the credit card number `5105–1051–0510–5100` is redacted to `*——**—5100`.
`DBMS_REDACT.REDACT_CCN_NUMBER`	Redacts the credit card number (other than American Express) by replacing everything but the last 4 digits by `9`. For example, the credit card number `5105105105105100` is redacted to `9999999999995100`. Note that the last four digits are not redacted.
`DBMS_REDACT.REDACT_CCN16_F12`	Redacts a 16-digit credit card number (other than American Express), leaving the last 4 digits displayed. For example, `5105 1051 0510 5100` becomes `**--**-5100`.
`DBMS_REDACT.REDACT_AMEX_CCN_FORMATTED`	Redacts the American Express credit card number by replacing the digits with `` except the last 5 digits. For example, the credit card number `3782 822463 10005` is redacted to `* **** 10005`.
`DBMS_REDACT.REDACT_AMEX_CCN_NUMBER`	Redacts the American Express Credit Card Number by replacing the digits with `0` except the last 5 digits. For example, the credit card number `3782 822463 10005` is redacted to `0000 000000 10005`.
`DBMS_REDACT.REDACT_ZIP_CODE`	Redacts a 5-digit postal code when the column is a `VARCHAR2` data type. For example, `95476` becomes `XXXXX`.
`DBMS_REDACT.REDACT_NUM_ZIP_CODE`	Redacts a 5-digit postal code when the column is a `NUMBER` data type. For example, `95476` becomes `99999`.
`DBMS_REDACT.REDACT_DATE_EPOCH`	Redacts all dates to `01-JAN-70`.
`DBMS_REDACT.REDACT_NA_PHONE_FORMATTED`	Redacts the North American phone number by leaving the area code, but replacing everything else with `X`. For example, `650-555–0100` is redacted to `650-XXX-XXXX`.
`DBMS_REDACT.REDACT_NA_PHONE_NUMBER`	Redacts the North American phone number by leaving the area code, but replacing everything else with `0`. For example, `6505550100` gets redacted to `6500000000`.
`DBMS_REDACT.REDACT_NA_PHONE_UNFORMATTED`	Redacts the North American phone number by leaving the area code, but replacing everything else with `X`. For example, `6505550100` is redacted to `650XXXXXXX`.
`DBMS_REDACT.REDACT_DATE_MILLENNIUM`	Redacts dates that are in the `DD-MON-YY` format to `01-JAN-00` (January 1, 2000).

Related Topics

General Syntax of the DBMS_REDACT.ADD_POLICY Procedure

3.8.3.2 Example: Partial Redaction Policy Using a Fixed Character Format

You can use the DBMS_REDACT.ADD_POLICY PL/SQL procedure to create a partial redaction policy that uses a fixed character format.

Example 3-3 shows how Social Security Numbers in a VARCHAR2 data type column can be redacted using the REDACT_US_SSN_F5 format.

Example 3-3 Partially Redacted Character Values

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'mavis', 
   object_name         => 'cust_info', 
   column_name         => 'ssn',
   policy_name         => 'redact_cust_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 Social Security Numbers',
   column_description  => 'ssn contains Social Security Numbers');
END;
/

Query and redacted result:

SELECT ssn FROM mavis.cust_info;

SSN
-------
XXX-XX-4320
XXX-XX-4323
XXX-XX-4325
XXX-XX-4329

3.8.4 Creating Partial Redaction Policies Using Character Data Types

The DBMS_REDACT.ADD_POLICY function_parameters parameter can be configured to support redaction of character data types.

3.8.4.1 Settings for Character Data Types

Oracle Data Redaction provides special settings to configure policies that use character data types.

When you set the DBMS_REDACT.ADD_POLICY function_parameters parameter to define partial redaction of character data types, enter values for the following settings in the order shown. Separate each value with a comma.

Note:

Be aware that you must use a fixed width character set for the partial redaction. In other words, each character redacted must be replaced by another of equal byte length. If you want to use a variable-length character set (for example, UTF-8), then you must use a regular expression-based redaction.

The settings are as follows:

Input format: Defines how the data is currently formatted. Enter V for each character that potentially can be redacted, such as all of the digits in a credit card number. Enter F for each character that you want to format using a formatting character, such as hyphens or blank spaces in the credit card number. Ensure that each character has a corresponding V or F value. (The input format values are not case-sensitive.)
Output format: Defines how the displayed data should be formatted. Enter V for each character to be potentially redacted. Replace each F character in the input format with the character that you want to use for the displayed output, such as a hyphen. (The output format values are not case-sensitive.)
Mask character: Specifies the character to be used for the redaction. Enter a single character to use for the redaction, such as an asterisk (*).
Starting digit position: Specifies the starting V digit position for the redaction.
Ending digit position: Specifies the ending V digit position for the redaction. Do not include the F positions when you decide on the ending position value.

For example, the following setting redacts the first 12 V digits of the credit card number 5105 1051 0510 5100, and replaces the F positions (which are blank spaces) with hyphens to format it in a style normally used for credit card numbers, resulting in ****-****-****-4320.

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

Related Topics

3.8.4.2 Example: Partial Redaction Policy Using a Character Data Type

The DBMS_REDACT.ADD_POLICY PL/SQL procedure can create a partial redaction policy that uses a character data type.

Example 3-4 shows how to redact Social Security Numbers that are in a VARCHAR2 data type column and to preserve the character hyphens in the Social Security Number.

Example 3-4 Partially Redacted Character Values

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'mavis', 
   object_name         => 'cust_info', 
   column_name         => 'ssn',
   policy_name         => 'redact_cust_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;
/

Query and redacted result:

SELECT ssn FROM mavis.cust_info;

SSN
-----------
***-**-4320
***-**-4323
***-**-4325
***-**-4329

3.8.5 Creating Partial Redaction Policies Using Number Data Types

The DBMS_REDACT.ADD_POLICY function_parameters parameter can be configured to support redaction of number data types.

3.8.5.1 Settings for Number Data Types

When you set values for the number data type, you must specify a redact character, a starting digit position, and an ending digit position.

For partial redaction of number data types, you can enter values for the following settings for the function_parameters parameter of the DBMS_REDACT.ADD_POLICY procedure, in the order shown.

Redact character: Specifies the character to display. Enter a number from 0 to 9.
Starting digit position: Specifies the starting digit position for the redaction, such as 1 for the first digit.
Ending digit position: Specifies the ending digit position for the redaction.

For example, the following setting redacts the first five digits of the Social Security Number 987654321, resulting in 999994321.

function_parameters  => '9,1,5',

Related Topics

General Syntax of the DBMS_REDACT.ADD_POLICY Procedure

3.8.5.2 Example: Partial Redaction Policy Using a Number Data Type

The DBMS_REDACT.ADD_POLICY procedure can create a partial redaction policy that uses a number data type.

Example 3-5 shows how to partially redact a set of Social Security Numbers in the mavis.cust_info table, for any application user who logs in. (Hence, the expression parameter evaluates to TRUE.)

This type of redaction is useful when the application is expecting a formatted number and not a string. In this scenario, the Social Security Numbers are in a column of the data type NUMBER. In other words, the ssn column contains numbers only, not other characters such as hyphens or blank spaces.

Example 3-5 Partially Redacted Data Redaction Numeric Values

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'mavis', 
   object_name         => 'cust_info', 
   column_name         => 'ssn',
   policy_name         => 'redact_cust_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;
/

Query and redacted result:

SELECT ssn FROM mavis.cust_info;

SSN
---------
777774320
777774323
777774325
777774329

3.8.6 Creating Partial Redaction Policies Using Date-Time Data Types

The DBMS_REDACT.ADD_POLICY function_parameters parameter can be configured to support redaction of date-time data types.

3.8.6.1 Settings for Date-Time Data Types

Oracle Data Redaction provides special settings for configuring date-time data types.

For partial redaction of date-time data types, enter values for the following DBMS_REDACT.ADD_POLICY function_parameters parameter settings.

Enter these values in the order shown:

m: Redacts the month. To redact with a month name, append 1–12 to lowercase m. For example, m5 displays as MAY. To omit redaction, enter an uppercase M.
d: Redacts the day of the month. To redact with a day of the month, append 1–31 to a lowercase d. For example, d7 displays as 07. If you enter a higher number than the days of the month (for example, 31 for the month of February), then the last day of the month is displayed (for example, 28). To omit redaction, enter an uppercase D.
y: Redacts the year. To redact with a year, append 1–9999 to a lowercase y. For example, y1984 displays as 84. To omit redaction, enter an uppercase Y.
h: Redacts the hour. To redact with an hour, append 0–23 to a lowercase h. For example, h20 displays as 20. To omit redaction, enter an uppercase H.
m: Redacts the minute. To redact with a minute, append 0–59 to a lowercase m. For example, m30 displays as 30. To omit redaction, enter an uppercase M.
s: Redacts the second. To redact with a second, append 0–59 to a lowercase s. For example, s45 displays as 45. To omit redaction, enter an uppercase S.

Related Topics

General Syntax of the DBMS_REDACT.ADD_POLICY Procedure

3.8.6.2 Example: Partial Redaction Policy Using Date-Time Data Type

The DBMS_REDACT.ADD_POLICY procedure can create a partial redaction policy that uses the date-time data type.

Example 3-6 shows how to partially redact a date. This example redacts the birth year of customers; replacing it with 13, but retaining the remaining values.

Example 3-6 Partially Redacted Data Redaction Using Date-Time Values

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'mavis', 
   object_name         => 'cust_info', 
   column_name         => 'birth_date',
   policy_name         => 'redact_cust_bdate', 
   function_type       => DBMS_REDACT.PARTIAL,
   function_parameters => 'MDy2013HMS',
   expression          => '1=1',
   policy_description  => 'Replaces birth year with 2013',
   column_description  => 'birth_date contains customer's birthdate');
END;
/

Query and redacted result:

SELECT birth_date FROM mavis.cust_info;

BIRTH_DATE
07-DEC-13 09.45.40.000000 AM
12-OCT-13 04.23.29.000000 AM