A Oracle Data Redaction named policy expression is designed to work as an alternative to the policy expression that is used in existing Data Redaction policies.

A policy expression enables you to redact data based on runtime conditions. This type of policy expression can affect whether or not redaction takes place on columns of the table or view on which the redaction policy is defined. By default, a Data Redaction policy expression applies to all the columns that belong to the Data Redaction policy defined on that table or view. Alternatively, you can choose to create and associate a policy expression for individual columns of a table or view. These column level policy expressions are called named policy expressions. The column to which you apply a named policy expression must already be redacted by a Data Redaction policy.

You can use Data Redaction named policy expressions in the following ways:

• Each named policy expression can be associated with multiple columns of the same or different tables or views.

• Each named policy expression can be associated with columns within the same or different Data Redaction policies.

• The named policy expression overrides the default policy expression of the associated columns. The default policy expression still applies to columns that have no named policy expressions applied to them.

• Any updates made to a named policy expression apply to all of the column associations of the expression.

• You cannot associate multiple named policy expressions for the same column.

• You cannot associate named policy expressions with columns in a different pluggable database (PDB).

Table 3-8 describes the DBMS_REDACT PL/SQL procedures that you can use to create and manage named policy expressions. To find information about policy expressions, query the REDACTION_EXPRESSIONS data dictionary view.

The DBMS_REDACT.CREATE_POLICY_EXPRESSION and DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL enable you to create and apply a Data Redaction named policy expression.

1. Connect to the PDB as a user who has the EXECUTE privilege on the DBMS_REDACT PL/SQL package and the ADMINISTER REDACTION POLICY system or schema privilege.
2. Ensure that the COMPATIBLE initialization parameter is set to 12.2.0.0 or later.
   To find the current COMPATIBLE setting, use the SHOW PARAMETER command.
3. To create the named policy expression, run the DBMS_REDACT.CREATE_POLICY_EXPRESSION procedure.

   For example:

   BEGIN
    DBMS_REDACT.CREATE_POLICY_EXPRESSION (
     policy_expression_name        => 'redact_pol',
     expression                    => '1=1',
     policy_expression_description => 'Determines whether the column will be redacted');
   END;
   /

4. Run the DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL procedure to apply the named policy expression to a table or view column.

   For example, assume that you have already created a Data Redaction policy on the SALARY column of the HR.EMPLOYEES table, as follows:

   BEGIN
     DBMS_REDACT.ADD_POLICY  (
      object_schema           => 'hr',
      object_name             => 'employees',
      policy_name             => 'overall_policy',
      expression              => '1=0'); 
   END;
   /
   BEGIN
     DBMS_REDACT.ALTER_POLICY (
      object_schema           => 'hr',
      object_name             => 'employees' ,
      policy_name             => 'overall_policy',
      function_type           => DBMS_REDACT.FULL,
      action                  => DBMS_REDACT.ADD_COLUMN,
      column_name             => 'salary');
   END; 
   /

   Then you can apply the policy expression to the SALARY column as follows:

   BEGIN
    DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL (
      object_schema           => 'hr',
      object_name             => 'employees',
      column_name             => 'salary',
      policy_expression_name  => 'redact_pol');
   END;
   /

   In this specification:

   • object_schema: Specifies the schema of the object on which named policy expression 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 named policy expression.

   • column_name: Specifies the column to which you want to apply the named policy expression.

   • policy_expression_name: Specifies the name of the named policy expression.

You can use the DBMS_REDACT.UPDATE_POLICY_EXPRESSION procedure to update a Data Redaction named policy expression. The update takes place immediately and is reflected in all the columns that use the named policy expression.

1. Connect to the PDB as a user who has the EXECUTE privilege on the DBMS_REDACT PL/SQL package and the ADMINISTER REDACTION POLICY system or schema privilege.
2. Ensure that the COMPATIBLE initialization parameter is set to 12.2.0.0 or later.
   To find the current COMPATIBLE setting, use the SHOW PARAMETER command.
3. Run the DBMS_REDACT.UPDATE_POLICY_EXPRESSION procedure to perform the update.

   For example:

   BEGIN
    DBMS_REDACT.UPDATE_POLICY_EXPRESSION(
     policy_expression_name => 'redact_pol',
     expression             => '1=0');
   END;
   /

You can use the DBMS_REDACT.DROP_POLICY_EXPRESSION procedure to drop a Data Redaction named policy expression.

1. Connect to the PDB as a user who has the EXECUTE privilege on the DBMS_REDACT PL/SQL package and the ADMINISTER REDACTION POLICY system or schema privilege.
2. Ensure that the COMPATIBLE initialization parameter is set to 12.2.0.0 or later.
   To find the current COMPATIBLE setting, use the SHOW PARAMETER command.
3. Remove the named policy expression's association with any table or view column.
   You cannot drop a named policy expression if it is associated with an existing table or view column. To remove a given column’s association with a named policy expression (to revert to redacting that column based on the evaluation result of the default policy expression), you must set the policy_expression_name parameter of the DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL procedure to NULL.

   For example:

   BEGIN
    DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL(
      object_schema          => 'hr',
      object_name            => 'employees', 
      column_name            => 'salary',
      policy_expression_name =>  null);
   END;
   /

4. Run DBMS_REDACT.DROP_POLICY_EXPRESSION to drop the policy expression.

   For example:

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

This tutorial shows how to create an Oracle Data Redaction named policy expression, apply it to multiple tables, and centrally manage the named policy expression.

• Step 1: Create Users for This Tutorial
  You must create two users for this tutorial: dr_admin, who will create the Oracle Data Redaction policies, and hr_clerk, who will test them.
• Step 2: Create an Oracle Data Redaction Policy
  User dr_admin is ready to create an Oracle Data Redaction policy to protect the HR.EMPLOYEES and HR.JOBS tables.
• Step 3: Test the Oracle Data Redaction Policy
  User hr_clerk is ready to query the tables that have redacted data.
• Step 4: Create and Apply a Named Policy Expression to the Redacted Table Columns
  Next, user dr_admin is ready to create a Data Redaction named policy expression (hr_redact_pol) and apply it to two of the three redacted table columns.
• Step 5: Test the Data Redaction Named Policy Expression
  User hr_clerk is now ready to test the hr_redact_pol named policy expression.
• Step 6: Modify the Data Redaction Named Policy Expression
  User dr_admin decides to modify the Data Redaction named policy expression so that user hr_clerk will get redacted data, not user HR.
• Step 7: Test the Modified Named Policy Expression
  Users HR and hr_clerk are ready to test the modified Data Redaction named policy expression.
• Step 8: Remove the Components of This Tutorial
  If you do not need the components of this tutorial, then you can remove them.