Oracle application security using Oracle's Row level security

As more and more application are being developed to support enterprise needs, security is becoming more and more demanding subject for research and innovation. Oracle's DBMS_RLS in collaboration with Application Context can be used for implementing fine grained access security. In this article we will see the step by step process to implement fine grained access using DBMS_RLS package.

DBMS_RLS package is used to add a dynamic predicate based on user's environment attributes set in its application context.
An application context enables application developers to create, set and access application attributes by acting as a lookup storage from where user's environment attributes can be accessed. Oracle has predefined application context, USERENV which can also be used for this purpose.

Here we will see steps to create custom application context for meeting requirements that cannot be fulfilled using USERENV application context.

Consider an application which has an employee table and orders table in OE schema. Employee table stores all the employee imformation along with the ORG_ID which points to the organisation in which the employee works in. We need to implement a RLS on orders table which allows and employee to see only those orders which are being ordered from their organization. Orders table also has a column called BILL_FROM_ORG_ID which stores the organization from which the order has been placed.

To create an application context ORD_SEC we need to create a package which can be used to set the attributes of the context. This package is called the trusted package.

1) Create a trusted package.

CREATE OR REPLACE PACKAGE app_security
IS
    PROCEDURE set_context_attribute;
END app_security;

CREATE OR REPLACE PACKAGE BODY app_security
IS
    PROCEDURE set_context_attribute IS
        v_org_id NUMBER;
    BEGIN
        SELECT ORG_ID INTO v_org_id from employee where emp_name=SYS_CONTEXT('USERENV','SESSION_USER');
        DBMS_SESSION.SET_CONTEXT('ORD_SEC','ORG_ID',v_org_id);
    END set_context_attribute;

END app_security;

2) After creation of the trusted package we need to create application context ORD_SEC which should be connected to the package app_security.

CREATE CONTEXT ORD_SEC USING app_security;

3) Now we need to create a logon trigger that should invoke the trusted package procedure app_security.set_context_attribute to set the attribute in the context so that it can be used by the application for further processing and security implementation.

4) Now when the custom application context has been created which sets the custom context attributes when ever user logs in to the database, we need to create a security policy package that will return the predicate to be used with statements assigned in the policy.

CREATE OR REPLACE PACKAGE sec_policy IS
FUNCTION get_policy_predicate RETURN VARCHAR2;
END sec_policy;

CREATE OR REPLACE PACKAGE BODY IS

FUNCTION get_policy_predicate RETURN VARCHAR2
v_org_id number;
v_predicate VARCHAR2(200);
IS
v_predicate:='BILL_TO_ORG_ID='||SYS_CONTEXT('ORD_SEC','ORG_ID');
RETURN v_predicate;

END get_policy_predicate;


5) After the creation of policy function, we need to create a policy that is used to add predicate to any database object.For this purpose we use DBMS_RLS predefined package.

EXEC DBMS_RLS.ADD_POLICY('OE','ORDERS','ORD_POLICY','SYS','SEC_POLICY.GET_POLICY_PREDICATE','SELECT');

The statement adds a policy ORD_POLICY to ORDERS table in OE schema using package procedure SEC_POLICY.GET_POLICY_PREDICATE in SYS schema for select statements.

Now whenever a user queries table ORDERS as,

SELECT * FROM ORDERS;

Then Oracle server will add the predicate WHERE BILL_TO_ORG_ID=<<The organization ID of the user>>.

You can try creating more complex security using DBMS_RLS package.