4 Configuring Oracle Data Redaction Policies

4.1 About Oracle Data Redaction Policies

To create and manage an Oracle Data Redaction policy, you must have the EXECUTE privilege on the DBMS_REDACT PL/SQL package.

A Data Redaction policy has the following characteristics:

• The Data Redaction policy defines the following: What kind of redaction to perform, how the redaction should occur, and when the redaction takes place. Oracle Database performs the redaction at execution time, just before the data is displayed to the application user.

• A Data Redaction policy can be defined in such a way as to fully redact the values, partially redact the values, or randomly redact the values. In addition, you can define a Data Redaction policy to not redact any data at all, for when you want to test your policies in a test environment.

• A Data Redaction policy can be defined with a policy expression which allows for different application users to be presented with either redacted data or actual data, based on whether the policy expression returns TRUE or FALSE. Redaction takes place when the boolean result of evaluating the policy expression is TRUE. For security reasons, the functions and operators that can be used in the policy expression are limited to SYS_CONTEXT and a few others. User-created functions are not allowed. Policy expressions can make use of the SYS_SESSION_ROLES namespace with the SYS_CONTEXT function to check for enabled roles.

Table 4-1 lists the procedures in the DBMS_REDACT package.

4.2 Planning the Creation of an Oracle Data Redaction Policy

Before you create an Oracle Data Redaction policy, it is important to plan the data redaction process that best suits your data.

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 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 to whom you want the redacted values to appear.

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

7. (Optional) Configure the policy to have additional columns to be redacted, as described in "Redacting Multiple Columns in an Oracle Data Redaction Policy".

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

4.3 General Syntax of the DBMS_REDACT.ADD_POLICY Procedure

To create a Data Redaction policy, use the DBMS_REDACT.ADD_POLICY procedure. The complete syntax 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. 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 "How Oracle Data Redaction Policies Affect 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 "Using Oracle Data Redaction with 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. Redaction takes place only if this policy expression evaluates to TRUE. Be careful when making comparisons with NULL. Remember that in SQL the value NULL is undefined, so comparisons with NULL tend to return FALSE. This expression must be based on SYS_CONTEXT values from 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.) In addition to SYS_CONTEXT, you can use the V and NV functions (for Oracle Application Express) and the DOMINATES function (for Oracle Label Security). You cannot use user-created functions in the expression parameter.

  In the policy expression, in addition to the SYS_CONTEXT function, you can use only the following operators: =, !=, >, <, >=, <=

  Another namespace that you can use in a policy expression is the SYS_SESSION_ROLES namespace, 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. You could use the following expression to have the policy 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''',
  

  If you want the Data Redaction policy to always apply, so that redaction always takes place irrespective of the context, then ensure that the Boolean expression evaluates to TRUE, as follows:

  expression  => '1=1',
  

  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 the following sections for more information about users who are exempted from Data Redaction policies:

  • "Exempting Users from Oracle Data Redaction Policies"

  • "Oracle Data Pump Security Model for Oracle Data Redaction"

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

4.4 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

4.5 Creating a Partial Redaction Policy

This section contains:

• About Creating Partial Redaction Policies

• Syntax for Creating a Partial Redaction Policy

• 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

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

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 numbers in SS numbers',
   column_description  => 'ssn contains numeric Social Security numbers');
END;
/

SELECT ssn FROM mavis.cust_info;

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

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

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

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

4.6 Creating a Regular Expression-Based Redaction Policy

This section contains:

• About Creating Regular Expression-Based Redaction Policies

• Syntax for Creating a Regular Expression-Based Redaction Policy

• Creating Regular Expression-Based Redaction Policies Using Shortcuts

• Creating Custom Regular Expression 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,
   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);

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

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

4.7 Creating a Random Redaction Policy

This section contains:

• About Creating Random Redaction Policies

• Syntax for Creating a Random Redaction Policy

• Example of a Random Redaction Policy

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   => 'mavis', 
   object_name     => 'cust_info', 
   column_name     => 'login_vals',
   policy_name     => 'redact_cust_rand_vals', 
   function_type   => DBMS_REDACT.RANDOM,
   expression      => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''APP_USER''');
END;
/

SELECT login_vals FROM mavis.cust_info;

