Skip Headers

PL/SQL Packages and Types Reference
10g Release 1 (10.1)

Part Number B10802-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

79
DBMS_RLS

The DBMS_RLS package contains the fine-grained access control administrative interface, which is used to implement Virtual Private Database (VPD). DBMS_RLS is available with the Enterprise Edition only.

See Also:

Oracle Database Security Guide for usage information on DBMS_RLS.

This chapter contains the following topics:


Using DBMS_RLS


Overview

The functionality to support fine-grained access control is based on dynamic predicates, where security rules are not embedded in views, but are acquired at the statement parse time, when the base table or view is referenced in a DML statement.

A dynamic predicate for a table, view, or synonym is generated by a PL/SQL function, which is associated with a security policy through a PL/SQL interface. For example:

DBMS_RLS.ADD_POLICY (
   'hr', 'employees', 'emp_policy', 'hr', 'emp_sec', 'select');

Whenever the EMPLOYEES table, under the HR schema, is referenced in a query or subquery (SELECT), the server calls the EMP_SEC function (under the HR schema). This function returns a predicate specific to the current user for the EMP_POLICY policy. The policy function may generate the predicates based on the session environment variables available during the function call. These variables usually appear in the form of application contexts. The policy can specify any combination of security-relevant columns and of these statement types: INDEX, SELECT, INSERT, UPDATE, or DELETE.

The server then produces a transient view with the text:

SELECT * FROM hr.employees WHERE P1

Here, P1 (for example, where SAL > 10000, or even a subquery) is the predicate returned from the EMP_SEC function. The server treats the EMPLOYEES table as a view and does the view expansion just like the ordinary view, except that the view text is taken from the transient view instead of the data dictionary.

If the predicate contains subqueries, then the owner (definer) of the policy function is used to resolve objects within the subqueries and checks security for those objects. In other words, users who have access privilege to the policy-protected objects do not need to know anything about the policy. They do not need to be granted object privileges for any underlying security policy. Furthermore, the users do not require EXECUTE privilege on the policy function, because the server makes the call with the function definer's right.


Note:

The transient view can preserve the updatability of the parent object because it is derived from a single table or view with predicate only; that is, no JOIN, ORDER BY, GROUP BY, and so on.


DBMS_RLS also provides the interface to drop or enable security policies. For example, you can drop or enable the EMP_POLICY with the following PL/SQL statements:

DBMS_RLS.DROP_POLICY('hr', 'employees', 'emp_policy'); 
DBMS_RLS.ENABLE_POLICY('hr', 'employees', 'emp_policy', FALSE);

Security Model

A security check is performed when the transient view is created with a subquery. The schema owning the policy function, which generates the dynamic predicate, is the transient view's definer for security check and object lookup.


Operational Notes

The DBMS_RLS procedures cause current DML transactions, if any, to commit before the operation. However, the procedures do not cause a commit first if they are inside a DDL event trigger. With DDL transactions, the DBMS_RLS procedures are part of the DDL transaction.

For example, you may create a trigger for CREATE TABLE. Inside the trigger, you may add a column through ALTER TABLE, and you can add a policy through DBMS_RLS. All these operations are in the same transaction as CREATE TABLE, even though each one is a DDL statement. The CREATE TABLE succeeds only if the trigger is completed successfully.

Views of current cursors and corresponding predicates are available from v$vpd_policies.

A synonym can reference only a view or a table.


Summary of DBMS_RLS Subprograms

Table 79-1  DBMS_RLS Subprograms Package 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

DISABLE_GROUPED_POLICY Procedure

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:

Operational 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,
   policy_type              IN BINARY_INTEGER NULL,
   long_predicate           IN BOOLEAN  FALSE,
   sec_relevant_cols        IN VARCHAR2,
   sec_relevant_cols_opt    IN BINARY_INTEGER NULL);

Parameters

Table 79-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 INDEX, SELECT, INSERT, UPDATE, or DELETE. The default is to apply to all of these types except INDEX.

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.

policy_type

Default is NULL, which means policy_type is decided by the value of static_policy. The available policy types are listed in Table 79-3. Specifying any of these policy types overrides the value of static_policy.

long_predicate

Default is FALSE, which means the policy function can return a predicate with a length of up to 4000 bytes. TRUE means the predicate text string length can be up to 32K bytes.Policies existing prior to the availability of this parameter retain a 32K limit.

sec_relevant_cols

Enables column-level Virtual Private Database (VPD), which enforces security policies when a column containing sensitive information is referenced in a query. Applies to tables and views, but not to synonyms. Specify a list of comma- or space-separated valid column names of the policy-protected object. The policy is enforced only if a specified column is referenced (or, for an abstract datatype column, its attributes are referenced) in the user SQL statement or its underlying view definition. Default is all the user-defined columns for the object.

sec_relevant_cols_opt

Use with sec_relevant_cols to display all rows for column-level VPD filtered queries (SELECT only), but where sensitive columns appear as NULL. Default is set to NULL, which allows the filtering defined with sec_relevant_cols to take effect. Set to dbms_rls.ALL_ROWS to display all rows, but with sensitive column values, which are filtered by sec_relevant_cols, displayed as NULL. See "Usage Notes" for restrictions and additional information about this option.

