DBMS_REDACT v13

Suggest edits

The DBMS_REDACT package enables the redacting or masking of data returned by a query. The DBMS_REDACT package provides a procedure to create policies, alter policies, enable policies, disable policies, and drop policies. The procedures available in the DBMS_REDACT package are listed in the following table.

Function/Procedure	Function or Procedure	Return Type	Description
`ADD_POLICY(object_schema, object_name, policy_name, policy_description, column_name, column_description, function_type, function_parameters, expression, enable, regexp_pattern, regexp_replace_string, regexp_position, regexp_occurence, regexp_match_parameter, custom_function_expression)`	Procedure	n/a	Adds a data redaction policy.
`ALTER_POLICY(object_schema, object_name, policy_name, action, column_name, function_type, function_parameters, expression, regexp_pattern, regexp_replace_string, regexp_position, regexp_occurence, regexp_match_parameter, policy_description, column_description, custom_function_expression)`	Procedure	n/a	Alters the existing data redaction policy.
`DISABLE_POLICY(object_schema, object_name, policy_name)`	Procedure	n/a	Disables the existing data redaction policy.
`ENABLE_POLICY(object_schema, object_name, policy_name)`	Procedure	n/a	Enables a previously disabled data redaction policy.
`DROP_POLICY(object_schema, object_name, policy_name)`	Procedure	n/a	Drops a data redaction policy.
`UPDATE_FULL_REDACTION_VALUES(number_val, binfloat_val, bindouble_val, char_val, varchar_val, nchar_val, nvarchar_val, datecol_val, ts_val, tswtz_val, blob_val, clob_val, nclob_val)`	Procedure	n/a	Updates the full redaction default values for the specified datatype.

The data redaction feature uses the DBMS_REDACT package to define policies or conditions to redact data in a column based on the table column type and redaction type.

Note that you must be the owner of the table to create or change the data redaction policies. The users are exempted from all the column redaction policies, which the table owner or super-user is by default.

Using DBMS_REDACT Constants and Function Parameters

The DBMS_REDACT package uses the constants and redacts the column data by using any one of the data redaction types. The redaction type can be decided based on the function_type parameter of dbms_redact.add_policy and dbms_redact.alter_policy procedure. The below table highlights the values for function_type parameters of dbms_redact.add_policy and dbms_redact.alter_policy.

Constant	Type	Value	Description
`NONE`	`INTEGER`	`0`	No redaction, zero effect on the result of a query against table.
`FULL`	`INTEGER`	`1`	Full redaction, redacts full values of the column data.
`PARTIAL`	`INTEGER`	`2`	Partial redaction, redacts a portion of the column data.
`RANDOM`	`INTEGER`	`4`	Random redaction, each query results in a different random value depending on the datatype of the column.
`REGEXP`	`INTEGER`	`5`	Regular Expression based redaction, searches for the pattern of data to redact.
`CUSTOM`	`INTEGER`	`99`	Custom redaction type.

The following table shows the values for the action parameter of dbms_redact.alter_policy.

Constant	Type	Value	Description
`ADD_COLUMN`	`INTEGER`	`1`	Adds a column to the redaction policy.
`DROP_COLUMN`	`INTEGER`	`2`	Drops a column from the redaction policy.
`MODIFY_EXPRESSION`	`INTEGER`	`3`	Modifies the expression of a redaction policy. The redaction is applied when the expression evaluates to the `BOOLEAN` value to `TRUE`.
`MODIFY_COLUMN`	`INTEGER`	`4`	Modifies a column in the redaction policy to change the redaction function type or function parameter.
`SET_POLICY_DESCRIPTION`	`INTEGER`	`5`	Sets the redaction policy description.
`SET_COLUMN_DESCRIPTION`	`INTEGER`	`6`	Sets a description for the redaction performed on the column.

The partial data redaction enables you to redact only a portion of the column data. To use partial redaction, you must set the dbms_redact.add_policy procedure function_type parameter to dbms_redact.partial and use the function_parameters parameter to specify the partial redaction behavior.

The data redaction feature provides a predefined format to configure policies that use the following datatype:

Character
Number
Datetime

The following table highlights the format descriptor for partial redaction with respect to datatype. The example described below shows how to perform a redaction for a string datatype (in this scenario, a Social Security Number (SSN)), a Number datatype, and a DATE datatype.

