Local Context

Within the USERENV namespace, both the Client Identifier and Client Info attributes can be set by the user or application. Because it’s not sufficient to capture all possible information in either or both of these, the database also supports the ability to define separate namespaces with additional attributes. These locally defined application contexts can then be used within any application for any reason.

While the values in the USERENV are automatically set by the database (except for the Client Identifier and Client Info values), the values in a local application context have to be set via a single PL/SQL program unit that is specified when you create the application context.

Creating an Application Context

To create an application context, the user needs the CREATE ANY CONTEXT system privilege. Issue the following SQL to create a context called “CTX_EX”.

sec_mgr@KNOX10g> CREATE CONTEXT ctx_ex USING sec_mgr.ctx_ex_mgr;  Context created.  

This creates an application context with a namespace of “CTX_EX”. The only program authorized to set and clear values within this application context is the “trusted” program CTX_ EX_MGR, which resides in the SEC_MGR schema. Other than calling it a trusted program, Oracle assigns no special name to the program that manages the values for an application context (CTX_ EX_MGR, in the previous example). Because it’s convenient to have a name, it’ll be referred to as the “namespace manager.”

Execute privileges on the CTX_EX_MGR program are required to set or clear—perform write operations on—the attributes in the CTX_EX namespace. However, reading the values is done through the publicly accessible SYS_CONTEXT function.

You probably don’t want all database users to have the ability to invoke the namespace manager, so you shouldn’t grant execute privileges to the namespace manager to PUBLIC. Restricting execute ability on the namespace manager provides an initial layer of security. Without the ability to manipulate the values, the values are considered secure.

A second layer of security comes from the implementation code itself. Often, the namespace manager performs the critical checks and validations prior to setting any values within the application context.

Setting Context Attributes and Values

The values in the application context are set by invoking the DBMS_SESSION.SET_CONTEXT procedure from within the namespace manager. Within an application context, you can create the name-value pairs relevant to your application(s). The most popular approach to exploiting application contexts is to store values that were derived as the result of a function, internal database query, or external query (for example, a query to an LDAP server). By executing the function or query once and storing the result in the application context, subsequent references by your database security implementations will be much faster than if they had to invoke a function or perform an internal or external query. This proves extremely valuable when the value is referenced in the SQL predicate or the where clause of a query. In this situation, the context values will be executed once for each row in the table—fast execution is critical to query performance.

To illustrate this, create a simple table that holds user information. Suppose you wish to restrict records for a user to those only within their organization or department. You’ll use an application context to capture the user’s department number. The department number value is set once, and then referenced many times. Each reference will use the context value, thus sparing a SQL query from looking up this information. You should also consider setting the value transparently via a logon trigger. First, create the data table to hold your lookup values:

sec_mgr@KNOX10g> CREATE TABLE lookup_dept     2    AS SELECT ename username, deptno FROM emp;    Table created.   

To set the values in the context, you have to implement a function, procedure, or package in the SEC_MGR schema that has the name of CTX_EX_MGR. Note you have to name the program CTX_EX_MGR because this was what you defined when you previously created the namespace. It’s most common to use a PL/SQL package for managing an application context. The package generally contains all the related procedures and functions that are required to perform verification or validation about the user and set the specific attributes within the namespace.

For this example, only one procedure is required for setting the value. In instances where the values need to be reset or cleared, such as when reusing a shared database session within a connection pool, an additional procedure will also be created. It’s good practice to provide one procedure to set and another procedure to clear individual attributes:

sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE ctx_ex_mgr     2   AS     3   PROCEDURE set_deptno;    4   PROCEDURE clear_deptno;    5   END;     6  /    Package created.    sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE BODY ctx_ex_mgr     2  AS    3  -----------------------------------------------------------   4     PROCEDURE set_deptno    5     AS    6       l_deptno NUMBER;     7     BEGIN    8       SELECT deptno    9         INTO l_deptno   10         FROM lookup_dept   11       WHERE username =   12                SYS_CONTEXT ('userenv',   13                             'session_user');   14      DBMS_SESSION.set_context   15                               (namespace => 'ctx_ex',   16                                ATTRIBUTE => 'deptno',   17                                VALUE => l_deptno);   18  END set_deptno;   19   20 –----------------------------------------------  21     PROCEDURE clear_deptno   22     AS   23     BEGIN   24       DBMS_SESSION.clear_context   25                              (namespace => 'ctx_ex',   26                               ATTRIBUTE => 'deptno');   27     END clear_deptno;   28 --------------------------------------------------–  29   END ctx_ex_mgr;   30 /    Package body created.   

