1. PL/SQL Packages and Types Reference
  2. DBMS_REDACT

136 DBMS_REDACT

The DBMS_REDACT package provides an interface to Oracle Data Redaction, which enables you to mask (redact) data that is returned from queries issued by low-privileged users or an application.

This chapter contains the following topics:

See Also:

136.1 DBMS_REDACT Overview

Data redaction provides a way to define masking policies for an application. Oracle Data Redaction provides functionality to mask (redact) data that is returned from user SELECT queries in an application. The masking takes place in real time. The Data Redaction policy applies to the querying user, depending on this user's SYS_CONTEXT or XS_SYS_CONTEXT values. This redaction process does not require that the queried data be static or unchanging, or for the entire data set to be redacted at one time in an off-line manner. Oracle Database redacts only the data for the rows specified by the user's query, not the data for the entire column. The redaction takes place immediately before the data is returned to the querying user or application.

136.2 DBMS_REDACT Security Model

If the querying user has the EXEMPT REDACTION POLICY system privilege, redaction will not be performed. If the user does not have the EXEMPT REDACTION POLICY system privilege, the policy expression will be evaluated in the current user's environment. If the policy expression evaluates to TRUE, then redaction will be performed, otherwise no redaction will be performed.

You need the EXECUTE privilege on the DBMS_REDACT package in order to execute its subprograms. Procedures in the interface are executed with privileges of the current user.

136.3 DBMS_REDACT Constants

The DBMS_REDACT package defines several constants for specifying parameter values.

Table 136-1 Values for function_type Parameter of DBMS_REDACT.ADD_POLICY

Constant Value Type Description

NONE

0

BINARY_INTEGER

No redaction

FULL

1

BINARY_INTEGER

Redact to fixed values

NULLIFY

6

BINARY_INTEGER

Returns a null value as a redacted value

PARTIAL

2

BINARY_INTEGER

Partial redaction, redact a portion of the column data

RANDOM

4

BINARY_INTEGER

Random redaction, each query results in a different random value

REGEXP

5

BINARY_INTEGER

Regular expression based redaction

REGEXP_WIDTH

7

BINARY_INTEGER

Regular expression based redaction that preserves the width of a column that uses a regular expression; designed for applications use the OCI_ATTR_CHAR_SIZE attribute of the Oracle OLE DB Provider interface

Table 136-2 Values for action Parameter of DBMS_REDACT.ALTER_POLICY

Constant Value Type Description

ADD_COLUMN

1

BINARY_INTEGER

Add a column to the redaction policy

DROP_COLUMN

2

BINARY_INTEGER

Drop a column from the redaction policy

MODIFY_EXPRESSION

3

BINARY_INTEGER

Modify the expression of a redaction policy (the expression evaluates to a BOOLEAN value: if TRUE then redaction is applied, otherwise not)

MODIFY_COLUMN

4

BINARY_INTEGER

Modify a column in the redaction policy to change the redaction function_type or the function_parameters

SET_POLICY_DESCRIPTION

5

BINARY_INTEGER

Set a description for the redaction policy

SET_COLUMN_DESCRIPTION

6

BINARY_INTEGER

Set a description for the redaction performed on the column

136.4 DBMS_REDACT Operating Procedures

The following table presents the relationship between the type of redaction function and its parameters, based on the datatype of the column being redacted. Examples of the various format strings are provided, showing how to perform some commonplace redaction for a string datatype (in this case, a Social Security Number (SSN)), a DATE datatype, and various examples of redaction for the number datatype.

Table 136-3 Data Redaction Function Types

function_type function_parameters Examples

DBMS_REDACT.NONE

-

-

DBMS_REDACT.FULL

-

-

DBMS_REDACT.PARTIAL (for character types)

A comma-separated list, containing the following five fields (with no spaces after the commas delimiting the fields):

  • REDACT_PARTIAL_INPUT_FORMAT

  • REDACT_PARTIAL_OUTPUT_FORMAT

  • REDACT_PARTIAL_MASKCHAR

  • REDACT_PARTIAL_MASKFROM

  • REDACT_PARTIAL_MASKTO

See Table 136-4.

'VVVFVVFVVVV,VVV-VV-VVVV,X,1,5' for masking the first 5 digits of SSN strings like 123-45-6789, adding dashes back to format it, resulting in strings like XXX-XX-6789

'VVVFVVFVVVV,VVV VV VVVV,X,1,5' for masking the first 5 digits of SSN strings like 123-45-6789, adding spaces to format it, resulting in strings like XXX XX 6789

DBMS_REDACT.PARTIAL (for character types), continued

REDACT_PARTIAL_INPUT_FORMAT - the input format: V for value to be possibly redacted, F for formatting character to be ignored

The REDACT_PARTIAL_INPUT_FORMAT field value VVVFVVFVVVV for matching SSN strings like 123-45-6789

DBMS_REDACT.PARTIAL (for character types), continued

REDACT_PARTIAL_OUTPUT_FORMAT - the output format: V for output of redaction. Any other character will be treated as a formatting character and output literally.

The REDACT_PARTIAL_OUTPUT_FORMATfield value VVV-VV-VVVV can be used to redact SSN strings into XXX-XX-6789 (X comes from REDACT_PARTIAL_MASKCHAR field).