Datatype	Format Descriptor	Description	Examples
Character	`REDACT_PARTIAL_INPUT_FORMAT`	Specifies the input format. Enter `V` for each character from the input string to be possibly redacted. Enter `F` for each character from the input string that can be considered as a separator such as blank spaces or hyphens.	Consider `'VVVFVVFVVVV,VVV-VV-VVVV,X,1,5'` for masking first 5 digits of SSN strings such as `123-45-6789`, adding hyphen to format it and thereby resulting in strings such as `XXX-XX-6789.` The field value `VVVFVVFVVVV` for matching SSN strings such as `123-45-6789`.
	`REDACT_PARTIAL_OUTPUT_FORMAT`	Specifies the output format. Enter `V` for each character from the input string to be possibly redacted. Replace each `F` character from the input format with a character such as a hyphen or any other separator.	The field value `VVV-VV-VVVV` can be used to redact SSN strings into `XXX-XX-6789` where `X` comes from `REDACT_PARTIAL_MASKCHAR` field.
	`REDACT_PARTIAL_MASKCHAR`	Specifies the character to be used for redaction.	The value `X` for redacting SSN strings into `XXX-XX-6789`.
	`REDACT_PARTIAL_MASKFROM`	Specifies which `V` within the input format from which to start the redaction.	The value `1` for redacting SSN strings starting at the first `V` of the input format of `VVVFVVFVVVV` into strings such as `XXX-XX-6789`.
	`REDACT_PARTIAL_MASKTO`	Specifies which `V` within the input format at which to end the redaction.	The value `5` for redacting SSN strings up to and including the fifth `V` within the input format of `VVVFVVFVVVV` into strings such as `XXX-XX-6789`.
Number	`REDACT_PARTIAL_MASKCHAR`	Specifies the character to be displayed in the range between 0 and 9.	`‘9, 1, 5’` for redacting the first five digits of the Social Security Number `123456789` into `999996789`.
	`REDACT_PARTIAL_MASKFROM`	Specifies the start digit position for redaction.
	`REDACT_PARTIAL_MASKTO`	Specifies the end digit position for redaction.
Datetime	`REDACT_PARTIAL_DATE_MONTH`	`‘m’` redacts the month. To mask a specific month, specify `‘m#’` where # indicates the month specified by its number between `1` and `12`.	`m3` displays as March.
	`REDACT_PARTIAL_DATE_DAY`	`‘d’` redacts the day of the month. To mask with a day of the month, append `1-31` to a lowercase `d`.	`d3` displays as `03`.
	`REDACT_PARTIAL_DATE_YEAR`	`‘y’` redacts the year. To mask with a year, append `1-9999` to a lowercase `y`.	`y1960` displays as `60`.
	`REDACT_PARTIAL_DATE_HOUR`	`‘h’` redacts the hour. To mask with an hour, append `0-23` to a lowercase `h`.	`h18` displays as `18`.
	`REDACT_PARTIAL_DATE_MINUTE`	`‘m’` redacts the minute. To mask with a minute, append `0-59` to a lowercase `m`.	`m20` displays as `20`.
	`REDACT_PARTIAL_DATE_SECOND`	`‘s’` redacts the second. To mask with a second, append `0-59` to a lowercase `s`.	`s40` displays as `40`.

The following table represents function_parameters values that can be used in partial redaction.

