DBMS_REDACT v14
The DBMS_REDACT
package enables you to redact or mask data returned by a query. The DBMS_REDACT
package provides a procedure to create, alter, enable, disable, 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.
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 superuser 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 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 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 a 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 use for redaction. | The value X for redacting SSN strings into XXX-XX-6789 . | |
REDACT_PARTIAL_MASKFROM | Specifies the V in 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 the V in the input format at which to end the redaction. | The value 5 for redacting SSN strings up to and including the fifth V in the input format of VVVFVVFVVVV into strings such as XXX-XX-6789 . | |
Number | REDACT_PARTIAL_MASKCHAR | Specifies the character to display 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 |
|