DBMS_REDACT.PARTIAL (for character types), continued

REDACT_PARTIAL_MASKCHAR - the character used to redact the input

The value X for redacting SSN strings into XXX-XX-6789.

DBMS_REDACT.PARTIAL (for character types), continued

REDACT_PARTIAL_MASKFROM - specifies which V within the REDACT_PARTIAL_INPUT_FORMAT from which to start the redaction (see explanation following the next entry, REDACT_PARTIAL_MASKTO)

The value 1 for redacting SSN strings starting at the first V of REDACT_PARTIAL_INPUT_FORMAT of VVVFVVFVVVV into strings like XXX-XX-6789

DBMS_REDACT.PARTIAL (for character types), continued

REDACT_PARTIAL_MASKTO - specifies which V within the REDACT_PARTIAL_INPUT_FORMAT at which to end the redaction

The value 5 for redacting SSN strings up to and including the fifth V within REDACT_PARTIAL_INPUT_FORMAT of VVVFVVFVVVV into strings like XXX-XX-6789. Note how the format character '-' (corresponding to the first F within REDACT_PARTIAL_INPUT_FORMAT) is ignored as far as redaction is concerned, so the value here is 5 as opposed to 6.

DBMS_REDACT.PARTIAL (for character types), continued

The REDACT_PARTIAL_MASKFROM and REDACT_PARTIAL_MASKTO field values are specified as counts of the number of V characters in the REDACT_PARTIAL_INPUT_FORMAT field, up to and including the intended position, starting from the leftmost V. This way, REDACT_PARTIAL_MASKFROM and REDACT_PARTIAL_MASKTO are independent of the specific formatting of the data. For example, in the common use case of masking an SSN to show only the last four digits, data like 123456789 (with REDACT_PARTIAL_INPUT_FORMAT of VVVVVVVVV) and data like 123-45-6789 (with REDACT_PARTIAL_INPUT_FORMAT of VVVFVVFVVVV), would both use REDACT_PARTIAL_MASKFROM of 1 and REDACT_PARTIAL_MASKTO of 5.

-

DBMS_REDACT.PARTIAL (for number types)

A comma-separated list, containing the following three fields (with no spaces after the commas delimiting the fields):

  • REDACT_PARTIAL_MASKCHAR

  • REDACT_PARTIAL_MASKFROM

  • REDACT_PARTIAL_MASKTO

See Table 136-4.

'9,1,5' for redacting the first 5 digits of an SSN number 123456789 into 999996789; or '0,1,2' for redacting a number 1.23 to 0.03.

DBMS_REDACT.PARTIAL (for number types), continued

REDACT_PARTIAL_MASKCHAR - the character used to redact the input, in the range between 0 and 9

REDACT_PARTIAL_MASKFROM - the position, starting from 1, from which to start the redaction. The position does not include the decimal point if it is present.

REDACT_PARTIAL_MASKTO - the position at which to end the redaction

-

DBMS_REDACT.PARTIAL (for datetime datatypes)

A list, containing the following five fields (concatenated so that there is no space between the fields):

  • REDACT_PARTIAL_DATE_MONTH

  • REDACT_PARTIAL_DATE_DAY

  • REDACT_PARTIAL_DATE_YEAR

  • REDACT_PARTIAL_DATE_HOUR

  • REDACT_PARTIAL_DATE_MINUTE

  • REDACT_PARTIAL_DATE_SECOND

See Table 136-4.

'm12DYHMS', which changes 01-May-01 01:01:01 to 01-Dec-01 01:01:01.

DBMS_REDACT.PARTIAL (for datetime datatypes), continued

REDACT_PARTIAL_DATE_MONTH: 'M' (no masking of month) or 'm#' (mask month to a specific month, if possible), where # (the month specified by its number) is between 1 and 12

