A database user session (UGA) stores session-based application context, using a user-created namespace.

Each application context must have a unique attribute and belong to a namespace. That is, context names must be unique within the database, not just within a schema.

You must have the CREATE ANY CONTEXT system privilege to create an application context, and the DROP ANY CONTEXT privilege to use the DROP CONTEXT statement if you want to drop the application context.

The ownership of the application context is as follows: Even though a user who has been granted the CREATE ANY CONTEXT and DROP ANY CONTEXT privileges can create and drop the application context, it is owned by the SYS schema. Oracle Database associates the context with the schema account that created it, but if you drop this user, the context still exists in the SYS schema. As user SYS, you can drop the application context.

You can find the names of existing application contexts by running the following query:

SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE ='CONTEXT';

The CREATE CONTEXT SQL statement can be used to create a database session-based application context.

For each application, you can create an application context that has its own attributes.

This defines procedures that manage the session data represented by the application context.

This package is usually created in the security administrator schema. The package must perform the following tasks:

• Retrieve session information. To retrieve the user session information, you can use the SYS_CONTEXT SQL function. The SYS_CONTEXT function returns the value of the parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements. Typically, you will use the built-in USERENV namespace to retrieve the session information of a user. You also can use the SYS_SESSION_ROLES namespace to indicate whether the specified role is currently enabled for the session.

• Set the name-value attributes of the application context you created with CREATE CONTEXT. You can use the DBMS_SESSION.SET_CONTEXT procedure to set the name-value attributes of the application context. The name-value attributes can hold information such as the user ID, IP address, authentication mode, the name of the application, and so on. The values of the attributes you set remain either until you reset them, or until the user ends the session. Note the following:

  • If the value of the parameter in the namespace already has been set, then SET_CONTEXT overwrites this value.

  • Be aware that any changes in the context value are reflected immediately and subsequent calls to access the value through the SYS_CONTEXT function will return the most recent value.

• Be executed by users. After you create the package, the user will need to execute the package when he or she logs on. You can create a logon trigger to execute the package automatically when the user logs on, or you can embed this functionality in your applications. Remember that the application context session values are cleared automatically when the user ends the session, so you do not need to manually remove the session data.

It is important to remember that the procedure is a trusted procedure: It is designed to prevent the user from setting his or her own application context attribute values. The user runs the procedure, but the procedure sets the application context values, not the user.

You can retrieve session information for the application context by using the SYS_CONTEXT function.

SYS_CONTEXT ('namespace','parameter'[,length])

You can check the SYS_CONTEXT settings, which are stored in the DUAL table.

SELECT SYS_CONTEXT ('USERENV', 'HOST') FROM DUAL;

SYS_CONTEXT(USERENV,HOST)
-------------------------
EMP_USERS\SHOBEEEN_PC

During a session in which you expect a change in policy between executions of a given query, the query must use dynamic SQL.

You must use dynamic SQL because static SQL and dynamic SQL parse statements differently:

• Static SQL statements are parsed at compile time. They are not parsed again at execution time for performance reasons.

• Dynamic SQL statements are parsed every time they are executed.

Consider a situation in which Policy A is in force when you compile a SQL statement, and then you switch to Policy B and run the statement. With static SQL, Policy A remains in force. Oracle Database parses the statement at compile time, but does not parse it again upon execution. With dynamic SQL, Oracle Database parses the statement upon execution, then the switch to Policy B takes effect.

For example, consider the following policy:

EMPLOYEE_NAME = SYS_CONTEXT ('USERENV', 'SESSION_USER')

The policy EMPLOYEE_NAME matches the database user name. It is represented in the form of a SQL predicate in Oracle Virtual Private Database: the predicate is considered a policy. If the predicate changes, then the statement must be parsed again to produce the correct result.

If you use SYS_CONTEXT inside a SQL function that is embedded in a parallel query, then the function includes the application context.

Consider a user-defined function within a SQL statement, which sets the user ID to 5:

CREATE FUNCTION set_id 
 RETURN NUMBER IS
BEGIN
 IF SYS_CONTEXT ('hr', 'id') = 5
   THEN RETURN 1; ELSE RETURN 2;
 END IF;
END;

Now consider the following statement:

SELECT * FROM emp WHERE set_id( ) = 1;

When this statement is run as a parallel query, the user session, which contains the application context information, is propagated to the parallel execution servers (query child processes).

The SYS_CONTEXT function is compatible with the use of database links.

When SQL statements within a user session involve database links, Oracle Database runs the SYS_CONTEXT function at the host computer of the database link, and then captures the context information in the host computer.

If remote PL/SQL procedure calls are run on a database link, then Oracle Database runs any SYS_CONTEXT function inside such a procedure at the destination database of the link.

In this case, only externally initialized application contexts are available at the database link destination site. For security reasons, Oracle Database propagates only the externally initialized application context information to the destination site from the initiating database link site.

After SYS_CONTEXT retrieves the session data of a user, you can set the application context values from the user session.

To set the context values, you can use the DBMS_SESSION.SET_CONTEXT procedure. You must have the EXECUTE privilege for the DBMS_SESSION PL/SQL package.

The syntax for DBMS_SESSION.SET_CONTEXT is as follows:

DBMS_SESSION.SET_CONTEXT (
   namespace VARCHAR2,
   attribute VARCHAR2,
   value     VARCHAR2,
   username  VARCHAR2,
   client_id VARCHAR2);

