DBMS_RLS , 2 of 2

Summary of DBMS_RLS Subprograms

Table 61-1 DBMS_RLS Subprograms

Subprogram	Description
ADD_POLICY Procedure	Adds a fine-grained access control policy to a table, view, or synonym..
DROP_POLICY Procedure	Drops a fine-grained access control policy from a table, view, or synonym..
REFRESH_POLICY Procedure	Causes all the cached statements associated with the policy to be reparsed.
ENABLE_POLICY Procedure	Enables or disables a fine-grained access control policy.
CREATE_POLICY_GROUP Procedure	Creates a policy group.
ADD_GROUPED_POLICY Procedure	Adds a policy associated with a policy group.
ADD_POLICY_CONTEXT Procedure	Adds the context for the active application.
DELETE_POLICY_GROUP Procedure	Deletes a policy group.
DROP_GROUPED_POLICY Procedure	Drops a policy associated with a policy group.
DROP_POLICY_CONTEXT Procedure	Drops a driving context from the object so that it will have one less driving context.
ENABLE__GROUPED_POLICY Procedure	Enables or disables a row-level group security policy.
REFRESH_GROUPED_POLICY Procedure	Reparses the SQL statements associated with a refreshed policy.

ADD_POLICY Procedure

This procedure adds a fine-grained access control policy to a table , view, or synonym.

The procedure causes the current transaction, if any, to commit before the operation is carried out. However, this does not cause a commit first if it is inside a DDL event trigger.

See Also:

"Usage Notes"

A COMMIT is also performed at the end of the operation.

Syntax

DBMS_RLS.ADD_POLICY (
   object_schema   IN VARCHAR2 NULL,
   object_name     IN VARCHAR2,
   policy_name     IN VARCHAR2,
   function_schema IN VARCHAR2 NULL,
   policy_function IN VARCHAR2,
   statement_types IN VARCHAR2 NULL,
   update_check    IN BOOLEAN  FALSE,
   enable          IN BOOLEAN  TRUE,
   static_policy   IN BOOLEAN  FALSE);

Parameters

Table 61-2 ADD_POLICY Procedure Parameters

Parameter	Description
object_schema	Schema containing the table,view, or synonym (current default schema, if `NULL`).
object_name	Name of table, view, or synonym to which the policy is added.
policy_name	Name of policy to be added. It must be unique for the same table or view.
function_schema	Schema of the policy function (current default schema, if `NULL`).
policy_function	Name of a function which generates a predicate for the policy. If the function is defined within a package, then the name of the package must be present.
statement_types	Statement types to which the policy applies. It can be any combination of `SELECT`, `INSERT`, `UPDATE`, and `DELETE`. The default is to apply to all of these types.
update_check	Optional argument for `INSERT` or `UPDATE` statement types. The default is `FALSE`. Setting `update_check` to `TRUE` causes the server to also check the policy against the value after insert or update.
enable	Indicates if the policy is enabled when it is added. The default is `TRUE`
static_policy	The default is `FALSE`. If it is set to `TRUE`, the server assumes that the policy function for the static policy produces the same predicate string for anyone accessing the object, except for `SYS` or the privilege user who has the `EXEMPT ACCESS POLICY` privilege.

Usage Notes

The server invokes the policy function once for each cursor and will therefore improve performance for statement parsing and execution. Declaring a policy function DETERMINISTIC does not affect performance.
SYS is free of any security policy.

The policy functions which generate dynamic predicates are called by the server. Following is the interface for the function:

    FUNCTION policy_function (object_schema IN VARCHAR2, object_name VARCHAR2) 
        RETURN VARCHAR2 
    --- object_schema is the schema owning the table of view.
    --- object_name is the name of table, view, or synonym to which the policy 
applies.

The maximum length of the predicate that the policy function can return is 32K.

The policy functions must have the purity level of WNDS (write no database state).

See Also:
The Oracle9i Application Developer's Guide - Fundamentals has more details about the RESTRICT_REFERENCES pragma.
Dynamic predicates generated out of different policies for the same object have the combined effect of a conjunction (ANDed) of all the predicates.
The security check and object lookup are performed against the owner of the policy function for objects in the subqueries of the dynamic predicates.
If the function returns a zero length predicate, then it is interpreted as no restriction being applied to the current user for the policy.
When a table alias is required (for example, parent object is a type table) in the predicate, the name of the table or view itself must be used as the name of the alias. The server constructs the transient view as something like "select c1, c2, ... from tab where <predicate>".
The checking of the validity of the function is done at runtime for ease of installation and other dependency issues during import/export.

DROP_POLICY Procedure

This procedure drops a fine-grained access control policy from a table, view, or synonym.

The procedure causes the current transaction, if any, to commit before the operation is carried out. However, this does not cause a commit first if it is inside a DDL event trigger.

See Also:

"Usage Notes"

A commit is also performed at the end of the operation.

Syntax

DBMS_RLS.DROP_POLICY (
   object_schema IN VARCHAR2 NULL,
   object_name   IN VARCHAR2,
   policy_name   IN VARCHAR2);

Parameters

Table 61-3 DROP_POLICY Procedure Parameters