Function Parameter	Data Type	Value	Description
`REDACT_US_SSN_F5`	`VARCHAR2`	`'VVVFVVFVVVV,VVV-VV-VVVV,X,1,5'`	Redacts the first 5 numbers of SSN. Example: The number `123-45-6789` becomes `XXX-XX-6789`.
`REDACT_US_SSN_L4`	`VARCHAR2`	`'VVVFVVFVVVV,VVV-VV-VVVV,X,6,9'`	Redacts the last 4 numbers of SSN. Example: The number `123-45-6789` becomes `123-45-XXXX`.
`REDACT_US_SSN_ENTIRE`	`VARCHAR2`	`'VVVFVVFVVVV,VVV-VV-VVVV,X,1,9'`	Redacts the entire SSN. Example: The number `123-45-6789` becomes `XXX-XX-XXXX`.
`REDACT_NUM_US_SSN_F5`	`VARCHAR2`	`'9,1,5'`	Redacts the first 5 numbers of SSN when the column is a number datatype. Example: The number `123456789` becomes `999996789`.
`REDACT_NUM_US_SSN_L4`	`VARCHAR2`	`'9,6,9'`	Redacts the last 4 numbers of SSN when the column is a number datatype. Example: The number `123456789` becomes `123459999`.
`REDACT_NUM_US_SSN_ENTIRE`	`VARCHAR2`	`'9,1,9'`	Redacts the entire SSN when the column is a number datatype. Example: The number `123456789` becomes `999999999`.
`REDACT_ZIP_CODE`	`VARCHAR2`	`'VVVVV,VVVVV,X,1,5'`	Redacts a 5 digit zip code. Example: `12345`becomes `XXXXX`.
`REDACT_NUM_ZIP_CODE`	`VARCHAR2`	`'9,1,5'`	Redacts a 5 digit zip code when the column is a number datatype. Example: `12345`becomes `99999`.
`REDACT_CCN16_F12`	`VARCHAR2`	`'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12'`	Redacts a 16 digit credit card number and displays only 4 digits. Example: `1234 5678 9000 2358` becomes `**--**-2358`.
`REDACT_DATE_MILLENNIUM`	`VARCHAR2`	`'m1d1y2000'`	Redacts a date that is in the `DD-MM-YY` format. Example: Redacts all date to `01-JAN-2000`.
`REDACT_DATE_EPOCH`	`VARCHAR2`	`'m1d1y1970'`	Redacts all dates to `01-JAN-70`.
`REDACT_AMEX_CCN_FORMATTED`	`VARCHAR2`	`'VVVVFVVVVVVFVVVVV,VVVV-VVVVVV-VVVVV,*,1,10'`	Redacts the Amercian Express credit card number and replaces the digit with `` except for the last 5 digits. Example:* The credit card number `1234 567890 34500` becomes `** **** 34500`.
`REDACT_AMEX_CCN_NUMBER`	`VARCHAR2`	`'0,1,10'`	Redacts the Amercian Express credit card number and replaces the digit with `0` except for the last 5 digits. Example: The credit card number `1234 567890 34500` becomes `0000 000000 34500`.
`REDACT_SIN_FORMATTED`	`VARCHAR2`	`'VVVFVVVFVVV,VVV-VVV-VVV,*,1,6'`	Redacts the Social Insurance Number by replacing the first 6 digits by ``. Example:* `123-456-789` becomes `*-*-789`.
`REDACT_SIN_NUMBER`	`VARCHAR2`	`'9,1,6'`	Redacts the Social Insurance Number by replacing the first 6 digits by `9`. Example: `123456789` becomes `999999789`.
`REDACT_SIN_UNFORMATTED`	`VARCHAR2`	`'VVVVVVVVV,VVVVVVVVV,*,1,6'`	Redacts the Social Insurance Number by replacing the first 6 digits by ``. Example:* `123456789` becomes `******789`.
`REDACT_CCN_FORMATTED`	`VARCHAR2`	`'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12'`	Redacts a credit card number by `` and displays only 4 digits. Example:* The credit card number `1234-5678-9000-4671` becomes `**--**-4671`.
`REDACT_CCN_NUMBER`	`VARCHAR2`	`'9,1,12'`	Redacts a credit card number by `0` except the last 4 digits. Example: The credit card number `1234567890004671` becomes `0000000000004671`.
`REDACT_NA_PHONE_FORMATTED`	`VARCHAR2`	`‘VVVFVVVFVVVV,VVV-VVV-VVVV,X,4,10'`	Redacts the North American phone number by `X` leaving the area code. Example: `123-456-7890` becomes `123-XXX-XXXX`.
`REDACT_NA_PHONE_NUMBER`	`VARCHAR2`	`'0,4,10'`	Redacts the North American phone number by `0` leaving the area code. Example: `1234567890` becomes `1230000000`.
`REDACT_NA_PHONE_UNFORMATTED`	`VARCHAR2`	`'VVVVVVVVVV,VVVVVVVVVV,X,4,10'`	Redacts the North American phone number by `X` leaving the area code. Example: `1234567890` becomes `123XXXXXXX`.
`REDACT_UK_NIN_FORMATTED`	`VARCHAR2`	`'VVFVVFVVFVVFV,VV VV VV VV V,X,3,8'`	Redacts the UK National Insurance Number by `X` but leaving the alphabetic characters. Example: `NY 22 01 34 D` becomes `NY XX XX XX D`.
`REDACT_UK_NIN_UNFORMATTED`	`VARCHAR2`	`'VVVVVVVVV,VVVVVVVVV,X,3,8'`	Redacts the UK National Insurance Number by `X` but leaving the alphabetic characters. Example: `NY220134D` becomes `NYXXXXXXD`.

A regular expression-based redaction searches for patterns of data to redact. The regexp_pattern search the values in order for the regexp_replace_string to change the value. The following table illustrates the regexp_pattern values that you can use during REGEXP based redaction.