In this specification:

• namespace is the namespace of the application context to be set, limited to 30 bytes. For example, if you were using a namespace called custno_ctx, you would specify it as follows:

  namespace => 'custno_ctx',
  

• attribute is the attribute of the application context to be set, limited to 30 bytes. For example, to create the ctx_attrib attribute for the custno_ctx namespace:

  attribute => 'ctx_attrib',
  

• value is the value of the application context to be set, limited to 4000 bytes. Typically, this is the value retrieved by the SYS_CONTEXT function and stored in a variable. For example:

  value => ctx_value,
  

• username is the database user name attribute of the application context. The default is NULL, which permits any user to access the session. For database session-based application contexts, omit this setting so that it uses the NULL default. This setting is optional.

  The username and client_id parameters are used for globally accessed application contexts. See DBMS_SESSION.SET_CONTEXT username and client_id Parameters for more information.

• client_id is the application-specific client_id attribute of the application context (64-byte maximum). The default is NULL, which means that no client ID is specified. For database session-based application contexts, omit this setting so that it uses the NULL default.

You can use the DBMS_SESSION.SET_CONTEXT statement in a procedure to set an application context value.

Example 11-1 shows how to create a simple procedure that creates an attribute for the empno_ctx application context.

CREATE OR REPLACE PROCEDURE set_empno_ctx_proc(
  emp_value IN VARCHAR2)
 IS   
 BEGIN
  DBMS_SESSION.SET_CONTEXT('empno_ctx', 'empno_attrib', emp_value);
 END; 
/

EXECUTE set_empno_ctx_proc ('42783');

SELECT SYS_CONTEXT ('empno_ctx', 'empno_attrib') empno_attrib FROM DUAL;

EMPNO_ATTRIB
--------------
42783

SELECT * FROM SESSION_CONTEXT;

NAMESPACE                ATTRIBUTE          VALUE
--------------------------------------------------
EMPNO_CTX                EMP_ID             42783

To begin this tutorial, you must create the necessary database accounts and endure that the SCOTT user account is active.

1. Log on as user SYS and connect using the SYSDBA administrative privilege.

   sqlplus sys as sysdba
   Enter password: password
   

2. In a multitenant environment, connect to the appropriate PDB.

   For example:

   CONNECT SYS@hrpdb AS SYSDBA
   Enter password: password
   

   To find the available PDBs, run the show pdbs command. To check the current PDB, run the show con_name command.

3. Create the local user account sysadmin_ctx, who will administer the database session-based application context.

   CREATE USER sysadmin_ctx IDENTIFIED BY password;
   GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO sysadmin_ctx;
   GRANT READ ON HR.EMPLOYEES TO sysadmin_ctx;
   GRANT EXECUTE ON DBMS_SESSION TO sysadmin_ctx;
   

   Follow the guidelines in Minimum Requirements for Passwords to replace password with a password that is secure.

4. Create the following user account for Lisa Ozer, who is listed as having lozer for her email account in the HR.EMPLOYEES table.

   GRANT CREATE SESSION TO LOZER IDENTIFIED BY password;
   

   Replace password with a password that is secure.

5. The sample user SCOTT will also be used in this tutorial, so query the DBA_USERS data dictionary view to ensure that the account status for SCOTT is OPEN.

   SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'SCOTT';
   

   If the DBA_USERS view lists user SCOTT as locked and expired, then enter the following statement to unlock the SCOTT account and create a new password for him:

   ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY password;
   

   Enter a password that is secure. For greater security, do not give the SCOTT account the same password from previous releases of Oracle Database. See Minimum Requirements for Passwords for the minimum requirements for creating passwords.

As the sysadmin_ctx user, you are ready to create the database session-based application context.

1. Log on to SQL*Plus as sysadmin_ctx.

   CONNECT sysadmin_ctx -- Or, CONNECT sysadmin_ctx@hrpdb
   Enter password: password
   

2. Create the application context using the following statement:

   CREATE CONTEXT empno_ctx USING set_empno_ctx_pkg;
   

   Remember that even though user sysadmin_ctx has created this application context, the SYS schema owns the context.

Next, you must create a PL/SQL package that retrieves the session data and then sets the application context.

CREATE OR REPLACE PACKAGE set_empno_ctx_pkg IS 
   PROCEDURE set_empno; 
 END; 
 /
 CREATE OR REPLACE PACKAGE BODY set_empno_ctx_pkg IS
   PROCEDURE set_empno 
   IS 
    emp_id HR.EMPLOYEES.EMPLOYEE_ID%TYPE;
   BEGIN 
    SELECT EMPLOYEE_ID INTO emp_id FROM HR.EMPLOYEES 
       WHERE email = SYS_CONTEXT('USERENV', 'SESSION_USER');
    DBMS_SESSION.SET_CONTEXT('empno_ctx', 'employee_id', emp_id);
   EXCEPTION  
    WHEN NO_DATA_FOUND THEN NULL;
  END;
 END;
/

The logon trigger will execute when the user logs in.

CREATE TRIGGER set_empno_ctx_trig AFTER LOGON ON DATABASE
 BEGIN
  sysadmin_ctx.set_empno_ctx_pkg.set_empno;
 END;
/

Now that the components are all in place, you are ready to test the application context.

