5 Configuring Oracle Data Redaction Policies

5.5.1 About Using Expressions in Data Redaction Policies

The expression parameter of the DBMS_REDACT.ADD_POLICY procedure defines a Boolean expression that must evaluate to TRUE before the redaction can table place.

This expression must be based on one of the following functions:

• SYS_CONTEXT, using a specified namespace. The default namespace for SYS_CONTEXT is USERENV, which includes values such as SESSION_USER and CLIENT_IDENTIFIER. (See Oracle Database SQL Language Reference for detailed information about this function.) Another namespace that you can use is the SYS_SESSION_ROLES namespace, which contains attributes for each role.

• The following Oracle Application Express functions:

  • V, which is a wrapper for the APEX_UTIL.GET_SESSION_STATE function

  • NV, which is a wrapper for the APEX_UTIL.GET_NUMERIC_SESSION_STATE function

  See Oracle Application Express API Reference for more information about these APEX_UTIL package functions.

• The OLS_LABEL_DOMINATES function, described in Oracle Label Security Administrator's Guide, which is a wrapper for the LBACSYS.OLS_LABEL_DOMINATES function.

Follow these guidelines when you write the expression:

• Use only the following operators: =, !=, >, <, >=, <=

• Because the expression must evaluate to TRUE for redaction, be careful when making comparisons with NULL. Remember that in SQL the value NULL is undefined, so comparisons with NULL tend to return FALSE.

• Do not use user-created functions in the expression parameter; this is not permitted.

Remember that for user SYS and users who have the EXEMPT REDACTION POLICY privilege, all of the Data Redaction policies are bypassed, so the results of their queries are not redacted. See for more information about users who are exempted from Data Redaction policies.

Remember that for user SYS and users who have the EXEMPT REDACTION POLICY privilege, all of the Data Redaction policies are bypassed, so the results of their queries are not redacted. See "Exempting Users from Oracle Data Redaction Policies" for more information about users who are exempted from Data Redaction policies.

5.5.2 Applying the Redaction Policy Based on User Environment

To apply a Data Redaction policy based on the user's environment (such as the session user name or client identifier), you can use the USERENV namespace of the SYS_CONTEXT function in the DBMS_REDACT.ADD_POLICY expression parameter.

Example 5-1 shows how to apply the policy only to the session user name psmith.

expression  => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''PSMITH'''

5.5.3 Applying the Redaction Policy Based on Database Role

To apply a Data Redaction policy based on database roles, you can use the SYS_SESSION_ROLES namespace in the SYS_CONTEXT function, which contains attributes for each role. The value of the attribute is TRUE if the specified role is enabled for the querying application user; the value is FALSE if the role is not enabled.

For example, suppose you wanted only supervisors to be allowed to see the actual data. Example 5-2 shows how to use the DBMS_REDACT.ADD_POLICY expression parameter to set the policy to show the actual data to any application user who has the supervisor role enabled, but redact the data for all of the other application users.

expression  => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''SUPERVISOR'') = ''FALSE'''

5.5.4 Applying the Redaction Policy Based on Oracle Application Express Session States

To apply a Data Redaction policy based on an Oracle Application Express (APEX) session state, you can use either of the following public Application Express APIs in the DBMS_REDACT.ADD_POLICY expression parameter:

• V, which is a synonym for the APEX_UTIL.GET_SESSION_STATE function

• NV, which is a synonym for the APEX_UTIL.GET_NUMERIC_SESSION_STATE function

You can, for example, use these functions to redact data based on a job or a privilege role that is stored in a session state in an APEX application.

Example 5-3 shows how to set the DBMS_REDACT.ADD_POLICY expression parameter if you wanted redaction to take place when the application item called G_JOB has the value CLERK.