LOGIN_VALS
----------
N[CG{\pTVcK

4.8 Creating a Policy That Uses No Redaction

This section contains:

• About Creating Policies That Use No Redaction

• Syntax for Creating a Policy with No Redaction

• Example of Performing No Redaction

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    => '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

4.9 Exempting Users from Oracle Data Redaction Policies

To exempt database users from the Data Redaction policy, grant them the EXEMPT REDACTION POLICY system privilege. Grant this privilege to trusted users only.

In addition to users who were granted this privilege, user SYS is also exempt from all Data Redaction policies. The person who creates the Data Redaction policy is by default not exempt from it, unless this person is user SYS or has the EXEMPT REDACTION POLICY system privilege.

The EXEMPT REDACTION POLICY system privilege is included in the DBA role, but this privilege must be granted explicitly to users because it is not included in the WITH ADMIN OPTION for DBA role grants. For better security, explicitly grant the EXEMPT REDACTION POLICY system privilege to trusted users.

Note the following:

• Users who have the INSERT privilege on a table can insert values into a redacted column, regardless of whether a Data Redaction policy exists on the table. Data Redaction only affects SQL SELECT statements (that is, queries) issued by a user, and has no effect on any other SQL issued by a user, including INSERT, UPDATE, or DELETE statements. (See the next bullet for exceptions to this rule.)

• Users cannot perform a CREATE TABLE AS SELECT where any of the columns being selected (source columns) is protected by a Data Redaction policy (and similarly, any DML operation where the source is a redacted column), unless the user was granted the EXEMPT REDACTION POLICY system privilege.

• Users who were granted the DBA role are exempt from redaction policies because the DBA role contains the EXP_FULL_DATABASE role, which is granted the EXEMPT REDACTION POLICY system privilege.

4.10 Altering an Oracle Data Redaction Policy

You can use the DBMS_REDACT.ALTER_POLICY procedure to modify Oracle Data Redaction policies. In addition to changing current settings, this procedure enables you to add columns to a policy, if you want to redact more than one column in a database table.

This section contains the following topics:

• About Altering an Oracle Data Redaction Policy

• Syntax for the DBMS_REDACT.ALTER_POLICY Procedure

• Parameters Required for Various DBMS_REDACT.ALTER_POLICY Actions

• Example of Altering an Oracle Data Redaction Policy

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

4.11 Redacting Multiple Columns in an Oracle Data Redaction Policy

You can redact more than one column in a Data Redaction policy. To do so, create the policy for the first column that you want to redact. Afterward, use the DBMS_REDACT.ALTER_POLICY procedure to add the next column. As necessary, set the action, column_name, function_type, and function_parameters (or the parameters that begin with regexp_) parameters to define the redaction for the new column, but do not change the object_schema, object_name, policy_name, or expression parameters. The additional columns that you add have no effect on earlier columns that were redacted in the table. In other words, each redacted column continues to have the same redaction parameters that were used to create it.

Example 4-11 shows how to add a column to an existing Data Redaction policy. In this example, the action parameter specifies that a new column must be added, using DBMS_REDACT.ADD_COLUMN. The name of the new column, card_num, is set by the column_name parameter.

BEGIN
 DBMS_REDACT.ALTER_POLICY(
  object_schema       => 'mavis', 
  object_name         => 'cust_info', 
  policy_name         => 'redact_cust_user_ids', 
  action              => DBMS_REDACT.ADD_COLUMN,
  column_name         => 'card_num',
  function_type       => DBMS_REDACT.FULL,
  function_parameters => '',
  expression          => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''ADM'') = ''TRUE''');
END;
/

4.12 Altering the Default Full Data Redaction Value

You can use the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure to change the default values that are returned for full Data Redaction policies.

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

4.13 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 4-12 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;
/

4.14 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 4-13 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;
/

4.15 Dropping an Oracle Data Redaction Policy

To drop a Data Redaction policy, use the DBMS_REDACT.DROP_POLICY procedure. To find the names of existing Data Redaction policies, query the POLICY_NAME column of the REDACTION_POLICIES view. The policy can be either enabled or disabled when you drop it. After you run the procedure, the drop takes effect immediately.

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 "Dropping Policies When the Recycle Bin Is Enabled" for more information.

The syntax for dropping a Data Redaction policy is as follows:

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.

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

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

4.16 How Oracle Data Redaction Policies Affect Tables and Views

Oracle Data Redaction policies apply to their target table or view and to any views that are created on this target, including materialized views. (See "Creating 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:

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 4-1 shows how these policies affect the chain of views described in the previous example.

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

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

4.17 Restricting Administrative Access to Oracle Data Redaction Policies

You can restrict the list of users who can create, view and edit Data Redaction policies by limiting who has the EXECUTE privilege on the DBMS_REDACT package and by limiting who has the SELECT privilege on the REDACTION_POLICIES and REDACTION_COLUMNS views. You also can restrict who is exempted from redaction by limiting the EXEMPT REDACTION POLICY privilege. If you use Oracle Database Vault to restrict privileged user access, then you can use realms to restrict granting of EXEMPT REDACTION POLICY.

4.18 Finding Information About Oracle Data Redaction Policies

Table 4-6 lists data dictionary views that provide information about Data Redaction policies. Before you can query these views, you must be granted the SELECT_CATALOG_ROLE role.