Oracle Application Context and Fine-Grained Access Control - Migration to SQL Server

You can use application context to enforce fine-grained access control in Oracle.

Context is a set of session-based attributes that can be only set by the package specified in CREATE CONTEXT statement.

   CREATE CONTEXT app1_ctx USING app1_ctx_package;

In this example, only procedures and functions of app1_ctx_package package are allowed to set or reset the attributes of the app1_ctx context.

Only the package specified in CREATE CONTEXT statement can change attributes of the context using DBMS_SESSION.SET_CONTEXT procedure:

    CREATE OR REPLACE PACKAGE app1_ctx_package IS
      PROCEDURE set_empno (empno NUMBER);
    END;
   /
   CREATE OR REPLACE PACKAGE BODY app1_ctx_package IS
     PROCEDURE set_empno (empno NUMBER) IS
     BEGIN
       DBMS_SESSION.SET_CONTEXT('app1_ctx', 'empno', empno);
     END;
   END;
   /

Note. You have to create the package (or just its specification) before you create the context.

An application can initialize context by calling the package procedure i.e

   CALL app1_ctx_package.set_empno(11);

Assume, you have the following data:

   CREATE TABLE orders (price NUMBER(10,2), empno NUMBER(5));
 
   INSERT INTO orders VALUES (295.00, 10);
   INSERT INTO orders VALUES (99.00, 11);
   INSERT INTO orders VALUES (125.00, 11);

Then you can restrict access to this table, so an employee can see only own data using SYS_CONTEXT function:

   SELECT * FROM orders WHERE empno = SYS_CONTEXT('app1_ctx', 'empno');

Result:

This example shows how application can restrict access to data by adding WHERE condition with SYS_CONTEXT function.

In Oracle, you can use DBMS_RLS.ADD_POLICY procedure to add this predicate automatically so users will not be able to bypass the restrictions using SQL query tools (SQL*Plus i.e.)

Another option is to create a view or stored procedure, and revoke SELECT privilege from users, so they are not allowed to access the tables directly.

SQL Server does not support context concepts, but you can develop a set of data structures and user-defined functions to emulate this functionality in SQL Server.

Since Oracle SYS_CONTEXT function is often used in queries (especially in views), and if you also want to have similar function (not stored procedure in SQL Server), you have to use a permanent table to store context information (SQL Server does not allow accessing temporary table within functions).

For example, the following table DDL can be used to store the context:

   CREATE TABLE t_syscontext
   (
     namespace VARCHAR(50) NOT NULL,
     parameter VARCHAR(50) NOT NULL,
     value VARCHAR(255),
     spid INTEGER NOT NULL DEFAULT @@SPID,
     created DATETIME DEFAULT GETDATE(),
     updated DATETIME DEFAULT GETDATE()  
   )

spid is required to separate the context between different sessions.

created and updated can be used to clean unused data or data from old sessions (this would happen automatically if we were able to use temporary tables). You can schedule a job to delete old rows.

You can use the following function to retrieve context information:

   CREATE FUNCTION SYS_CONTEXT(@namespace VARCHAR(50), @parameter VARCHAR(50))
    RETURNS VARCHAR(255)
   AS
   BEGIN
     DECLARE @value VARCHAR(255)
 
     SELECT @value = value FROM t_syscontext
	WHERE namespace = @namespace AND parameter = @parameter AND spid = @@spid
 
     RETURN @value
   END

Note. In SQL Server, you have to call a function with dbo. prefix:

   SELECT * FROM orders WHERE empno = dbo.SYS_CONTEXT('app1_ctx', 'empno');

You can use the following procedure to set context information:

    CREATE PROCEDURE SET_CONTEXT 
        (@namespace VARCHAR(50), @parameter VARCHAR(50), @value VARCHAR(255))
    AS
    BEGIN
      MERGE t_syscontext
       USING (SELECT @namespace, @parameter, @value) AS c (namespace, parameter, value)
             ON (t_syscontext.namespace = c.namespace AND t_syscontext.parameter = c.parameter 
                   AND t_syscontext.spid = @@spid)
      WHEN MATCHED THEN 
            UPDATE SET value = c.value, updated = GETDATE()
      WHEN NOT MATCHED THEN	
	    INSERT (namespace, parameter, value, spid) VALUES (c.namespace, c.parameter, c.value, @@spid);
   END

Note. MERGE statement is available since SQL Server 2008 only.

Then call this procedure to set context information as follows:

    SET_CONTEXT 'app1_ctx', 'empno', 11

After the call, the query SELECT * FROM t_syscontext returns:

Let's update the empno:

    SET_CONTEXT 'app1_ctx', 'empno', 13

t_syscontext now contains:

Let's set another parameter city:

    SET_CONTEXT 'app1_ctx', 'city', 'New York'

t_syscontext now contains 2 rows:

• Oracle White Papers, Articles and Reference