Parameter	Description
object_schema	Schema containing the table, view or synonym (current default schema if `NULL`).
object_name	Name of table, view, or synonym.
policy_name	Name of policy to be dropped from table, view, or synonym..

REFRESH_POLICY Procedure

This procedure causes all the cached statements associated with the policy to be reparsed. This guarantees that the latest change to this policy will have immediate effect after the procedure is executed.

The procedure causes the current transaction, if any, to commit before the operation is carried out. However, this does not cause a commit first if it is inside a DDL event trigger.

See Also:

"Usage Notes"

A commit is also performed at the end of the operation.

Syntax

DBMS_RLS.REFRESH_POLICY (
   object_schema IN VARCHAR2 NULL,
   object_name   IN VARCHAR2 NULL,
   policy_name   IN VARCHAR2 NULL);

Parameters

Table 61-4 REFRESH_POLICY Procedure Parameters

Parameter	Description
object_schema	Schema containing the table, view, or synonym.
object_name	Name of table, view, or synonym with which the policy is associated.
policy_name	Name of policy to be refreshed.

Errors

The procedure returns an error if it tries to refresh a disabled policy.

ENABLE_POLICY Procedure

This procedure enables or disables a fine-grained access control policy. A policy is enabled when it is created.

The procedure causes the current transaction, if any, to commit before the operation is carried out. However, this does not cause a commit first if it is inside a DDL event trigger.

See Also:

"Usage Notes"

A commit is also performed at the end of the operation.

Syntax

DBMS_RLS.ENABLE_POLICY (
   object_schema IN VARCHAR2 NULL,
   object_name   IN VARCHAR2,
   policy_name   IN VARCHAR2,
   enable        IN BOOLEAN);

Parameters

Table 61-5 ENABLE_POLICY Procedure Parameters

Parameter	Description
object_schema	Schema containing table, view, or synonym (current default schema if `NULL`).
object_name	Name of table, view, or synonym with which the policy is associated.
policy_name	Name of policy to be enabled or disabled.
enable	`TRUE` to enable the policy, `FALSE` to disable the policy.

CREATE_POLICY_GROUP Procedure

This procedure creates a policy group.

Syntax

DBMS_RLS.CREATE_POLICY_GROUP (
   object_schema   VARCHAR2,
   object_name     VARCHAR2,
   policy_group    VARCHAR2 );

Parameters

Table 61-6 CREATE_POLICY_GROUP Procedure Parameters

Parameter	Description
object_schema	Schema containing the table, view, or synonym.
object_name	Name of the table, view, or synonym to which the policy is added.
policy_group	Name of the policy group that the policy belongs to.

Usage Notes

The group must be unique for each table or view.

ADD_GROUPED_POLICY Procedure

This procedure adds a policy associated with a policy group.

Syntax

DBMS_RLS.ADD_GROUPED_POLICY(
   object_schema   VARCHAR2,
   object_name     VARCHAR2,
   policy_group    VARCHAR2,
   policy_name     VARCHAR2,
   function_schema VARCHAR2,
   policy_function VARCHAR2,
   statement_types VARCHAR2,
   update_check    BOOLEAN,
   enabled         BOOLEAN,
   static_policy   BOOLEAN  FALSE );

Parameters

Table 61-7 ADD_GROUPED_POLICY Procedure Parameters

Parameter	Description
object_schema	The schema containing the table, view, or synonym.
object_name	The name of the table, view, or synonym to which the policy is added.
policy_group	The name of the policy group that the policy belongs to.
policy_name	The name of the policy; must be unique for the same table or view.
function_schema	The schema owning the policy function.
policy_function	The name of the function that generates a predicate for the policy. If the function is defined within a package, the name of the package must be present.
statement_types	The list of statement types to which the policy can apply. It can be any combination of `SELECT, INSERT, UPDATE,` or `DELETE`. Optional.
update_check	For `INSERT` and `UPDATE` statements only, setting update_check to `TRUE` causes the server to check the policy against the value after `INSERT` or `UPDATE.`
enable	Indicates if the policy is enable when it is added. The default is `TRUE`.
static_policy	The default is `FALSE`. If it is set to `TRUE`, the server assumes that the policy function for the static policy produces the same predicate string for anyone accessing the object, except for `SYS` or the privilege user who has the `EXEMPT ACCESS POLICY` privilege.

Usage Notes

The server invokes the policy function once for each cursor and will therefore improve performance for statement parsing and execution. Declaring a policy function DETERMINISTIC does not affect performance.
This procedure adds a policy to the specified table, view, or synonym and associates the policy with the specified policy group.
The policy group must have been created using the CREATE_POLICY_GROUP interface.
The policy name must be unique within a policy group for a specific object.
Policies from the default policy group, SYS_DEFAULT, are always executed regardless of the active policy group; however, fine-grained access control policies do not apply to users with EXEMPT ACCESS POLICY system privilege.

ADD_POLICY_CONTEXT Procedure

This procedure adds the context for the active application.

Syntax

DBMS_RLS.ADD_POLICY_CONTEXT (
   object_schema   VARCHAR2,
   object_name     VARCHAR2,
   namespace       VARCHAR2,
   attribute       VARCHAR2 );