1. Log on as user lozer.

   CONNECT lozer -- Or, CONNECT lozer@hrpdb
   Enter password: password
   

   When user lozer logs on, the empno_ctx application context collects her employee ID. You can check it as follows:

   SELECT SYS_CONTEXT('empno_ctx', 'employee_id') emp_id FROM DUAL;
   

   The following output should appear:

   EMP_ID
   --------------------------------------------------------
   168
   

2. Log on as user SCOTT.

   CONNECT SCOTT -- Or, CONNECT SCOTT@hrpdb
   Enter password: password
   

   User SCOTT is not listed as an employee in the HR.EMPLOYEES table, so the empno_ctx application context cannot collect an employee ID for him.

   SELECT SYS_CONTEXT('empno_ctx', 'employee_id') emp_id FROM DUAL;
   

   The following output should appear:

   EMP_ID
   --------------------------------------------------------
   

If you no longer need the components of this tutorial, then you can remove them.

1. Connect as SYS with the SYSDBA administrative privilege.

   CONNECT SYS AS SYSDBA -- Or, CONNECT SYS@hrpdb AS SYSDBA
   Enter password: password
   

2. Drop the users sysadmin_ctx and lozer:

   DROP USER sysadmin_ctx CASCADE;
   DROP USER lozer;
   

3. Drop the application context.

   DROP CONTEXT empno_ctx;
   

   Remember that even though sysadmin_ctx created the application context, it is owned by the SYS schema.

4. If you want, lock and expire SCOTT, unless other users want to use this account:

   ALTER USER SCOTT PASSWORD EXPIRE ACCOUNT LOCK; 

You must use a special type of namespace to initialize session-based application context externally.

This namespace must accept the initialization of attribute values from external resources and then stores them in the local user session.

Initializing an application context externally enhances performance because it is stored in the UGA and enables the automatic propagation of attributes from one session to another. Connected user database links are supported only by application contexts initialized from OCI-based external sources.

Oracle Database enables you to capture and use default values from users for your applications.

Sometimes you need the default values from users. Initially, these default values may be hints or preferences, and then after validation, they become trusted contexts. Similarly, it may be more convenient for clients to initialize some default values, and then rely on a login event trigger or applications to validate the values.

For job queues, the job submission routine records the context being set at the time the job is submitted, and restores it when executing the batched job. To maintain the integrity of the context, job queues cannot bypass the designated PL/SQL package to set the context. Rather, the externally initialized application context accepts initialization of context values from the job queue process.

Automatic propagation of context to a remote session may create security problems. Developers or administrators can effectively handle the context that takes default values from resources other than the designated PL/SQL procedure by using logon triggers to reset the context when users log in.

An application context can accept the initialization of attributes and values through external resources.

Examples include an Oracle Call Interface (OCI) interface, a job queue process, or a database link.

Externally initialized application contexts provide the following features:

• For remote sessions, automatic propagation of context values that are in the externally initialized application context namespace

• For job queues, restoration of context values that are in the externally initialized application context namespace

• For OCI interfaces, a mechanism to initialize context values that are in the externally initialized application context namespace

Although any client program that is using Oracle Call Interface can initialize this type of namespace, you can use login event triggers to verify the values. It is up to the application to interpret and trust the values of the attributes.

Middle-tier servers can initialize application context values on behalf of database users.

In this process, context attributes are propagated for the remote session at initialization time, and the remote database accepts the values if the namespace is externally initialized.

For example, a three-tier application creating lightweight user sessions through OCI or JDBC/OCI can access the PROXY_USER attribute in USERENV. This attribute enables you to determine if the user session was created by a middle-tier application. You could allow a user to access data only for connections where the user is proxied. If users connect directly to the database, then they would not be able to access any data.

You can use the PROXY_USER attribute from the USERENV namespace within Oracle Virtual Private Database to ensure that users only access data through a particular middle-tier application. For a different approach, you can develop a secure application role to enforce your policy that users access the database only through a specific proxy.

You can use a centralized location to store the database session-based application context of the user.

A centralized location enables applications to set up a user context during initialization based upon user identity.

In particular, this feature supports Oracle Label Security labels and privileges. Initializing an application context globally makes it easier to manage contexts for large numbers of users and databases.

For example, many organizations want to manage user information centrally, in an LDAP-based directory. Enterprise User Security supports centralized user and authorization management in Oracle Internet Directory. However, there may be additional attributes an application must retrieve from Lightweight Directory Access Protocol (LDAP) to use for Oracle Virtual Private Database enforcement, such as the user title, organization, or physical location. Initializing an application context globally enables you to retrieve these types of attributes.

An application context that is initialized globally uses LDAP, a standard, extensible, and efficient directory access protocol.

The LDAP directory stores a list of users to which this application is assigned. Oracle Database uses a directory service, typically Oracle Internet Directory, to authenticate and authorize enterprise users.

The orclDBApplicationContext LDAP object (a subclass of groupOfUniqueNames) stores the application context values in the directory. The location of the application context object is described in Figure 11-1, which is based on the Human Resources example.

The LDAP object inetOrgPerson enables multiple entries to exist for some attributes. However, be aware that when these entries are loaded into the database and accessed with the SYS_LDAP_USER_DEFAULT context namespace, then only the first of these entries is returned. For example, the inetOrgPerson object for a user allows multiple entries for telephoneNumber (thus allowing a user to have multiple telephone numbers stored). When you use the SYS_LDAP_USER_DEFAULT context namespace, only the first telephone number is retrieved. If the list of attributes and values that are provided are not sufficient for your needs, then you can use the DBMS_LDAP PL/SQL package to fetch additional values from the directory.

