General Syntax of the DBMS_REDACT.ADD

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

In this specification:

Table 3-2 DBMS_REDACT.ADD_POLICY Parameters

Parameter	Description
`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.
`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.
`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: `table_1` has the Data Redaction policy `table_1_pol`. `view_1` is created on `table_1`. `view_1` has the Data Redaction policy `view_1_pol`. `view_2` is created on `view_1`. `view_2` 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. Can be specified only when `column_name` is specified. If you omit the `function_type` parameter, then the default redaction `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. Required if `function_type` is `DBMS_REDACT.PARTIAL`. See Syntax for Creating a Partial Redaction Policy.
`enable`	When set to `TRUE` (the default), 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 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 Database SQL Language Reference for detailed information.

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