REDACT_PARTIAL_DATE_DAY: 'D' (no masking of date) or 'd#' (mask day to #, if possible), # between 1 and 31

REDACT_PARTIAL_DATE_YEAR: 'Y' (no masking of year) or 'y#' (mask year to #, if possible), # between 1 and 9999

REDACT_PARTIAL_DATE_HOUR: 'H' (no masking of hour) or 'h#' (mask hour to #, if possible), # between 0 and 23

REDACT_PARTIAL_DATE_MINUTE: 'M' (no masking of minute) or 'm#' (mask minute to #, if possible), # between 0 and 59

REDACT_PARTIAL_DATE_SECOND: 'S' (no masking of second) or 's#' (mask second to #, if possible), # between 0 and 59

-

DBMS_REDACT.PARTIAL_WIDTH

Same as DBMS_REDACT.PARTIAL

-

DBMS_REDACT.RANDOM

-

-

Table 136-4 Format Descriptors with Component Field Names and Delimiters

Datatype Format Descriptor for Partial redaction

Character

REDACT_PARTIAL_INPUT_FORMAT || ',' || REDACT_PARTIAL_OUTPUT_FORMAT || ',' || REDACT_PARTIAL_MASKCHAR || ',' || REDACT_PARTIAL_MASKFROM || ',' || REDACT_PARTIAL_MASKTO

Number

REDACT_PARTIAL_MASKCHAR || ',' || REDACT_PARTIAL_MASKFROM || ',' || REDACT_PARTIAL_MASKTO

Datetime

REDACT_PARTIAL_DATE_MONTH || REDACT_PARTIAL_DATE_DAY || REDACT_PARTIAL_DATE_YEAR || REDACT_PARTIAL_DATE_HOUR || REDACT_PARTIAL_DATE_MINUTE || REDACT_PARTIAL_DATE_SECOND

136.5 Summary of DBMS_REDACT Subprograms

This table lists and briefly describes the DBMS_REDACT package subprograms.

Table 136-5 DBMS_REDACT Package Subprograms

Subprogram Description

ADD_POLICY Procedure

Defines a Data Redaction policy for a table or view

ALTER_POLICY Procedure

Alters a Data Redaction policy for a table or view

APPLY_POLICY_EXPR_TO_COL Procedure

Applies a Data Redaction policy expression to a redacted column

CREATE_POLICY_EXPRESSION Procedure

Creates a Data Redaction policy expression

DISABLE_POLICY Procedure

Disables a Data Redaction policy

DROP_POLICY Procedure

Drops a Data Redaction policy

DROP_POLICY_EXPRESSION Procedure

Drops a Data Redaction policy expression

ENABLE_POLICY Procedure

Enables a Data Redaction policy

UPDATE_FULL_REDACTION_VALUES Procedure

Modifies the default displayed values for a Data Redaction policy for full redaction

UPDATE_POLICY_EXPRESSION Procedure

Updates a Data Redaction policy expression

136.5.1 ADD_POLICY Procedure

This procedure defines a Data Redaction policy for a table or view.

Syntax 

DBMS_REDACT.ADD_POLICY (
   object_schema                IN    VARCHAR2 := NULL,
   object_name                  IN    VARCHAR2,
   policy_name                  IN    VARCHAR2,
   column_name                  IN    VARCHAR2 := NULL,
   function_type                IN    BINARY_INTEGER := DBMS_REDACT.FULL,
   function_parameters          IN    VARCHAR2 := NULL,
   expression                   IN    VARCHAR2,
   enable                       IN    BOOLEAN := TRUE,
   regexp_pattern               IN    VARCHAR2 := NULL,
   regexp_replace_string        IN    VARCHAR2 := NULL,
   regexp_position              IN    BINARY_INTEGER := 1,
   regexp_occurrence            IN    BINARY_INTEGER := 0,
   regexp_match_parameter       IN    VARCHAR2 := NULL,
   policy_description           IN    VARCHAR2 := NULL,
   column_description           IN    VARCHAR2 := NULL);

Parameters

Table 136-6 ADD_POLICY Procedure Parameters

Parameter Description

object_schema

Schema owning the table, current user if NULL

object_name

Name of table or view to which to add a Data Redaction policy

policy_name

Name of policy

column_name

[Optional] Name of one column to which the redaction policy applies. If you must redact more than one column, use the ALTER_POLICY Procedure to add the additional columns.

function_type

Type of redaction function to use. Possible values are:

- DBMS_REDACT.NONE

- DBMS_REDACT.FULL (default)

- DBMS_REDACT.NULLIFY

- DBMS_REDACT.PARTIAL

- DBMS_REDACT.RANDOM

- DBMS_REDACT.REGEXP

- DBMS_REDACT.REGEXP_WIDTH

If the function_type is DBMS_REDACT.REGEXP or DBMS_REDACT.REGEXP_WIDTH, then you must omit the function_parameters parameter, and use the regexp_* parameters to define the Data Redaction policy.

See Table 136-1 for an overview of the meanings of these values, and for some examples of their use.

function_parameters

Parameters to the redaction function. The possible values depend on the value of the function_type provided.

If the function_type is DBMS_REDACT.REGEXP or DBMS_REDACT.REGEXP_WIDTH, then you must omit the function_parameters parameter, and use the regexp_* parameters to define the Data Redaction policy.

- DBMS_REDACT.NONE: Can be omitted entirely and defaults to NULL

- DBMS_REDACT.FULL: Can be omitted entirely and defaults to NULL

- Masking parameters for partial character masking. For character datatypes, a comma-separated list containing these fields:

  • Input format: 'V' for value to be possibly masked, 'F' for formatting character to be ignored

  • Output format: 'V' for output of masking, any other characters will be treated as formatting characters.

  • Mask character: a character that will be used to replace the actual values. Examples are '*' and 'x'.

  • Starting digit position: specifies the starting (character) position to begin replacing actual values with the masking character. The beginning of the string is position 1. Positions do not include formatting characters.

  • Ending digit position: specifies the ending (character) position to end masking. An example is 'VVVFVVFVVVV,VVV-VV-VVVV,X,1,5' for masking the first 5 digits of SSN string 123-45-6789, and adding dashes back to format it like an SSN, resulting in XXX-XX-6789.

For number datatypes, a comma-separated list containing these fields:

  • Mask character: this is a character between '0' to '9' that will be used to replace the actual values.

  • Starting digit position: specifies the starting (digit) position to begin replacing actual values with the masking character. The beginning of the string is position 1. Positions do not include the decimal point.

  • Ending digit position: this specifies the ending digit position to end masking. An example is '9,1,5' for masking the first 5 digits of a Social Security number number 123456789, resulting in 999996789.

For datetime datatypes, the format is a packed string (no spaces or commas) containing the following sequence of fields. Please note that each field can consist of one or more characters, and the field length depends on whether masking is required. The one-character fields are used to specify that no redaction of that component of the datetime value is to take place. The longer fields indicate a specific time or date to use as the redacted value of that component of the datetime value.

  • Month: 'M' (no masking of month) or 'm#' (mask month to a specific month, if possible), where # (the month specified by its number) is between 1 and 12

  • Day: 'D' (no masking of date) or 'd#' (mask day to #, if possible), # between 1 and 31

  • Year: 'Y' (no masking of year) or 'y#' (mask year to #, if possible), # between 1 and 9999

  • Hour: 'H' (no masking of hour) or 'h#' (mask hour to #, if possible), # between 0 and 23

  • Minute: 'M' (no masking of minute) or 'm#' (mask minute to #, if possible), # between 0 and 59

  • Second: 'S' (no masking of second) or 's#' (mask second to #, if possible), # between 0 and 59

An example is 'm12dyhs', which changes 02-May-13 12:30:23 to 01-Dec-01 01:01:01

For partial character and number-masking shortcuts, see Oracle Database Advanced Security Guide.

expression

Default boolean expression for the table or view. If this expression is used, then redaction takes place only if this policy expression evaluates to TRUE.

See

The following functions are supported:

  • SYS_CONTEXT

  • XS_SYS_CONTEXT

  • SUBSTR functions (includes SUBSTRB, SUBSTRC, SUBSTR2, SUBSTR4)

  • LENGTH functions (includes LENGTHB, LENGTHC, LENGTH2, LENGTH4)

  • V (APEX_UTIL.GET_SESSION_STATE)

  • NV (APEX_UTIL.GET_NUMERIC_SESSION_STATE)

  • OLS_LABEL_DOMINATES

  • DOMINATES

  • OLS_DOMINATES

  • OLS_DOM

  • DOM

  • OLS_STRICTLY_DOMINATES

  • STRICTLY_DOMINATES

  • S_DOM

  • SA_UTL.DOMINATES

  • SA_UTL.CHECK_READ

  • SA_UTL.NUMERIC_LABEL

  • CHAR_TO_LABEL

  • SA_SESSION.LABEL

See Oracle Database Advanced Security Guide for more information about these supported functions

enable

Boolean value that determines whether the Data Redaction policy is enabled on creation.

The default value is TRUE, which means that the policy is automatically enabled upon creation. If the enable parameter is set to FALSE, the policy takes effect only when it is subsequently enabled by calling the DBMS_REDACT.ENABLE_POLICY procedure.

regexp_pattern

Regular expression pattern up to 512 bytes.

Use only if the function_type parameter is DBMS_REDACT.REGEXP. Also, do not specify the function_parameters parameter when function_type is DBMS_REDACT.REGEXP.

See Oracle Database SQL Language Reference for more information and examples on using regular expression patterns.

regexp_replace_string

Replacement string (up to 4000 characters in length) with up to 500 back-references to subexpressions in the form \n, where n is a number between 1 and 9.

Use only if the function_type parameter is DBMS_REDACT.REGEXP

regexp_position

Integer counting from 1, specifies the position where the search must begin.

Use only if the function_type parameter is DBMS_REDACT.REGEXP

regexp_occurrence

  • Use 0 to replace all occurrences of the match

  • Use positive integer n to replace the n-th occurence of the match.

Use only if the function_type parameter is DBMS_REDACT.REGEXP

regexp_match_parameter

Changes the default matching behavior, possible values are a combination of 'i', 'c', 'n', 'm', 'x'

Use only if the function_type parameter is DBMS_REDACT.REGEXP

See Oracle Database SQL Language Reference for more information and examples on using regular expression match parameters.

policy_description

Description of redaction policy

column_description

Description of the column being redacted

Exceptions

  • ORA-28060 - A Data Redaction policy already exists on this column.

  • ORA-28061 - This object cannot have a Data Redaction policy defined on it.

  • ORA-28062 - The policy expression is too long.

  • ORA-28063 - The policy expression is empty.

  • ORA-28064 - The type of redaction function is not valid.

  • ORA-28066 - Invalid column column

  • ORA-28069 - A Data Redaction policy already exists on this object.

  • ORA-28073 - The column column_name has an unsupported datatype.

  • ORA-28074 - The field field_name of the masking parameters is not valid

    The field can be any of the following:

    • REDACT_PARTIAL_INPUT_FORMAT

    • REDACT_PARTIAL_OUTPUT_FORMAT

    • REDACT_PARTIAL_MASKCHAR

    • REDACT_PARTIAL_MASKFROM

    • REDACT_PARTIAL_MASKTO

    • REDACT_PARTIAL_DATE_MONTH

    • REDACT_PARTIAL_DATE_DAY

    • REDACT_PARTIAL_DATE_YEAR

    • REDACT_PARTIAL_DATE_HOUR

    • REDACT_PARTIAL_DATE_MINUTE

    • REDACT_PARTIAL_DATE_SECOND

    See Table 136-3 and Table 136-4 for examples of the field contents and field ordering.

  • ORA-28075 - The policy expression has unsupported functions

  • ORA-28076 - An attribute was not specified for SYS_SESSION_ROLES

  • ORA-28077 - The attribute specified (attribute) exceeds the maximum length

  • ORA-28078 - A regular expression parameter is missing or invalid

  • ORA-28082 - The parameter parameter is invalid (where the possible values are function_parameters, column_description , policy_name and policy_description )

  • ORA-28085 - The input and output lengths of the redaction do not match.

Usage Notes

See Operating Procedures for more information regarding function types and function parameters with related examples.

A named Data Redaction policy expression that has been applied to a redacted column takes precedence over the expression defined in the expression parameter. To find redacted columns that are affected by named policy expressions, query the REDACTION_EXPRESSIONS data dictionary view.

Example

Partial redaction policy:

BEGIN
   DBMS_REDACT.ADD_POLICY(
     object_schema        => 'hr',
     object_name          => 'employees',
     column_name          => 'employee_id',
     policy_name          => 'mask_emp_id_nums',
     function_type        => DBMS_REDACT.PARTIAL,
     function_parameters  => '7,1,5',
     expression           => '1=1');
END;

Full redaction policy:

BEGIN
   DBMS_REDACT.ADD_POLICY(
     object_schema        => 'hr',
     object_name          => 'employees',
     column_name          => 'employee_id',
     policy_name          => 'mask_emp_ids',
     function_type        => DBMS_REDACT.FULL,
     expression           => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''CLERK'') 
                                                                    = ''FALSE''');
END;

136.5.2 ALTER_POLICY Procedure

This procedure alters an existing Data Redaction policy for a table or view.

It alters a the policy in one or more of the following ways:

  • By changing the policy expression

  • By changing the type of redaction for a specified column

  • By changing the parameters to the redaction function for a specified column

  • By adding a column to the redaction policy (the redaction type and any parameters must be specified).

  • By removing a column from the redaction policy

Syntax 

DBMS_REDACT.ALTER_POLICY (
   object_schema                IN    VARCHAR2 := NULL,
   object_name                  IN    VARCHAR2,
   policy_name                  IN    VARCHAR2,
   action                       IN    BINARY_INTEGER := DBMS_REDACT.ADD_COLUMN,
   column_name                  IN    VARCHAR2 := NULL,
   function_type                IN    BINARY_INTEGER := DBMS_REDACT.FULL,
   function_parameters          IN    VARCHAR2 := NULL,
   expression                   IN    VARCHAR2,
   regexp_pattern               IN    VARCHAR2 := NULL,
   regexp_replace_string        IN    VARCHAR2 := NULL,
   regexp_position              IN    BINARY_INTEGER := 1,
   regexp_occurrence            IN    BINARY_INTEGER := 0,
   regexp_match_parameter       IN    VARCHAR2 := NULL,
   policy_description           IN    VARCHAR2 := NULL,
   column_description           IN    VARCHAR2 := NULL);

Parameters

Table 136-7 ALTER_POLICY Procedure Parameters

Parameter Description

object_schema

Schema owning the table, current user if NULL

object_name

Name of table or view to which to alter a Data Redaction policy

policy_name

Name of policy limited to 30 bytes

action

Action to take. For more information see Table 136-2.

column_name

[Optional] Name of one column to which the redaction policy applies.

function_type

Type of redaction function to use. Possible values are:

- DBMS_REDACT.NONE

- DBMS_REDACT.FULL (default)

- DBMS_REDACT.NULLIY

- DBMS_REDACT.PARTIAL

- DBMS_REDACT.RANDOM

- DBMS_REDACT.REGEXP

- DBMS_REDACT.REGEXP_WIDTH

If the function_type is DBMS_REDACT.REGEXP or DBMS_REDACT.REGEXP_WIDTH, then you must omit the function_parameters parameter, and use the regexp_pattern, regexp_replace_string, regexp_position, regexp_occurrence, and regexp_match_parameter to define the Data Redaction policy.

See Table 136-1 for an overview of the meanings of these values, and for some examples of their use.

function_parameters

Parameters to the redaction function. The possible values depend on the value of the function_type provided.

If the function_type is DBMS_REDACT.REGEXP or DBMS_REDACT.REGEXP_WIDTH, then you must omit the function_parameters parameter, and use the regexp_pattern, regexp_replace_string, regexp_position, regexp_occurrence, and regexp_match_parameter to define the Data Redaction policy.

- If the function_type is DBMS_REDACT.NONE, the function_parameters parameter can be omitted entirely and defaults to NULL.

- If the function_type is DBMS_REDACT.FULL, the function_parameters parameter can be omitted entirely and defaults to NULL.

- If the function_type is DBMS_REDACT.PARTIAL, the function_parameters parameter represents the masking parameters for partial masking.

  • Input format: 'V' for value to be possibly masked, 'F' for formatting character to be ignored

  • Output format: 'V' for output of masking, any other characters will be treated as formatting characters.

  • Mask character: a character that will be used to replace the actual values. Examples are '*' and 'x'.

  • Starting digit position: specifies the starting (character) position to begin replacing actual values with the masking character. The beginning of the string is position 1. Positions do not include formatting characters.

  • Ending digit position: specifies the ending (character) position to end masking. An example is 'VVVFVVFVVVV,VVV-VV-VVVV,X,1,5' for masking the first 5 digits of SSN string 123-45-6789, and adding dashes back to format it like an SSN, resulting in XXX-XX-6789.

For number datatypes, a comma-separated list containing these fields:

  • Mask character: this is a character between '0' to '9' that will be used to replace the actual values.

  • Starting digit position: specifies the starting (digit) position to begin replacing actual values with the masking character. The beginning of the string is position 1. Positions do not include the decimal point.

  • Ending digit position: this specifies the ending digit position to end masking. An example is '9,1,5' for masking the first 5 digits of a Social Security number number 123456789, resulting in 999996789.

For datetime datatypes, the format is a packed string (no spaces or commas) containing the following sequence of fields. Please note that each field can consist of one or more characters, and the field length depends on whether masking is required. The one-character fields are used to specify that no redaction of that component of the datetime value is to take place. The longer fields indicate a specific time or date to use as the redacted value of that component of the datetime value.

  • Month: 'M' (no masking of month) or 'm#' (mask month to a specific month, if possible), where # (the month specified by its number) is between 1 and 12

  • Day: 'D' (no masking of date) or 'd#' (mask day to #, if possible), # between 1 and 31

  • Year: 'Y' (no masking of year) or 'y#' (mask year to #, if possible), # between 1 and 9999

  • Hour: 'H' (no masking of hour) or 'h#' (mask hour to #, if possible), # between 0 and 23

  • Minute: 'M' (no masking of minute) or 'm#' (mask minute to #, if possible), # between 0 and 59

  • Second: 'S' (no masking of second) or 's#' (mask second to #, if possible), # between 0 and 59

An example is 'm12DYHMS', which changes 01-May-01 01:01:01 to 01-Dec-01 01:01:01

For partial character and number-masking shortcuts, see Oracle Database Advanced Security Guide.

expression

Default boolean expression for the table or view. If this expression is used, then redaction takes place only if this policy expression evaluates to TRUE.

The following functions are supported:

  • SYS_CONTEXT

  • NV (APEX_UTIL.GET_NUMERIC_SESSION_STATE)

  • V (APEX_UTIL.GET_SESSION_STATE)

  • OLS_LABEL_DOMINATES

  • DOMINATES

  • OLS_DOMINATES

  • OLS_DOM

  • DOM

  • OLS_STRICTLY_DOMINATES

  • STRICTLY_DOMINATES

  • S_DOM

  • SA_UTL.DOMINATES

  • SA_UTL.CHECK_READ

  • SA_UTL.NUMERIC_LABEL

  • CHAR_TO_LABEL

  • SA_SESSION.LABEL

regexp_pattern

Regular expression pattern up to 512 bytes.

Use only if the function_type parameter is DBMS_REDACT.REGEXP. Also, do not specify the function_parameters parameter when function_type is DBMS_REDACT.REGEXP.

See Oracle Database SQL Language Reference for more information and examples on using regular expression patterns

regexp_replace_string

Replacement string (up to 4000 characters in length) with up to 500 back-references to subexpressions in the form \n, where n is a number between 1 and 9.

Use only if the function_type parameter is DBMS_REDACT.REGEXP

regexp_position

Integer counting from 1, specifies the position where the search must begin.

Use only if the function_type parameter is DBMS_REDACT.REGEXP

regexp_occurrence

  • Use 0 to replace all occurrences of the match

  • Use positive integer n to replace the n-th occurence of the match.

Use only if the function_type parameter is DBMS_REDACT.REGEXP

regexp_match_parameter

Changes the default matching behavior, possible values are a combination of 'i', 'c', 'n', 'm', 'x'

Use only if the function_type parameter is DBMS_REDACT.REGEXP

See Oracle Database SQL Language Reference for more information and examples on using regular expression match parameters.

policy_description

Description of redaction policy

column_description

Description of the column being redacted

Exceptions

  • ORA-28062 - The policy expression is too long.

  • ORA-28063 - The policy expression is empty.

  • ORA-28064 - The type of redaction function is not valid.

  • ORA-28066 - Invalid column column

  • ORA-28067 - Missing or invalid column name

  • ORA-28068 - The object object does not have a Data Redaction policy.

  • ORA-28070 - The column column does not have a Data Redaction policy.

  • ORA-28071 - The action is not valid.

  • ORA-28072 - The specified policy name is incorrect.

  • ORA-28073 - The column column_name has an unsupported datatype.

  • ORA-28074 - The field field_name of the masking parameters is not valid

    The field can be any of the following:

    • REDACT_PARTIAL_INPUT_FORMAT

    • REDACT_PARTIAL_OUTPUT_FORMAT

    • REDACT_PARTIAL_MASKCHAR

    • REDACT_PARTIAL_MASKFROM

    • REDACT_PARTIAL_MASKTO

    • REDACT_PARTIAL_DATE_MONTH

    • REDACT_PARTIAL_DATE_DAY

    • REDACT_PARTIAL_DATE_YEAR

    • REDACT_PARTIAL_DATE_HOUR

    • REDACT_PARTIAL_DATE_MINUTE

    • REDACT_PARTIAL_DATE_SECOND

    See Table 136-3 and Table 136-4 for examples of the field contents and field ordering.

  • ORA-28075 - The policy expression has unsupported functions.

  • ORA-28076 - An attribute was not specified for SYS_SESSION_ROLES.

  • ORA-28077 - The attribute specified (attribute) exceeds the maximum length.

  • ORA-28078 - A regular expression parameter is missing or invalid.

  • ORA-28082 - The parameter parameter is invalid (where the possible values are function_parameters, column_description, policy_name and policy_description )

  • ORA-28085 - The input and output lengths of the redaction do not match.

Usage Notes

See Operating Procedures for more information regarding Function Types and Function Parameters with related examples.

A named Data Redaction policy expression that has been applied to a redacted column takes precedence over the expression defined in the expression parameter. To find redacted columns that are affected by named policy expressions, query the REDACTION_EXPRESSIONS data dictionary view.

Examples

BEGIN
   DBMS_REDACT.ALTER_POLICY(
     object_schema          => 'HR',
     object_name            => 'EMPLOYEES',
     policy_name            => 'mask_emp_id_nums',
     action                 => DBMS_REDACT.DROP_COLUMN,
     column_name            => 'EMAIL');
END;

136.5.3 APPLY_POLICY_EXPR_TO_COL Procedure

This procedure associates a named Oracle Data Redaction policy expression with a redacted column from a table or view.

Syntax 

DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL (
   object_schema                IN VARCHAR2 := NULL,
   object_name                  IN VARCHAR2,
   column_name                  IN VARCHAR2, 
   policy_expression_name       IN VARCHAR2 := NULL);

Parameters

Table 136-8 APPLY_POLICY_EXPR_TO_COL Procedure Parameters

Parameter Description

object_schema

Name of the schema that contains the redacted column

object_name

Name of the object (table or view) that contains the redacted column

column_name

Name of the redacted column to which the policy expression is applied

policy_expression_name

Name of the policy expression

Exceptions

  • ORA-28068 - The object object does not have a Data Redaction policy.

  • ORA–28082 - The parameter parameter is invalid.

  • ORA-28092 - The parameter parameter with value value has an error.

Usage Notes

You can find existing Data Redaction policy expressions by querying the REDACTION_EXPRESSIONS data dictionary view. To find columns that have been redacted, query the REDACTION_COLUMNS data dictionary view.

Example

BEGIN
   DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL(
      object_schema           => 'OE',
      object_name             => 'CUSTOMERS',
      column_name             => 'INCOME_LEVEL',
      policy_expression_name  => 'oe_redact_pol');
END;

136.5.4 CREATE_POLICY_EXPRESSION Procedure

This procedure creates a named Oracle Data Redaction policy expression.

Syntax 

DBMS_REDACT.CREATE_POLICY_EXPRESSION (
   policy_expression_name          IN    VARCHAR2,
   expression                      IN    VARCHAR2,
   policy_expression_description   IN    VARCHAR2 := NULL);

Parameters

Table 136-9 CREATE_POLICY_EXPRESSION Procedure Parameters

Parameter Description

policy_expression_name

Name of the policy expression

expression

Definition of the policy expression

policy_expression_description

Description of the policy expression

Exceptions

  • ORA–28082 - The parameter parameter is invalid.

  • ORA-28092 - The parameter parameter with value value has an error.

Usage Notes

See Operating Procedures for more information regarding function types and function parameters with related examples.

After you create a policy expression, you can associate it with a redacted table or view column by running the DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL procedure. To find existing redacted columns, query the REDACTION_COLUMNS data dictionary view.

Example

BEGIN
   DBMS_REDACT.CREATE_POLICY_EXPRESSION(
     policy_expression_name          => 'oe_redact_pol',
     expression                      => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''OE'''),
     policy_expression_description   => 'Enables policy for user OE ');
END;

136.5.5 DISABLE_POLICY Procedure

This procedure disables a Data Redaction policy.

Syntax 

DBMS_REDACT.DISABLE_POLICY (
   object_schema                IN    VARCHAR2 := NULL,
   object_name                  IN    VARCHAR2,
   policy_name                  IN    VARCHAR2);

Parameters

Table 136-10 DISABLE_POLICY Procedure Parameters

Parameter Description

object_schema

Schema owning the table or view, current user if NULL

object_name

Name of table or view for which to disable a Data Redaction policy

policy_name

Name of policy to be disabled

Exceptions

  • ORA-28068 - The object object does not have a Data Redaction policy.

  • ORA-28072 - The specified policy name is incorrect.

  • ORA-28080 - The policy was already disabled.

Examples

BEGIN
   DBMS_REDACT.DISABLE_POLICY (
     object_schema    => 'hr',
     object_name      => 'employees',
     policy_name      => 'mask_emp_ids');
END;

136.5.6 DROP_POLICY Procedure

This procedure drops a Data Redaction policy by removing a masking policy from the table or view.

Syntax 

DBMS_REDACT.DROP_POLICY (
   object_schema                IN    VARCHAR2 := NULL,
   object_name                  IN    VARCHAR2,
   policy_name                  IN    VARCHAR2);

Parameters

Table 136-11 DROP_POLICY Procedure Parameters

Parameter Description

object_schema

Schema owning the table or view, current user if NULL

object_name

Name of table or view from which to drop a Data Redaction policy

policy_name

Name of policy to be dropped

Exceptions

  • ORA-28068 - The object object does not have a Data Redaction policy.

  • ORA-28072 - The specified policy name is incorrect.

Examples

BEGIN
   DBMS_REDACT.DROP_POLICY (
      object_schema    =>  'hr',
      object_name      =>  'employees',
      policy_name      =>  'mask_emp_ids');
END;

136.5.7 DROP_POLICY_EXPRESSION Procedure

This procedure drops a named policy expression.

Syntax 

DBMS_REDACT.DROP_POLICY_EXPRESSION (
   policy_expression_name       IN    VARCHAR2);

Parameters

Table 136-12 DROP_POLICY_EXPRESSION Procedure Parameters

Parameter Description

policy_expression_name

Name of the policy expression

Exceptions

  • ORA–28082 - The parameter parameter is invalid.

  • ORA-28092 - The parameter parameter with value value has an error.

Usage Notes

You can find existing Data Redaction policy expressions by querying the REDACTION_EXPRESSIONS data dictionary view.

Example

BEGIN
   DBMS_REDACT.DROP_POLICY_EXPRESSION(
     policy_expression_name  => 'oe_redact_pol');
END;

136.5.8 ENABLE_POLICY Procedure

This procedure re-enables a Data Redaction policy.

Syntax 

DBMS_REDACT.ENABLE_POLICY (
   object_schema                IN    VARCHAR2 := NULL,
   object_name                  IN    VARCHAR2,
   policy_name                  IN    VARCHAR2);

Parameters

Table 136-13 ENABLE_POLICY Procedure Parameters

Parameter Description

object_schema

Schema owning the table or view, current user if NULL

object_name

Name of table or view to which to enable a Data Redaction policy

policy_name

Name of policy to be enabled

Exceptions

  • ORA-28068 - The object object does not have a Data Redaction policy.

  • ORA-28071 - The action is not valid.

  • ORA-28072 - The specified policy name is incorrect.

  • ORA-28079 - The policy was already enabled.

Examples

BEGIN
   DBMS_REDACT.ENABLE_POLICY (
      object_schema    =>  'hr',
      object_name      =>  'employees',
      policy_name      =>  'mask_emp_ids');
END;

136.5.9 UPDATE_FULL_REDACTION_VALUES Procedure

This procedure modifies the default displayed values for a Data Redaction policy for full redaction.

Syntax 

DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES (
   number_val       IN NUMBER                    :=  NULL,
   binfloat_val     IN BINARY_FLOAT              :=  NULL,
   bindouble_val    IN BINARY_DOUBLE             :=  NULL,
   char_val         IN CHAR                      :=  NULL,
   varchar_val      IN VARCHAR2                  :=  NULL,
   nchar_val        IN NCHAR                     :=  NULL,
   nvarchar_val     IN NVARCHAR2                 :=  NULL,
   date_val         IN DATE                      :=  NULL,
   ts_val           IN TIMESTAMP                 :=  NULL,
   tswtz_val        IN TIMESTAMP WITH TIME ZONE  :=  NULL,
   blob_val         IN BLOB                      :=  NULL,
   clob_val         IN CLOB                      :=  NULL,
   nclob_val        IN NCLOB                     NULL);

Parameters

Table 136-14 UPDATE_FULL_REDACTION_VALUES Procedure Parameters

Parameter Description

number_val

Modifies the default value for columns of the NUMBER datatype

binfloat_val

Modifies the default value for columns of the BINARY_FLOAT datatype

bindouble_val

Modifies the default value for columns of the BINARY_DOUBLE datatype

char_val

Modifies the default value for columns of the CHAR datatype

varchar_val

Modifies the default value for columns of the VARCHAR2 datatype

nchar_val

Modifies the default value for columns of the NCHAR datatype

nvarchar_val

Modifies the default value for columns of the NVARCHAR2 datatype

date

Modifies the default value for columns of the DATE datatype

ts_val

Modifies the default value for columns of the TIMESTAMP datatype

tswtz_val

Modifies the default value for columns of the TIMESTAMP WITH TIME ZONE datatype

blob_val

Modifies the default value for columns of the BLOB datatype

clob_val

Modifies the default value for columns of the CLOB datatype

nclob_val

Modifies the default value for columns of the NCLOB datatype

Exceptions

ORA-28082 - The parameter parameter is invalid (where the possible values are char_val, nchar_val, varchar_val and nvarchar_val)

136.5.10 UPDATE_POLICY_EXPRESSION Procedure

This procedure updates a named Oracle Data Redaction policy expression.

Syntax 

DBMS_REDACT.UPDATE_POLICY_EXPRESSION (
   policy_expression_name          IN    VARCHAR2,
   expression                      IN    VARCHAR2,
   policy_expression_description   IN    VARCHAR2 := NULL);

Parameters

Table 136-15 UPDATE_POLICY_EXPRESSION Procedure Parameters

Parameter Description

policy_expression_name

Name of the policy expression

expression

Definition of the policy expression

policy_expression_description

Description of the policy expression

Exceptions

  • ORA–28082 - The parameter parameter is invalid.

  • ORA-28092 - The parameter parameter with value value has an error.

Usage Notes

You can find existing policy expressions by querying the REDACTION_EXPRESSIONS data dictionary view.

Example

BEGIN
   DBMS_REDACT.UPDATE_POLICY_EXPRESSION(
     policy_expression_name         => 'oe_redact_pol',
     expression                     => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''OE'''),
     policy_expression_description  => 'Disables policy for user OE ');
END;