On the LDAP side, an internal C function is required to retrieve the orclDBApplicationContext value, which returns a list of application context values to the database. In this example, HR is the namespace; Title and Project are the attributes; and Manager and Promotion are the values.

Figure 11-1 Location of Application Context in LDAP Directory Information Tree

Description of "Figure 11-1 Location of Application Context in LDAP Directory Information Tree"

To use a globally initialized secure application, you must first configure Enterprise User Security.

Then, you configure the application context values for the user in the database and the directory.

When a global user (enterprise user) connects to the database, Enterprise User Security verifies the identity of the user connecting to the database. After authentication, the global user roles and application context are retrieved from the directory. When the user logs on to the database, the global roles and initial application context are already set.

You can configure and store the initial application context for a user, such as the department name and title, in the LDAP directory.

1. Create the application context in the database.

   CREATE CONTEXT hr USING hrapps.hr_manage_pkg INITIALIZED GLOBALLY;
   

2. Create and add new entries in the LDAP directory.

   An example of the entries added to the LDAP directory follows. These entries create an attribute named Title with the attribute value Manager for the application (namespace) HR, and assign user names user1 and user2. In the following, cn=example refers to the name of the domain.

   dn: cn=OracleDBAppContext,cn=example,cn=OracleDBSecurity,cn=Products,cn=OracleContext,ou=Americas,o=oracle,c=US
   changetype: add
   cn: OracleDBAppContext
   objectclass: top
   objectclass: orclContainer
   
   dn: cn=hr,cn=OracleDBAppContext,cn=example,cn=OracleDBSecurity,cn=Products,cn=OracleContext,ou=Americas,o=oracle,c=US
   changetype: add
   cn: hr
   objectclass: top
   objectclass: orclContainer
   
   dn: cn=Title,cn=hr, cn=OracleDBAppContext,cn=example,cn=OracleDBSecurity,cn=Products,cn=OracleContext,ou=Americas,o=oracle,c=US
   changetype: add
   cn: Title
   objectclass: top
   objectclass: orclContainer
   
   dn: cn=Manager,cn=Title,cn=hr, cn=OracleDBAppContext,cn=example,cn=OracleDBSecurity,cn=Products,cn=OracleContext,ou=Americas,o=oracle,c=US
   cn: Manager
   objectclass: top
   objectclass: groupofuniquenames
   objectclass: orclDBApplicationContext
   uniquemember: CN=user1,OU=Americas,O=Oracle,L=Redwoodshores,ST=CA,C=US
   uniquemember: CN=user2,OU=Americas,O=Oracle,L=Redwoodshores,ST=CA,C=US

3. If an LDAP inetOrgPerson object entry exists for the user, then the connection retrieves the attributes from inetOrgPerson, and assigns them to the namespace SYS_LDAP_USER_DEFAULT. Note that the context is only populated with non-NULL values that are part of the inetOrgPerson object class. No other attributes will be populated.

   The following is an example of an inetOrgPerson entry:

   dn: cn=user1,ou=Americas,O=oracle,L=redwoodshores,ST=CA,C=US
   changetype: add
   objectClass: top
   objectClass: person
   objectClass: organizationalPerson
   objectClass: inetOrgPerson
   cn: user1
   sn: One
   givenName: User
   initials: UO
   title: manager, product development
   uid: uone
   mail: uone@us.example.com
   telephoneNumber: +1 650 555 0105
   employeeNumber: 00001
   employeeType: full time

