Oracle Application Context详解(原创)

Step 2: Create the Global Application Context

1. Log on as the security administratorsysadmin_ctx.

   CONNECT sysadmin_ctx
   Enter password: password
   

2. Create thecust_ctxglobal application context.

   CREATE CONTEXT global_cust_ctx USING cust_ctx_pkg ACCESSED GLOBALLY;
   

   Thecust_ctxcontext is created and associated with the schema of the security administratorsysadmin_ctx. However,theSYSschema owns the application context.

Step 3: Create a Package for the Global Application Context

1. Assysadmin_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,seeExample 6-12.

2. GrantEXECUTEprivileges on thecust_ctx_pkgpackage 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,userapps_user.

   CONNECT apps_user
   Enter password: password
   

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

3. As userEXEC 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 Step2.)

4. Enter the followingSELECT SYS_CONTEXTstatement 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
   

Whatapps_userhas done here,within the client session 34256,is set a global application context on behalf of a nondatabase user. This context sets theCategoryandBenefit LevelDBMS_SESSION.SET_CONTEXT attributesto beGold PartnerandHighest,respectively. The context exists only for userapps_userwith client ID 34256. When a nondatabase user logs in,behind the scenes,he or she is really logging on as the connection pool userapps_user. Hence,monospace; font-size:1em; white-space:pre">Highestcontext 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 userapps_usersession. If the user runs theSELECT SYS_CONTEXTstatement,the following output appears:

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

Next,try the following test:

1. As userEXEC sysadmin_ctx.cust_ctx_pkg.clear_hr_session(34256);

2. Check the global application context settings again.

   SELECT SYS_CONTEXT('global_cust_ctx','Benefit Level') benefit_level FROM dual;
   
   CATEGORY       BENEFIT_LEVEL
   -------------  --------------
   

   Becauseapps_userhas 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','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 onlyoncefor this user,but the client session ID must be seteach timethe 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','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.