Oracle Data Redaction policies are responsible to redact data in column based on column data type as well as type of redaction. We can enable and disable policies according to our convenience.
In this article we going to discuss on following topics where as in my earlier article we already cover Data Redaction Introduction part.
- About Data Redaction Policies
- DBMS package used for Data Redaction and list of procedures in the package.
- Privilege required to execute Data Redaction
- Ask yourself sort of questions before planning Data Redaction Policies
- General syntax
Lets start one by one:
About Data Redaction Policies:
Policies can defines the conditions into which data redaction going to occurs for table/view. Policies who defines kind of redaction to perform, How the data redaction should occur and when data redaction should take place.
Data redaction policies can be fully redact, partially redact, randomly redact and No redact for test purpose.
Policy can be defined with a policy expression which allows for different application users to be presented with either redacted data or actual data, based on whether the policy expression returns TRUE or FALSE. Redaction takes place when the boolean result of evaluating the policy expression is TRUE.
DBMS_REDACT DBMS package is used for Data Redaction and list of procedures in the package are as follows:
DBMS_REDACT.ADD_POLICY : Used to add policy to a table or view.
DBMS_REDACT.ALTER_POLICY : Used to modify already created policy.
DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES : Globally updates the full redaction value for a given data type. Instance restart will required for the same.
DBMS_REDACT.ENABLE_POLICY : Used to enable policy.
DBMS_REDACT.DISABLE_POLICY : Used to disable policy.
DBMS_REDACT.DROP_POLICY : Used to drop policy.
Privilege required to execute Data Redaction
To create data redaction policy, user must have EXECUTE permission/privilege on DBMS_REDACT PL/SQL package. No need of any privileges required to access the underlying tables or views that will be protected against policies.
Ask yourself following questions before planning Data Redaction Policies:
- Have you granted with EXECUTE privilege on the DBMS_REDACT PL/SQL package?
- Which data type of the table/view column that you want to redact?
- Do you use desire column in Oracle Virtual Private Database (VPD) row filtering condition? Because we cant use same column for data redaction.
- Which type of redaction you want to perform on table/view? (i.e. full, random, partial, regular expressions, or none)
- Which users to apply the Data Redaction policy to?
Note: When you create policy, it will be automatically ready to redact data.
General syntax of the DBMS_REDACT.ADD_POLICY Procedure:
DBMS_REDACT.ADD_POLICY procedure is used to create data redaction policy.
Syntax is as follows:
DBMS_REDACT.ADD_POLICY ( object_schema IN VARCHAR2 := NULL, object_name IN VARCHAR2 := NULL, 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);
Details of above syntax parameter:
object_schema: Schema of the object on which the policy will be applied.
object_name: Name of object on which the Data Redaction policy applies.
policy_name: Name of the policy. Should be unique through out instance. Find already created policies by querying data dictionary view: REDACTION_POLICIES.
policy_description: Purpose to create it with precise description.
column_name: Column name on which policy applies.
- We can apply policy on multiple columns.
- We can define only one policy on a table/view.
- No columns are redacted by the policy, in case you don’t specify a column. (for example, by entering NULL)
- We can’t define a policy on a column that is involved in the SQL expression of any virtual column.
column_description: Precise description of column that are going to be redacted.
function_type: defines types of data redacted policies. Note: Default function_type would be full redaction in case of we omit to specify it.
function_parameters: In case of partial redaction, parameter specifies how column redaction should appear.
expression: Specifies a Boolean SQL expression to decide how the policy is applied.
enable: If parameter set to TRUE, policy will automatically enable at the time of creation. else FALSE will be considered it to enable letter on.
Rest of all parameters: all used for regular expression to redact data, either full or partial. If regexp_pattern parameter don’t match with anything in target data than full redaction will be take place.
Kindly stay tune with my next article about Hand-on on Full Data Redaction Policy.