Function Parameter and Description	Data Type	Value
`RE_PATTERN_CC_L6_T4`: Searches for the middle digits of a credit card number that includes 6 leading digits and 4 trailing digits. The `regexp_replace_string` setting to use with the format is `RE_REDACT_CC_MIDDLE_DIGITS` that replaces the identified pattern with the characters specified by the `RE_REDACT_CC_MIDDLE_DIGITS` parameter.	`VARCHAR2`	`'(\d\d\d\d\d\d)(\d\d\d*)(\d\d\d\d)'`
`RE_PATTERN_ANY_DIGIT`: Searches for any digit and replaces the identified pattern with the characters specified by the following values of the `regexp_replace_string` parameter. `regexp_replace_string=> RE_REDACT_WITH_SINGLE_X` (replaces any matched digit with the `X` character). `regexp_replace_string=> RE_REDACT_WITH_SINGLE_1` (replaces any matched digit with the `1` character).	`VARCHAR2`	`'\d'`
`RE_PATTERN_US_PHONE`: Searches for the U.S phone number and replaces the identified pattern with the characters specified by the `regexp_replace_string` parameter. `regexp_replace_string=> RE_REDACT_US_PHONE_L7` (searches the phone number and then replaces the last 7 digits).	`VARCHAR2`	`'($\d\d\d$\|\d\d\d)-(\d\d\d)-(\d\d\d\d)'`
`RE_PATTERN_EMAIL_ADDRESS`: Searches for the email address and replaces the identified pattern with the characters specified by the following values of the `regexp_replace_string` parameter. `regexp_replace_string=> RE_REDACT_EMAIL_NAME` (finds the email address and redacts the email username). `regexp_replace_string=> RE_REDACT_EMAIL_DOMAIN` (finds the email address and redacts the email domain). `regexp_replace_string=> RE_REDACT_EMAIL_ENTIRE` (finds the email address and redacts the entire email address).	`VARCHAR2`	`'([A-Za-z0-9._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})'`
`RE_PATTERN_IP_ADDRESS`: Searches for an IP address and replaces the identified pattern with the characters specified by the `regexp_replace_string` parameter. The `regexp_replace_string` parameter to be used is `RE_REDACT_IP_L3` that replaces the last section of an IP address with `999` and indicates it is redacted.	`VARCHAR2`	`'(\d{1,3}\.\d{1,3}\.\d{1,3})\.\d{1,3}'`
`RE_PATTERN_AMEX_CCN`: Searches for the American Express credit card number. The `regexp_replace_string` parameter to be used is `RE_REDACT_AMEX_CCN` that redacts all of the digits except the last 5.	`VARCHAR2`	`'.*(\d\d\d\d\d)$'`
`RE_PATTERN_CCN`: Searches for the credit card number other than American Express credit cards. The `regexp_replace_string` parameter to be used is `RE_REDACT_CCN` that redacts all of the digits except the last 4.	`VARCHAR2`	`'.*(\d\d\d\d)$'`
`RE_PATTERN_US_SSN`: Searches the SSN number and replaces the identified pattern with the characters specified by the `regexp_replace_string` parameter. `'\1-XXX-XXXX'` or `'XXX-XXX-\3'` will return `123-XXX-XXXX` or `XXX-XXX-6789` for the value `'123-45-6789'` respectively.	`VARCHAR2`	`'(\d\d\d)-(\d\d)-(\d\d\d\d)'`

The below table illustrates the regexp_replace_string values that you can use during REGEXP based redaction.

Function Parameter	Data Type	Value	Description
`RE_REDACT_CC_MIDDLE_DIGITS`	`VARCHAR2`	`'\1XXXXXX\3'`	Redacts the middle digits of a credit card number according to the `regexp_pattern` parameter with the `RE_PATTERN_CC_L6_T4` format and replaces each redacted character with an `X`. Example: The credit card number `1234 5678 9000 2490` becomes `1234 56XX XXXX 2490`.
`RE_REDACT_WITH_SINGLE_X`	`VARCHAR2`	`'X'`	Replaces the data with a single `X` character for each matching pattern as specified by setting the `regexp_pattern` parameter with the `RE_PATTERN_ANY_DIGIT` format. Example: The credit card number `1234 5678 9000 2490` becomes `XXXX XXXX XXXX XXXX`.
`RE_REDACT_WITH_SINGLE_1`	`VARCHAR2`	`'1'`	Replaces the data with a single `1` digit for each of the data digits as specified by setting the `regexp_pattern` parameter with the `RE_PATTERN_ANY_DIGIT` format. Example: The credit card number `1234 5678 9000 2490` becomes `1111 1111 1111 1111`.
`RE_REDACT_US_PHONE_L7`	`VARCHAR2`	`'\1-XXX-XXXX'`	Redacts the last 7 digits of U.S phone number according to the `regexp_pattern` parameter with the `RE_PATTERN_US_PHONE` format and replaces each redacted character with an `X`. Example: The phone number `123-444-5900` becomes `123-XXX-XXXX`.
`RE_REDACT_EMAIL_NAME`	`VARCHAR2`	`'xxxx@\2'`	Redacts the email name according to the `regexp_pattern` parameter with the `RE_PATTERN_EMAIL_ADDRESS` format and replaces the email username with the four `x` characters. Example: The email address `sjohn@example.com` becomes `xxxx@example.com`.
`RE_REDACT_EMAIL_DOMAIN`	`VARCHAR2`	`'\1@xxxxx.com'`	Redacts the email domain name according to the `regexp_pattern` parameter with the `RE_PATTERN_EMAIL_ADDRESS` format and replaces the domain with the five `x` characters. Example: The email address `sjohn@example.com` becomes `sjohn@xxxxx.com`.
`RE_REDACT_EMAIL_ENTIRE`	`VARCHAR2`	`'xxxx@xxxxx.com'`	Redacts the entire email address according to the `regexp_pattern` parameter with the `RE_PATTERN_EMAIL_ADDRESS` format and replaces the email address with the `x` characters. Example: The email address `sjohn@example.com` becomes `xxxx@xxxxx.com`.
`RE_REDACT_IP_L3`	`VARCHAR2`	`'\1.999'`	Redacts the last 3 digits of an IP address according to the `regexp_pattern` parameter with the `RE_PATTERN_IP_ADDRESS` format. Example: The IP address `172.0.1.258` becomes `172.0.1.999`, which is an invalid IP address.
`RE_REDACT_AMEX_CCN`	`VARCHAR2`	`'**********\1'`	Redacts the first 10 digits of an American Express credit card number according to the `regexp_pattern` parameter with the `RE_PATTERN_AMEX_CCN` format. Example: `123456789062816` becomes `**********62816`.
`RE_REDACT_CCN`	`VARCHAR2`	`'************\1'`	Redacts the first 12 digits of a credit card number as specified by the `regexp_pattern` parameter with the `RE_PATTERN_CCN` format. Example: `8749012678345671` becomes `************5671`.