expression => 'V'(''G_JOB'') = ''CLERK'''

If you want redaction to take place when the querying user is not within the context of an APEX application (when the query is issued from outside the APEX framework, for example directly through SQL*Plus), then use an IS NULL clause as follows. This policy expression causes actual data to be shown to user mavis only when her query comes from within an APEX application. Otherwise, the query result is redacted.

expression => 'V(''APP_USER'') != ''mavis@example.com'' or V(''APP_USER'') is null'

5.5.5 Applying the Redaction Policy with No Filtering

You can apply the policy irrespective of the context to any user, with no filtering. However, be aware that user SYS and users who have the EXEMPT REDACTION POLICY privilege are always except from Oracle Data Redaction policies. To apply the policy to users who are not SYS or have been granted the EXEMPT REDACTION POLICY privilege, write the DBMS_REDACT.ADD_POLICY expression parameter to evaluate to TRUE, as shown Example 5-4.

expression  => '1=1'

5.6.1 Creating a Full Redaction Policy

This section contains:

• About Creating Full Data Redaction Policies

• Syntax for Creating a Full Redaction Policy

• Examples of Full Data Redaction Policies

DBMS_REDACT.ADD_POLICY (
   object_schema           IN VARCHAR2 := NULL, 
   object_name             IN VARCHAR2,
   column_name             IN VARCHAR2 := NULL,
   policy_name             IN VARCHAR2,
   function_type           IN BINARY_INTEGER := NULL,
   expression              IN VARCHAR2,
   enable                  IN BOOLEAN := TRUE);

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema    => 'hr', 
   object_name      => 'employees', 
   column_name      => 'commission_pct',
   policy_name      => 'redact_com_pct', 
   function_type    => DBMS_REDACT.FULL,
   expression       => '1=1');
END;
/

SELECT COMMISSION_PCT FROM HR.EMPLOYEES;

COMMISSION_PCT
--------------
0
0
0

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema   => 'mavis', 
   object_name     => 'cust_info', 
   column_name     => 'user_id',
   policy_name     => 'redact_cust_user_ids', 
   function_type   => DBMS_REDACT.FULL,
   expression      => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''MGR'') = ''FALSE''');
END;
/

SELECT user_id FROM mavis.cust_info;

USER_ID
------------
0
0
0

5.6.2 Altering the Default Full Data Redaction Value

To alter the default full data redaction value, you use the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure to modify this value.

This section contains:

• About Altering the Default Full Data Redaction Value

• Altering the Default Full Data Redaction Value for Non-LOB Data Type Columns

• Altering the Default Full Data Redaction Value for LOB Data Type Columns

5.7.1 About Creating Partial Redaction Policies

In partial data redaction, only a portion of the data, such as the first five digits of an identification number, are redacted. For example, you can redact most of a credit card number with asterisks (*), except for the last 4 digits. You can create policies for columns that use character, number, or date-time data types. For policies that redact character data types, you can use fixed character redaction shortcuts.

5.7.2 Syntax for Creating a Partial Redaction Policy

The DBMS_REDACT.ADD_POLICY fields for creating a partial redaction policy are as follows:

DBMS_REDACT.ADD_POLICY (
   object_schema           IN VARCHAR2 := NULL, 
   object_name             IN VARCHAR2,
   column_name             IN VARCHAR2 := NULL,
   policy_name             IN VARCHAR2,
   function_type           IN BINARY_INTEGER := NULL,
   function_parameters     IN VARCHAR2 := NULL,
   expression              IN VARCHAR2,
   enable                  IN BOOLEAN := TRUE);

In this specification:

• object_schema, object_name, column_name, policy_name, expression, enable: See "General Syntax of the DBMS_REDACT.ADD_POLICY Procedure"

• function_type: Specifies the function used to set the type of redaction. Enter DBMS_REDACT.PARTIAL.

• function_parameters: The parameters that you set here depend on the data type of the column specified for the column_name parameter. See the following sections for details:

  • "Creating Partial Redaction Policies Using Fixed Character Shortcuts"

  • "Creating Partial Redaction Policies Using Character Data Types"

  • "Creating Partial Redaction Policies Using Number Data Types"

  • "Creating Partial Redaction Policies Using Date-Time Data Types"

5.7.3 Creating Partial Redaction Policies Using Fixed Character Shortcuts

The DBMS_REDACT.ADD_POLICY function_parameters parameter enables you to use fixed character shortcuts.

This section contains:

• Settings for Fixed Character Shortcuts

