Table of Contents Previous Next


3 Built-In Packages : 3.13 DBMS_REDACT

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.
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)
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)
DISABLE_POLICY(object_schema, object_name, policy_name)
ENABLE_POLICY(object_schema, object_name, policy_name)
DROP_POLICY(object_schema, object_name, policy_name)
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)
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.
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.
The following table shows the values for the action parameter of dbms_redact.alter_policy.
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 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.
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.
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.
The value X for redacting SSN strings into XXX-XX-6789.
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.
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.
‘9, 1, 5’ for redacting the first five digits of the Social Security Number 123456789 into 999996789.
‘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.
‘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.
‘y’ redacts the year. To mask with a year, append 1-9999 to a lowercase y.
y1960 displays as 60.
‘h’ redacts the hour. To mask with an hour, append 0-23 to a lowercase h.
h18 displays as 18.
‘m’ redacts the minute. To mask with a minute, append 0-59 to a lowercase m.
m20 displays as 20.
‘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.
Example: The number 123-45-6789 becomes XXX-XX-6789.
Example: The number 123-45-6789 becomes 123-45-XXXX.
Example: The number 123-45-6789 becomes XXX-XX-XXXX.
Example: The number 123456789 becomes 999996789.
Example: The number 123456789 becomes 123459999.
Example: The number 123456789 becomes 999999999.
Example: 12345 becomes XXXXX.
Example: 12345 becomes 99999.
Example: 1234 5678 9000 2358 becomes ****-****-****-2358.
Example: Redacts all date to 01-JAN-2000.
Example: The credit card number 1234 567890 34500 becomes **** ****** 34500.
Example: The credit card number 1234 567890 34500 becomes 0000 000000 34500.
Example: 123-456-789 becomes ***-***-789.
Example: 123456789 becomes 999999789.
Example: 123456789 becomes ******789.
Redacts a credit card number by * and displays only 4 digits.
Example: The credit card number 1234-5678-9000-4671 becomes ****-****-****-4671.
Redacts a credit card number by 0 except the last 4 digits.
Example: The credit card number 1234567890004671 becomes 0000000000004671.
Example: 123-456-7890 becomes 123-XXX-XXXX.
Example: 1234567890 becomes 1230000000.
Example: 1234567890 becomes 123XXXXXXX.
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.
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.
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
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).
regexp_replace_string=> RE_REDACT_US_PHONE_L7 (searches the phone number and then replaces the last 7 digits).
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).
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.
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.
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.
'\1-XXX-XXXX' or 'XXX-XXX-\3' will return 123-XXX-XXXX or XXX-XXX-6789 for the value '123-45-6789' respectively.
The below table illustrates the regexp_replace_string values that you can use during REGEXP based redaction.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Specifies the replacement occurrence of a substring. If the value is 0, then the replacement of each matching substring occurs.
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.
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.
3.13.2 ADD_POLICY
The add_policy procedure creates a new data redaction policy for a table.
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
Name of the policy to be added. Ensure that the policy_name is unique for the table on which the policy is created.
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.
The type of redaction function to be used. The possible values are NONE, FULL, PARTIAL, RANDOM, REGEXP, and CUSTOM.
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.
Specifies the regular expression pattern to redact data. If the regexp_pattern does not match, then the NULL value is returned.
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.
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.
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.

3 Built-In Packages : 3.13 DBMS_REDACT

Table of Contents Previous Next