General Syntax of the DBMS_REDACT.ADD_POLICY Procedure

To create a Data Redaction policy, you must use the DBMS_REDACT.ADD_POLICY procedure.

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

DBMS_REDACT.ADD_POLICY Parameters

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

Required

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

policy_name

Required

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. For example, suppose you have a table table_1 and two views view_1 and view_2, and each of these has a Data Redaction policy as follows:

  The table_1 table has the Data Redaction policy table_1_pol. The view_1 view is created on table_1 and has the Data Redaction policy view_1_pol. The view_2 view is created on view_1 and has the Data Redaction policy view_2_pol.

  If table_1 is queried, then policy table_1_pol is applied on the columns. When view_1 is queried, then policy view_1_pol is applied. Similarly, if view_2 is queried, columns in view_2 are redacted according to view_2_pol.

• 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. If you omit a column name when setting DBMS_REDACT.ADD_POLICY, then only the expression parameter is required.

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

column_description

Specifies a brief description of the purpose of the policy.

function_type

Specifies a function that sets the type of redaction. You can specify this parameter only when column_name is specified.

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

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 Nullify Redaction Policy
• Syntax for Creating a Policy with No Redaction

function_parameters

Specifies how the column redaction should appear for partial redaction. This parameter is required if function_type is DBMS_REDACT.PARTIAL. See Syntax for Creating a Partial Redaction Policy.

expression

Required

Specifies a Boolean SQL expression to determine when 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.

By default, a Data Redaction policy expression applies to all the columns that belong to the Data Redaction policy defined on that table or view. Alternatively, you can choose to create and associate a policy expression for individual columns to override the existing expression. These column-level expressions are called named policy expressions. See Creating and Managing Multiple Named Policy Expressions.

enable

When set to TRUE (the default), this parameter enables the policy upon creation. When set to FALSE, it creates the policy as a disabled policy. 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

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.

regexp_replace_string

Specifies how you want to replace the data to be redacted. This data can be of any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The regexp_replace_string can contain up to 500 back references to subexpressions in the form \n, where n is a number from one to nine. If you want to include a backslash (\) in the regexp_replace_string setting, then you must precede it with the escape character, which is also a backslash.

For example, to literally replace the matched pattern with \2 (rather than replace it with the second matched subexpression of the matched pattern), you enter \2 in the regexp_replace_string setting.

For more information, see Oracle AI Database SQL Language Reference.

regexp_position

Specifies the starting position for the string search. The value that you enter must be a positive integer indicating the character of the column_name data where Oracle Database should begin the search. The default is 1 or the DBMS_REDACT.RE_BEGINNING format, meaning that Oracle Database begins the search at the first character of the column_name data.

regexp_occurrence

Specifies how to perform the search and replace operation. The value that you enter must be a non-negative integer indicating the occurrence of the replace operation.

If you specify 0 or the DBMS_REDACT.RE_ALL format, then Oracle Database replaces all the occurrences of the match.

If you specify the DBMS_REDACT.RE_FIRST format, then Oracle Database replaces the first occurrence of the match.

If you specify a positive integer n, then Oracle Database replaces the nth occurrence of the match. If the occurrence is greater than 1, then the database searches for the second occurrence beginning with the first character following the first occurrence of pattern, and so forth.

regexp_match_parameter

Specifies a text literal that lets you change the default matching behavior of the function. The behavior of this parameter is the same for this function as for the REGEXP_REPLACE SQL function. To filter the search so that it is not case sensitive, specify the RE_CASE_INSENSITIVE format.

See Oracle AI Database SQL Language Reference for detailed information.

To find information about redaction policies, you can query the REDACTION_POLICIES data dictionary view.

The first time you add a full redaction policy to a boolean column 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.