4. Connect to the database.

   When user1 connects to a database that belongs to the example domain, user1 will have his Title set to Manager. Any information related to user1 will be retrieved from the LDAP directory. The value can be obtained using the following syntax:

   SYS_CONTEXT('namespace','attribute name') 
   

   For example:

   DECLARE 
    tmpstr1 VARCHAR2(30);
    tmpstr2 VARCHAR2(30);
   BEGIN
    tmpstr1 = SYS_CONTEXT('HR','TITLE);
    tmpstr2 = SYS_CONTEXT('SYS_LDAP_USER_DEFAULT','telephoneNumber');
    DBMS_OUTPUT.PUT_LINE('Title is ' || tmpstr1);
    DBMS_OUTPUT.PUT_LINE('Telephone Number is ' || tmpstr2);
   END;
   

   The output of this example is:

   Title is Manager
   Telephone Number is +1 650 555 0105

A global application context is owned by the SYS schema.

The ownership of the global application context is as follows: Even though a user who has been granted the CREATE ANY CONTEXT and DROP ANY CONTEXT privileges can create and drop the global application context, it is owned by the SYS schema.

Oracle Database associates the context with the schema account that created it, but if you drop this user, the context still exists in the SYS schema. As user SYS, you can drop the application context.

As with local application contexts, the global application context is created and stored in the security administrator’s database schema.

CREATE OR REPLACE CONTEXT global_hr_ctx USING hr_ctx_pkg ACCESSED GLOBALLY CONTAINER = ALL;

The package that is associated with a global application context uses the DBMS_SESSION package to set and clear the global application context values.

You must have the EXECUTE privilege for the DBMS_SESSION package before you use its procedures. Typically, you create and store this package in the database schema of a security administrator. The SYS schema owns the DBMS_SESSION package.

Unlike PL/SQL packages used to set a local application context, you do not include a SYS_CONTEXT function to get the user session data. You do not need to include this function because the owner of the session, recorded in the USERENV context, is the same for every user who is connecting.

You can run the procedures within the PL/SQL package for a global application context at any time. You do not need to create logon and logoff triggers to execute the package procedures associated with the global application context. A common practice is to run the package procedures from within the database application. Additionally, for nondatabase users, you use middle-tier applications to get and set client session IDs.

Global application context packages, Oracle Virtual Private Database packages, and fine-grained audit policies can be used across multiple editions.

Follow these guidelines:

• If you want to have the PL/SQL package results be the same across all editions. To do so, create the package in the schema of a user who has not been editions enabled. To find users who are not editions enabled, you can query the DBA_USERS and USER_USERS data dictionary views. Remember that SYS, SYSTEM, and other default Oracle Database administrative accounts that are listed in the DBA_REGISTRY data dictionary view are not and cannot be editions enabled.

• If you want to have the PL/SQL package results depend on the current state of the edition in which the package is run. Here, the results may be different across all editions to which the package applies. In this case, create the package in the schema of a user who has been editions enabled. If the schema is editions enabled, then it is likely that there will be different actual copies of the package in different editions, where each copy has different behavior. This is useful for the following types of scenarios:

  • The package must use a new application context.

  • The package must encode input values using a different scheme.

  • The package must apply different validation rules for users logging in to the database.

  For PL/SQL packages that set a global application context, use a single getter function to wrap the primitive SYS_CONTEXT calls that will read the key-value application context pairs. You can put this getter function in the same package as the application context setter procedure. This approach lets you tag the value for the application context key to reflect a relevant concept. For example, the tag can be the edition in which the setter function is actual. Or, it can be the current edition of the session that set the context, which you can find by using SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME'). This tag can be any specific notion to which the setter function applies.

  See Also:

  Oracle Database Development Guide for detailed information about editions

The DBMS_SESSION.SYS_CONTEXT procedure provides the client_id and username parameters, to be used for global application contexts.

You can share global application values for all database users to give them access to data in the database.

The CREATE PACKAGE statement can manage global application values for all database users.

Example 11-7 shows how to create a package that sets and clears a global application context for all database users.

CREATE OR REPLACE PACKAGE hr_ctx_pkg 
   AS  
    PROCEDURE set_hr_ctx(sec_level IN VARCHAR2); 
    PROCEDURE clear_hr_context;
   END;  
  /
  CREATE OR REPLACE PACKAGE BODY hr_ctx_pkg 
   AS                                            
    PROCEDURE set_hr_ctx(sec_level IN VARCHAR2)      
    AS   
    BEGIN  
     DBMS_SESSION.SET_CONTEXT(  
      namespace  => 'global_hr_ctx', 
      attribute  => 'job_role', 
      value      => sec_level);
     END set_hr_ctx;
           
  PROCEDURE clear_hr_context  
    AS
    BEGIN  
     DBMS_SESSION.CLEAR_CONTEXT('global_hr_ctx', 'job_role'); 
    END clear_context;  
  END; 
 /

BEGIN
 hr_ctx_pkg.set_hr_ctx('clerk');
END;
/

SELECT SYS_CONTEXT('global_hr_ctx', 'job_role') job_role FROM DUAL;

JOB_ROLE
-----------
clerk

BEGIN
 hr_ctx_pkg.clear_hr_context;
END;
/

SELECT SYS_CONTEXT('global_hr_ctx', 'job_role') job_role FROM DUAL;

JOB_ROLE
-----------

EXEC DBMS_SESSION.CLEAR_IDENTIFIER;

A global application context can be used for database users who move between application, even when the applications have different access requirements.

To do so, you must include the username parameter in the DBMS_SESSION.SET_CONTEXT procedure.

This parameter specifies that the same schema be used for all sessions.

You can use the following DBMS_SESSION.SET_CONTEXT parameters:

• namespace

• attribute

• value

• username

Oracle Database matches the username value so that the other application can recognize the application context. This enables the user to move between applications.

By omitting the client_id setting, its value is NULL, the default. This means that values can be seen by multiple sessions if the username setting is the same for a database user who maintains the same context in different applications. For example, you can have a suite of applications that control user access with Oracle Virtual Private Database policies, with each user restricted to a job role.

Example 11-8 demonstrates how to set the username parameter so that a specific user can move between applications. The use of the username parameter is indicated in bold typeface.

CREATE OR REPLACE PACKAGE hr_ctx_pkg
  AS
    PROCEDURE set_hr_ctx(sec_level IN VARCHAR2, user_name IN VARCHAR2);
    PROCEDURE clear_hr_context;
   END;
  /
  CREATE OR REPLACE PACKAGE BODY hr_ctx_pkg
   AS
    PROCEDURE set_hr_ctx(sec_level IN VARCHAR2, user_name IN VARCHAR2)
    AS
     BEGIN
      DBMS_SESSION.SET_CONTEXT(
       namespace  => 'global_hr_ctx',
       attribute  => 'job_role',
       value      => sec_level,
       username   => user_name);
      END set_hr_ctx;
 
   PROCEDURE clear_hr_context
    AS
     BEGIN
      DBMS_SESSION.CLEAR_CONTEXT('global_hr_ctx');
     END clear_context;
  END;
 / 

BEGIN
 hr_ctx_pkg.set_hr_ctx('clerk', 'scott');
END;

When a nondatabase user starts a client session, the application server generates a client session ID.

A nondatabase user is a user who is not known to the database, such as a Web application user.

Once this ID is set on the application server, it must be passed to the database server side. You can do this by using the DBMS_SESSION.SET_IDENTIFIER procedure to set the client session ID.

To set the context, you can set the client_id parameter in the DBMS_SESSION.SET_CONTEXT procedure, in a PL/SQL procedure on the server side. This enables you to manage the application context globally, yet each client sees only his or her assigned application context.

The client_id value is the key here to getting and setting the correct attributes for the global application context. Remember that the client identifier is controlled by the middle-tier application, and once set, it remains open until it is cleared.

A typical way to manage this type of application context is to place the session_id value (client_identifier) in a cookie, and send it to the end user's HTML page so that is returned on the next request. A lookup table in the application should also keep client identifiers so that they are prevented from being reused for other users and to implement an end-user session time out.

For nondatabase users, configure the following SET_CONTEXT parameters:

• namespace

• attribute

• value

• username

• client_id

The CREATE PACKAGE statement can manage global application context values for nondatabase users.

Example 11-9 shows how to create a package that manages this type of global application context.

CREATE OR REPLACE PACKAGE hr_ctx_pkg  
  AS                         
   PROCEDURE set_session_id(session_id_p IN NUMBER); 
   PROCEDURE set_hr_ctx(sec_level_attr IN VARCHAR2,  
      sec_level_val IN VARCHAR2);    
   PROCEDURE clear_hr_session(session_id_p IN NUMBER); 
   PROCEDURE clear_hr_context;  
  END;     
/
  CREATE OR REPLACE PACKAGE BODY hr_ctx_pkg 
   AS                     
    session_id_global NUMBER;  
  PROCEDURE set_session_id(session_id_p IN NUMBER) 
   AS                     
   BEGIN   
    session_id_global := session_id_p; 
    DBMS_SESSION.SET_IDENTIFIER(session_id_p);
  END set_session_id; 
     
  PROCEDURE set_hr_ctx(sec_level_attr IN VARCHAR2,
     sec_level_val IN VARCHAR2) 
   AS     
   BEGIN      
    DBMS_SESSION.SET_CONTEXT(  
     namespace  => 'global_hr_ctx',  
     attribute  => sec_level_attr,
     value      => sec_level_val,
     username   => USER,
     client_id  => session_id_global); 
   END set_hr_ctx;  
        
  PROCEDURE clear_hr_session(session_id_p IN NUMBER)
   AS 
   BEGIN   
      DBMS_SESSION.SET_IDENTIFIER(session_id_p); 
      DBMS_SESSION.CLEAR_IDENTIFIER;  
   END clear_hr_session;   
   
  PROCEDURE clear_hr_context 
  AS       
  BEGIN            
   DBMS_SESSION.CLEAR_CONTEXT('global_hr_ctx', session_id_global);
  END clear_hr_context;  
 END;  
 /

The application context exists within memory, so when the user exits a session, you must clear the client_identifier context value.

The application server generates the client session ID.

From a middle-tier application, you can get, set, and clear the client session IDs. To do so, you can embed either Oracle Call Interface (OCI) calls or DBMS_SESSION PL/SQL package procedures into the middle-tier application code.

The application authenticates the user, sets the client identifier, and sets it in the current session. The PL/SQL package SET_CONTEXT sets the client_identifier value in the application context.

When a user starts a client session, the application server generates a client session ID.

oratext    clientid[31];  
 OCIDefine  *defnp1 = (OCIDefine *) 0;
 OCIStmt    *statementhndle;
 oratext    *selcid = (oratext *)"SELECT SYS_CONTEXT('userenv',
            'client_identifier') FROM  DUAL";  

