Configuring Oracle Data Redaction Policies 5-33
REDACTION_POLICIES view. The policy can be either enabled or disabled when you drop it. After you run the procedure, the drop takes effect immediately.
When you drop a table or view that is associated with an Oracle Data Redaction policy, the policy is automatically dropped. As a best practice, drop the policy first, and then drop the table or view afterward. See "Dropping Policies When the Recycle Bin Is Enabled" on page 7-3 for more information.
The syntax for dropping a Data Redaction policy is as follows:
DBMS_REDACT.DROP_POLICY (
object_schema IN VARCHAR2 DEFAULT NULL, object_name IN VARCHAR2,
policy_name IN VARCHAR2);
In this specification:
■ object_schema: Specifies the schema of the object to which the Data Redaction policy applies. If you omit this setting (or enter NULL), then Oracle Database uses the name of the current schema.
■ object_name: Specifies the name of the table or view to be used for the Data Redaction policy.
■ policy_name: Specifies the name of the policy to be dropped.
Example 5–18 shows how to drop a Data Redaction policy.
Example 5–18 Dropping a Data Redaction Policy BEGIN DBMS_REDACT.DROP_POLICY ( object_schema => 'mavis', object_name => 'cust_info', policy_name => 'redact_cust_user_ids'); END; /
Example: How Oracle Data Redaction Affects Tables and Views
Oracle Data Redaction policies apply to their target table or view and to any views that are created on this target, including materialized views. (See "Creating Policies on Materialized Views" on page 7-3 for restrictions on creating Data Redaction policies on materialized views.) If you create a view chain (that is, a view based on another view), then the Data Redaction policy also applies throughout this view chain. The policies remain in effect all of the way up through this view chain, but if another policy is created for one of these views, then for the columns affected in the subsequent views, this new policy takes precedence.
To understand how this concept works, try the following example:
1. Create and populate the following table:
CREATE TABLE TABLE1 (TC1 VARCHAR2(20), TN1 NUMBER(10)); INSERT INTO TABLE1 VALUES ('5111-1111-1111-1118', 987654329);
2. Create the following views, which will constitute the view chain for table table1:
CREATE VIEW view1 (vc1, vn1) AS SELECT tc1, tn1 FROM table1; CREATE VIEW view2 (vc2, vn2) AS SELECT vc1, vn1 FROM view1; CREATE VIEW view3 (vc3, vn3) AS SELECT vc2, vn2 FROM view2;
Example: How Oracle Data Redaction Affects Tables and Views
3. Create the following policy on the table1 table, which changes the display of the
tc1 column to random values.
BEGIN DBMS_REDACT.ADD_POLICY( object_schema => 'NULL', object_name => 'table1', column_name => 'tc1', policy_name => 't1pol', function_type => DBMS_REDACT.RANDOM, expression => '1=1'); END; /
4. Query table1.tc1, view1.vc1, view2.vc2, and view3.vc3, and you will see that they all produce random output, based on the t1pol Data Redaction policy. For example:
SELECT vc3 FROM view3; VC3
--- M,v]3(z+U4~e;0#3]<'
5. Create the following policy on view2, which changes the output of column vc2 to display no output at all (that is, a blank space).
BEGIN DBMS_REDACT.ADD_POLICY( object_schema => 'NULL', object_name => 'view2', column_name => 'vc2', policy_name => 'v2pol', function_type => DBMS_REDACT.FULL, expression => '1=1'); END; /
6. Query views view2 and view3.
SELECT vc2 FROM view2; SELECT vc3 FROM view3;
Both queries produce the same output (a blank space), which illustrates how for these views, policy v2pol overrides the base table policy, t1pol.
7. Query table table1 and view view1.
SELECT tc1 FROM table1; SELECT vc1 FROM view1;
Because table1 and view1 are lower in the chain, they are not affected by policy
v2pol1. The output for both remains as random values.
8. Create the following policy on view1, which redacts the first 5 digits of the numeric values in column vn1 to 9.
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'NULL', object_name => 'view1',
Example: How Oracle Data Redaction Affects Tables and Views
Configuring Oracle Data Redaction Policies 5-35
column_name => 'vn1', policy_name => 'v1pol', function_type => DBMS_REDACT.PARTIAL, function_parameters => '9,1,5', expression => '1=1'); END; /
9. Query view view1:
SELECT vc1, vn1 FROM view1;
VC1 VN1
--- --- :'F6`B<dB/N>hJDlJ7V 999994329
Here, view view1 is using two policies. Policy t1pol (on table table1) continues to redact column vc1, and policy v1pol (on view view1) redacts column vn1.
10. Query view view2:
SELECT vc2, vn2 FROM view2;
VC2 VN2
--- --- 999994329
View view2 also uses two policies: the blank space for its column vc2 is generated by policy v2pol, and the partial numeric redaction for vn2 comes from policy
v1pol for view view1.
11. Query view view3:
SELECT vc3, vn3 FROM view3;
VC3 VN3
--- --- 999994329
Because view view3 has no direct policies, it uses the policy settings from both
view1 and view2. Hence, the output is the same as the output for view2.
12. Disable the policy.
If you disable a policy, then the output for all of the views along the view chain that are affected by the policy is also changed.
For example, disable the policy t1pol, which was created for table table1:
EXEC DBMS_REDACT.DISABLE_POLICY (NULL, 'TABLE1', 'T1POL');
Now query view1 again:
SELECT vc1, vn1 FROM view1;
VC1 VN1
--- --- 5111-1111-1111-1118 999994329
Column vc1 shows the values from the base table table1. Column vn1 still shows the redacted values from policy v2pol.