Oracle Concepts - Policy Usage Oracle Tips by Burleson Consulting

Policy Usage

Policy usage is controlled internally by the Oracle system and adheres to the following usage guidelines:

1. SYS user is not restricted by any security policy.

2. The policy functions which generate dynamic predicates are called by the server. The following is the required structure for the function:

FUNCTION policy_function (object_schema IN VARCHAR2, object_name VARCHAR2)
             RETURN VARCHAR2

Where:

       object_schema is the schema owning the table of view.
       object_name is the name of table of view that the policy will apply.

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

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

5. Dynamic predicates generated out of different policies for the same object have the combined effect of a conjunction (ANDed) of all the predicates.

6. The security check and object lookup are performed against the owner of the policy function for objects in the subqueries of the dynamic predicates.

7. If the function returns a zero length predicate, then it is interpreted as no restriction being applied to the current user for the  policy.

8. 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>".

9. The checking of the validity of the function is done at runtime for ease of installation and other dependency issues import/export.

DBMS_RLS Package

The entire concept of row level security is based on the use of policies stored in the database. The only way to store policies in the database is to use the DBMS_RLS package.

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.

The DBMS_RLS package has the procedures shown in Table 1.

Table 1     Procedure in DBMS_RLS Package

The syntax for calling the DBMS_RLS procedures are shown in the next sections.

Syntax for the ADD_POLICY Procedure:

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

Where:

object_schema - schema owning the table/view, current user if NULL
object_name - name of table or view
policy_name - name of policy to be added
function_schema - schema of the policy function, current user if NULL
policy_function - function to generate predicates for this policy
statement_types - statement type that the policy apply, default is any
update_check - policy checked against updated or inserted value?
enable - policy is enabled?

Syntax for the DROP_POLICY Procedure:

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

Where:

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.

Syntax for the REFRESH_POLICY Procedure:

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

Where:

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.

Syntax for the ENABLE_POLICY Procedure:

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

Where:

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. 

Through the use of the above procedures DBAs and developers can easily manage policies.

Summary

Oracle has given DBAs and developers a powerful new tool to manage row level security. This new tool is a combination of contexts, triggers and packages and a new package named DBMS_RLS through which security policies are implemented.

Through the proper use of policies, contexts, packages and database level triggers row level security can be easily integrated in Oracle8i applications.

(The above lesson was excerpted from the book: "Oracle8i Administration and Management", Michael R. Ault, John Wiley and Sons Publishers).

This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It’s only $19.95 when you buy it directly from the publisher here.