56
DBMS_RLS

The DBMS_RLS package contains the fine-grained access control administrative interface. DBMS_RLS is available with the Enterprise Edition only.

See Also:

Oracle9i Application Developer's Guide - Fundamentals for a fuller discussion and more usage information on DBMS_RLS.

This chapter discusses the following topics:

Dynamic Predicates

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 or view 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 (
   'scott', 'emp', 'emp_policy', 'secusr', 'emp_sec', 'select');

Whenever EMP table, under SCOTT schema, is referenced in a query or subquery (SELECT), the server calls the EMP_SEC function (under SECUSR schema). This returns a predicate specific to the current user for the EMP_POLICY policy. The policy function may generate the predicates based on whatever session environment variables are available during the function call. These variables usually appear in the form of application contexts.

The server then produces a transient view with the text:

SELECT * FROM scott.emp WHERE P1

Here, P1 (e.g., SAL > 10000, or even a subquery) is the predicate returned from the EMP_SEC function. The server treats the EMP 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 also 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 and enable/disable security policies. For example, you can drop or disable the EMP_POLICY with the following PL/SQL statements:

DBMS_RLS.DROP_POLICY('scott', 'emp', 'emp_policy'); 
DBMS_RLS.ENABLE_POLICY('scott', 'emp', 'emp_policy', FALSE)

Security

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

Usage 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.

Summary of DBMS_RLS Subprograms

Table 56-1 DBMS_RLS Subprograms

Subprogram	Description
"ADD_POLICY Procedure"	Adds a fine-grained access control policy to a table or view.
"DROP_POLICY Procedure"	Drops a fine-grained access control policy from a table or view.
"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.

56 DBMS_RLS