• Example of a Partial Redaction Policy Using a Fixed Character Shortcut

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'mavis', 
   object_name         => 'cust_info', 
   column_name         => 'ssn',
   policy_name         => 'redact_cust_ssns3', 
   function_type       => DBMS_REDACT.PARTIAL,
   function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,
   expression          => '1=1',
   policy_description  => 'Partially redacts 1st 5 digits in SS numbers',
   column_description  => 'ssn contains Social Security numbers');
END;
/

SELECT ssn FROM mavis.cust_info;

SSN
-------
XXX-XX-4320
XXX-XX-4323
XXX-XX-4325
XXX-XX-4329

5.7.4 Creating Partial Redaction Policies Using Character Data Types

The DBMS_REDACT.ADD_POLICY function_parameters parameter enables you to redact character data types.

This section contains:

• Settings for Character Data Types

• Example of a Partial Redaction Policy Using Character a Data Type

function_parameters  => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12',

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'mavis', 
   object_name         => 'cust_info', 
   column_name         => 'ssn',
   policy_name         => 'redact_cust_ssns2', 
   function_type       => DBMS_REDACT.PARTIAL,
   function_parameters => 'VVVFVVFVVVV,VVV-VV-VVVV,*,1,5',
   expression          => '1=1',
   policy_description  => 'Partially redacts Social Security numbers',
   column_description  => 'ssn contains character Social Security numbers');
END;
/

SELECT ssn FROM mavis.cust_info;

SSN
-----------
***-**-4320
***-**-4323
***-**-4325
***-**-4329

5.7.5 Creating Partial Redaction Policies Using Number Data Types

The DBMS_REDACT.ADD_POLICY function_parameters parameter enables you to redact number data types.

This section contains:

• Settings for Number Data Types

• Example of a Partial Redaction Policy Using a Number Data Type

function_parameters  => '9,1,5',

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'mavis', 
   object_name         => 'cust_info', 
   column_name         => 'ssn',
   policy_name         => 'redact_cust_ssns1', 
   function_type       => DBMS_REDACT.PARTIAL,
   function_parameters => '7,1,5',
   expression          => '1=1',
   policy_description  => 'Partially redacts Social Security numbers',
   column_description  => 'ssn contains numeric Social Security numbers');
END;
/

SELECT ssn FROM mavis.cust_info;

SSN
---------
777774320
777774323
777774325
777774329

5.7.6 Creating Partial Redaction Policies Using Date-Time Data Types

The DBMS_REDACT.ADD_POLICY function_parameters parameter enables you to redact date-time data types.

This section contains:

• Settings for Date-Time Data Types

• Example of a Partial Redaction Policy Using Date-Time Data Type

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'mavis', 
   object_name         => 'cust_info', 
   column_name         => 'birth_date',
   policy_name         => 'redact_cust_bdate', 
   function_type       => DBMS_REDACT.PARTIAL,
   function_parameters => 'mdy2013HMS',
   expression          => '1=1',
   policy_description  => 'Replaces birth year with 2013',
   column_description  => 'birth_date contains customer's birthdate');
END;
/

SELECT birth_date FROM mavis.cust_info;

BIRTH_DATE
07-DEC-13 09.45.40.000000 AM
12-OCT-13 04.23.29.000000 AM

5.8.1 About Creating Regular Expression-Based Redaction Policies

Regular expression-based redaction enables you to search for patterns of data to redact. For example, you can use regular expressions to redact email addresses, which can have varying character lengths. It is designed for use with character data only. You can use shortcuts for the search and replace operation, or you can create custom patterns.

You cannot use regular expressions to redact a subset of the values in a column. The REGEXP_PATTERN (regular expression pattern) must match all of the values in order for the REGEXP_REPLACE_STRING setting to take effect, and the REGEXP_REPLACE_STRING must change the value.

For rows where the REGEXP_PATTERN fails to match, Data Redaction performs DBMS_REDACT.FULL redaction. This mitigates the risk of a mistake in the REGEXP_PATTERN which causes the regular expression to fail to match all of the values in the column, from showing the actual data for those rows which it failed to match.

In addition, if no change to the value occurs as a result of the REGEXP_REPLACE_STRING setting during regular expression replacement operation, Data Redaction performs DBMS_REDACT.FULL redaction.

