Row Level Security Tips Oracle Tips by Burleson Consulting

Restricting Access by Rows in Oracle

New to Oracle8i is the concept of row level access restriction. For years DBAs have requested some form of conditional grant were 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. In a data warehouse there may be data that is restricted in nature, the pay for a particular department, the locations of specific assets, etc. The new row level security, since it is restricted at the database level, prohibits access to restricted rows even when ad hoc tools are used to query the warehouse.

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, know 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:   Steps to Implement a security policy

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

In the first step a context package or procedure is developed which will then be used by a login trigger to set each users 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 in figure 2.

Figure 2:   Example Context Setting Procedure

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 package in figure 2 are two procedures, one that retrieves a users 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).

Of course the procedures in figure 2 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 pointing 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 in figure 3.

Figure 3:   Example Database 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;
/

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. Figure 4 shows an example context checking package.

Figure 4: Example Context 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:=' 1=1';
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:=' 1=1';
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;
/
SHOW ERR

The entire purpose of the package in figure 4 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.

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;

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

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

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.