DBMS_REDACT v11

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/ProcedureFunction or ProcedureReturn TypeDescription
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)Proceduren/aAdds 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)Proceduren/aAlters the existing data redaction policy.
DISABLE_POLICY(object_schema, object_name, policy_name)Proceduren/aDisables the existing data redaction policy.
ENABLE_POLICY(object_schema, object_name, policy_name)Proceduren/aEnables a previously disabled data redaction policy.
DROP_POLICY(object_schema, object_name, policy_name)Proceduren/aDrops 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)Proceduren/aUpdates 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.

ConstantTypeValueDescription
NONEINTEGER0No redaction, zero effect on the result of a query against table.
FULLINTEGER1Full redaction, redacts full values of the column data.
PARTIALINTEGER2Partial redaction, redacts a portion of the column data.
RANDOMINTEGER4Random redaction, each query results in a different random value depending on the datatype of the column.
REGEXPINTEGER5Regular Expression based redaction, searches for the pattern of data to redact.
CUSTOMINTEGER99Custom redaction type.

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

ConstantTypeValueDescription
ADD_COLUMNINTEGER1Adds a column to the redaction policy.
DROP_COLUMNINTEGER2Drops a column from the redaction policy.
MODIFY_EXPRESSIONINTEGER3Modifies the expression of a redaction policy. The redaction is applied when the expression evaluates to the BOOLEAN value to TRUE.
MODIFY_COLUMNINTEGER4Modifies a column in the redaction policy to change the redaction function type or function parameter.
SET_POLICY_DESCRIPTIONINTEGER5Sets the redaction policy description.
SET_COLUMN_DESCRIPTIONINTEGER6Sets 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.

DatatypeFormat DescriptorDescriptionExamples
CharacterREDACT_PARTIAL_INPUT_FORMATSpecifies 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_FORMATSpecifies 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_MASKCHARSpecifies the character to be used for redaction.The value X for redacting SSN strings into XXX-XX-6789.
REDACT_PARTIAL_MASKFROMSpecifies 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_MASKTOSpecifies 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.
NumberREDACT_PARTIAL_MASKCHARSpecifies 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_MASKFROMSpecifies the start digit position for redaction.
REDACT_PARTIAL_MASKTOSpecifies the end digit position for redaction.
DatetimeREDACT_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 ParameterData TypeValueDescription
REDACT_US_SSN_F5VARCHAR2'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_L4VARCHAR2'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_ENTIREVARCHAR2'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_F5VARCHAR2'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_L4VARCHAR2'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_ENTIREVARCHAR2'9,1,9'Redacts the entire SSN when the column is a number datatype. Example: The number 123456789 becomes 999999999.
REDACT_ZIP_CODEVARCHAR2'VVVVV,VVVVV,X,1,5'Redacts a 5 digit zip code. Example: 12345becomes XXXXX.
REDACT_NUM_ZIP_CODEVARCHAR2'9,1,5'Redacts a 5 digit zip code when the column is a number datatype. Example: 12345becomes 99999.
REDACT_CCN16_F12VARCHAR2'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_MILLENNIUMVARCHAR2'm1d1y2000'Redacts a date that is in the DD-MM-YY format. Example: Redacts all date to 01-JAN-2000.
REDACT_DATE_EPOCHVARCHAR2'm1d1y1970'Redacts all dates to 01-JAN-70.
REDACT_AMEX_CCN_FORMATTEDVARCHAR2'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_NUMBERVARCHAR2'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_FORMATTEDVARCHAR2'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_NUMBERVARCHAR2'9,1,6'Redacts the Social Insurance Number by replacing the first 6 digits by 9. Example: 123456789 becomes 999999789.
REDACT_SIN_UNFORMATTEDVARCHAR2'VVVVVVVVV,VVVVVVVVV,*,1,6'Redacts the Social Insurance Number by replacing the first 6 digits by *. Example: 123456789 becomes ******789.
REDACT_CCN_FORMATTEDVARCHAR2'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_NUMBERVARCHAR2'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_FORMATTEDVARCHAR2‘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_NUMBERVARCHAR2'0,4,10'Redacts the North American phone number by 0 leaving the area code. Example: 1234567890 becomes 1230000000.
REDACT_NA_PHONE_UNFORMATTEDVARCHAR2'VVVVVVVVVV,VVVVVVVVVV,X,4,10'Redacts the North American phone number by X leaving the area code. Example: 1234567890 becomes 123XXXXXXX.
REDACT_UK_NIN_FORMATTEDVARCHAR2'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_UNFORMATTEDVARCHAR2'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 DescriptionData TypeValue
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 ParameterData TypeValueDescription
RE_REDACT_CC_MIDDLE_DIGITSVARCHAR2'\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_XVARCHAR2'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_1VARCHAR2'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_L7VARCHAR2'\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_NAMEVARCHAR2'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_DOMAINVARCHAR2'\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_ENTIREVARCHAR2'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_L3VARCHAR2'\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_CCNVARCHAR2'**********\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_CCNVARCHAR2'************\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 ParameterData TypeValueDescription
RE_BEGINNINGINTEGER1Specifies 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 ParameterData TypeValueDescription
RE_ALLINTEGER0Specifies the replacement occurrence of a substring. If the value is 0, then the replacement of each matching substring occurs.
RE_FIRSTINTEGER1Specifies 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 ParameterData TypeValueDescription
RE_CASE_SENSITIVEVARCHAR2'c'Specifies the case-sensitive matching.
RE_CASE_INSENSITIVEVARCHAR2'i'Specifies the case-insensitive matching.
RE_MULTIPLE_LINESVARCHAR2'm'Treats the source string as multiple lines but if you omit this parameter, then it indicates as a single line.
RE_NEWLINE_WILDCARDVARCHAR2'n'Specifies the period (.), but if you omit this parameter, then the period does not match the newline character.
RE_IGNORE_WHITESPACEVARCHAR2'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

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.

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

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

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

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

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