Table 79-3   DBMS_RLS.ADD_POLICY Policy Types
Policy Type Description

STATIC

Predicate is assumed to be the same regardless of the runtime environment. Static policy functions are executed once and then cached in SGA. Statements accessing the same object do not reexecute the policy function. However, each execution of the same cursor could produce a different row set even for the same predicate because the predicate may filter the data differently based on attributes such as SYS_CONTEXT or SYSDATE. Applies to only one object.

SHARED_STATIC

Same as STATIC except that the server first looks for a cached predicate generated by the same policy function of the same policy type. Shared across multiple objects.

CONTEXT_SENSITIVE

Server re-evaluates the policy function at statement execution time if it detects context changes since the last use of the cursor. For session pooling where multiple clients share a database session, the middle tier must reset context during client switches. Note that the server does not cache the value returned by the function for this policy type; it always executes the policy function on statement parsing. Applies to only one object.

SHARED_CONTEXT_SENSITIVE

Same as CONTEXT_SENSITIVE except that the server first looks for a cached predicate generated by the same policy function of the same policy type within the same database session. If the predicate is found in the session memory, the policy function is not reexecuted and the cached value is valid until session private application context changes occur. Shared across multiple objects.

DYNAMIC

The default policy type. Server assumes the predicate may be affected by any system or session environment at any time, and so always reexecutes the policy function on each statement parsing or execution. Applies to only one object.

Usage Notes

Examples

As the first of two examples, the following creates a policy that applies to the hr.employee table. This is a column-level VPD policy that will be enforced only if a SELECT or an INDEX statement refers to the salary, birthdate, or SSN columns of the table explicitly, or implicitly through a view. It is also a CONTEXT_SENSITIVE policy, so the server will invoke the policy function hr.hrfun at parse time. During execution, it will only invoke the function if there has been any session private context change since the last use of the statement cursor. The predicate generated by the policy function must not exceed 4000 bytes, the default length limit, since the long_predicate parameter is omitted from the call.

BEGIN
dbms_rls.add_policy(object_schema => 'hr',

object_name => 'employee',
policy_name => 'hr_policy',
function_schema =>'hr',
policy_function => 'hrfun',
statement_types =>'select,index',
policy_type => dbms_rls.CONTEXT_SENSITIVE,
sec_relevant_cols=>'salary,birthdate,ssn');
END; /

As the second example, the following command creates another policy that applies to the same object for hosting, so users can access only data based on their subscriber ID. Since it is defined as a SHARED_STATIC policy type, the server will first try to find the predicate in the SGA cache. The server will only invoke the policy function, subfun, if that search fails.

BEGIN
dbms_rls.add_policy(object_schema => 'hr',

object_name => 'employee',
policy_name => 'hosting_policy',
function_schema =>'hr',
policy_function => 'subfun',
policy_type => dbms_rls.SHARED_STATIC);
END; /

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:

Operational 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 79-4  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:

Operational 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 79-5  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.

Usage Notes

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:

Operational 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 79-6  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 79-7  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   IN BOOLEAN  FALSE,
   policy_type     IN BINARY_INTEGER NULL,
   long_predicate  IN BOOLEAN  FALSE,
   sec_relevant_cols IN VARCHAR2);

Parameters

Table 79-8  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

Statement types to which the policy applies. It can be any combination of INDEX, SELECT, INSERT, UPDATE, or DELETE. The default is to apply to all of these types except INDEX.

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.

policy_type

Default is NULL, which means policy_type is decided by the value of static_policy. The available policy types are listed in Table 79-3. Specifying any of these policy types overrides the value of static_policy.

long_predicate

Default is FALSE, which means the policy function can return a predicate with a length of up to 4000 bytes. TRUE means the predicate text string length can be up to 32K bytes.Policies existing prior to the availability of this parameter retain a 32K limit.

sec_relevant_cols

Enables column-level Virtual Private Database (VPD), which enforces security policies when a column containing sensitive information is referenced in a query. Applies to tables and views, but not to synonyms. Specify a list of comma- or space-separated valid column names of the policy-protected object. The policy is enforced only if a specified column is referenced (or, for an abstract datatype column, its attributes are referenced) in the user SQL statement or its underlying view definition. Default is all the user-defined columns for the object.

sec_relevant_cols_opt

Use with sec_relevant_cols to display all rows for column-level VPD filtered queries (SELECT only), but where sensitive columns appear as NULL. Default is set to NULL, which allows the filtering defined with sec_relevant_cols to take effect. Set to dbms_rls.ALL_ROWS to display all rows, but with sensitive column values, which are filtered by sec_relevant_cols, displayed as NULL. See "Usage Notes" for restrictions and additional information about this option.

Usage Notes


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 79-9  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:


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 79-10  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:


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 79-11  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 79-12  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 79-13  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


DISABLE_GROUPED_POLICY Procedure

This procedure disables a row-level group security policy.

Syntax

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

Parameters

Table 79-14 E NABLE_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.

Usage Notes


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 79-15  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