9 Applying Policies to Tables and Schemas

This chapter describes the SA_POLICY_ADMIN package, which enables you to administer policies on tables and schemas. It contains these sections:

Policy Administration Terminology
Subscribing Policies in Directory-Enabled Label Security
Policy Administration Functions for Tables and Schemas
Administering Policies on TablesUsing SA_POLICY_ADMIN
Administering Policies on Schemaswith SA_POLICY_ADMIN

9.1 Policy Administration Terminology

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.

9.2 Subscribing Policies in Directory-Enabled Label Security

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.

9.2.1 Subscribing to a Policy with SA_POLICY_ADMIN.POLICY_SUBSCRIBE

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.

9.2.1.1 Syntax

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.

9.2.1.1.1 Example:

The following statement subscribes the database to the HUMAN_RESOURCES policy so that it can used by applying on tables and schema.

SA_POLICY_ADMIN.POLICY_SUBSCRIBE('human_resources');

9.2.2 Unsubscribing to a Policy with SA_POLICY_ADMIN.POLICY_UNSUBSCRIBE

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.

9.2.2.1 Syntax

PROCEDURE POLICY_UNSUBSCRIBE(
  policy_name  IN VARCHAR2);

where policy_name specifies an existing policy.

9.2.2.1.1 Example:

The following statement unsubscribes the database to the HUMAN_RESOURCES policy.

SA_POLICY_ADMIN.POLICY_UNSUBSCRIBE('human_resources');

9.3 Policy Administration Functions for Tables and Schemas

Two sets of functions are available to administer Oracle Label Security policies:

functions to administer policies at the table level
functions to administer policies at the schema level

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.

Table 9-1 Policy Administration Functions

Purpose	Table-Level Function	Schema-Level Function
Apply policy	APPLY_TABLE_POLICY	APPLY_SCHEMA_POLICY
Alter policy	Not applicable	ALTER_SCHEMA_POLICY
Disable policy	DISABLE_TABLE_POLICY	DISABLE_SCHEMA_POLICY
Reenable policy	ENABLE_TABLE_POLICY	ENABLE_SCHEMA_POLICY
Remove policy	REMOVE_TABLE_POLICY	REMOVE_SCHEMA_POLICY

To perform these functions with Oracle Policy Manager, go to Oracle Label Security Policies,policyname,Protected Objects. Select either Schemas or Tables, and use the corresponding property sheet.

Note:

You should restrict access to application tables when using Oracle Policy Manager to change enforcement options. This is because Oracle Policy Manager must remove the policy to make such changes, and then reapply the policy after the changes have been made.

9.4 Administering Policies on Tables Using SA_POLICY_ADMIN

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. Authorized users can also perform these functions with the Oracle Policy Manager. This section contains these topics:

Applying a Policy with SA_POLICY_ADMIN.APPLY_TABLE_POLICY
Removing a Policy with SA_POLICY_ADMIN.REMOVE_TABLE_POLICY
Disabling a Policy with SA_POLICY_ADMIN.DISABLE_TABLE_POLICY
Reenabling a Policy with SA_POLICY_ADMIN.ENABLE_TABLE_POLICY

9.4.1 Applying a Policy with SA_POLICY_ADMIN.APPLY_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.

9.4.1.1 Syntax

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

9.4.1.1.1 Example:

The following statement applies the HUMAN_RESOURCES policy to the EMP table in the SA_DEMO schema.

SA_POLICY_ADMIN.APPLY_TABLE_POLICY('human_resources',

'sa_demo','emp','no_control');

9.4.2 Removing a Policy with SA_POLICY_ADMIN.REMOVE_TABLE_POLICY

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.

9.4.2.1 Syntax

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

9.4.2.1.1 Example:

The following statement removes the HUMAN_RESOURCES policy from the EMP table in the SA_DEMO schema:

SA_POLICY_ADMIN.REMOVE_TABLE_POLICY('human_resources','sa_demo','emp');

9.4.3 Disabling a Policy with SA_POLICY_ADMIN.DISABLE_TABLE_POLICY

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.

9.4.3.1 Syntax

PROCEDURE DISABLE_TABLE_POLICY (
  policy_name      IN VARCHAR2,
  schema_name      IN VARCHAR2,
  table_name       IN VARCHAR2);

Parameter	Specifies
policy_name	An existing policy
schema_name	The schema that contains the table
table_name	The table

9.4.3.1.1 Example:

The following statement disables the HUMAN_RESOURCES policy on the EMP table in the SA_DEMO schema:

SA_POLICY_ADMIN.DISABLE_TABLE_POLICY('human_resources','sa_demo','emp');

9.4.4 Reenabling a Policy with SA_POLICY_ADMIN.ENABLE_TABLE_POLICY

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.

9.4.4.1 Syntax

PROCEDURE ENABLE_TABLE_POLICY (
  policy_name     IN VARCHAR2,
  schema_name     IN VARCHAR2,
  table_name      IN VARCHAR2);

Parameter	Specifies
policy_name	An existing policy
schema_name	The schema that contains the table
table_name	The table

9.4.4.1.1 Example:

The following statement reenables the HUMAN_RESOURCES policy on the EMP table in the SA_DEMO schema:

SA_POLICY_ADMIN.ENABLE_TABLE_POLICY('human_resources','sa_demo','emp');

9.5 Administering Policies on Schemas with SA_POLICY_ADMIN

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. Authorized users can also use the Oracle Policy Manager to perform these functions.

This section contains these topics:

Applying a Policy with SA_POLICY_ADMIN.APPLY_SCHEMA_POLICY
Altering Enforcement Options: SA_POLICY_ADMIN.ALTER_SCHEMA_POLICY
Removing a Policy with SA_POLICY_ADMIN.REMOVE_SCHEMA_POLICY
Disabling a Policy with SA_POLICY_ADMIN.DISABLE_SCHEMA_POLICY
Reenabling a Policy with SA_POLICY_ADMIN.ENABLE_SCHEMA_POLICY
Policy Issues for Schemas

9.5.1 Applying a Policy with SA_POLICY_ADMIN.APPLY_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.

9.5.1.1 Syntax

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.

9.5.2 Altering Enforcement Options: SA_POLICY_ADMIN.ALTER_SCHEMA_POLICY

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.

9.5.2.1 Syntax

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.

9.5.3 Removing a Policy with SA_POLICY_ADMIN.REMOVE_SCHEMA_POLICY

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.

9.5.3.1 Syntax

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.

9.5.4 Disabling a Policy with SA_POLICY_ADMIN.DISABLE_SCHEMA_POLICY

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.

9.5.4.1 Syntax

PROCEDURE DISABLE_SCHEMA_POLICY (
  policy_name    IN VARCHAR2,
  schema_name    IN VARCHAR2);

Parameter	Specifies
policy_name	An existing policy
schema_name	The schema that contains the table

9.5.5 Reenabling a Policy with SA_POLICY_ADMIN.ENABLE_SCHEMA_POLICY

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.

9.5.5.1 Syntax

PROCEDURE ENABLE_TABLE_POLICY (
  policy_name    IN VARCHAR2,
  schema_name    IN VARCHAR2);

Parameter	Specifies
policy_name	An existing policy
schema_name	The schema that contains the table

The result is like enabling a policy for a table, but it covers all the tables in the schema.

9.5.6 Policy Issues for Schemas

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.