概述

Application Context是内存中的一组name-value对，application context从属于某个命名空间(namespace)。
用 户只能通过一个自定义procedure调用dbms_session.set_context来设置application context的值。用户使用sys_context(<namespace>,<name>)来获取某个 application context的值。
Application context分为三种

  • dabase session-based application context，又称为local application context。Local application context存储在UGA中，dedicated server mode, UGA在PGA中，shared server mode，UGA在SGA中。local application context是会话/server process级别的。只有本会话能够访问。当会话/server process终止时，local application context的生命周期也结束了。另一篇博客Oracle VPD http://blog.csdn.net/chncaesar/article/details/18550029 给出了一个local application context的例子。
  • global application context，Global application context存储在SGA中，只要SGA不消亡，global application context就一直存在。global application context常常用于跨会话，与会话无关的场景。dbms_session.set_context有两个默认值为NULL的参数：
        username
        client_id
    username = null , client_id=null。所有用户都能访问。
    username = null, client_id=<client_id>。 只要session 的client_id =<client_id>，而不管username，都能访问。
    username = <database username>, client_id=null。只要session使用指定的oracle schema登陆，不管client_id，都能访问。
    username = <database username>, client_id=<client_id>. 常用于statless web session ，如http。
    username = <non-database username>, client_id=<client_id> . 该username将用于数据库连接池的owner。
  • client session-based application context。通常被OCI使用，存储在客户端内存，而不是Oracle服务器端，也由OCI程序管理。

实例

例子1： 所有用户都能访问的application context

CREATE OR REPLACE CONTEXT global_hr_ctx USING hr_ctx_pkg ACCESSED GLOBALLY; 
CREATE OR REPLACE PACKAGE hr_ctx_pkg  
   AS   
    PROCEDURE set_hr_ctx(sec_level IN VARCHAR2);  
    PROCEDURE clear_hr_ctx; 
   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_ctx
   as
 begin
   dbms_session.clear_context('global_hr_ctx', 'job_role');
 end clear_hr_ctx;
 end;
 /

SQL> BEGIN
  2   hr_ctx_pkg.set_hr_ctx('clerk');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT SYS_CONTEXT('global_hr_ctx', 'job_role') job_role FROM DUAL;
JOB_ROLE
------------------------------------
clerk

例子2: 跨session/application，但是使用同一个数据库schema的application context。

Setting a Global Context for Database Users Who Move Between Applications
CREATE OR REPLACE PACKAGE hr_ctx1_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_ctx1_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',
       value      => sec_level,
       username   => user_name);    --- 这里的user_name表示用户为数据库用户
      END set_hr_ctx;
   PROCEDURE clear_hr_context
    AS
     BEGIN
      DBMS_SESSION.CLEAR_CONTEXT('global_hr_ctx');
     END clear_hr_context;
  END hr_ctx1_pkg;
 /
例3 Tutorial: Creating a Global Application Context That Uses a Client Session ID


Step 1: Create User Accounts


You must create two users for this example: a security administrator who will manage the application context and its package, and a user account that owns the connection pool.

In this tutorial:

  1. Log on to SQL*Plus as ​​SYS​​ and connect using ​​AS SYSDBA​​.
sqlplus sys as sysdba
Enter password: password
  1. Create the ​​sysadmin_ctx​​ account, who will administer the global application context.
GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE TO sysadmin_ctx IDENTIFIED BY password;

GRANT EXECUTE ON DBMS_SESSION TO sysadmin_ctx;

Replace ​​password​​​ with a password that is secure. See ​​"Minimum Requirements for Passwords"​​ for more information.

  1. Create the database account ​​apps_user​​, who will own the connection pool.
GRANT CREATE SESSION TO apps_user IDENTIFIED BY password;

Replace ​​password​​​ with a password that is secure. See ​​"Minimum Requirements for Passwords"​​ for more information.



