Database Row Level Security - Part 2 - Oracle

Hello guys,

As I promised, in the second part I’m going to show you how to implement Record Level Security (RLS) in Oracle and in the next one we will talk about SQL Server’s way to do such a thing.
Before I start, I must thank all my friends who sent me e-mails with ideas and suggestions. But I want to ask you guys to post them on the blog next time as comments so that the others will know what you think.
Ok, back to the main road.
Oracle 8i introduced a feature called VPD (which stands for Virtual Private Database); also known as Fine Grained Access Control that provides powerful row-level security capabilities.
VPD works by modifying the SQL commands to present a partial view of data to the users based on a set of predefined criteria. During runtime, WHERE clauses are appended to all the queries to filter only those rows the user is supposed to see.

Here is the list of what we need:

1. An Application Context
2. A procedure that set a variable in the above mentioned context and is called when user login to the database
3. A secured procedure that makes the WHERE clause using the variable that has been set in the context
4. A RLS POLICY that put all these together to tell database how to filter the data

You might have already figured out what is going to happen. For those that haven’t yet, I explain it with a very simple example which also refers back to the doctors and patients tables in the previous installment of this series.
Let’s assume those two tables look like what is in the following picture:

And also a doctor can login to our database using a username named “Jim”. Every time he logs in to the system, a procedure comes up and sets a variable, let’s say “logged_in_doc_id” in an Application Context to the doctor’s doc_id which in this case is Jim’s id and has been queried from the Doctors table.
There is a very important note here that I have to mention to not make you confused. “Jim” is the doctor’s username that has been created in the database and also matches to the doc_name field in the Doctors table. We must not forget that implementing RLS using VPD needs users to have the actual user object in database, not only just a record in a table.

When he tries to run a query to get the list of patients, another procedure is ready to filter the data. What it does is depending to what we have written in there. I simply say that it just creates a WHERE clause like this:
“WHERE f_doc_id = logged_in_doc_id”
This WHERE clause will be concatenated to the SELECT command that he has written and executed by Jim and so it becomes for example SELECT * FROM PATIENTS WHERE f_doc_id = 1, if we assume Jim’s doc_id is 1.

Here is the code:

1 & 2 –
As you can see, I’ve called the Application Context “NMSPC” and I’ve set the logged_in_doc_id variable to the doc_id field of the record that its doc_name field matches to the current logged in user’s name (USER). Simply I’ve set the logged_in_doc_id variable to the current doctor’s ID.

CREATE OR REPLACE PACKAGE CONTEXT_SETUP AS
   PROCEDURE SET_SESSION_VAR;
END;

CREATE OR REPLACE PACKAGE BODY CONTEXT_SETUP AS
   PROCEDURE SET_SESSION_VAR IS
      t_id NUMBER(5);
   BEGIN
      SELECT doc_id INTO t_id
      FROM DOCTORS
      WHERE UPPER(doc_name) = UPPER(USER);
      DBMS_SESSION.SET_CONTEXT
           (‘NMSPC’, ‘logged_in_doc_id’, t_id);
   END;
END;

CREATE CONTEXT NMSPC USING CONTEXT_SETUP;

CREATE OR REPLACE TRIGGER EXEC_CONTEXT_SETUP
AFTER LOGON ON JIM.SCHEMABEGIN
   CONTEXT_SETUP.SET_SESSION_VAR();
END;

The last command is a logon trigger for JIM to call SET_SESSION_VAR procedure whenever JIM logs in.

3 –
We got to create a procedure that creates the predicate, the WHERE clause:

CREATE OR REPLCAE PACKAGE CONTEXT_WHERE_MAKER AS
   FUNCTION WHERE_MAKER(obj_schema VARCHAR2,
                        obj_name VARCHAR2)
   RETURN VARCHAR2;
END;

CREATE OR REPLCAE PACKAGE BODY CONTEXT_WHERE_MAKER AS
   FUNCTION WHERE_MAKER(obj_schema VARCHAR2,
                        obj_name VARCHAR2)
   RETURN VARCHAR2 IS
   BEGIN
     IF UPPER(obj_name) = ‘PATIENTS’
     BEGIN
      RETURN ‘f_doc_id = SYS_CONTEXT(“NMSPC”,
                              “logged_in_doc_id”)‘;
     END;
   END;
END;

SYS_CONTEXT is a function that helps you to retrieve value of a variable in a particular context.
The function’s signature is also considerable. It has to have two VARCHAR2 arguments. As this function might be used to filter other tables’ data as well, we need to know which table is that.

4 –
The last thing we are going to create is a RLS POLICY that put all these together to make them meaningful.

BEGIN
   DBMS_RLS.ADD_POLICY
   ('JIM', 'Patients',
    'pat_policy',
    'JIM', 'CONTEXT_WHERE_MAKER.WHERE_MAKER',
    'SELECT');
End;

You have to note that as our example is very simple, I assumed the Doctors Table is created under JIM’s schema. That’s why the first argument of the ADD_POLICY is JIM. But in fact it better be under a separate schema that all users including JIM have access to it.
Ok, that was it. See the SQL Server fans next time.
By the way, the blogspot's HTML editor really sucks!!!