5.8.2 Syntax for Creating a Regular Expression-Based Redaction Policy

The DBMS_REDACT.ADD_POLICY fields for creating a regular expression-based data redaction policy are as follows:

DBMS_REDACT.ADD_POLICY (
   object_schema           IN VARCHAR2 := NULL, 
   object_name             IN VARCHAR2,
   column_name             IN VARCHAR2 := NULL,
   policy_name             IN VARCHAR2,
   function_type           IN BINARY_INTEGER := 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);

In this specification:

• object_schema, object_name, column_name, policy_name, expression, enable: See "General Syntax of the DBMS_REDACT.ADD_POLICY Procedure".

• function_type: Specifies the function used to set the type of redaction. Enter DBMS_REDACT.REGEXP.

  Note the following:

  • When you set the function_type parameter to DBMS_REDACT.REGEXP, omit the function_parameters parameter.

  • Specify the regular expressions—regexp_pattern, regexp_replace, regexp_position, regexp_occurrence, and regexp_match_parameter—in much the same way that you specify the pattern, replace, position, occurrence, and match_parameter arguments to the REGEXP_REPLACE SQL function. See Oracle Database SQL Language Reference for information about the REGEXP_REPLACE SQL function.

• regexp_pattern: Describes the search pattern for data that must be matched. If it finds a match, then Oracle Database replaces the data as specified by the regexp_replace_string setting. See the following sections for more information:

  • "Creating Regular Expression-Based Redaction Policies Using Shortcuts"

  • "Creating Custom Regular Expression Redaction Policies"

• regexp_replace_string: Specifies how you want to replace the data to be redacted. See the following sections for more information:

  • "Creating Regular Expression-Based Redaction Policies Using Shortcuts"

  • "Creating Custom Regular Expression Redaction Policies"

• regexp_position: Specifies the starting position for the string search. The value that you enter must be a positive integer indicating the character of the column_name data where Oracle Database should begin the search. The default is 1 or the RE_BEGINNING shortcut, meaning that Oracle Database begins the search at the first character of the column_name data.

• regexp_occurrence: Specifies how to perform the search and replace operation. The value that you enter must be a nonnegative integer indicating the occurrence of the replace operation:

  • If you specify 0 or the RE_ALL shortcut, then Oracle Database replaces all of the occurrences of the match.

  • If you specify the RE_FIRST shortcut, then Oracle Database replaces the first occurrence of the match.

  • If you specify a positive integer n, then Oracle Database replaces the nth occurrence of the first match.

  If the occurrence is greater than 1, then the database searches for the second occurrence beginning with the first character following the first occurrence of pattern, and so forth.

• regexp_match_parameter: Specifies a text literal that lets you change the default matching behavior of the function. The behavior of this parameter is the same for this function as for the REGEXP_REPLACE SQL function. See Oracle Database SQL Language Reference for detailed information.

  To filter the search so that it is not case sensitive, specify the RE_MATCH_CASE_INSENSITIVE shortcut.

5.8.3 Creating Regular Expression-Based Redaction Policies Using Shortcuts

You can use shortcuts for both the regexp_pattern and regexp_replace_string parameters in the DBMS_REDACT.ADD_POLICY procedure.

This section contains:

• Regular Expression Shortcuts

• Example of a Regular Expression Redaction Policy Using Shortcuts

regexp_replace_string => DBMS_REDACT.RE_REDACT_WITH_SINGLE_X, 

regexp_replace_string => DBMS_REDACT.RE_REDACT_WITH_SINGLE_1, 

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema          => 'mavis', 
   object_name            => 'cust_info', 
   column_name            => 'cc_num',
   policy_name            => 'redact_cust_cc_nums', 
   function_type          => DBMS_REDACT.REGEXP,
   function_parameters    => NULL,
   expression             => '1=1',
   regexp_pattern         => DBMS_REDACT.RE_PATTERN_CC_L6_T4,
   regexp_replace_string  => DBMS_REDACT.RE_REDACT_CC_MIDDLE_DIGITS,
   regexp_position        => DBMS_REDACT.RE_BEGINNING,
   regexp_occurrence      => DBMS_REDACT.RE_FIRST,
   regexp_match_parameter => DBMS_REDACT.RE_MATCH_CASE_INSENSITIVE,
   policy_description     => 'Regular expressions to redact credit card numbers',
   column_description     => 'cc_num contains customer credit card numbers');
