10 Configuring Oracle Data Redaction Policies

1. Ensure that you have been granted the EXECUTE privilege on the DBMS_REDACT PL/SQL package.

2. Determine the data type of the table or view column that you want to redact.

3. Ensure that this column is not used in an Oracle Virtual Private Database (VPD) row filtering condition. That is, it must not be part of the VPD predicate generated by the VPD policy function.

4. Decide on the type of redaction that you want to perform: full, random, partial, regular expressions, or none.

5. Decide which users to apply the Data Redaction policy to.

6. Based on this information, create the Data Redaction policy by using the DBMS_REDACT.ADD_POLICY procedure.

7. Configure the policy to have additional columns to be redacted, as described in "Redacting Multiple Columns".

After you create the Data Redaction policy, it is automatically enabled and ready to redact data.

The complete syntax for the DBMS_REDACT.ADD_POLICY procedure is as follows:

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

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 current user's name. Be aware that the meaning of "current user" here can change, depending on where you invoke the DBMS_REDACT.ADD_POLICY procedure.

  For example, suppose user mpike grants user fbrown the EXECUTE privilege on a definer's rights PL/SQL package called mpike.protect_data in mpike's schema. From within this package, mpike has coded a procedure called protect_cust_data, which invokes the DBMS_REDACT.ADD_POLICY procedure. User mpike has set the object_schema parameter to NULL.

  When fbrown invokes the protect_cust_data procedure in the mpike.protect_data package, Oracle Database attempts to define the Data Redaction policy around the object cust_data in the mpike schema, not the cust_data object in the schema that belongs to fbrown.

• object_name: Specifies the name of the table or view to which the Data Redaction policy applies.

• policy_name: Specifies the name of the policy to be created. Ensure that this name is unique in the database instance. You can find a list of existing Data Redaction policies by querying the POLICY_NAME column of the REDACTION_POLICIES data dictionary view.

• policy_description: Specifies a brief description of the purpose of the policy.

• column_name: Specifies the column whose data you want to redact. Note the following:

  • You can apply the Data Redaction policy to multiple columns. If you want to apply the Data Redaction policy to multiple columns, then after you use DBMS_REDACT.ADD_POLICY to create the policy, run the DBMS_REDACT.ALTER_POLICY procedure as many times as necessary to add each of the remaining required columns to the policy. See "Altering an Oracle Data Redaction Policy".

  • Only one policy can be defined on a table or view. You can, however, create a new view on the table, and by defining a second redaction policy on this new view, you can choose to redact the columns in a different way when a query is issued against this new view. When deciding how to redact a given column, Oracle Database uses the policy of the earliest view in a view chain. See "Tutorial: How Oracle Data Redaction Affects Tables and Views" for more information about using Data Redaction policies with views.

  • If you do not specify a column (for example, by entering NULL), then no columns are redacted by the policy. This enables you to create your policies so that they are in place, and then later on, you can add the column specification when you are ready.

  • Do not use a column that is currently used in an Oracle Virtual Private Database (VPD) row filtering condition. In other words, the column should not be part of the VPD predicate generated by the VPD policy function. (See "Oracle Data Redaction and Oracle Virtual Private Database" for more information about using Data Redaction with VPD.)

  • You cannot define a Data Redaction policy on a virtual column. In addition, you cannot define a Data Redaction policy on a column that is involved in the SQL expression of any virtual column.

• column_description: Specifies a brief description of the column that you are redacting.

• function_type: Specifies a function that sets the type of redaction. See the following sections for more information:

  • "Syntax for Creating a Full Redaction Policy"

  • "Syntax for Creating a Partial Redaction Policy"

  • "Syntax for Creating a Regular Expression-Based Redaction Policy"

  • "Syntax for Creating a Random Redaction Policy"

  • "Syntax for Creating a Policy with No Redaction"

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

• function_parameters: Specifies how the column redaction should appear for partial redaction. See "Syntax for Creating a Partial Redaction Policy".

• expression: Specifies a Boolean SQL expression to determine how the policy is applied. Redaction takes place only if this policy expression evaluates to TRUE. See "Using Expressions to Define Conditions for Data Redaction Policies".

• enable: When set to TRUE, enables the policy upon creation. When set to FALSE, it creates the policy as a disabled policy. The default is TRUE. After you create the policy, you can disable or enable it. See the following sections:

  • "Disabling an Oracle Data Redaction Policy"

  • "Enabling an Oracle Data Redaction Policy"

• regexp_pattern, regexp_replace_string, regexp_position, regexp_position, regexp_occurrence, regexp_match_parameter: Enable you to use regular expressions to redact data, either fully or partially. If the regexp_pattern does not match anything in the actual data, then full redaction will take place, so be careful when specifying the regexp_pattern. Ensure that all of the values in the column conform to the semantics of the regular expression you are using. See "Syntax for Creating a Regular Expression-Based Redaction Policy" for more information.

