Oracle Application Context详解(原创)

最新推荐文章于 2021-04-05 01:51:26 发布

czmmiao

最新推荐文章于 2021-04-05 01:51:26 发布

阅读量254

点赞数

分类专栏： Oracle日常管理文章标签：数据库

Oracle日常管理专栏收录该内容

141 篇文章 2 订阅

订阅专栏

概述

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:

Log on to SQL*Plus as SYS and connect using AS SYSDBA.
```
sqlplus sys as sysdba
Enter password: password
```
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.
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

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

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.

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.

Log on to SQL*Plus as the connection pool owner, user apps_user.
```
CONNECT apps_user
Enter password: password
```
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
```
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.)

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:

As user apps_user, clear the session ID.

EXEC sysadmin_ctx.cust_ctx_pkg.clear_hr_session(34256);

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.

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

Log on as user SYS and connect using the AS SYSDBA privilege.
```
sqlplus sys as sysdba
Enter password: password
```

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.

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

Log on to SQL*Plus as sysadmin_ctx.

CONNECT sysadmin_ctx
Enter password: password

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

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

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

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

Log on as SYS and connect using AS SYSDBA.

CONNECT SYS/AS SYSDBA
Enter password: password

Drop the users sysadmin_ctx and lozer:

DROP USER sysadmin_ctx CASCADE;
DROP USER lozer;

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.
If you want, lock and expire SCOTT, unless other users want to use this account:
```
ALTER USER SCOTT PASSWORD EXPIRE ACCOUNT LOCK; 
```