END;
/

SELECT cc_num FROM mavis.cust_info;

CC_NUM
-------
401288XXXXXX1881
411111XXXXXX1111
555555XXXXXX1111
511111XXXXXX1118

5.8.4 Creating Custom Regular Expression Redaction Policies

You can customize regular expressions in Data Redaction policies.

This section contains:

• Settings for Custom Regular Expressions

• Example of a Custom Regular Expression Redaction Policy

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema          => 'mavis', 
   object_name            => 'cust_info', 
   column_name            => 'emp_id',
   policy_name            => 'redact_cust_ids', 
   function_type          => DBMS_REDACT.REGEXP,
   expression             => '1=1',
   regexp_pattern         => '(\d\d\d)(\d\d)(\d\d\d\d)',
   regexp_replace_string  => 'XXXXX\3',
   regexp_position        => 1,
   regexp_occurrence      => 0,
   regexp_match_parameter => 'i',
   policy_description     => 'Redacts customer IDs using regular expression',
   column_description     => 'emp_id contains employee ID numbers');
END;
/

SELECT emp_id FROM mavis.cust_info;

EMP_ID
------------
XXXXX1234
XXXXX5678

5.9.1 About Creating Random Redaction Policies

A random redaction policy presents the redacted data to the querying application user as randomly generated values each time it is displayed, depending on the data type of the column. Be aware that LOB columns are not supported.

5.9.2 Syntax for Creating a Random Redaction Policy

The DBMS_REDACT.ADD_POLICY fields for creating a random redaction policy are as follows:

DBMS_REDACT.ADD_POLICY (
   object_schema           IN VARCHAR2 := NULL, 
   object_name             IN VARCHAR2,
   column_name             IN VARCHAR2 := NULL,
   policy_name             IN VARCHAR2,
   function_type           IN BINARY_INTEGER := NULL,
   expression              IN VARCHAR2,
   enable                  IN BOOLEAN := TRUE);

In this specification:

• object_schema, object_name, column_name, policy_name, expression, enable: See "General Syntax of the DBMS_REDACT.ADD_POLICY Procedure".

• function_type: Specifies the function used to set the type of redaction. Enter DBMS_REDACT.RANDOM.

  If you omit the function_type parameter, then the default redaction function_type setting is DBMS_REDACT.FULL.

  Remember that the data type of the column determines which function_type settings that you are permitted to use. See "Comparison of Full, Partial, and Random Redaction Based on Data Types".

5.9.3 Example of a Random Redaction Policy