You can drop an Oracle Data Redaction policy whether it is enabled or disabled. You can find the names of existing Data Redaction policies, by querying the POLICY_NAME column of the REDACTION_POLICIES view. When you drop a table or view that is associated with an Oracle Data Redaction policy, the policy is automatically dropped. As a best practice, drop the policy first, and then drop the table or view afterward. See "Dropped Oracle Data Redaction Policies When the Recycle Bin Is Enabled" for more information.

• To drop a Data Redaction policy, run the DBMS_REDACT.DROP_POLICY procedure.

  Use the following syntax:

  DBMS_REDACT.DROP_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 to which the Data Redaction policy applies. 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 dropped.

After you run the DBMS_REDACT.DROP_POLICY procedure, the drop takes effect immediately.

Example 10-14 shows how to drop a Data Redaction policy.

See "Oracle Data Redaction Policies on Materialized Views" for restrictions on creating Data Redaction policies on materialized views.

If you create a view chain (that is, a view based on another view), then the Data Redaction policy also applies throughout this view chain. The policies remain in effect all of the way up through this view chain, but if another policy is created for one of these views, then for the columns affected in the subsequent views, this new policy takes precedence.

To understand how this concept works, follow these steps:

1. Create and populate the following table:

   CREATE TABLE TABLE1 (TC1 VARCHAR2(20), TN1 NUMBER(10));
   
   INSERT INTO TABLE1 VALUES ('5111-1111-1111-1118', 987654329);
   

2. Create the following views, which will constitute the view chain for table table1:

   CREATE VIEW view1 (vc1, vn1) AS SELECT tc1, tn1 FROM table1;
   CREATE VIEW view2 (vc2, vn2) AS SELECT vc1, vn1 FROM view1;
   CREATE VIEW view3 (vc3, vn3) AS SELECT vc2, vn2 FROM view2;
   

3. Create the following policy on the table1 table, which changes the display of the tc1 column to random values.

   BEGIN
    DBMS_REDACT.ADD_POLICY(
      object_schema    => 'NULL', 
      object_name      => 'table1', 
      column_name      => 'tc1',
      policy_name      => 't1pol', 
      function_type    => DBMS_REDACT.RANDOM,
      expression       => '1=1');
   END;
   /
   