The following tables show the regexp_position value and regexp_occurence values that you can use during REGEXP based redaction.

Function Parameter	Data Type	Value	Description
`RE_BEGINNING`	`INTEGER`	`1`	Specifies the position of a character where search must begin. By default, the value is `1` that indicates the search begins at the first character of `source_char`.

Function Parameter	Data Type	Value	Description
`RE_ALL`	`INTEGER`	`0`	Specifies the replacement occurrence of a substring. If the value is `0`, then the replacement of each matching substring occurs.
`RE_FIRST`	`INTEGER`	`1`	Specifies the replacement occurrence of a substring. If the value is `1`, then the replacement of the first matching substring occurs.

The following table shows the regexp_match_parameter values that you can use during REGEXP based redaction which lets you change the default matching behavior of a function.

Function Parameter	Data Type	Value	Description
`RE_CASE_SENSITIVE`	`VARCHAR2`	`'c'`	Specifies the case-sensitive matching.
`RE_CASE_INSENSITIVE`	`VARCHAR2`	`'i'`	Specifies the case-insensitive matching.
`RE_MULTIPLE_LINES`	`VARCHAR2`	`'m'`	Treats the source string as multiple lines but if you omit this parameter, then it indicates as a single line.
`RE_NEWLINE_WILDCARD`	`VARCHAR2`	`'n'`	Specifies the period (.), but if you omit this parameter, then the period does not match the newline character.
`RE_IGNORE_WHITESPACE`	`VARCHAR2`	`'x'`	Ignores the whitespace characters.

Note

If you create a redaction policy based on a numeric type column, then make sure that the result after redaction is a number and accordingly set the replacement string to avoid runtime errors.

Note

If you create a redaction policy based on a character type column, then make sure that a length of the result after redaction is compatible with the column type and accordingly set the replacement string to avoid runtime errors.

ADD_POLICY

The add_policy procedure creates a new data redaction policy for a table.

PROCEDURE add_policy (
  <object_schema>                IN VARCHAR2 DEFAULT NULL,
  <object_name>                  IN VARCHAR2,
  <policy_name>                  IN VARCHAR2,
  <policy_description>           IN VARCHAR2 DEFAULT NULL,
  <column_name>                  IN VARCHAR2 DEFAULT NULL,
  <column_description>           IN VARCHAR2 DEFAULT NULL,
  <function_type>                IN INTEGER DEFAULT DBMS_REDACT.FULL,
  <function_parameters>          IN VARCHAR2 DEFAULT NULL,
  <expression>                   IN VARCHAR2,
  <enable>                       IN BOOLEAN DEFAULT TRUE,
  <regexp_pattern>               IN VARCHAR2 DEFAULT NULL,
  <regexp_replace_string>        IN VARCHAR2 DEFAULT NULL,
  <regexp_position>              IN INTEGER DEFAULT DBMS_REDACT.RE_BEGINNING,
  <regexp_occurrence>            IN INTEGER DEFAULT DBMS_REDACT.RE_ALL,
  <regexp_match_parameter>       IN VARCHAR2 DEFAULT NULL,
  <custom_function_expression>   IN VARCHAR2 DEFAULT NULL
  )

Parameters

object_schema

Specifies the name of the schema in which the object resides and on which the data redaction policy will be applied. If you specify NULL then the given object is searched by the order specified by search_path setting.

object_name

Name of the table on which the data redaction policy is created.

