Creating a Full Redaction Policy and Altering the Full Redaction Value

3.7 Creating a Full Redaction Policy and Altering the Full Redaction Value

You can create a full redaction policy to redact all contents in a data column, and optionally, you can alter the default full redaction value.

3.7.1 Creating a Full Redaction Policy

A full data redaction policy redacts all the contents of a data column.

3.7.1.1 About Creating Full Data Redaction Policies

To set a redaction policy to redact all data in the column, you must set the function_type parameter to DBMS_REDACT.FULL.

By default, NUMBER data type columns are replaced with zero (0) and character data type columns are replaced with a single space ( ). You can modify this default by using the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure.

Related Topics

Altering the Default Full Data Redaction Value

3.7.1.2 Syntax for Creating a Full Redaction Policy

The DBMS_REDACT.ADD_POLICY procedure enables you to create a full redaction policy.

The DBMS_REDACT.ADD_POLICY fields for creating a full data redaction policy are as follows:

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

In this specification:

object_schema, object_name, column_name, policy_name, expression, enable: See General Syntax of the DBMS_REDACT.ADD_POLICY Procedure.
function_type: Specifies the function used to set the type of redaction. Enter DBMS_REDACT.FULL.

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

Remember that the data type of the column determines which function_type settings that you are permitted to use. See Comparison of Full, Partial, Regexp, Random, and Nullify Redaction Based on Data Types.

3.7.1.3 Example: Full Redaction Policy

You can use the DBMS_REDACT.ADD_POLICY PL/SQL procedure to create a full redaction policy.

Example 3-1 shows how to use full redaction for all the values in the HR.EMPLOYEES table COMMISSION_PCT column. Because the expression parameter in this example always evaluates to TRUE, the data redaction policy is applied to any user querying the table except for users who have been granted the EXEMPT REDACTION POLICY system or schema privilege, or the SYSDBA administrative privilege.

Example 3-1 Full Data Redaction Policy

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

Query and redacted result:

SELECT COMMISSION_PCT FROM HR.EMPLOYEES;

COMMISSION_PCT
--------------
0
0
0

Related Topics

Exemption of Users from Oracle Data Redaction Policies

3.7.1.4 Example: Fully Redacted Character Values

You can use the DBMS_REDACT.ADD_POLICY PL/SQL procedure to create a policy that fully redacts character values.

Example 3-2 shows how to fully redact the user IDs of the user_id column in the mavis.cust_info table. The user_id column is of the VARCHAR2 data type. The output is a single space. The expression setting enables users who have the MGR role to view the user IDs.

Example 3-2 Fully Redacted Character Values

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

Query and redacted result:

SELECT user_id FROM mavis.cust_info;

USER_ID
------------
0
0
0

3.7.2 Altering the Default Full Data Redaction Value

The DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure alters the default full data redaction value.

3.7.2.1 About Altering the Default Full Data Redaction Value

You can alter the default displayed values for full Data Redaction polices.

By default, 0 is the redacted value when Oracle Database performs full redaction (DBMS_REDACT.FULL) on a column of the NUMBER data type. If you want to change it to another value (for example, 7), then you can run the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure to modify this value. The modification applies to all of the Data Redaction policies in the current database instance. After you modify a value, you must restart the database for it to take effect. You can find the current values by querying the REDACTION_VALUES_FOR_TYPE_FULL data dictionary view.

Be aware that this change affects all Data Redaction policies in the database that use full data redaction. Before you alter the default full data redaction value, examine the affect that this change would have on existing full Data Redaction policies.

3.7.2.2 Syntax for the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES Procedure

The DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure accommodates the standard supported Oracle Database data types.

The syntax is as follows:

DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES (
 number_val       IN NUMBER                    NULL,
 binfloat_val     IN BINARY_FLOAT              NULL,
 bindouble_val    IN BINARY_DOUBLE             NULL,
 char_val         IN CHAR                      NULL,
 varchar_val      IN VARCHAR2                  NULL,
 nchar_val        IN NCHAR                     NULL,
 nvarchar_val     IN NVARCHAR2                 NULL,
 date_val         IN DATE                      NULL,
 ts_val           IN TIMESTAMP                 NULL,
 tswtz_val        IN TIMESTAMP WITH TIME ZONE  NULL,
 blob_val         IN BLOB                      NULL,
 clob_val         IN CLOB                      NULL,
 nclob_val        IN NCLOB                     NULL,
 boolean_val      IN BOOLEAN                   NULL);

In this specification:

number_val modifies the default value for columns of the NUMBER data type.
binfloat_val modifies the default value for columns of the BINARY_FLOAT data type.
bindouble_val modifies the default value for columns of the BINARY_DOUBLE data type.
char_val modifies the default value for columns of the CHAR data type.
varchar_val modifies the default value for columns of the VARCHAR2 data type.
nchar_val modifies the default value for columns of the NCHAR data type.
nvarchar_val modifies the default value for columns of the NVARCHAR2 data type.
date_val modifies the default value for columns of the DATE data type.
ts_val modifies the default value for columns of the TIMESTAMP data type.
tswtz_val modifies the default value for columns of the TIMESTAMP WITH TIME ZONE data type.
blob_val modifies the default value for columns of the BLOB data type.
clob_val modifies the default value for columns of the CLOB data type.
nclob modifies the default value for columns of the NCLOB data type.
boolean_val modifies the default value for columns of the BOOLEAN data type.

The first time DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES is invoked with a non-NULL value for its boolean_val 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.

3.7.2.3 Modifying the Default Full Data Redaction Value

To modify the default full data redaction value, use the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure.

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.
Check the value that you want to change.
For example, to check the current value for columns that use the NUMBER data type:
```
SELECT NUMBER_VALUE FROM REDACTION_VALUES_FOR_TYPE_FULL;

NUMBER_VALUE
------------
           0
```
Run the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure to modify the value.
For example:
```
EXEC DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES (number_val => 7);
```

Close and then re-open the PDB.

ALTER PLUGGABLE DATABASE pdb_name CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE pdb_name OPEN;

Check the value that you changed.

SELECT NUMBER_VALUE FROM REDACTION_VALUES_FOR_TYPE_FULL;

NUMBER_VALUE
–-----------
           7