Managing Row Level Security in Oracle8i
By Michael R. Ault, TUSC

Introduction

New in Oracle8i is the concept of row level access restriction.  For years DBAs have requested some form of conditional grant where access to specific rows can be easily restricted or granted based on user or group membership.  Oracle has finally given DBAs the functionality of conditional grants in the form of row level security.  The DBMS_RLS package is only available with the Enterprise edition of Oracle8i.

Fine Grained Access Control (Row Level Security)

The concept of row level security uses dynamic predicates.  Dynamic predicates are created by a function that is executed whenever a specific table of view is accessed.  The predicate returned by the function creates what is known as a transitive view.  The transient view can preserve the updatability of the parent join object because it is derived from a single table or view with predicate only; i.e., no JOIN, ORDER BY, GROUP BY, etc.

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

Row level security is managed using a combination of Oracle8i contexts, stored procedures, database level triggers and the DBMS_RLS package.  The entire row level security concept is tightly bound to the concept of a database policy.  Generally speaking a policy will require:

1. a context
2. a procedure to implement the context
3. a database (Oracle8i) level trigger that monitors login activity
4. a security procedure to implement the policy
5. a policy declaration

Row level security control depends on certain environment variables, known as contexts, to be set.  The DBMS_CONTEXT package is used to set the various context variables used by the RLS policy.  Figure 1 shows a flowchart of how to implement a simple security policy.

Figure 1

As you can see the process is not very complex.  Let's examine each step and see what is really involved.

Step 1

In the first step a context package or procedure is developed which is used by a login trigger to set each user's context variables.  This step is vital in that if the context variables aren't set it is many times more difficult to implement row level security using the DBMS_RLS package.  The package or procedure used to set the context variables should resemble the one shown below:

CREATE OR REPLACE PACKAGE graphics_app AUTHID DEFINER AS

PROCEDURE get_graphics_function(usern IN VARCHAR2,

graphics_function OUT VARCHAR2);

PROCEDURE set_graphics_context(usern IN VARCHAR2);

END;

/

SET ARRAYSIZE 1

SHO ERR

CREATE OR REPLACE PACKAGE BODY graphics_app AS

graphics_user VARCHAR2(32);

graphics_function VARCHAR2(32);

PROCEDURE get_graphics_function(usern IN VARCHAR2, graphics_function OUT VARCHAR2) IS

BEGIN

SELECT user_function INTO graphics_function FROM graphics_dba.graphics_users

  WHERE username=usern;
  
END get_graphics_function;

PROCEDURE set_graphics_context(usern IN VARCHAR2) IS

BEGIN

  graphics_app.get_graphics_function(usern,graphics_function);

  DBMS_SESSION.SET_CONTEXT('GRAPHICS_SEC','GRAPHICS_FUNCTION',graphics_function);

  DBMS_SESSION.SET_CONTEXT('GRAPHICS_SEC','GRAPHICS_USER',usern);

END set_graphics_context;

END graphics_app;

/

SHOW ERR

In the above package are tow procedures, one that retrieves a user's graphics function from a pre-built and populated table (GET_GRAPHICS_FUNCTION) and the other which is used to set the users context variables based on using the DBMS_SESSION.SET_CONTEXT procedure provided by Oracle (SET_GRAPHICS_CONTEXT).

Step 2

Of course the procedures above wouldn't be much use without a trigger that could run the procedure whenever a user logged on the system.  Until Oracle8i this would have involved setting auditing on for login, moving the aud$ table from SYS ownership and setting the ownership to another user, resetting all of the synonyms point to aud$ and then building an on-insert trigger to perform the actual work.  In Oracle8i all we have to do is build a database level trigger similar to the one shown below.  An important point to notice is that the trigger includes a WHEN OTHERS construct to handle exceptions. Without the WHEN OTHERS exception a database login trigger that generates an error would result in the user not being able to log on to the database.  Only the INTERNAL or SYS users can log in if there is an error in the logon trigger.

