Creating and Managing Multiple Named Policy Expressions

A named, centrally managed Oracle Data Redaction policy expression can be used in multiple redaction policies and applied to multiple tables or views.

About Data Redaction Policy Expressions to Define Conditions

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:

The following table 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.

Procedure Description
DBMS_REDACT.CREATE_POLICY_EXPRESSION Creates a Data Redaction named policy expression
DBMS_REDACT.UPDATE_POLICY_EXPRESSION Updates a Data Redaction named policy expression
DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL Applies a Data Redaction named policy expression to a table or a view column
DBMS_REDACT.DROP_POLICY_EXPRESSION Drops a Data Redaction named policy expression

Related Topics

Creating and Applying a Data Redaction Named Policy Expression

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.

Updating a Data Redaction 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.

You can query the REDACTION_EXPRESSIONS data dictionary view to find existing Data Redaction policy expressions.

  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;
/

Dropping a Data Redaction Named Policy Expression

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

You can query the REDACTION_EXPRESSIONS data dictionary view to find the existing Data Redaction named policy expressions.

  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;
/

Tutorial: Creating and Sharing a Data Redaction Named Policy Expression

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.

Before you begin this tutorial, ensure that the COMPATIBLE initialization parameter is set to 12.2.0.0 or later. You can check this setting by using the SHOW PARAMETER command.

  1. Connect to a PDB as user SYS with the SYSDBA administrative privilege.

  2. Create the dr_admin and hr_clerk user accounts.

    GRANT CREATE SESSION TO dr_admin IDENTIFIED BY password;
GRANT CREATE SESSION TO hr_clerk IDENTIFIED BY password;

  3. Grant the following privileges to the dr_admin user:

    GRANT EXECUTE ON DBMS_REDACT TO dr_admin;
GRANT ADMINISTER REDACTION POLICY TO dr_admin;

  4. Connect as user HR.

  5. Grant hr_clerk the SELECT privilege on the EMPLOYEES and JOBS tables.

    GRANT SELECT on EMPLOYEES to hr_clerk;
GRANT SELECT on JOBS to hr_clerk;

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.

  1. Connect to the PDB as user dr_admin.

  2. Create the hr_emp_redact_comp_pol policy, which will perform full redaction of the HR.EMPLOYEES.SALARY column.

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

  3. Alter the hr_redact_comp_pol policy to also redact the COMMISSION_PCT column of the HR.EMPLOYEES table.

    BEGIN
 DBMS_REDACT.ALTER_POLICY(
   object_schema       => 'hr',
   object_name         => 'employees',
   policy_name         => 'hr_emp_redact_comp_pol',
   action              => DBMS_REDACT.ADD_COLUMN,
   column_name         => 'commission_pct',
   function_type       => DBMS_REDACT.FULL);
END;
/

  4. Create the hr_jobs_redact_comp_pol policy for the max_salary column of the HR.JOBS table.

    BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'hr',
   object_name         => 'jobs',
   column_name         => 'max_salary',
   policy_name         => 'hr_jobs_redact_comp_pol',
   function_type       => DBMS_REDACT.FULL,
   expression          => '1=1');
END;
/

At this stage, the data in the HR.EMPLOYEES.SALARY, HR.EMPLOYEES.COMMISSION_PCT, and HR.JOBS.MAX_SALARY columns are redacted.

Step 3: Test the Oracle Data Redaction Policy

User hr_clerk is ready to query the tables that have redacted data.

  1. Connect as user hr_clerk.

  2. Query the HR.EMPLOYEES table.

    SELECT SALARY, COMMISSION_PCT FROM HR.EMPLOYEES WHERE SALARY > 15000;

    The output should be as follows:

        SALARY COMMISSION_PCT

---------- --------------
         0
         0
         0

  3. Query the HR.JOBS table.

    SELECT MAX_SALARY FROM HR.JOBS WHERE MAX_SALARY > 15000;

    The output should be as follows:

    MAX_SALARY

----------
         0
         0
         0
         0
         0

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.