Example 5-13 shows how to generate random values. Each time you run the SELECT statement, the output will be different.

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema   => 'mavis', 
   object_name     => 'cust_info', 
   column_name     => 'login_username',
   policy_name     => 'redact_cust_rand_username', 
   function_type   => DBMS_REDACT.RANDOM,
   expression      => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''APP_USER''');
END;
/

SELECT login_username FROM mavis.cust_info;

LOGIN_USERNAME
----------
N[CG{\pTVcK

5.10.1 About Creating Policies That Use No Redaction

The None redaction type option enables you to test the internal operation of your redaction policies, with no effect on the results of queries against tables with policies defined on them. You can use this option to test the redaction policy definitions before applying them to a production environment. Be aware that LOB columns are not supported.

5.10.2 Syntax for Creating a Policy with No Redaction

The DBMS_REDACT.ADD_POLICY fields for creating a policy with no redaction are as follows:

DBMS_REDACT.ADD_POLICY (
   object_schema           IN VARCHAR2 := NULL, 
   object_name             IN VARCHAR2,
   column_name             IN VARCHAR2 := NULL,
   policy_name             IN VARCHAR2,
   function_type           IN BINARY_INTEGER := NULL,
   expression              IN VARCHAR2,
   enable                  IN BOOLEAN := TRUE);

In this specification:

• object_schema, object_name, column_name, policy_name, expression, enable: See "General Syntax of the DBMS_REDACT.ADD_POLICY Procedure".

• function_type: Specifies the functions used to set the type of data redaction. Enter DBMS_REDACT.NONE.

  If you omit the function_type parameter, then the default redaction function_type setting is DBMS_REDACT.FULL.

5.10.3 Example of Performing No Redaction

Example 5-14 shows how to create a Data Redaction policy that does not redact any of the displayed values.

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema    => 'mavis', 
   object_name      => 'cust_info', 
   column_name      => 'user_name',
   policy_name      => 'redact_cust_no_vals', 
   function_type    => DBMS_REDACT.NONE,
   expression       => '1=1');
END;
/

SELECT user_name FROM mavis.cust_info;

USER_NAME
----------
IDA NEAU

5.12.1 About Altering an Oracle Data Redaction Policy

To alter a Data Redaction policy, use the DBMS_REDACT.ALTER_POLICY procedure. If the policy is already enabled, then you do not need to disable it first, and after you alter the policy, it remains enabled.

You can find the names of existing Data Redaction policies by querying the POLICY_NAME column of the REDACTION_POLICIES data dictionary view, and information about the columns, functions, and parameters specified in a policy by querying the REDACTION_COLUMNS view. To find the current value for policies that use full data redaction, you can query the REDACTION_VALUES_FOR_TYPE_FULL data dictionary view.

The action parameter specifies the type of modification that you want to perform. At a minimum, you must include the object_name and policy_name parameters when you run this procedure.

5.12.2 Syntax for the DBMS_REDACT.ALTER_POLICY Procedure

The syntax for the DBMS_REDACT.ALTER_POLICY procedure is as follows:

DBMS_REDACT.ALTER_POLICY (
   object_schema          IN VARCHAR2 := NULL, 
   object_name            IN VARCHAR2 := NULL, 
   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 := NULL,
   regexp_pattern         IN VARCHAR2 := NULL,
   regexp_replace_string  IN VARCHAR2 := NULL,
   regexp_position        IN BINARY_INTEGER := NULL,
   regexp_occurrence      IN BINARY_INTEGER := NULL,
   regexp_match_parameter IN VARCHAR2 := NULL,
   policy_description     IN VARCHAR2 := NULL,
   column_description     IN VARCHAR2 := NULL);

In this specification:

• action: Enter one of the following values to define the kind of action to use:

  • DBMS_REDACT.MODIFY_COLUMN if you plan to change the column_name value.

  • DBMS_REDACT.ADD_COLUMN if you plan to add a new column (in addition to columns that are already protected by the policy) for redaction. This setting is the default for the action parameter.

  • DBMS_REDACT.DROP_COLUMN if you want to remove redaction from a column.

  • DBMS_REDACT.MODIFY_EXPRESSION if you plan to change the expression value. Each policy can have only one policy expression. In other words, when you modify the policy expression, you are replacing the existing policy expression with a new policy expression.

  • DBMS_REDACT.SET_POLICY_DESCRIPTION if you want to change the description of the policy.

  • DBMS_REDACT.SET_COLUMN_DESCRIPTION if you want to change the description of the column.

5.12.3 Parameters Required for Various DBMS_REDACT.ALTER_POLICY Actions

Table 5-5 shows the combinations of parameters that you must use to perform various DBMS_REDACT.ALTER_POLICY actions.

5.12.4 Example of Altering an Oracle Data Redaction Policy

The exercise in this section shows how to modify a Data Redaction policy so that multiple columns are redacted. It also shows how to change the expression setting for the policy. To accomplish this, you must run the DBMS_REDACT.ALTER_POLICY procedure in stages.

1. Create the policy.

   BEGIN
    DBMS_REDACT.ADD_POLICY(
      object_schema   => 'hr', 
      object_name     => 'employees', 
      column_name     => 'email',
      policy_name     => 'hr_employees_pol', 
      function_type   => DBMS_REDACT.FULL,
      expression      => '1=1');
   END;
   /
   

   At this point, when application users (including HR) query the email column, the email addresses are redacted to show a single space.

   CONNECT HR
   Enter password: password
   
   SELECT EMAIL FROM HR.EMPLOYEES;
   
   EMAIL
   ------
   
   

2. Alter this policy to redact the hire_date column to show 01-JAN-70.

   BEGIN
    DBMS_REDACT.ALTER_POLICY(
      object_schema       => 'hr', 
      object_name         => 'employees', 
      policy_name         => 'hr_employees_pol', 
      action              => DBMS_REDACT.ADD_COLUMN,
      column_name         => 'hire_date',
      function_type       => DBMS_REDACT.PARTIAL,
      function_parameters => DBMS_REDACT.REDACT_DATE_EPOCH);
   END;
   /
   

   To redact the hire_date column, you must change the function_type parameter to use partial redaction, and you must include the function_parameters parameter to specify the DBMS_REDACT.REDACT_DATE_EPOCH shortcut. The expression parameter is omitted because for this particular alteration, it does not need to change. The email column is still redacted, so a query shows the following:

   SELECT EMAIL, HIRE_DATE FROM HR.EMPLOYEES;
   
   EMAIL   HIRE_DATE
   ------  ----------
           01-JAN-70
   

3. Change the expression parameter so that user HR is the only user who can see the actual data for the EMAIL and HIRE_DATE columns.

   BEGIN
    DBMS_REDACT.ALTER_POLICY(
      object_schema   => 'hr', 
      object_name     => 'employees', 
      policy_name     => 'hr_employees_pol', 
      action          => DBMS_REDACT.MODIFY_EXPRESSION,
      expression      => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR''');
   END;
   /
   

   To change the expression setting, you set the action parameter to DBMS_REDACT.MODIFY_EXPRESSION, and then enter the new expression in the expression parameter. At this stage, when user HR queries the EMAIL and HIRE_DATE columns, he or she can see the actual data.

   SELECT EMAIL, HIRE_DATE FROM HR.EMPLOYEES;
   
   EMAIL   HIRE_DATE
   ------  ----------
   SKING   17-JUN-03
   ...
   