policy_name

Name of the policy to be added. Ensure that the policy_name is unique for the table on which the policy is created.

policy_description

Specify the description of a redaction policy.

column_name

Name of the column to which the redaction policy applies. To redact more than one column, use the alter_policy procedure to add additional columns.

column_description

Description of the column to be redacted. The column_description is not supported, but if you specify the description for a column then, you will get a warning message.

function_type

The type of redaction function to be used. The possible values are NONE, FULL, PARTIAL, RANDOM, REGEXP, and CUSTOM.

function_parameters

Specifies the function parameters for the partition redaction and is applicable only for partial redaction.

expression

Specifies the Boolean expression for the table and determines how the policy is to be applied. The redaction occurs if this policy expression is evaluated to TRUE.

enable

When set to TRUE, the policy is enabled upon creation. The default is set as TRUE. When set to FALSE, the policy is disabled but the policy can be enabled by calling the enable_policy procedure.

regexp_pattern

Specifies the regular expression pattern to redact data. If the regexp_pattern does not match, then the NULL value is returned.

regexp_replace_string

Specifies the replacement string value.

regexp_position

Specifies the position of a character where search must begin. By default, the function parameter is RE_BEGINNING.

regexp_occurrence

Specifies the replacement occurrence of a substring. If the constant is RE_ALL, then the replacement of each matching substring occurs. If the constant is RE_FIRST, then the replacement of the first matching substring occurs.

regexp_match_parameter

Changes the default matching behavior of a function. The possible regexp_match_parameter constants can be ‘RE_CASE_SENSITIVE’, ‘RE_CASE_INSENSITIVE’, ‘RE_MULTIPLE_LINES’, ‘RE_NEWLINE_WILDCARD’, ‘RE_IGNORE_WHITESPACE’.

Note: For more information on constants, function_parameters, or regexp (regular expressions) see, Using DBMS_REDACT Constants and Function Parameters.

custom_function_expression

The custom_function_expression is applicable only for the CUSTOM redaction type. The custom_function_expression is a function expression that is, schema-qualified function with a parameter such as schema_name.function_name (argument1, …) that allows a user to use their redaction logic to redact the column data.

Example

The following example illustrates how to create a policy and use full redaction for values in the payment_details_tab table customer id column.

edb=# CREATE TABLE payment_details_tab (
customer_id NUMBER       NOT NULL,
card_string VARCHAR2(19) NOT NULL);
CREATE TABLE

edb=# BEGIN
  INSERT INTO payment_details_tab VALUES (4000, '1234-1234-1234-1234');
  INSERT INTO payment_details_tab VALUES (4001, '2345-2345-2345-2345');
END;

EDB-SPL Procedure successfully completed

edb=# CREATE USER redact_user;
CREATE ROLE
edb=# GRANT SELECT ON payment_details_tab TO redact_user;
GRANT

\c edb base_user

BEGIN
  DBMS_REDACT.add_policy(
     object_schema              => 'public',
     object_name                => 'payment_details_tab',
     policy_name                => 'redactPolicy_001',
     policy_description         => 'redactPolicy_001 for payment_details_tab table',
     column_name                => 'customer_id',
     function_type              => DBMS_REDACT.full,
     expression                 => '1=1',
     enable                     => TRUE);
END;

Redacted Result:

edb=# \c edb redact_user
You are now connected to database "edb" as user "redact_user".

edb=> select customer_id from payment_details_tab order by 1;
 customer_id
-------------
           0
           0
(2 rows)

ALTER_POLICY

The alter_policy procedure alters or modifies an existing data redaction policy for a table.

PROCEDURE alter_policy (
  <object_schema>                IN VARCHAR2 DEFAULT NULL,
  <object_name>                  IN VARCHAR2,
  <policy_name>                  IN VARCHAR2,
  <action>                       IN INTEGER DEFAULT DBMS_REDACT.ADD_COLUMN,
  <column_name>                  IN VARCHAR2 DEFAULT NULL,
  <function_type>                IN INTEGER DEFAULT DBMS_REDACT.FULL,
  <function_parameters>          IN VARCHAR2 DEFAULT NULL,
  <expression>                   IN VARCHAR2 DEFAULT NULL,
  <regexp_pattern>               IN VARCHAR2 DEFAULT NULL,
  <regexp_replace_string>        IN VARCHAR2 DEFAULT NULL,
  <regexp_position>              IN INTEGER DEFAULT DBMS_REDACT.RE_BEGINNING,
  <regexp_occurrence>            IN INTEGER DEFAULT DBMS_REDACT.RE_ALL,
  <regexp_match_parameter>       IN VARCHAR2 DEFAULT NULL,
  <policy_description>           IN VARCHAR2 DEFAULT NULL,
  <column_description>           IN VARCHAR2 DEFAULT NULL,
  <custom_function_expression>   IN VARCHAR2 DEFAULT NULL
  )

