DBMS_REDACT

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

This chapter contains the following topics:

See Also:

DBMS_REDACT Overview

Data redaction provides a way to define redaction policies for an application. Oracle Data Redaction provides functionality to redact data that is returned from user SELECT queries in an application. The redaction 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 the data only 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.

DBMS_REDACT Security Model

If the querying user has the EXEMPT REDACTION POLICY privilege, redaction will not be performed. If the user does not have the EXEMPT REDACTION POLICY 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 must have the EXECUTE privilege on the DBMS_REDACT package, as well as the ADMINISTER REDACTION POLICY system or schema privilege, to execute its subprograms. Procedures in the interface are executed with privileges of the current user.

DBMS_REDACT Constants

The DBMS_REDACT package defines several constants for specifying parameter values.

Table 1 Values for function_type Parameter of DBMS_REDACT.ADD_POLICY

Constant Value Type Description
FULL 1 BINARY_INTEGER Redact to fixed values
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 that use the OCI_ATTR_CHAR_SIZE attribute of the Oracle OLE DB Provider interface
NULLIFY 6 BINARY_INTEGER Returns a null value as a redacted value
NONE 0 BINARY_INTEGER No redaction

Table 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

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 3 Data Redaction Function Types

function_type function_parameters Examples
DBMS_REDACT.NONE - -
DBMS_REDACT.FULL - -
DBMS_REDACT.NULLIFY - -
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 C-4.