This policy expression will enable user hr_clerk to view the redacted data.

  1. Connect as user dr_admin.

  2. Create the policy expression.

    BEGIN
 DBMS_REDACT.CREATE_POLICY_EXPRESSION(
   policy_expression_name  => 'hr_redact_pol',
   expression              => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR_CLERK''');
END;
/

    This expression returns FALSE for the hr_clerk user, which enables the hr_clerk user to view actual data in the HR.EMPLOYEES and HR.JOBS tables that are subject to the Data Redaction policies.

  3. Apply the hr_redact_pol policy expression to the HR.EMPLOYEES.SALARY column.

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

  4. Apply the hr_redact_pol policy expression to the HR.JOBS.MAX_SALARY column.

    BEGIN
 DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL(
   object_schema          => 'hr',
   object_name            => 'jobs',
   column_name            => 'max_salary',
   policy_expression_name => 'hr_redact_pol');
END;
/

User hr_clerk can view data in the HR.EMPLOYEES.SALARY and HR.JOBS.MAX_SALARY, but the data in the HR.EMPLOYEES.COMMISSION_PCT column will still be redacted for this user.

Step 5: Test the Data Redaction Named Policy Expression

User hr_clerk is now ready to test the hr_redact_pol named policy expression.

  1. Connect as user hr_clerk.

  2. Query the HR.EMPLOYEES table.

    SELECT SALARY, COMMISSION_PCT FROM HR.EMPLOYEES WHERE SALARY > 15000;

    The output should be as follows:

        SALARY COMMISSION_PCT

---------- --------------
     24000
     17000
     17000

    User hr_clerk now can view the SALARY column data, but still has no access to the COMMISSION_PCT column data.

  3. Query the HR.JOBS table.

    SELECT MAX_SALARY FROM HR.JOBS WHERE MAX_SALARY > 15000;

    The output should be as follows:

    MAX_SALARY

----------
     40000
     30000
     16000
     16000
     20080

    User hr_clerk now can view the MAX_SALARY column data.

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.

  1. Connect as user dr_admin.

  2. Modify the hr_redact_pol policy as follows:

    BEGIN
 DBMS_REDACT.UPDATE_POLICY_EXPRESSION(
  policy_expression_name => 'hr_redact_pol',
  expression             => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR''');
END;
/

Step 7: Test the Modified Named Policy Expression

Users HR and hr_clerk are ready to test the modified Data Redaction named policy expression.

  1. Connect as user HR.

  2. Query the HR.EMPLOYEES table.

    SELECT SALARY, COMMISSION_PCT FROM HR.EMPLOYEES WHERE SALARY > 15000;

    The output should be as follows:

        SALARY COMMISSION_PCT

---------- --------------
     24000
     17000
     17000

    User HR now has access to the actual data. A query by HR on the HR.JOBS.MAX_SALARY column will produce similar results.

    SELECT MAX_SALARY FROM HR.JOBS WHERE MAX_SALARY > 15000;

MAX_SALARY

----------
     40000
     30000
     16000
     16000
     20080

  3. Connect as user hr_clerk.

  4. Query the HR.EMPLOYEES and HR.JOBS tables and then observe the results.

    SELECT SALARY, COMMISSION_PCT FROM HR.EMPLOYEES WHERE SALARY > 15000;

    SALARY COMMISSION_PCT

---------- --------------
         0
         0
         0

SELECT MAX_SALARY FROM HR.JOBS WHERE MAX_SALARY > 15000;

MAX_SALARY

----------
         0
         0
         0
         0
         0

Step 8: Remove the Components of This Tutorial

If you do not need the components of this tutorial, then you can remove them.

  1. Connect as user dr_admin.

  2. Modify the policy expression so that it is no longer associated with the table columns that are associated with the expression.

    To do so, you must set the policy_expression_name parameter to NULL.

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

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

  3. Drop the policy expression.

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

  4. Drop the hr_emp_redact_comp_pol and hr_jobs_redact_comp_pol Data Redaction policies.

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

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

  5. Connect as the SYSTEM user or a user who has privileges to drop user accounts.

  6. Drop the dr_admin and hr_clerk user accounts.

    DROP USER dr_admin;
DROP USER hr_clerk;