Parameters

Table 61-8 ADD_POLICY_CONTEXT Procedure Parameters

Parameter	Description
`object_schema`	The schema containing the table, view, or synonym.
`object_name`	The name of the table, view, or synonym to which the policy is added.
`namespace`	The namespace of the driving context
`attribute`	The attribute of the driving context.

Usage Notes

Note the following:

This procedure indicates the application context that drives the enforcement of policies; this is the context that determines which application is running.
The driving context can be session or global.
At execution time, the server retrieves the name of the active policy group from the value of this context.
There must be at least one driving context defined for each object that has fine- grained access control policies; otherwise, all policies for the object will be executed.
Adding multiple context to the same object will cause policies from multiple policy groups to be enforced.
If the driving context is NULL, policies from all policy groups are used.
If the driving context is a policy group with policies, all enabled policies from that policy group will be applied, along with all policies from the SYS_DEFAULT policy group.

To add a policy to table hr.employees in group access_control_group, the following command is issued:

DBMS_RLS.ADD_GROUPED_POLICY('hr','employees','access_control_    
group','policy1','SYS', 'HR.ACCESS');

DELETE_POLICY_GROUP Procedure

This procedure deletes a policy group.

Syntax

DBMS_RLS.DELETE_POLICY_GROUP (
  object_schema   VARCHAR2,
  object_name     VARCHAR2,
  policy_group    VARCHAR2 );

Parameters

Table 61-9 DELETE_POLICY_GROUP Procedure Parameters

Parameter	Description
`object_schema`	The schema containing the table, view, or synonym.
`object_name`	The name of the table, view, or synonym to which the policy is added.
`policy_group`	The name of the policy group that the policy belongs to

Usage Notes

Note the following:

This procedure deletes a policy group for the specified table, view, or synonym.
No policy can be in the policy group.

DROP_GROUPED_POLICY Procedure

This procedure drops a policy associated with a policy group.

Syntax

DBMS_RLS.DROP_GROUPED_POLICY (
   object_schema   VARCHAR2,
   object_name     VARCHAR2,
   policy_group    VARCHAR2,
   policy_name     VARCHAR2 );

Parameters

Table 61-10 DROP_GROUPED_POLICY Procedure Parameters

Parameter	Description
`object_schema`	The schema containing the table, view, or synonym.
`object_name`	The name of the table, view, or synonym to which the policy is dropped.
`policy_group`	The name of the policy group that the policy belongs to.
`policy_name`	The name of the policy.

DROP_POLICY_CONTEXT Procedure

This procedure drops a driving context from the object so that it will have one less driving context.

Syntax

DBMS_RLS.DROP_POLICY_CONTEXT (
   object_schema   VARCHAR2,
   object_name     VARCHAR2,
   namespace       VARCHAR2,
   attribute       VARCHAR2 );

Parameters

Table 61-11 DROP_POLICY_CONTEXT Procedure Parameters

Parameter	Description
`object_schema`	The schema containing the table, view, or synonym..
`object_name`	The name of the table, view, or synonym to which the policy is dropped.
`namespace`	The namespace of the driving context.
`attribute`	The attribute of the driving context.

ENABLE__GROUPED_POLICY Procedure

This procedure enables or disables a row-level group security policy.

Syntax

DBMS_RLS.ENABLE_GROUPED_POLICY (
   object_schema   VARCHAR2,
   object_name     VARCHAR2,
   group_name      VARCHAR2,
   policy_name     VARCHAR2,
   enable          BOOLEAN  );

Parameters

Table 61-12 ENABLE_GROUPED_POLICY Procedure Parameters

Parameter	Description
`object_schema`	The schema containing the table, view, or synonym.
`object_name`	The name of the table, view, or synonym with which the policy is associated.
`group_name`	The name of the group of the policy.
`policy_name`	The name of the policy to be enabled or disabled.
`enable`	`TRUE` enables the policy; `FALSE` disables the policy.

Usage Notes

The procedure causes the current transaction, if any, to commit before the operation is carried out.
A commit is performed at the end of the operation.
A policy is enabled when it is created.

REFRESH_GROUPED_POLICY Procedure

This procedure reparses the SQL statements associated with a refreshed policy.

Syntax

DBMS_RLS.REFRESH_GROUPED_POLICY (
   object_schema   VARCHAR2,
   object_name     VARCHAR2,
   group_name      VARCHAR2,
   policy_name     VARCHAR2 );

Parameters

Table 61-13 REFRESH_GROUPED_POLICY Procedure Parameters

Parameter	Description
`object_schema`	The schema containing the table, view, or synonym..
`object_name`	The name of the table, view, or synonym with which the policy is associated.
`group_name`	The name of the group of the policy.
`policy_name`	The name of the policy.

Usage Notes

This procedure causes all the cached statements associated with the policy to be reparsed. This guarantees that the latest change to the policy has immediate effect after the procedure is executed.
The procedure causes the current transaction, if any, to commit before the operation is carried out.
A commit is performed at the end of the operation.
The procedure returns an error if it tries to refresh a disabled policy.