Step 2: Create the Global Application Context

  1. Log on as the security administrator ​​sysadmin_ctx​​.
CONNECT sysadmin_ctx
Enter password: password
  1. 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.



Step 3: Create a Package for the Global Application Context

  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 6-12​​.

  1. 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;



Step 4: Test the Global Application Context

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

  1. Log on to SQL*Plus as the connection pool owner, user ​​apps_user​​.
CONNECT apps_user
Enter password: password
  1. When the connection pool user logs on, the application sets the client session identifier as follows:BEGIN sysadmin_ctx.cust_ctx_pkg.set_session_id(34256); END; /You can test and check the value of the client session identifier as follows:
  1. Connect to SQL*Plus as the connection pool user ​​apps_user​​.
  2. Set the session ID: EXEC sysadmin_ctx.cust_ctx_pkg.set_session_id(34256);
  3. Check the session ID: SELECT SYS_CONTEXT('userenv', 'client_identifier') FROM dual; The following output should appear: SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') -------------------------------------------------- 34256
  1. As user ​​apps_user​​, set the global application context as follows: EXEC sysadmin_ctx.cust_ctx_pkg.set_cust_ctx('Category', 'Gold Partner'); EXEC sysadmin_ctx.cust_ctx_pkg.set_cust_ctx('Benefit Level', 'Highest'); (In a real-world scenario, the middle-tier application would set the global application context values, similar to how the client session identifier was set in Step ​​2​​.)
  2. Enter the following ​​SELECT SYS_CONTEXT​​ statement to check that the settings were successful: col category format a13 col benefit_level format a14 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

What ​​apps_user​​​ has done here, within the client session 34256, is set a global application context on behalf of a nondatabase user. This context sets the ​​Category​​​ and ​​Benefit Level​​​ ​​DBMS_SESSION.SET_CONTEXT attributes​​​ to be ​​Gold Partner​​​ and ​​Highest​​​, respectively. The context exists only for user ​​apps_user​​​ with client ID 34256. When a nondatabase user logs in, behind the scenes, he or she is really logging on as the connection pool user ​​apps_user​​​. Hence, the ​​Gold Partner​​​ and ​​Highest​​ context values are available to the nondatabase user.

Suppose the user had been a database user and could log in without using the intended application. (For example, the user logs in using SQL*Plus.) Because the user has not logged in through the connection pool user ​​apps_user​​​, the global application context appears empty to our errant user. This is because the context was created and set under the ​​apps_user​​​ session. If the user runs the ​​SELECT SYS_CONTEXT​​ statement, the following output appears:



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



Next, try the following test:

  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
  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.



3个例子都提供了clear_hr_context 存储过程。这是因为global application context常驻SGA，需要用户使用完毕就清理，以免信息泄露。

Tutorial: Creating and Using a Database Session-Based Application Context



Step 1: Create User Accounts and Ensure the User SCOTT Is Active

  1. Log on as user ​​SYS​​ and connect using the ​​AS SYSDBA​​ privilege.
sqlplus sys as sysdba
Enter password: password
  1. Create the ​​sysadmin_ctx​​ account, who will administer the database session-based application context.
GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO sysadmin_ctx IDENTIFIED BY password;
GRANT SELECT ON HR.EMPLOYEES TO sysadmin_ctx;
GRANT EXECUTE ON DBMS_SESSION TO sysadmin_ctx;

Replace ​​password​​​ with a password that is secure. See ​​"Minimum Requirements for Passwords"​​ for more information.

  1. 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. See ​​"Minimum Requirements for Passwords"​​ for more information.

  1. The sample user ​​SCOTT​​ will also be used in this tutorial, so query the ​​DBA_USERS​​ data dictionary view to ensure that ​​SCOTT​​ is not locked or expired.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.



Step 2: Create the Database Session-Based Application Context

  1. Log on to SQL*Plus as ​​sysadmin_ctx​​.
CONNECT sysadmin_ctx
Enter password: password
  1. 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.



