Altering an Oracle Data Redaction Policy

3.15 Altering an Oracle Data Redaction Policy

The DBMS_REDACT.ALTER_POLICY procedure enables you to modify Oracle Data Redaction policies.

About Altering Oracle Data Redaction Policies
The DBMS_REDACT.ALTER_POLICY procedure alters a Data Redaction policy.
Syntax for the DBMS_REDACT.ALTER_POLICY Procedure
The DBMS_REDACT.ALTER_POLICY procedure syntax can be used to alter all types of Data Redaction policies.
Parameters Required for DBMS_REDACT.ALTER_POLICY Actions
The DBMS_REDACT.ALTER_POLICY procedure provides parameters than can perform various actions, such as adding or modifying a column.
Tutorial: Altering an Oracle Data Redaction Policy
You can redact multiple columns in a table or view, with each column having its own redaction setting and named policy expression.

Parent topic: Configuring Oracle Data Redaction Policies

3.15.1 About Altering Oracle Data Redaction Policies

The DBMS_REDACT.ALTER_POLICY procedure alters a Data Redaction policy.

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.

Related Topics

Parent topic: Altering an Oracle Data Redaction Policy

3.15.2 Syntax for the DBMS_REDACT.ALTER_POLICY Procedure

The DBMS_REDACT.ALTER_POLICY procedure syntax can be used to alter all types of Data Redaction policies.

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, 
   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 := 1,
   regexp_occurrence      IN BINARY_INTEGER := 0,
   regexp_match_parameter IN VARCHAR2 := NULL,
   policy_description     IN VARCHAR2 := NULL,
   column_description     IN VARCHAR2 := NULL);

The first time you alter a Data Redaction policy on a column of boolean datatype to turn it into a full redaction policy, which is done by specifying the value DBMS_REDACT.FULL for the function_type parameter, after the COMPATIBLE database initialization parameter is set to 23 or higher, you will see the BOOLEAN_VALUE column appear in the catalog view for REDACTION_VALUES_FOR_TYPE_FULL and the BOOLCOL column appear in the SYS.RADM_FPTM$ data dictionary table.

In this specification:

action: Enter one of the following values to define the kind of action to use:
- 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.MODIFY_COLUMN if you plan to change function_parameters, regexp_* parameters, or the redaction type in function_type.
- 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.
  
  Each column in the table can have a different named policy expression. (You can create and manage multiple named policy expressions.) You can modify named policy expressions by using DBMS_REDACT.UPDATE_POLICY_EXPRESSION. You cannot modify named policy expressions by using DBMS_REDACT.MODIFY_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.

Related Topics

Parent topic: Altering an Oracle Data Redaction Policy

3.15.3 Parameters Required for DBMS_REDACT.ALTER_POLICY Actions

The DBMS_REDACT.ALTER_POLICY procedure provides parameters than can perform various actions, such as adding or modifying a column.

Table 3-12 shows the combinations of these parameters.

Table 3-12 Parameters Required for Various DBMS_REDACT.ALTER_POLICY Actions

Desired Alteration	Parameters to Set
Add a column	`action` (`DBMS_REDACT.ADD_COLUMN`, optional, default is `DBMS_REDACT.ADD_COLUMN`) `column_name` (required) `function_type` (optional, default is `DBMS_REDACT.FULL`) `function_parameters` (only when `function_type` is `DBMS_REDACT.PARTIAL`) `regexp`* (only when `function_type` is `DBMS_REDACT.REGEXP` or `DBMS_REDACT.REGEXP_WIDTH`) `policy_description` (optional) `column_description` (optional)
Modify a column	`action` (`DBMS_REDACT.MODIFY_COLUMN`, required) `column_name` (required) `function_type` (optional, default is `DBMS_REDACT.FULL`) `function_parameters` (only when `function_type` is `DBMS_REDACT.PARTIAL`) `regexp`* (only when `function_type` is `DBMS_REDACT.REGEXP` or `DBMS_REDACT.REGEXP_WIDTH`) `policy_description` (optional) `column_description` (optional)
Drop a column	action (`DBMS_REDACT.DROP_COLUMN`, required) `column_name` (required)
Change the policy expression	`action` (`DBMS_REDACT.MODIFY_EXPRESSION`, required) `expression` (required) `policy_description` (optional)
Change the description of the policy	`action` (`DBMS_REDACT.SET_POLICY_DESCRIPTION`, required) `policy_description` (required)
Change the description of the column	`action` (`DBMS_REDACT.SET_COLUMN_DESCRIPTION`, required) `column_description` (required) `column_name` (required)

Parent topic: Altering an Oracle Data Redaction Policy

3.15.4 Tutorial: Altering an Oracle Data Redaction Policy

You can redact multiple columns in a table or view, with each column having its own redaction setting and named policy expression.

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.

Connect to the PDB as a user who has privileges to create users and grant them privileges.