4. Query table1.tc1, view1.vc1, view2.vc2, and view3.vc3, and you will see that they all produce random output, based on the t1pol Data Redaction policy.

   For example:

   SELECT vc3 FROM view3;
   
   VC3
   -----------------------
   M,v]3(z+U4~e;0#3]<'
   

5. Create the following policy on view2, which changes the output of column vc2 to display no output at all (that is, a blank space).

   BEGIN
    DBMS_REDACT.ADD_POLICY(
      object_schema    => 'NULL', 
      object_name      => 'view2', 
      column_name      => 'vc2',
      policy_name      => 'v2pol', 
      function_type    => DBMS_REDACT.FULL,
      expression       => '1=1');
   END;
   /
   

6. Query views view2 and view3.

   SELECT vc2 FROM view2;
   SELECT vc3 FROM view3;
   

   Both queries produce the same output (a blank space), which illustrates how for these views, policy v2pol overrides the base table policy, t1pol.

7. Query table table1 and view view1.

   SELECT tc1 FROM table1;
   SELECT vc1 FROM view1;
   

   Because table1 and view1 are lower in the chain, they are not affected by policy v2pol1. The output for both remains as random values.

8. Create the following policy on view1, which redacts the first 5 digits of the numeric values in column vn1 to 9.

   BEGIN
    DBMS_REDACT.ADD_POLICY(
      object_schema       => 'NULL', 
      object_name         => 'view1', 
      column_name         => 'vn1',
      policy_name         => 'v1pol', 
      function_type       => DBMS_REDACT.PARTIAL,
      function_parameters => '9,1,5',
      expression          => '1=1');
   END;
   /
   

9. Query view view1:

   SELECT vc1, vn1 FROM view1;
   
   VC1                                   VN1
   ------------------------------------- ----------------
   :'F6`B<dB/N>hJDlJ7V                   999994329
   

   Here, view view1 is using two policies. Policy t1pol (on table table1) continues to redact column vc1, and policy v1pol (on view view1) redacts column vn1.

10. Query view view2:

    SELECT vc2, vn2 FROM view2;
    
    VC2                                   VN2
    ------------------------------------- ----------------
                                          999994329
    

    View view2 also uses two policies: the blank space for its column vc2 is generated by policy v2pol, and the partial numeric redaction for vn2 comes from policy v1pol for view view1.

11. Query view view3:

    SELECT vc3, vn3 FROM view3;
    
    VC3                                   VN3
    ------------------------------------- ----------------
                                          999994329
    

    Because view view3 has no direct policies, it uses the policy settings from both view1 and view2. Hence, the output is the same as the output for view2.

12. Disable the policy.

    If you disable a policy, then the output for all of the views along the view chain that are affected by the policy is also changed.

    For example, disable the policy t1pol, which was created for table table1:

    EXEC DBMS_REDACT.DISABLE_POLICY (NULL, 'TABLE1', 'T1POL');
    

    Now query view1 again:

    SELECT vc1, vn1 FROM view1;
    
    VC1                                   VN1
    ------------------------------------- ----------------
    5111-1111-1111-1118                   999994329
    

    Column vc1 shows the values from the base table table1. Column vn1 still shows the redacted values from policy v2pol.

13. To remove the components of this exercise:

    EXEC DBMS_REDACT.DROP_POLICY (NULL, 'table1', 't1pol');
    EXEC DBMS_REDACT.DROP_POLICY (NULL, 'view1', 'v1pol');
    EXEC DBMS_REDACT.DROP_POLICY (NULL, 'view2', 'v2pol');
    DROP TABLE table1;
    DROP VIEW view1;
    DROP VIEW view2;
    DROP VIEW view3;
    

Figure 10-1 shows how these policies affect the chain of views described in the previous example.

Figure 10-1 How Oracle Data Redaction Policies Work in a Chain of Views

Description of "Figure 10-1 How Oracle Data Redaction Policies Work in a Chain of Views"

The values used in the SQL expression will be redacted. This redaction occurs in such a way that the redaction takes place before the SQL expression is evaluated: the result value that is displayed in the report is the end result of the evaluated SQL expression over the redacted values, rather than the redacted result of the SQL expression as a whole.

1. Create the following Data Redaction policy for the HR.EMPLOYEES table.

   This policy will replace the first 4 digits of the value from the SALARY column with the number 9 and the first digit of the value from the COMMISSION_PCT column with a 9.

   BEGIN
    DBMS_REDACT.ADD_POLICY(
      object_schema          => 'HR', 
      object_name            => 'EMPLOYEES', 
      column_name            => 'SALARY',
      column_description     => 'emp_sal_comm shows employee salary and commission',
      policy_name            => 'redact_emp_sal_comm', 
      policy_description     => 'Partially redacts the emp_sal_comm column',
      function_type          => DBMS_REDACT.PARTIAL,
      function_parameters    => '9,1,4',
      expression             => '1=1');
   END;
   /
   BEGIN
    DBMS_REDACT.ALTER_POLICY(
      object_schema          => 'HR',
      object_name            => 'EMPLOYEES',
      policy_name            => 'redact_emp_sal_comm',
      action                 => DBMS_REDACT.ADD_COLUMN,
      column_name            => 'COMMISSION_PCT',
      function_type          => DBMS_REDACT.PARTIAL,
      function_parameters    => '9,1,1',
      expression             => '1=1');
   END;
   /
   

2. Log in to the HR schema and then run the following report.

   This report will use the SQL expression (SALARY + COMMISSION_PCT) to combine the employees' salaries and commissions.

   SELECT (SALARY + COMMISSION_PCT) total_emp_compensation
   FROM EMPLOYEES
   WHERE DEPARTMENT_ID = 80;
   
   TOTAL_EMP_COMPENSATION
   ----------------------
                   9999.9
                  9999.95
                 99990.95
   ...
   

3. Use SQL expressions for the report, including concatenation.

   For example:

   SELECT 'Employee ID '          || EMPLOYEE_ID ||
          ' has a salary of '     || SALARY || 
          ' and a commission of ' || COMMISSION_PCT || '.' detailed_emp_compensation
   FROM EMPLOYEES
   WHERE DEPARTMENT_ID = 80
   ORDER BY EMPLOYEE_ID;
   
   DETAILED_EMP_COMPENSATION
   -------------------------------------------------------------
   Employee ID 150 has a salary of 99990 and a commission of .9.
   Employee ID 151 has a salary of 9999 and a commission of .95.
   Employee ID 152 has a salary of 9999 and a commission of .95.
   ...
   

4. Run the following statement to drop the redact_emp_sal_comm Data Redaction policy:

   BEGIN
     DBMS_REDACT.DROP_POLICY (
       object_schema => 'HR',
       object_name   => 'EMPLOYEES',
       policy_name   => 'redact_emp_sal_comm');
   END;
   /
   

Table 10-6 lists the Data Redaction data dictionary views.