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/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.
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.
Redacted Result:
ALTER_POLICY
The alter_policy
procedure alters or modifies an existing data redaction policy for a table.
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.
Redacted Result:
DISABLE_POLICY
The disable_policy
procedure disables an existing data redaction policy.
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.
Redacted Result: Data is no longer redacted after disabling a policy.
ENABLE_POLICY
The enable_policy
procedure enables the previously disabled data redaction policy.
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.
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.
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.
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.
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:
Now, update the default values for full redaction type. The NULL
values will be ignored.
You can now see the updated values using redaction_values_for_type_full
view.
Redacted Result: