124 DBMS_RLS

Using DBMS_RLS

• Overview

• Security Model

• Operational Notes

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.

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

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

ADD_GROUPED_POLICY Procedure

This procedure adds a policy associated with a policy group.

Syntax

DBMS_RLS.ADD_GROUPED_POLICY(
   object_schema         IN VARCHAR2 NULL,
   object_name           IN VARCHAR2,
   policy_group          IN VARCHAR2 'SYS_DEFAULT',
   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           BOOLEAN  FALSE,
   sec_relevant_cols     IN VARCHAR2,
   sec_relevant_cols_opt IN BINARY_INTEGER NULL);

Parameters

Usage Notes

• 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 by using the CREATE_POLICY_GROUP Procedure.

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

• If no object_schema is specified, the current user's schema is assumed.

• If no function_schema is specified, the current user's schema is assumed.

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.

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 NULL,
   sec_relevant_cols_opt    IN BINARY_INTEGER NULL);

Parameters

Usage Notes

• SYS is free of any security policy.

• If no object_schema is specified, the current user's schema is assumed.

• If no function_schema is specified, the current user's schema is assumed.

• The policy functions 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 or view.
    --- object_name is the name of table, view, or synonym to which the policy applies. 

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

  See Also:

  The Oracle Database Advanced Application Developer's Guide has more details about the RESTRICT_REFERENCES pragma.

• Predicates generated from different VPD 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 tab where <predicate>"
   
  

• Validity of the function is checked at runtime for ease of installation and other dependency issues during import and export.

• Column-level VPD column masking behavior (specified with sec_relevant_cols_opt => dbms_rls.ALL_ROWS) is fundamentally different from all other VPD policies, which return only a subset of rows. Instead the column masking behavior returns all rows specified by the user's query, but the sensitive column values display as NULL. The restrictions for this option are as follows:

  • Only applies to SELECT statements

  • Unlike regular VPD predicates, the masking condition that is generated by the policy function must be a simple boolean expression.

  • If your application performs calculations, or does not expect NULL values, then you should use the default behavior of column-level VPD, which is specified with the sec_relevant_cols parameter.

  • If you use UPDATE AS SELECT with this option, then only the values in the columns you are allowed to see will be updated.

  • This option may prevent some rows from displaying. For example:

    select * from employees
    where salary = 10
    

    This query may not return rows if the salary column returns a NULL value because the column masking option has been set.

• When you add a VPD policy to a synonym, it causes all the dependent objects of the synonym, including policy functions that reference the synonym, to be marked INVALID.

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

ADD_POLICY_CONTEXT Procedure

This procedure adds the context for the active application.

Syntax

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

Parameters

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.

• If no object_schema is specified, the current user's schema is assumed.

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

CREATE_POLICY_GROUP Procedure

This procedure creates a policy group.

Syntax

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

Parameters

Usage Notes

The group must be unique for each table or view.

DELETE_POLICY_GROUP Procedure

This procedure deletes a policy group.

Syntax

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

Parameters

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.

DISABLE_GROUPED_POLICY Procedure

This procedure disables a row-level group security policy.

Syntax

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

Parameters

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 disabled when this procedure is executed or when the ENABLE_GROUPED_POLICY procedure is executed with "enable" set to FALSE.

DROP_GROUPED_POLICY Procedure

This procedure drops a policy associated with a policy group.

Syntax

DBMS_RLS.DROP_GROUPED_POLICY (
   object_schema   IN VARCHAR2 NULL,
   object_name     IN VARCHAR2,
   policy_group    IN VARCHAR2 'SYS_DEFAULT',
   policy_name     IN VARCHAR2); 

Parameters

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.

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

Usage Notes

• When you drop a VPD policy from a synonym, it causes all the dependent objects of the synonym, including policy functions that reference the synonym, to be marked INVALID.

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   IN VARCHAR2 NULL,
   object_name     IN VARCHAR2,
   namespace       IN VARCHAR2,
   attribute       IN VARCHAR2); 

Parameters

ENABLE_GROUPED_POLICY Procedure

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

Syntax

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

Parameters

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.

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.

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

Parameters

REFRESH_GROUPED_POLICY Procedure

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

Syntax

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

Parameters

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.

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.

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

Usage Notes

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