Parameters

object_schema

Specifies the name of the schema in which the object resides and on which the data redaction policy will be altered. If you specify NULL then the given object is searched by the order specified by search_path setting.

object_name

Name of the table to which to alter a data redaction policy.

policy_name

Name of the policy to be altered.

action

The action to perform. For more information about action parameters see, DBMS_REDACT Constants and Function Parameters.

column_name

Name of the column to which the redaction policy applies.

function_type

The type of redaction function to be used. The possible values are NONE, FULL, PARTIAL, RANDOM, REGEXP, and CUSTOM.

function_parameters

Specifies the function parameters for the redaction function.

expression

Specifies the Boolean expression for the table and determines how the policy is to be applied. The redaction occurs if this policy expression is evaluated to TRUE.

regexp_pattern

Enables the use of regular expressions to redact data. If the regexp_pattern does not match the data, then the NULL value is returned.

regexp_replace_string

Specifies the replacement string value.

regexp_position

Specifies the position of a character where search must begin. By default, the function parameter is RE_BEGINNING.

regexp_occurence

regexp_match_parameter

Note: For more information on constants, function_parameters, or regexp (regular expressions) see, Using DBMS_REDACT Constants and Function Parameters.

policy_description

Specify the description of a redaction policy.

column_description

Description of the column to be redacted. The column_description is not supported, but if you specify the description for a column then, you will get a warning message.

custom_function_expression

Example

The following example illustrates to alter a policy using partial redaction for values in the payment_details_tab table card_string (usually a credit card number) column.

\c edb base _user

BEGIN
  DBMS_REDACT.alter_policy (
     object_schema               => 'public',
     object_name                 => 'payment_details_tab',
     policy_name                 => 'redactPolicy_001',
     action                      => DBMS_REDACT.ADD_COLUMN,
     column_name                 => 'card_string',
     function_type               => DBMS_REDACT.partial,
     function_parameters         => DBMS_REDACT.REDACT_CCN16_F12);
END;

Redacted Result:

edb=# \c - redact_user
You are now connected to database "edb" as user "redact_user".
edb=> SELECT * FROM payment_details_tab;
 customer_id |     card_string
-------------+---------------------
           0 | ****-****-****-1234
           0 | ****-****-****-2345
(2 rows)

DISABLE_POLICY

The disable_policy procedure disables an existing data redaction policy.

PROCEDURE disable_policy (
  <object_schema>       IN VARCHAR2 DEFAULT NULL,
  <object_name>         IN VARCHAR2,
  <policy_name>         IN VARCHAR2
  )

Parameters

object_schema

object_name

Name of the table for which to disable a data redaction policy.

policy_name

Name of the policy to be disabled.

Example

The following example illustrates how to disable a policy.

\c edb base_user

BEGIN
  DBMS_REDACT.disable_policy(
     object_schema => 'public',
     object_name => 'payment_details_tab',
     policy_name => 'redactPolicy_001');
END;

Redacted Result: Data is no longer redacted after disabling a policy.

ENABLE_POLICY

The enable_policy procedure enables the previously disabled data redaction policy.

PROCEDURE enable_policy (
  <object_schema>          IN VARCHAR2 DEFAULT NULL,
  <object_name>            IN VARCHAR2,
  <policy_name>            IN VARCHAR2
  )

Parameters

object_schema

object_name

Name of the table to which to enable a data redaction policy.

policy_name

Name of the policy to be enabled.

Example

The following example illustrates how to enable a policy.

\c edb base_user

BEGIN
  DBMS_REDACT.enable_policy(
     object_schema => 'public',
     object_name => 'payment_details_tab',
     policy_name => 'redactPolicy_001');
END;

Redacted Result: Data is redacted after enabling a policy.

DROP_POLICY

The drop_policy procedure drops a data redaction policy by removing the masking policy from a table.

PROCEDURE drop_policy (
  <object_schema>       IN VARCHAR2 DEFAULT NULL,
  <object_name>         IN VARCHAR2,
  <policy_name>         IN VARCHAR2
  )

Parameters

object_schema

object_name

Name of the table from which to drop a data redaction policy.

policy_name

Name of the policy to be dropped.

Example

The following example illustrates how to drop a policy.

\c edb base_user

BEGIN
  DBMS_REDACT.drop_policy(
     object_schema => 'public',
     object_name => 'payment_details_tab',
     policy_name => 'redactPolicy_001');
END;

Redacted Result: The server drops the specified policy.

UPDATE_FULL_REDACTION_VALUES

The update_full_redaction_values procedure updates the default displayed values for a data redaction policy and these default values can be viewed using the redaction_values_for_type_full view that use the full redaction type.