'VVVFVVFVVVV,VVV-VV-VVVV,X,1,5' for redacting 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 redacting 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_FORMAT field 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 redacting 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 C-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 C-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 redacting of month) or 'm#' (redact 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 redacting of date) or 'd#' (redact day to #, if possible), # between 1 and 31

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

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

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

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

 -
DBMS_REDACT.REGEXP - -
DBMS_REDACT.REGEXP_WIDTH - -
DBMS_REDACT.RANDOM - -

Table 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

Summary of DBMS_REDACT Subprograms

This list briefly describes the DBMS_REDACT package subprograms.

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

object_schema

Description: Schema owning the table or view, current user if NULL

object_name

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

policy_name

Description: Name of policy

column_name

Description: [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

Description: Type of redaction function to use. Possible values are:

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

function_parameters

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

Use function_parameters parameter only for DBMS_REDACT.PARTIAL. Use regexp_* parameters only for DBMS_REDACT.REGEXP or DBMS_REDACT.REGEXP_WIDTH to define the Data Redaction policy.

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.

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

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

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

For partial character and number-redacting shortcuts, see Oracle AI Database Data Redaction Guide.

expression

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

See Oracle AI Database Data Redaction Guide for more information about these supported functions

enable

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

Description: Regular expression pattern up to 512 bytes.

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

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

regexp_replace_string

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

Description: Integer counting from 1, specifies the position where the search must begin. The default is 1.

Use only if the function_type parameter is DBMS_REDACT.REGEXP or DBMS_REDACT.REGEXP_WIDTH.

regexp_occurrence

Description:

Use only if the function_type parameter is DBMS_REDACT.REGEXP or DBMS_REDACT.REGEXP_WIDTH.

regexp_match_parameter

Description: 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 or DBMS_REDACT.REGEXP_WIDTH.

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

policy_description

Description: Description of redaction policy

column_description

Description: Description of the column being redacted

Exceptions

Usage Notes

If you specify 1=1 for the expression parameter, then it is always TRUE, which causes the redaction to be performed as long as the querying user is not exempt from the Oracle Data Redaction policy. Specifying 1=1 for the expression parameter will improve Data Redaction performance because Oracle Database will not evaluate the policy expression.

See DBMS_REDACT Security Model for a list of the required privileges for this procedure.

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

A Data Redaction named 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          => 'redact_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          => 'redact_emp_ids',
     function_type        => DBMS_REDACT.FULL,
     expression           => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''CLERK'')
                                                                    = ''FALSE''');
END;

ALTER_POLICY Procedure

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

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

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

object_schema

Description: Schema owning the table or view, current user if NULL

object_name

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

policy_name

Description: Name of policy limited to 30 bytes

action

Description: Action to take. For more information see Table C-2.

column_name

Description: (Optional) Name of one column to which the redaction policy applies. Required for the following actions:

function_type

Description: Type of redaction function to use. Possible values are:

If the function_type is not DBMS_REDACT.PARTIAL, then you must omit the function_parameters parameter.

If the function_type is not DBMS_REDACT.REGEXP or DBMS_REDACT.REGEXP_WIDTH, then you must omit the regexp_* parameters.

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

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

function_parameters

Description: Parameters to the redaction function. Use only if function_type is DBMS_REDACT.PARTIAL.

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.

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

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

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

For partial character and number-redacting shortcuts, see Oracle AI Database Data Redaction Guide.

expression

Description: Default boolean expression for the table or view. Redaction takes place only if this policy expression evaluates to TRUE.

The following functions are supported:

regexp_pattern

Description: Regular expression pattern up to 512 bytes.

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

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

regexp_replace_string

Description: 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 or DBMS_REDACT.REGEXP_WIDTH.

regexp_position

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

Use only if the function_type parameter is DBMS_REDACT.REGEXP or DBMS_REDACT.REGEXP_WIDTH. The default is 1 meaning that Oracle Database begins the search at the first character of the column_name data.

regexp_occurrence

Description:

Use only if the function_type parameter is DBMS_REDACT.REGEXP or DBMS_REDACT.REGEXP_WIDTH.

regexp_match_parameter

Description: Changes the default matching behavior, possible values are a combination of ‘i’, ‘c’, ‘ic’, ‘iq’, ‘n’, ‘m’, ‘x’.

The behavior of this parameter is the same for this function as for the REGEXP_REPLACE SQL function.

Use only if the function_type parameter is DBMS_REDACT.REGEXP or DBMS_REDACT.REGEXP_WIDTH.

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

policy_description

Description: Description of redaction policy

column_description

Description: Description of the column being redacted

Exceptions

Usage Notes

See DBMS_REDACT Security Model for a list of the required privileges for this procedure.

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

A Data Redaction named 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            => 'redact_emp_id_nums',
     action                 => DBMS_REDACT.DROP_COLUMN,
     column_name            => 'EMAIL');
END;

APPLY_POLICY_EXPR_TO_COL Procedure

This procedure associates an Oracle Data Redaction named 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

object_schema

Description: Name of the schema that contains the redacted column. If omitted, then the current schema is used.

object_name

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

column_name

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

policy_expression_name

Description: If NULL, then the named policy expression associated with the redacted column column_name is removed.

Exceptions

Usage Notes

See DBMS_REDACT Security Model for a list of the required privileges for this procedure.

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;

CREATE_POLICY_EXPRESSION Procedure

This procedure creates an Oracle Data Redaction named policy expression.

Syntax

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

Parameters

policy_expression_name

Description: Name of the policy expression

expression

Description: Definition of the policy expression.

policy_expression_description

Description: Description of the policy expression

Exceptions

Usage Notes

If the expression parameter is 1=1, then it is always TRUE, which causes the redaction to be performed as long as the querying user is not exempt from the Oracle Data Redaction policy. Specifying this parameter as 1=1 will improve Data Redaction performance because Oracle Database will not evaluate the policy expression.

See DBMS_REDACT Security Model for a list of the required privileges for this procedure.

See APPLY_POLICY_EXPR_TO_COL Procedure for how to apply policy expressions to table columns.

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;

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

object_schema

Description: Schema owning the table or view, current user if NULL

object_name

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

policy_name

Description: Name of policy to be disabled

Exceptions

Usage Notes

See DBMS_REDACT Security Model for a list of the required privileges for this procedure.

Examples

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

DROP_POLICY Procedure

This procedure drops a Data Redaction policy by removing a redaction 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

object_schema

Description: Schema owning the table or view, current user if NULL

object_name

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

policy_name

Description: Name of policy to be dropped

Exceptions

Usage Notes

See DBMS_REDACT Security Model for a list of the required privileges for this procedure.

Examples

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

DROP_POLICY_EXPRESSION Procedure

This procedure drops a named policy expression.

Syntax

DBMS_REDACT.DROP_POLICY_EXPRESSION (
   policy_expression_name       IN    VARCHAR2);

Parameters

policy_expression_name

Description: Name of the policy expression

Exceptions

Usage Notes

See DBMS_REDACT Security Model for a list of the required privileges for this procedure.

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;

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

object_schema

Description: Schema owning the table or view, current user if NULL

object_name

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

policy_name

Description: Name of policy to be enabled

Exceptions

Usage Notes

See DBMS_REDACT Security Model for a list of the required privileges for this procedure.

Examples

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

UPDATE_FULL_REDACTION_VALUES Procedure

This procedure modifies the redacted output that will be displayed when querying a column having a Data Redaction policy where the function_type is DBMS_REDACT.FULL. Restart the database instance to apply these changes.

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,
   boolean_val      IN BOOLEAN                   := NULL);

Parameters

number_val

Description: Modifies the default value for columns of the NUMBER datatype

binfloat_val

Description: Modifies the default value for columns of the BINARY_FLOAT datatype

bindouble_val

Description: Modifies the default value for columns of the BINARY_DOUBLE datatype

char_val

Description: Modifies the default value for columns of the CHAR datatype

varchar_val

Description: Modifies the default value for columns of the VARCHAR2 datatype

nchar_val

Description: Modifies the default value for columns of the NCHAR datatype

nvarchar_val

Description: Modifies the default value for columns of the NVARCHAR2 datatype

date

Description: Modifies the default value for columns of the DATE datatype

ts_val

Description: Modifies the default value for columns of the TIMESTAMP datatype

tswtz_val

Description: Modifies the default value for columns of the TIMESTAMP WITH TIME ZONEdatatype

blob_val

Description: Modifies the default value for columns of the BLOB datatype

clob_val

Description: Modifies the default value for columns of the CLOB datatype

nclob_val

Description: Modifies the default value for columns of the NCLOB datatype

boolean_val

Description: Modifies the default value for columns of the BOOLEAN datatype

Example C-1 Change the Current Value for Columns That Use the NUMBER Data Type

  1. Run the following:

    EXEC DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES (number_val => 7);

  2. Close the PDB:

    ALTER PLUGGABLE DATABASE <pdb_name> CLOSE IMMEDIATE;

  3. Re-open the PDB:

    ALTER PLUGGABLE DATABASE <pdb_name> OPEN;

Usage Notes

See DBMS_REDACT Security Model for a list of the required privileges for this procedure.

UPDATE_POLICY_EXPRESSION Procedure

This procedure updates an Oracle Data Redaction named policy expression.

Syntax

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

Parameters

policy_expression_name

Description: Name of the policy expression

expression

Description: Definition of the policy expression

policy_expression_description

Description: Description of the policy expression

Exceptions

Usage Notes

See DBMS_REDACT Security Model for a list of the required privileges for this procedure.

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  => 'Updates policy expression for oe_redact_pol');
END;