Step 3: Create a Package to Retrieve Session Data and Set the Application Context

​​Example 6-7​​​ shows how to create the package you need to retrieve the session data and set the application context. Before creating the package, ensure that you are still logged on as user ​​sysadmin_ctx​​​. (You can copy and paste this text by positioning the cursor at the start of ​​CREATE OR REPLACE​​ in the first line.)



Example 6-7 Package to Retrieve Session Data and Set a Database Session Context



1234567891011121314151617

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;/



This package creates a procedure called ​​set_empno​​ that performs the following actions:

  • Line 8: Declares a variable, ​​emp_id​​, to store the employee ID for the user who logs on. It uses the same data type as the ​​EMPLOYEE_ID​​ column in ​​HR.EMPLOYEES​​.
  • Line 10: Performs a ​​SELECT​​ statement to copy the employee ID that is stored in the ​​employee_id​​ column data from the ​​HR.EMPLOYEES​​ table into the ​​emp_id​​ variable.
  • Line 11: Uses a ​​WHERE​​ clause to find all employee IDs that match the email account for the session user. The ​​SYS_CONTEXT​​ function uses the predefined ​​USERENV​​ context to retrieve the user session ID, which is the same as the ​​email​​ column data. For example, the user ID and email address for Lisa Ozer are both the same: ​​lozer​​.
  • Line 12: Uses the ​​DBMS_SESSION.SET_CONTEXT​​ procedure to set the application context:
  • ​​'empno_ctx'​​​: Calls the application context ​​empno_ctx​​. Enclose ​​empno_ctx​​ in single quotes.
  • ​​'employee_id'​​​: Creates the attribute value of the ​​empno_ctx​​ application context name-value pair, by naming it ​​employee_id​​. Enclose ​​employee_id​​ in single quotes.
  • ​​emp_id​​​: Sets the value for the ​​employee_id​​ attribute to the value stored in the ​​emp_id​​ variable. The ​​emp_id​​ variable was created in Line 8 and the employee ID was retrieved in Lines 10–11.

To summarize, the ​​set_empno_ctx_pkg.set_empno​​​ procedure says, "Get the session ID of the user and then match it with the employee ID and email address of any user listed in the ​​HR.EMPLOYEES​​ table."

  • Lines 13–14: Add a ​​WHEN NO_DATA_FOUND​​​ system exception to catch any ​​no data found​​​ errors that may result from the ​​SELECT​​​ statement in Lines 10–11. Without this exception, the package and logon trigger will work fine and set the application context as needed, but then any non-system administrator users other than the users listed in the ​​HR.EMPLOYEES​​ table will not be able to log in to the database. Other users should be able to log in to the database, assuming they are valid database users. Once the application context information is set, then you can use this session information as a way to control user access to a particular application.



Step 4: Create a Logon Trigger for the Package

As user ​​sysadmin_ctx​​, create the following trigger:



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



Step 5: Test the Application Context

  1. Log on as user ​​lozer​​.
CONNECT lozer
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

  1. Log on as user ​​SCOTT​​.
CONNECT SCOTT
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 --------------------------------------------------------

From here, the application can use the user session information to determine how much access the user can have in the database. You can use Oracle Virtual Private Database to accomplish this. See ​​Chapter 7, "Using Oracle Virtual Private Database to Control Data Access,"​​ for more information.



Step 6: Remove the Components for This Tutorial

  1. Log on as ​​SYS​​​ and connect using ​​AS SYSDBA​​.
CONNECT SYS/AS SYSDBA
Enter password: password
  1. Drop the users ​​sysadmin_ctx​​​ and ​​lozer​​: DROP USER sysadmin_ctx CASCADE; DROP USER lozer;
  2. 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.
  3. If you want, lock and expire ​​SCOTT​​, unless other users want to use this account: ALTER USER SCOTT PASSWORD EXPIRE ACCOUNT LOCK;