This chapter describes the SA_POLICY_ADMIN package, which enables you to administer policies on tables and schemas. It contains these sections:
When you apply a policy to a table, the policy is automatically enabled. To disable a policy is to turn off its protections, although it is still applied to the table. To enable a policy is to turn on and enforce its protections for a particular table or schema.
To remove a policy is to take it entirely away from the table or schema. Note, however, that the policy label column and the labels remain in the table unless you explicitly drop them.
You can alter the default policy enforcement options for future tables that may be created in a schema. This does not, however, affect policy enforcement options on existing tables in the schema.
To change the enforcement options on an existing table, you must first remove the policy from the table, make the desired changes, and then reapply the policy to the table.
See Also:
"Choosing Policy Options"In an Oracle Internet Directory-enabled Oracle Label Security, a policy must be subscribed before it can be applied (by APPLY_TABLE_POLICY or APPLY_SCHEMA_POLICY). In a standalone Oracle Label Security installation, the latter functions can be used directly without the need to subscribe.
You subscribe a policy by using SA_POLICY_ADMIN.POLICY_SUBSCRIBE, as described in the next section.
Such a policy cannot be dropped unless it has been removed from any table or schema to which it was applied, and then has been unsubscribed.
You unsubscribe a policy by using SA_POLICY_ADMIN.POLICY_UNSUBSCRIBE as described in a subsequent section.
In an Oracle Internet Directory-enabled Oracle Label Security configuration, use the POLICY_SUBSCRIBE procedure to subscribe to the policy for usage in APPLY_TABLE_POLICY and APPLY_SCHEMA_POLICY. This procedure must be called for a policy before that policy can be applied to a table or schema. Subscribing is needed only once, not for each use of the policy in a table or schema.
PROCEDURE POLICY_SUBSCRIBE( policy_name IN VARCHAR2);
where policy_name specifies an existing policy.
Note::
This procedure needs to be used before policy usage only in the case of Oracle Internet Directory-enabled Oracle Label Security configuration. In the standalone Oracle Label Security case, the policy can be used in APPLY_TABLE_POLICY and APPLY_SCHEMA_POLICY directly without the need to subscribe.In an Oracle Internet Directory enabled Oracle Label Security configuration, use the POLICY_UNSUBSCRIBE procedure to unsubscribe to the policy. This procedure can be used only if the policy is not in use, that is, it has not been applied to any table or schema. (If it has been applied to tables or schemas, then it must be removed from all of them before it can be unsubscribed.) A policy can be dropped in Oracle Internet Directory (olsadmintool droppolicy in Appendix B) only if is not subscribed in any of the databases that have registered with that Oracle Internet Directory.
Two sets of functions are available to administer Oracle Label Security policies:
Schema-level functions are provided for convenience. Note, however, that administrative operations that you perform at the table level will override operations performed at the schema level.
To administer policies on tables, a user must have the EXECUTE privilege for the SA_POLICY_ADMIN package, and must have been granted the policy_DBA role. This section contains these topics:
Disabling a Policy with SA_POLICY_ADMIN.DISABLE_TABLE_POLICY
Reenabling a Policy with SA_POLICY_ADMIN.ENABLE_TABLE_POLICY
Use the APPLY_TABLE_POLICY procedure to add the specified policy to a table. A policy label column is added to the table if it does not exist, and is set to NULL. When a policy is applied, it is automatically enabled. To change the table options, labeling function, or predicate, you must first remove the policy, and then reapply it.
PROCEDURE APPLY_TABLE_POLICY ( policy_name IN VARCHAR2, schema_name IN VARCHAR2, table_name IN VARCHAR2, table_options IN VARCHAR2 DEFAULT NULL, label_function IN VARCHAR2 DEFAULT NULL, predicate IN VARCHAR2 DEFAULT NULL);
| Parameter | Specifies |
|---|---|
| policy_name | An existing policy |
| schema_name | The schema that contains the table |
| table_name | The table to be controlled by the policy |
| table_options | A comma-delimited list of policy enforcement options to be used for the table. If NULL, then the policy's default options are used. |
| label_function | A string calling a function to return a label value to use as the default. For example, my_label(:new.dept,:new.status) computes the label based on the new values of the DEPT and STATUS columns in the row. |
| predicate | An additional predicate to combine (using AND or OR) with the label-based predicate for READ_CONTROL |
The REMOVE_TABLE_POLICY procedure removes the specified policy from a table. The policy predicate and any DML triggers will be removed from the table, and the policy label column can optionally be dropped. Policies can be removed from tables belonging to a schema that is protected by the policy.
PROCEDURE REMOVE_TABLE_POLICY ( policy_name IN VARCHAR2, schema_name IN VARCHAR2, table_name IN VARCHAR2, drop_column IN BOOLEAN DEFAULT FALSE);
| Parameter | Specifies |
|---|---|
| policy_name | An existing policy |
| schema_name | The schema that contains the table |
| table_name | The table |
| drop_column | Whether the column is to be dropped: if TRUE, then the policy's column will be dropped from the table, otherwise, it will remain |
The DISABLE_TABLE_POLICY procedure disables the enforcement of the policy for the specified table without changing the enforcement options, labeling function, or predicate values. It removes the RLS predicate and DML triggers from the table.
The ENABLE_TABLE_POLICY procedure reenables the current enforcement options, labeling function, and predicate for the specified table by reapplying the RLS predicate and DML triggers.
To administer policies on schemas, a user must have the EXECUTE privilege on the SA_POLICY_ADMIN package, and must have been granted the policy_DBA role.
This section contains these topics:
Altering Enforcement Options: SA_POLICY_ADMIN.ALTER_SCHEMA_POLICY
Disabling a Policy with SA_POLICY_ADMIN.DISABLE_SCHEMA_POLICY
Reenabling a Policy with SA_POLICY_ADMIN.ENABLE_SCHEMA_POLICY
In addition to applying a policy to individual tables, you can apply a policy at the schema level. The APPLY_SCHEMA_POLICY procedure applies the specified policy to all of the existing tables in a schema (that is, to those which do not already have the policy applied) and enables the policy for these tables. Then, whenever a new table is created in the schema, the policy is automatically applied to that table, using the schema's default options. No changes are made to existing tables in the schema that already have the policy applied.
PROCEDURE APPLY_SCHEMA_POLICY ( policy_name IN VARCHAR2, schema_name IN VARCHAR2, default_options IN VARCHAR2 DEFAULT NULL);
| Parameter | Specifies |
|---|---|
| policy_name | An existing policy |
| schema_name | The schema that contains the table |
| default_options | The default options to be used for tables in the schema |
If the default_options parameter is NULL, then the policy's default options will be used to apply the policy to the tables in the schema.
The ALTER_SCHEMA_POLICY procedure changes the default enforcement options for the policy. Any new tables created in the schema will automatically have the new enforcement options applied. The existing tables in the schema are not affected.
PROCEDURE ALTER_SCHEMA_POLICY ( policy_name IN VARCHAR2, schema_name IN VARCHAR2, default_options IN VARCHAR2);
| Parameter | Specifies |
|---|---|
| policy_name | An existing policy |
| schema_name | The schema that contains the table |
| default_options | The default options to be used for new tables in the schema |
To change enforcement options on a table (rather than a schema), you must first drop the policy from the table, make the change, and then reapply the policy.
If you alter the enforcement options on a schema, then this will take effect the next time a table is created in the schema. As a result, different tables within a schema may have different policy enforcement options in force.
The REMOVE_SCHEMA_POLICY procedure removes the specified policy from a schema. The policy will be removed from all the tables in the schema and, optionally, the label column for the policy will be dropped from all the tables.
PROCEDURE REMOVE_SCHEMA_POLICY ( policy_name IN VARCHAR2, schema_name IN VARCHAR2, drop_column IN BOOLEAN DEFAULT FALSE);
| Parameter | Specifies |
|---|---|
| policy_name | An existing policy |
| schema_name | The schema that contains the table |
| drop_column | If TRUE, then the policy's column will be dropped from the tables, otherwise, the column will remain. |
The DISABLE_SCHEMA_POLICY procedure disables the enforcement of the policy for all of the tables in the specified schema, without changing the enforcement options, labeling function, or predicate values. It removes the RLS predicate and DML triggers from all the tables in the schema.
The ENABLE_SCHEMA_POLICY procedure reenables the current enforcement options, labeling function, and predicate for the tables in the specified schema by re-applying the RLS predicate and DML triggers.
Note the following aspects of using Oracle Label Security policies with schemas:
If you apply a policy to an empty schema, then every time you create a table within that schema, the policy is applied. Once the policy is applied to the schema, the default options you choose are applied to every table added.
If you remove the policy from a table so that it is unprotected, and then run SA_POLICY_ADMIN.ENABLE_SCHEMA_POLICY, then the table will remain unprotected. If you wish to protect the table once again, then you must apply the policy to the table, or re-apply the policy to the schema.
If you apply a policy to a schema that already contains tables protected by the policy, then all future tables will have the new options that were specified when you applied the policy. The existing tables will retain the options they already had.