PROCEDURE update_full_redaction_values (
  <number_val>        IN NUMBER            DEFAULT NULL,
  <binfloat_val>      IN FLOAT4            DEFAULT NULL,
  <bindouble_val>     IN FLOAT8            DEFAULT NULL,
  <char_val>          IN CHAR              DEFAULT NULL,
  <varchar_val>       IN VARCHAR2          DEFAULT NULL,
  <nchar_val>         IN NCHAR             DEFAULT NULL,
  <nvarchar_val>      IN NVARCHAR2         DEFAULT NULL,
  <datecol_val>       IN DATE              DEFAULT NULL,
  <ts_val>            IN TIMESTAMP         DEFAULT NULL,
  <tswtz_val>         IN TIMESTAMPTZ       DEFAULT NULL,
  <blob_val>          IN BLOB              DEFAULT NULL,
  <clob_val>          IN CLOB              DEFAULT NULL,
  <nclob_val>         IN CLOB              DEFAULT NULL
  )

Parameters

number_val

Updates the default value for columns of the NUMBER datatype.

binfloat_val

The FLOAT4 datatype is a random value. The binary float datatype is not supported.

bindouble_val

The FLOAT8 datatype is a random value. The binary double datatype is not supported.

char_val

Updates the default value for columns of the CHAR datatype.

varchar_val

Updates the default value for columns of the VARCHAR2 datatype.

nchar_val

The nchar_val is mapped to CHAR datatype and returns the CHAR value.

nvarchar_val

The nvarchar_val is mapped to VARCHAR2 datatype and returns the VARCHAR value.

datecol_val

Updates the default value for columns of the DATE datatype.

ts_val

Updates the default value for columns of the TIMESTAMP datatype.

tswtz_val

Updates the default value for columns of the TIMESTAMPTZ datatype.

blob_val

Updates the default value for columns of the BLOB datatype.

clob_val

Updates the default value for columns of the CLOB datatype.

nclob_val

The nclob_val is mapped to CLOB datatype and returns the CLOB value.

Example

The following example illustrates how to update the full redaction values but before updating the values, you can:

View the default values using redaction_values_for_type_full view as shown below:

edb=# \x
Expanded display is on.
edb=# SELECT number_value, char_value, varchar_value, date_value,
       timestamp_value, timestamp_with_time_zone_value, blob_value,
clob_value
FROM redaction_values_for_type_full;
-[ RECORD 1 ]------------------+--------------------------
number_value                   | 0
char_value                     |
varchar_value                  |
date_value                     | 01-JAN-01 00:00:00
timestamp_value                | 01-JAN-01 01:00:00
timestamp_with_time_zone_value | 31-DEC-00 20:00:00 -05:00
blob_value                     | \x5b72656461637465645d
clob_value                     | [redacted]
(1 row)

Now, update the default values for full redaction type. The NULL values will be ignored.

\c edb base_user

edb=# BEGIN
    DBMS_REDACT.update_full_redaction_values (
    number_val => 9999999,
    char_val => 'Z',
    varchar_val => 'V',
    datecol_val => to_date('17/10/2018', 'DD/MM/YYYY'),
    ts_val => to_timestamp('17/10/2018 11:12:13', 'DD/MM/YYYY HH24:MI:SS'),
    tswtz_val => NULL,
    blob_val => 'NEW REDACTED VALUE',
    clob_val => 'NEW REDACTED VALUE');
END;

You can now see the updated values using redaction_values_for_type_full view.

EDB-SPL Procedure successfully completed
edb=# SELECT number_value, char_value, varchar_value, date_value,
       timestamp_value, timestamp_with_time_zone_value, blob_value,
clob_value
FROM redaction_values_for_type_full;
-[ RECORD 1 ]------------------+---------------------------------------
number_value                   | 9999999
char_value                     | Z
varchar_value                  | V
date_value                     | 17-OCT-18 00:00:00
timestamp_value                | 17-OCT-18 11:12:13
timestamp_with_time_zone_value | 31-DEC-00 20:00:00 -05:00
blob_value                     | \x4e45572052454441435445442056414c5545
clob_value                     | NEW REDACTED VALUE
(1 row)

Redacted Result:

edb=# \c edb redact_user
You are now connected to database "edb" as user "redact_user".

edb=> select * from payment_details_tab order by 1;
 customer_id | card_string
-------------+-------------
     9999999 | V
     9999999 | V
(2 rows)

← Prev

DBMS_RANDOM

↑ Up

Built-In Packages

DBMS_RLS

DBMS_REDACT v13

Using DBMS_REDACT Constants and Function Parameters

Note

Note

ADD_POLICY

ALTER_POLICY

DISABLE_POLICY

ENABLE_POLICY

DROP_POLICY

UPDATE_FULL_REDACTION_VALUES

← Prev

↑ Up

Next →