CREATE OR REPLACE TRIGGER set_graphics_context AFTER LOGON ON DATABASE

DECLARE

 username VARCHAR2(30);

BEGIN

 username:=SYS_CONTEXT('USERENV','SESSION_USER');

 graphics_app.set_graphics_context(username);

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

/

In the trigger shown above, the logon event causes the user's session user name to be placed into an internal variable called username.  The username variable is then passed into the graphics_app.set_graphics_context procedure from step 1 which then sets the users context variables.

Step 3

Once we have an operating context setting package and a database login trigger we can proceed to create the required context checking package and the context it checks.  Below is an example context checking package.

CREATE OR REPLACE PACKAGE graphics_sec AUTHID DEFINER AS

FUNCTION graphics_check(obj_schema VARCHAR2, obj_name VARCHAR2)

 RETURN VARCHAR2;

PRAGMA RESTRICT_REFERENCES(GRAPHICS_CHECK,WNDS);

END;

/

SET ARRAYSIZE 1

SHOW ERR

CREATE OR REPLACE PACKAGE BODY graphics_sec AS

FUNCTION graphics_check(obj_schema VARCHAR2, obj_name VARCHAR2)

 RETURN VARCHAR2 AS

d_predicate VARCHAR2(2000);

user_context VARCHAR2(32);

BEGIN

   user_context:=SYS_CONTEXT('graphics_sec','graphics_function');

   IF user_context = 'ADMIN' THEN

     d_predicate IS NULL;

dbms_output.put_line(d_predicate);

   ELSIF user_context = 'GENERAL USER' THEN

     d_predicate:=' graphics_usage='||chr(39)||'UNRESTRICTED'||chr(39);

dbms_output.put_line(d_predicate);

   ELSIF user_context='DEVELOPER' THEN

     d_predicate IS NULL;

dbms_output.put_line(d_predicate);

   ELSIF user_context IS NULL THEN

     d_predicate:='1=2';

   END IF;

   RETURN d_predicate;

END graphics_check;

END;

/

The entire purpose of the package above is to return a d_predicate value based on a users graphics_function context value.  The d_predicate value is appended to whatever WHERE clause is included with their command, or is appended as a WHERE clause whenever there is no pre-existing clause.  In the above package the function graphics_check looks at the context values set by the logon trigger and based the value of the user_context (ADMIN, GENERAL USER or DEVELOPER) sets the value of d_predicate.  By setting the predicate to NULL it effectively mitigates the restriction.  The user of the '1=2' predicate prohibits selection from the table.  The pragma restriction clause is required for the function to be available.

Step 4

The creation of our graphics security context is rather simple once we have finished the preliminary work, it boils down to one command:

CREATE OR REPLACE CONTEXT graphics_sec USING sys.graphics_app;

Step 5

The final step is to set the policy into the database.  This is done with the DBMS_RLS package using the procedure ADD_POLICY:

BEGIN

 dbms_rls.add_policy('GRAPHICS_DBA','INTERNAL_GRAPHICS','GRAPHICS_POLICY', 'GRAPHICS_DBA','GRAPHICS_SEC.GRAPHICS_CHECK','SELECT,INSERT,UPDATE,DELETE');

The above policy simply ties the components we previously defined into a coherent entity called GRAPHICS_POLICY and implements this policy against the table INTERNAL_GRAPHICS which is in the schema GRAPHICS_DBA.  The policy GRAPHICS_POLICY is owned by GRAPHICS_DBA and uses the procedure GRAPHICS_SEC.GRAPHICS_CHECK to verify users can perform SELECT, INSERT, UPDATE and DELETE operations.

The table graphics_users is required in the above example.  The table contains the username and their graphics function.

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 the 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 the following table.

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,

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,

Where:

• object_schema - schema containing the table/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,

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,

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 article was excerpted from the book: "Oracle8i Administration and Management", Michael R. Ault, John Wiley and Sons Publishers).

Michael Ault can be contacted through the web page http://www.tusc.com or via email at aultm@tusc.com.