8
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
• Policy Administration Functions for Tables and Schemas
• Administering Policies on Tables Using SA_POLICY_ADMIN
• Administering Policies on Schemas with SA_POLICY_ADMIN

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 labels remain in the table unless you explicitly drop them.

You can alter the default policy enforcement options for future tables which 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 re-apply the policy to the table.

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 which you perform at the table level will override operations performed at the schema level.

Table 8-1 Policy Administration Functions

Purpose	Table-Level Function	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
Re-enable 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.

Administering Policies on Tables Using SA_POLICY_ADMIN

To administer policies on tables, a user must have 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
• Re-enabling a Policy with SA_POLICY_ADMIN.ENABLE_TABLE_POLICY

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, then re-apply it.

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

policy_name	Specifies an existing policy
schema_name	Specifies the schema which contains the table
table_name	Specifies the table to be controlled by the policy
table_options	A comma-separated 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 invoking 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	Specifies an additional predicate to combine (using AND or OR) with the label-based predicate for READ_CONTROL

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

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.

Syntax:

PROCEDURE REMOVE_TABLE_POLICY (
policy_name        IN VARCHAR2,
schema_name        IN VARCHAR2,
table_name         IN VARCHAR2,
  drop_column      IN BOOLEAN DEFAULT FALSE);

policy_name	Specifies an existing policy
schema_name	Specifies the schema which contains the table
table_name	Specifies the table
drop_column	If TRUE, the policy's column will be dropped from the table. Otherwise, the column will remain.

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

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.

Syntax:

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

policy_name	Specifies an existing policy
schema_name	Specifies the schema which contains the table
table_name	Specifies the table

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

Re-enabling a Policy with SA_POLICY_ADMIN.ENABLE_TABLE_POLICY

The ENABLE_TABLE_POLICY procedure re-enables the current enforcement options, labeling function, and predicate for the specified table by re-applying the RLS predicate and DML triggers.

Syntax:

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

policy_name	Specifies an existing policy
schema_name	Specifies the schema which contains the table
table_name	Specifies the table

Example:

The following statement re-enables the HUMAN_RESOURCES policy on the EMP table in the SA_DEMO schema:

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

Administering Policies on Schemas with SA_POLICY_ADMIN

To administer policies on schemas, a user must have 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
• Re-Enabling a Policy with SA_POLICY_ADMIN.ENABLE_SCHEMA_POLICY
• Policy Issues for Schemas

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 which already have the policy applied.

Syntax:

PROCEDURE APPLY_SCHEMA_POLICY (
  policy_name        IN VARCHAR2,
  schema_name        IN VARCHAR2,
  default_options    IN VARCHAR2 DEFAULT NULL);

policy_name	Specifies an existing policy.
schema_name	Specifies the schema name to control with the policy.
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.

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; existing tables in the schema are not affected.

Syntax:

PROCEDURE ALTER_SCHEMA_POLICY (
  policy_name         IN VARCHAR2,
  schema_name         IN VARCHAR2,
  default_options     IN VARCHAR2);

policy_name	Specifies an existing policy.
schema_name	Specifies the schema name to control with the policy.
default_options	The default options to be used for new tables created 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 re-apply the policy.

If you alter the enforcement options on a schema, 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.

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 of the tables in the schema and, optionally, the label column for the policy will be dropped from all of the tables.

Syntax:

PROCEDURE REMOVE_SCHEMA_POLICY (
  policy_name     IN VARCHAR2,
  schema_name     IN VARCHAR2,
  drop_column     IN BOOLEAN DEFAULT FALSE);

policy_name	Specifies an existing policy
schema_name	Specifies the schema name
drop_column	If TRUE, the policy's column will be dropped from the tables; otherwise, the column will remain.

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.

Syntax:

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

policy_name	Specifies an existing policy
schema_name	Specifies the schema name containing the table

Re-Enabling a Policy with SA_POLICY_ADMIN.ENABLE_SCHEMA_POLICY

The ENABLE_SCHEMA_POLICY procedure re-enables the current enforcement optiofns, labeling function, and predicate for the tables in the specified schema by re-applying the RLS predicate and DML triggers.

Syntax:

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

policy_name	Specifies an existing policy
schema_name	Specifies the schema name containing the table

This is the same as enabling a policy for a table, but it covers all tables in the schema.

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 execute SA_POLICY_ADMIN.ENABLE_SCHEMA_POLICY, the table will remain unprotected. If you wish to protect the table once again, you must apply the policy to the table, or re-apply the policy to the schema.

If you apply a policy to a schema which 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 keep the options they already had.