Create the following users:

GRANT CREATE SESSION TO dr_admin IDENTIFIED BY password;
GRANT CREATE SESSION TO sales_rep IDENTIFIED BY password;
GRANT CREATE SESSION TO support_rep IDENTIFIED BY password;

Grant EXECUTE on the DBMS_REDACT PL/SQL package to user dr_admin.
```
GRANT EXECUTE ON DBMS_REDACT TO dr_admin;
```
Grant the ADMINISTER REDACTION POLICY system privilege to user dr_admin.
```
GRANT ADMINISTER REDACTION POLICY TO dr_admin;
```
Connect as user OE.

Create and populate a table that contains customer credit card information.

CREATE TABLE cust_order_info(
 first_name varchar2(20),
 last_name varchar2(20),
 address varchar2(30),
 city varchar2(30),
 state varchar2(3),
 zip varchar2(5),
 cc_num varchar2(19),
 cc_exp varchar2(7));


INSERT INTO cust_order_info VALUES ('Jane','Doe','39 Mockingbird Lane', 'San Francisco', 'CA', 94114, '5105 1051 0510 5100', '10/2018');
INSERT INTO cust_order_info VALUES ('Mary','Hightower','2319 Maple Street', 'Sonoma', 'CA', 95476, '5111 1111 1111 1118', '03/2019'); 
INSERT INTO cust_order_info VALUES ('Herbert','Donahue','292 Winsome Way', 'San Francisco', 'CA', 94117, '5454 5454 5454 5454', '08/2018');

Grant the SELECT privilege on the cust_order_info table to the sales_rep and support_rep users.
```
GRANT SELECT ON cust_order_info TO sales_rep, support_rep;
```
Connect as user dr_admin.

Modify the policy to include redaction of the expiration date.

BEGIN DBMS_REDACT.ALTER_POLICY(
    object_schema     => 'oe',
    object_name       => 'cust_order_info',
    policy_name       => 'redact_cust_cc_info',
    action            => DBMS_REDACT.ADD_COLUMN,
    column_name       => 'cc_exp',
    function_type     => DBMS_REDACT.RANDOM);
END;
/

Modify the policy again, to use a condition so that the sales_rep user views the redacted values and the support_rep user views the actual data.

BEGIN
   DBMS_REDACT.ALTER_POLICY(
   object_schema     => 'oe',
   object_name       => 'cust_order_info',
   policy_name       => 'redact_cust_cc_info',
   action            => DBMS_REDACT.MODIFY_EXPRESSION,
   expression        => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''SALES_REP''');
END;
/

To test the policy, have the two users query the cust_order_info table.

First, connect as support_rep and query the table.

SELECT cc_num, cc_exp FROM OE.cust_order_info;

CC_NUM               CC_EXP
-------------------  -------
5105 1051 0510 5100  10/2018
5111 1111 1111 1118  03/2019
5454 5454 5454 5454  08/2018

User support_rep can view the actual data. Next, connect as sales_rep and query the table.

SELECT cc_num, cc_exp FROM OE.cust_order_info;

CC_NUM             CC_EXP
----------------   -------
************5100   lST=033
************1118   OZA.w4C
************5454   B(9+;O1

Data is redacted for user sales_rep.

As user dr_admin, alter the cust_order_info to include a condition so that only support_rep sees the redacted data but sales_rep sees the actual data.

BEGIN
   DBMS_REDACT.ALTER_POLICY(
   object_schema      => 'oe',
   object_name        => 'cust_order_info',
   policy_name        => 'redact_cust_cc_info',
   action             => DBMS_REDACT.MODIFY_EXPRESSION,
   expression         => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''SUPPORT_REP''');
END;
/

Have the users test the policy again.

First, support_rep tests the policy:

SELECT cc_num, cc_exp FROM OE.cust_order_info;

CC_NUM             CC_EXP
----------------   -------
************5100   1^XMF~`
************1118   qz+9=#S
************5454   *KCaUkm

User support_rep can no longer view the actual data; it is now redacted.

Next, connect as sales_rep and query the table.

SELECT cc_num, cc_exp FROM OE.cust_order_info;

CC_NUM               CC_EXP
-------------------  -------
5105 1051 0510 5100  10/2018
5111 1111 1111 1118  03/2019
5454 5454 5454 5454  08/2018

User sales_rep now can view the actual data.

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

Connect as dr_admin and drop the policy.

BEGIN
  DBMS_REDACT.DROP_POLICY (
    object_schema  => 'oe',
    object_name    => 'cust_order_info',
    policy_name    => 'redact_cust_cc_info');
END;
/

Connect as a security administrator and drop the users.

DROP USER dr_admin;
DROP USER sales_rep;
DROP USER support_rep;

Connect as user OE and drop the cust_order_info table.

DROP TABLE cust_order_info;

Parent topic: Altering an Oracle Data Redaction Policy