DBMS_RLS , 2 of 2

Summary of Subprograms

Table 44-1 DBMS_RLS Subprograms

Subprogram	Description
ADD_POLICY Procedure	Creates 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 re-parsed.
ENABLE_POLICY Procedure	Enables or disables a fine-grained access control policy.

ADD_POLICY Procedure

This procedure creates a fine-grained access control policy to a table or view.

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

Parameters

Table 44-2 ADD_POLICY Procedure Parameters

Parameter	Description
object_schema	Schema containing the table or view (logon user, if `NULL`).
object_name	Name of table or view 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 (logon user, 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 that the policy will apply. 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`

Usage Notes

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 of view that the policy will apply.

The maximum length of the predicate that the policy function can return is 2,000 bytes.

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

See Also:
The Oracle8i 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 table alias is required (e.g., 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 or view.

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 44-3 DROP_POLICY Procedure Parameters

Parameter	Description
object_schema	Schema containing the table or view (logon user if `NULL`).
object_name	Name of table or view.
policy_name	Name of policy to be dropped from the table or view.

REFRESH_POLICY Procedure

This procedure causes all the cached statements associated with the policy to be re-parsed. 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 44-4 REFRESH_POLICY Procedure Parameters

Parameter	Description
object_schema	Schema containing the table or view.
object_name	Name of table or view that the policy is associated with.
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 44-5 ENABLE_POLICY Procedure Parameters

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

Example

This example illustrates the necessary steps to enforce a fine-grained access control policy.

In an Oracle HR application, PER_PEOPLE is a view for the PER_ALL_PEOPLE table, and both objects are under APPS schema.

CREATE TABLE per_all_people 
            (person_id NUMBER(15), 
             last_name VARCHAR2(30), 
             emp_no VARCHAR2(15), ...);
CREATE VIEW per_people AS 
       SELECT * FROM per_all_people;

There should be a security policy that limits access to the PER_PEOPLE view based on the user's role in the company. The predicates for the policy can be generated by the SECURE_PERSON function in the HR_SECURITY package. The package is under schema APPS and contains functions to support all security policies related to the HR application. Also, all the application contexts are under the APPS_SEC namespace.

CREATE PACKAGE BODY hr_security IS
  FUNCTION secure_person(obj_schema VARCHAR2, obj_name VARCHAR2)
                     RETURN VARCHAR2 IS
      d_predicate VARCHAR2(2000);
  BEGIN
      -- for users with HR_ROLE set to EMP, map logon user name
      -- to employee id. FND_USER table stores relationship
      -- among database users, application users, 
      -- and people held in the HR person table.
      IF SYS_CONTEXT('apps_sec', 'hr_role') = 'EMP' THEN
        d_predicate = 'person_id IN 
                     (SELECT employee_id FROM apps.fnd_user 
                      WHERE user_name = SYS_CONTEXT(''userenv'', ''session_
user''))';
      -- for users with HR_ROLE set to MGR (manager), map
      -- security profile id to a list of employee id that 
      -- the user can access
      ELSE IF SYS_CONTEXT('apps_sec', 'hr_role') = 'MGR' THEN
        d_predicate = 'person_id IN
                     (SELECT ppl.employee_id FROM per_person_list ppl WHERE
                      ppl.security_profile_id = SYS_CONTEXT(''apps_sec'', 
''security_profile_id''))
                      OR EXISTS (SELECT NULL FROM apps.per security_profiles psp 
WHERE
                      SYS_CONTEXT(''apps_sec'', ''security_profile_id'') =
                      psp.security_profile_id AND psp.view_all_flag = ''Y''))';
      ELSE 
           d_predicate = '1=2';  -- deny access to other users, may use 
something like 'keycol=null'
      END IF;
      RETURN d_predicate;
     END secure_person; 
    END hr_security;

The next step is to associate a policy (here we call it PER_PEOPLE_SEC) for the PER_PEOPLE view to the HR_SECURITY.SECURE_PERSON function that generates the dynamic predicates:

DBMS_RLS.ADD_POLICY('apps', 'per_people', 'per_people_sec', 'apps'    
                    'hr_security.secure_person', 'select, update, delete');

Now, any SELECT, UPDATE, and DELETE statement with the PER_PEOPLE view involved will pick up one of the three predicates based on the value of the application context HR_ROLE.

Note:
The same security function that secured the PER_ALL_PEOPLE table can also be used to generate the dynamic predicates to secure the PER_ADDRESSES table, because they have the same policy to limit access to data.