4. To drop the policy, enter the following procedure.

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

5.14.1 Disabling an Oracle Data Redaction Policy

To disable a Data Redaction policy, use the DBMS_REDACT.DISABLE_POLICY procedure. You can find the names of existing Data Redaction policies and whether they are enabled by querying the POLICY_NAME and ENABLE columns of the REDACTION_POLICIES view. However, as long as the policy still exists, you cannot create another policy for that table or view, even if the original policy is disabled. In other words, if you want to create a different policy on the same table column, then you must drop the first policy before you can create and use the new policy.

The syntax is as follows:

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

In this specification:

• object_schema: Specifies the schema of the object on which the Data Redaction policy will be applied. If you omit this setting (or enter NULL), then Oracle Database uses the name of the current schema.

• object_name: Specifies the name of the table or view to be used for the Data Redaction policy.

• policy_name: Specifies the name of the policy to be disabled.

Example 5-16 shows how to disable a Data Redaction policy.

BEGIN
  DBMS_REDACT.DISABLE_POLICY (
    object_schema  => 'mavis',
    object_name    => 'cust_info',
    policy_name    => 'redact_cust_user_ids');
END;
/

5.14.2 Enabling an Oracle Data Redaction Policy

To enable a Data Redaction policy, use the DBMS_REDACT.ENABLE_POLICY procedure. Remember that immediately after you create a new policy, you do not need to enable it; the creation process handles that for you. To find the names of existing Data Redaction policies and whether they are enabled, query the POLICY_NAME and ENABLE columns of the REDACTION_POLICIES view. After you run the procedure, the enablement takes effect immediately.

The syntax is as follows:

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

In this specification:

• object_schema: Specifies the schema of the object on which the Data Redaction policy will be applied. If you omit this setting (or enter NULL), then Oracle Database uses the name of the current schema.

• object_name: Specifies the name of the table or view to be used for the Data Redaction policy.

• policy_name: Specifies the name of the policy to be enabled.

Example 5-17 shows how to enable a Data Redaction policy.

BEGIN
  DBMS_REDACT.ENABLE_POLICY (
    object_schema  => 'mavis',
    object_name    => 'cust_info',
    policy_name    => 'redact_cust_user_ids');
END;
/