Creating a Nullify Redaction Policy

3.11 Creating a Nullify Redaction Policy

You can create Oracle Data Redaction policies that return null values for the displayed value of the table or view column.

3.11.1 About Creating a Policy That Returns Null Values

The DBMS_REDACT.NULLIFY function_type parameter redacts all the query-result data in a column and replaces it with null values.

You can use this function type on all supported column data types that the DBMS_REDACT.NULLIFY function type supports. It also supports the CLOB and NCLOB data types. To use the DBMS_REDACT.NULLIFY function, you must first ensure that the COMPATIBLE parameter is set to 12.2.0.0 or later.

3.11.2 Syntax for Creating a Nullify Redaction Policy

The DBMS_REDACT.ADD_POLICY procedure can create a redaction policy that performs a full redaction and displays null values for the redacted columns.

The syntax for using DBMS_REDACT.ADD_POLICY to return null values is 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.NULLIFY.

If you omit the function_type parameter, then the default 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.11.3 Example: Redaction Policy That Returns Null Values

The DBMS_REDACT.ADD_POLICY procedure will add Nullify redaction for the COMMISSION_PCT column of the HR.EMPLOYEES table.

The expression parameter applies the policy to any user who queries the table, except for users who have been granted the EXEMPT REDACTION POLICY system or schema privilege.

Example 3-10 shows how to create the Oracle Data Redaction policy.

Example 3-10 Redaction Policy That Returns Null Values

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema    => 'hr', 
   object_name      => 'employees', 
   column_name      => 'commission_pct',
   policy_name      => 'nullify_com_pct', 
   function_type    => DBMS_REDACT.NULLIFY,
   expression       => '1=1');
END;
/

Query and redacted result:

SELECT COMMISSION_PCT FROM HR.EMPLOYEES;

COMMISSION_PCT
--------------

Related Topics

Exemption of Users from Oracle Data Redaction Policies