Caution 

The security for this example is based on the data stored in the LOOKUP_DEPT table. Guarding access and manipulation of this data is therefore critical.

To set an application context automatically, use a database logon trigger:

sec_mgr@KNOX10g> CREATE OR REPLACE TRIGGER set_user_deptno     2     AFTER LOGON ON DATABASE     3   BEGIN    4      sec_mgr.ctx_ex_mgr.set_deptno;    5   EXCEPTION    6      WHEN NO_DATA_FOUND    7      THEN    8        -- If no data is found, user is not in the table    9        -- so the value will not be set.   10        -- If this exception is not handled, then some users   11        -- may be unable to logon   12        NULL;   13   END;   14  /    Trigger created.   

Note that you do not have to grant execute privileges on the namespace manager for your users to be able to execute the program when it’s called from a database logon trigger. The ability to execute the namespace manager comes from the definer rights model used by the logon trigger, which fires automatically after users have authenticated.

Testing the design by logging in as SCOTT, the value is automatically set and can be retrieved by querying the SYS_CONTEXT function:

C:\>sqlplus scott/tiger    SQL*Plus: Release 10.1.0.2.0 - Production on Thu Mar 25 13:24:29 2004    Copyright (c) 1982, 2004, Oracle. All rights reserved.    Connected to:  Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production  With the Partitioning, Oracle Label Security, OLAP and Data Mining  options    scott@KNOX10g> COL deptno format a10  scott@KNOX10g> SELECT SYS_CONTEXT ('ctx_ex', 'deptno') deptno     2   FROM DUAL;    DEPTNO  ------ 20  

Note 

The namespaces and attribute values are case-insensitive. Setting or referencing these can be done in either upper- or lowercase. The names given to the namespaces, however, have to be unique across the database.

Applying the Application Context to Security

You can now use this application context to implement your database security policies. You’ll see plenty more examples of how to use application contexts for security within views and for Virtual Private Database (VPD) in 11. For a quick example, consider a view that restricts users’ queries to records within the department in which they work. Using the application context, create a view that implements this record level security (also known as fine-grained access) with the following:

scott@KNOX10g> CREATE OR REPLACE VIEW my_dept_ctx     2  AS    3     SELECT *    4        FROM emp    5     WHERE deptno = SYS_CONTEXT ('ctx_ex', 'deptno');    View created.    scott@KNOX10g> -- View restricts records based on value of  scott@KNOX10g> -- application context  scott@KNOX10g> SELECT ename, deptno FROM my_dept_ctx;    ENAME         DEPTNO  ---------- ------------ SMITH          20  JONES          20  SCOTT          20  ADAMS          20  FORD           20 

A different example shows how you might employ the context to do trigger-based security. Say you wish to create a trigger that prevents users from modifying data outside their department. You should consider using the application context for this implementation because the trigger will execute for each row and consequently needs to perform very quickly. The following example illustrates this point:

scott@KNOX10g> -- Create a trigger which prevents a user from  scott@KNOX10g> -- modifying data outside their department  scott@KNOX10g> CREATE OR REPLACE TRIGGER restrict_updates     2    BEFORE DELETE OR UPDATE    3    ON emp    4    FOR EACH ROW    5  BEGIN    6    IF (:OLD.deptno !=     7                     SYS_CONTEXT ('ctx_ex', 'deptno'))    8    THEN    9      raise_application_error   10     (-20001,   11         CHR (10)   12      || '** You can only update records within your department.'   13      || CHR (10)    14      || '** Your department number is '   15      || SYS_CONTEXT ('ctx_ex', 'deptno'));   16    END IF;   17  END;  18 /    Trigger created.   

Issuing an unqualified update or delete validates the trigger is working:

scott@KNOX10g> UPDATE emp     2   SET ename = ename    3   WHERE deptno = 10;  UPDATE emp          *   ERROR at line 1:  ORA-20001:  ** You can only update records within your department.  ** Your department number is 20  ORA-06512: at "SCOTT.RESTRICT_UPDATES", line 5  ORA-04088: error during execution of trigger 'SCOTT.RESTRICT_UPDATES'    scott@KNOX10g> DELETE FROM emp;  DELETE FROM emp               *   ERROR at line 1:  ORA-20001:  ** You can only update records within your department.  ** Your department number is 20  ORA-06512: at "SCOTT.RESTRICT_UPDATES", line 5  ORA-04088: error during execution of trigger 'SCOTT.RESTRICT_UPDATES'   

You may want to check how this implementation compares with a SQL-based alternative. To illustrate this, you can time how long it takes to issue updates on the table. Each update causes the trigger to fire. You can run two tests—one using the application context and another querying the department number from within the trigger itself:

scott@KNOX10g> SET timing on  scott@KNOX10g> -- Issue authorized updates.  scott@KNOX10g> -- Each update will cause trigger to fire  scott@KNOX10g> BEGIN     2    FOR i IN 1 .. 100000    3    LOOP    4      UPDATE emp    5        SET ename = ename    6      WHERE deptno = 20;    7    END LOOP;     8  END;    9 /   PL/SQL procedure successfully completed.  

It took 41 seconds using the application context implementation. Changing the trigger code implementation, you have to fetch the department number from the lookup table. The trigger is based on the value returned from this table:

scott@KNOX10g> set timing off  scott@KNOX10g> -- Modify trigger to use select instead of context  scott@KNOX10g> CREATE OR REPLACE TRIGGER restrict_updates     2   BEFORE DELETE OR UPDATE    3   ON emp    4   FOR EACH ROW    5  DECLARE    6    l_deptno NUMBER;     7  BEGIN    8    SELECT deptno    9      INTO l_deptno   10      FROM lookup_dept   11      WHERE ename = USER;   12    13    IF (:OLD.deptno != l_deptno)   14    THEN   15      raise_application_error   16        (-20001,   17            CHR (10)   18        || '** You can only update records within your department.'   19        || CHR (10)   20        || '** Your department number is '   21        || l_deptno);   22    END IF;   23   END;   24  /  Trigger created.    scott@KNOX10g> SET timing on  scott@KNOX10g> -- Time new trigger implementation based on SQL query  scott@KNOX10g> BEGIN     2    FOR i IN 1 .. 100000    3    LOOP    4     UPDATE emp    5        SET ename = ename    6     WHERE deptno = 20;    7    END LOOP;     8   END;    9  /    PL/SQL procedure successfully completed.    

The performance increase you see from using an application context is directly proportional to the amount of time you save from not having to fetch the value from a query. In the previous example, querying the table to get a user’s department number is not overwhelmingly time consuming. In real applications, the queries typically perform several joins and take more time to execute. It’s in these cases that application contexts provide the most benefit.

Secure Use

An important point to using the locally defined application context is that the values are private to the user’s session. The values are stored in the database session’s User Global Area (UGA). The values can’t be viewed by the DBA or anyone else. This allows you to set very sensitive information in the context with the assurance that the values will not be recovered by anyone other than the session user.

The corollary to this is that anytime you’re sharing a database session among different users, when using a connection pool for example, then you are responsible for clearing the application context between users. You can use the CLEAR_CONTEXT procedure to clear individual values as is shown in the CTX_EX_MGR, or you can clear all application contexts for the session by invoking DBMS_SESSION.CLEAR_ALL_CONTEXT.

Common Mistakes

While there is tremendous flexibility in using an application context, they can be a little difficult to debug. The following represent some of the popular mistakes encountered by people new to developing applications that use an application context.

Incorrect Namespace or Attribute

Debugging code that references application context values can be frustrating if the program has misspelled either the namespace or the attribute. Referencing a context namespace that doesn’t exist or a value that doesn’t exist within a namespace does not cause an error—it simply returns a null value. This is in contrast to accessing an undefined attribute in the USERENV namespace. In this case, the database does throw an error:

sec_mgr@KNOX10g> -- Database errors with undefined attribute  sec_mgr@KNOX10g> SELECT SYS_CONTEXT ('userenv', 'foo') FROM DUAL;  SELECT SYS_CONTEXT ('userenv', 'foo') FROM DUAL                                              *   ERROR at line 1:  ORA-02003: invalid USERENV parameter    sec_mgr@KNOX10g> -- No errors for user's undefined attribute  sec_mgr@KNOX10g> SELECT SYS_CONTEXT ('ctx_ex', 'dptno') FROM DUAL;    SYS_CONTEXT('CTX_EX','DPTNO')  -------------------------------------------------------- sec_mgr@KNOX10g>   

Good coding practices can resolve this. For example, using package variable constants for the attribute names in the namespace manager is one good solution to this problem:

sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE ctx_ex_mgr     2   AS    3     namespace CONSTANT VARCHAR2 (6) := 'CTX_EX';    4     deptno CONSTANT VARCHAR2 (6) := 'DEPTNO';    5     PROCEDURE set_deptno;     6     PROCEDURE clear_deptno;    7   END;     8 /    Package created.    sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE BODY ctx_ex_mgr     2   AS    3 --------------------------------------------------–   4    PROCEDURE set_deptno    5    AS    6      v_deptno NUMBER;     7      BEGIN    8        SELECT deptno    9          INTO v_deptno   10          FROM lookup_dept   11        WHERE username =   12                 SYS_CONTEXT ('userenv',   13                              'session_user');   14       DBMS_SESSION.set_context   15                (namespace => ctx_ex_mgr.namespace,   16                ATTRIBUTE => ctx_ex_mgr.deptno,   17                VALUE => v_deptno);   18   END set_deptno;   19 -----------------------------------------  20   PROCEDURE clear_deptno   21   AS   22   BEGIN   23      DBMS_SESSION.clear_context   24                 (namespace => ctx_ex_mgr.namespace,   25                  ATTRIBUTE => ctx_ex_mgr.deptno);   26    END clear_deptno;   27 --------------------------------------------  28  END ctx_ex_mgr;   29 /    Package body created.   

Incorrect Namespace Manager

Another frequent mistake occurs when misnaming or having an inconsistency with the namespace manager, that is, between the program defined to implement the context and the one you think is implementing the context. The context creation DDL defines what the program is. During compilation of the non-namespace manager program, the database doesn’t (and can’t) tell you that you’re trying to set a context namespace that isn’t registered for the program. Upon execution, the error message indicates “insufficient privileges,” which isn’t particularly helpful.

In this example, the namespace manager is set to be the CTX_MGR program. Since the program isn’t qualified with a schema name, the database assumes the current user’s schema (SYSTEM in this example):

 system@KNOX10g> CREATE CONTEXT ctx_ex USING ctx_mgr;  Context created.  

Implementing the CTX_MGR in a different schema or using a program with a name other than CTX_MGR will cause an error. Notice a correctly named program in the SEC_MGR schema compiles, but fails to execute:

system@KNOX10g> CONN sec_mgr/oracle10g  Connected.  sec_mgr@KNOX10g> -- Oops, this is in the wrong schema  sec_mgr@KNOX10g> CREATE OR REPLACE PROCEDURE ctx_mgr     2  AS    3  BEGIN    4     DBMS_SESSION.set_context    5                        (namespace => 'CTX_EX',    6                         ATTRIBUTE => 'favorite_color',    7                         VALUE => 'blue');     8  END;    9 /    Procedure created.  sec_mgr@KNOX10g> -- No compile errors, so assume everything is fine  sec_mgr@KNOX10g> EXEC ctx_mgr;   BEGIN ctx_mgr; END;  *   ERROR at line 1:  ORA-01031: insufficient privileges  ORA-06512: at "SYS.DBMS_SESSION", line 82  ORA-06512: at "SEC_MGR.CTX_MGR", line 4  ORA-06512: at line 1  

It’s only upon checking the DBA_CONTEXT view that you see the mistake.

sec_mgr@KNOX10g> COL schema format a15  sec_mgr@KNOX10g> SELECT SCHEMA, PACKAGE, TYPE     2  FROM dba_context    3  WHERE namespace = 'CTX_EX';    SCHEMA         PACKAGE        TYPE  ------------ --------------- -------- SYSTEM         CTX_MGR       ACCESSED LOCALLY