OCIStmtPrepare(statementhndle, errhp, selcid,
  (ub4) strlen((char *) selcid), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);

OCIDefineByPos(statementhndle, &defnp1, errhp, 1, (dvoid *)clientid, 31,
  SQLT_STR, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT);

OCIStmtExecute(servhndle, statementhndle, errhp, (ub4) 1, (ub4) 0,
 (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);

  printf("CLIENT_IDENTIFIER = %s \n", clientid);

Next, you are ready to set the client session ID using a middle-tier application.

• About Setting the Client Session ID Using a Middle-Tier Application
  After you use the OCIStmtExecute call to retrieve the client session ID, you are ready to set this ID.
• Setting the Client Session ID Using a Middle-Tier Application
  Oracle Call Interface or the DBMS_SESSION PL/SQL package can set the client session ID using a middle-tier application.
• Checking the Value of the Client Identifier
  For both OCIAttrSet and DBMS_SESSION.SET_IDENTIFIER, you can check the value of the client identifier.

The application context exists entirely within memory.

This tutorial shows how to create a global application context that uses a client session ID for a lightweight user application.

It demonstrates how to control nondatabase user access by using a connection pool. If you are using a multitenant environment, then this tutorial applies to the current PDB only.

A security administrator will manage the application context and its package, and a user account will own the connection pool.

1. Log on to SQL*Plus as SYS with the SYSDBA administrative privilege.

   sqlplus sys as sysdba
   Enter password: password
   

2. In a multitenant environment, connect to the appropriate PDB.

   For example:

   CONNECT SYS@my_pdb AS SYSDBA
   Enter password: password
   

   To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

3. Create the local user account sysadmin_ctx, who will administer the global application context.

   CREATE USER sysadmin_ctx IDENTIFIED BY password CONTAINER = CURRENT;
   GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE TO sysadmin_ctx;
   
   GRANT EXECUTE ON DBMS_SESSION TO sysadmin_ctx;
   

   Follow the guidelines in Minimum Requirements for Passwords to replace password with a password that is secure.

4. Create the local database account apps_user, who will own the connection pool.

   CREATE USER apps_user IDENTIFIED BY password CONTAINER = CURRENT;
   GRANT CREATE SESSION TO apps_user;
   

   Replace password with a password that is secure.

Next, you are ready to create the global application context.

1. Log on as the security administrator sysadmin_ctx.

   CONNECT sysadmin_ctx -- Or, CONNECT sysadmin_ctx@hrpdb
   Enter password: password
   

2. Create the cust_ctx global application context.

   CREATE CONTEXT global_cust_ctx USING cust_ctx_pkg ACCESSED GLOBALLY;
   

   The cust_ctx context is created and associated with the schema of the security administrator sysadmin_ctx. However, the SYS schema owns the application context.

The PL/SQL package will manage the global application context that you created.

1. As sysadmin_ctx, create the following PL/SQL package:

   CREATE OR REPLACE PACKAGE cust_ctx_pkg
     AS
      PROCEDURE set_session_id(session_id_p IN NUMBER); 
      PROCEDURE set_cust_ctx(sec_level_attr IN VARCHAR2, 
        sec_level_val IN VARCHAR2);
      PROCEDURE clear_hr_session(session_id_p IN NUMBER);
      PROCEDURE clear_hr_context;
     END;
    /
   CREATE OR REPLACE PACKAGE BODY cust_ctx_pkg
     AS
     session_id_global NUMBER;
    
    PROCEDURE set_session_id(session_id_p IN NUMBER) 
     AS
     BEGIN
      session_id_global := session_id_p;
      DBMS_SESSION.SET_IDENTIFIER(session_id_p);
    END set_session_id;
    
    PROCEDURE set_cust_ctx(sec_level_attr IN VARCHAR2, sec_level_val IN VARCHAR2)
     AS
     BEGIN
      DBMS_SESSION.SET_CONTEXT(
       namespace  => 'global_cust_ctx',
       attribute  => sec_level_attr,
       value      => sec_level_val,
       username   => USER, -- Retrieves the session user, in this case, apps_user
       client_id  => session_id_global);
     END set_cust_ctx;
    
     PROCEDURE clear_hr_session(session_id_p IN NUMBER)
      AS
      BEGIN
        DBMS_SESSION.SET_IDENTIFIER(session_id_p);
        DBMS_SESSION.CLEAR_IDENTIFIER;
      END clear_hr_session;
   
    PROCEDURE clear_hr_context
     AS
     BEGIN
      DBMS_SESSION.CLEAR_CONTEXT('global_cust_ctx', session_id_global);
     END clear_hr_context;
    END;
   /
   

   For a detailed explanation of how this type of package works, see Example 11-9.

2. Grant EXECUTE privileges on the cust_ctx_pkg package to the connection pool owner, apps_user.

   GRANT EXECUTE ON cust_ctx_pkg TO apps_user;

At this stage, you are ready to explore how this global application context and session ID settings work.

CATEGORY       BENEFIT_LEVEL
-------------  --------------

Next, clear and then modify the session ID and test the global application context again.

1. As user apps_user, clear the session ID.

   EXEC sysadmin_ctx.cust_ctx_pkg.clear_hr_session(34256);
   

2. Check the global application context settings again.

   SELECT SYS_CONTEXT('global_cust_ctx', 'Category') category, SYS_CONTEXT('global_cust_ctx', 'Benefit Level') benefit_level FROM DUAL;
   
   CATEGORY       BENEFIT_LEVEL
   -------------  --------------
   

   Because apps_user has cleared the session ID, the global application context settings are no longer available.

3. Restore the session ID to 34256, and then check the context values.

   EXEC sysadmin_ctx.cust_ctx_pkg.set_session_id(34256);
   
   SELECT SYS_CONTEXT('global_cust_ctx', 'Category') category, SYS_CONTEXT('global_cust_ctx', 'Benefit Level') benefit_level FROM DUAL;
   

   The following output should appear:

   CATEGORY       BENEFIT_LEVEL
   -------------  --------------
   Gold Partner   Highest
   

   As you can see, resetting the session ID to 34256 brings the application context values back again. To summarize, the global application context must be set only once for this user, but the client session ID must be set each time the user logs on.

4. Now try clearing and then checking the global application context values.

   EXEC sysadmin_ctx.cust_ctx_pkg.clear_hr_context;
   
   SELECT SYS_CONTEXT('global_cust_ctx', 'Category') category, SYS_CONTEXT('global_cust_ctx', 'Benefit Level') benefit_level FROM DUAL;
   

   The following output should appear:

   CATEGORY       BENEFIT_LEVEL
   -------------  --------------
   

   At this stage, the client session ID, 34256 is still in place, but the application context settings no longer exist. This enables you to continue the session for this user but without using the previously set application context values.

If you no longer need the components of this tutorial, then you can remove them.

1. Connect as SYS with the SYSDBA administrative privilege.

   CONNECT SYS AS SYSDBA -- Or, CONNECT SYS@mypdb AS SYSDBA
   Enter password: password
   

2. Drop the global application context.

   DROP CONTEXT global_cust_ctx;
   

   Remember that even though sysadmin_ctx created the global application context, it is owned by the SYS schema.

3. Drop the two sample users.

   DROP USER sysadmin_ctx CASCADE;
   DROP USER apps_user;

In a simple global application context process, the application uses a database user to create a user session.

The value for the context attribute of a simple global application context process can be retrieved from a SELECT statement.

Consider the application server, AppSvr, which has assigned the client identifier 12345 to client SCOTT. The AppSvr application uses the SCOTT user to create a session. (In other words, it is not a connection pool.) The value assigned to the context attribute can come from anywhere, for example, from running a SELECT statement on a table that holds the responsibility codes for users. When the application context is populated, it is stored in memory. As a result, any action that needs the responsibility code can access it quickly with a SYS_CONTEXT call, without the overhead of accessing a table. The only advantage of a global context over a local context in this case is if SCOTT were changing applications frequently and used the same context in each application.

The following steps show how the global application context process sets the client identifier for SCOTT:

1. The administrator creates a global context namespace by using the following statement:

   CREATE OR REPLACE CONTEXT hr_ctx USING hr.init ACCESSED GLOBALLY;
   

2. The administrator creates a PL/SQL package for the hr_ctx application context to indicate that, for this client identifier, there is an application context called responsibility with a value of 13 in the HR namespace.:

   CREATE OR REPLACE PROCEDURE hr.init 
    AS
    BEGIN
     DBMS_SESSION.SET_CONTEXT(
       namespace => 'hr_ctx', 
       attribute => 'responsibility', 
       value     => '13', 
       username  => 'SCOTT', 
       client_id => '12345' );
    END;
   /
   

   This PL/SQL procedure is stored in the HR database schema, but typically it is stored in the schema of the security administrator.

3. The AppSvr application issues the following command to indicate the connecting client identity each time scott uses AppSvr to connect to the database:

   EXEC DBMS_SESSION.SET_IDENTIFIER('12345');
   

4. When there is a SYS_CONTEXT('hr_ctx','responsibility') call within the database session, the database matches the client identifier, 12345, to the global context, and then returns the value 13.

5. When exiting this database session, AppSvr clears the client identifier by issuing the following procedure:

   EXEC DBMS_SESSION.CLEAR_IDENTIFIER( );
   

6. To release the memory used by the application context, AppSvr issues the following procedure:

   DBMS_SESSION.CLEAR_CONTEXT('hr_ctx', '12345');
   

   CLEAR_CONTEXT is needed when the user session is no longer active, either on an explicit logout, timeout, or other conditions determined by the AppSvr application.

You can set a global application contexts for lightweight users.

You can configure this access so that when other users log in, they cannot access the global application context.

The following steps show the global application context process for a lightweight user application. The lightweight user, robert, is not known to the database through the application.

1. The administrator creates the global context namespace by using the following statement:

   CREATE CONTEXT hr_ctx USING hr.init ACCESSED GLOBALLY;
   

2. The HR application server, AppSvr, starts and then establishes multiple connections to the HR database as the appsmgr user.

3. User robert logs in to the HR application server.

4. AppSvr authenticates robert to the application.

5. AppSvr assigns a temporary session ID (or uses the application user ID), 12345, for this connection.

6. The session ID is returned to the Web browser used by robert as part of a cookie or is maintained by AppSvr.

7. AppSvr initializes the application context for this client by calling the hr.init package, which issues the following statements:

   DBMS_SESSION.SET_CONTEXT( 'hr_ctx', 'id', 'robert', 'APPSMGR', 12345 );
   DBMS_SESSION.SET_CONTEXT( 'hr_ctx', 'dept', 'sales', 'APPSMGR', 12345 );
   

8. AppSvr assigns a database connection to this session and initializes the session by issuing the following statement:

   DBMS_SESSION.SET_IDENTIFIER( 12345 );
   

9. All SYS_CONTEXT calls within this database session return application context values that belong only to the client session.

   For example, SYS_CONTEXT('hr','id') returns the value robert.

10. When finished with the session, AppSvr issues the following statement to clean up the client identity:

    DBMS_SESSION.CLEAR_IDENTIFIER ( );
    

Even if another user logged in to the database, this user cannot access the global context set by AppSvr, because AppSvr specified that only the application with user APPSMGR logged in can see it. If AppSvr used the following, then any user session with client ID set to 12345 can see the global context:

DBMS_SESSION.SET_CONTEXT( 'hr_ctx', 'id', 'robert', NULL , 12345 );
DBMS_SESSION.SET_CONTEXT( 'hr_ctx', 'dept', 'sales', NULL , 12345 );

Setting USERNAME to NULL enables different users to share the same context.

You can query the client identifier set in the session as follows:

SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') FROM DUAL;

The following output should appear:

SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')
-------------------------------------------------
12345

A security administrator can see which sessions have the client identifier set by querying the V$SESSION view for the CLIENT_IDENTIFIER and USERNAME, for example:

COL client_identifier format a18
SELECT CLIENT_IDENTIFIER, USERNAME from V$SESSION;

The following output should appear:

CLIENT_IDENTIFIER   USERNAME
------------------  --------
12345               APPSMGR

To check the amount of global context area (in bytes) being used, use the following query:

SELECT SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY') FROM DUAL;

The following output should appear:

SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY')
----------------------------------------------
584