Database Row Level Security - Part 2 - Oracle

In the first episode we briefly introduced Row Level Security. In this part I’m going to show you how to implement it in an Oracle 10g database.

Oracle 8i introduced a feature called VPD (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 user is supposed to see.

Important: implementing row level security using VPD requires each user to have an actual corresponding user object in database, not just a record in a table. If that's not feasible, then part 3 might be the way to go.

Here is the list of what we need to implement this:

1. An Application Context.
2. A procedure that sets 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 queries.

I explain it with the doctors and patients example in part 1.
Let’s assume the tables look something like the following picture (the relationship could be actually many-to-many, but I simplified it).
 Every time a doctor logs in to the system, a procedure is invoked which then sets the value of a variable in Application Context called, say, "logged_in_doc_id" to doctor's doc_id field queried from the Doctors table.

When a doctor queries the list of patients, another procedure filters the data. In this case, we simply add a WHERE clause to the query. Something like this: WHERE f_doc_id = logged_in_doc_id. This WHERE clause will be concatenated to the SELECT command and make it look like this (assuming his id in database is 1): SELECT * FROM PATIENTS WHERE f_doc_id = 1.

Here is the code:

1 & 2 –
As it can be seen, I’ve named the Application Context "NMSPC" and I’ve set the "logged_in_doc_id" variable to the value of "doc_id" field of the record that its "doc_name" field matches with the current logged in user’s name (USER). 

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;

EXEC_CONTEXT_SETUP trigger is called whenever a user (in this instance "Jim") logs in.

3 – We need 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 us to retrieve the value of a variable in a particular context.

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
   ('ALL_TABLES', 'Patients',
    'pat_policy',
    'JIM', 'CONTEXT_WHERE_MAKER.WHERE_MAKER',
    'SELECT');
End;

Please note that "ALL_TABLES" is our schema under which all tables are created and all users have access to.

Database Row Level Security - Part 1

In this 3 parts series I intend to explain what Row Level Security in database management is and how to implement it in Oracle and SQL Server.

In almost all database management systems the level at which information is controlled extends only to a certain level of granularity. In some scenarios, however, there is a requirement to control access at a more granular level. A list of patients, for example, may be stored in a single table. Any doctor, however, is only be permitted to view his own patients' records. Another example can be those applications that implement feature access control. That is, users' credentials and their access to application's features and modules are stored in database and used by application.
In such cases, merely issuing a GRANT/DENY SELECT command on the table will not meet the requirement. One solution is to implement this requirement at the application level - in Business Logic Layer if application is using 3 tier architecture - to filter rows of data. But what if someone bypasses the code and gain direct access to data? The presentation layer creates the illusion of security. What actually needs to be done is to automatically filter queries at the database level. In the doctors and patients example, all users might have access to the patient table, but SELECT * FROM Patient only returns the rows that a user should see; wherever that query is executed from.

In part 2, I’ll explain how RLS can be implemented in Oracle 10g utilizing auditing features.