Application Contexts

下面主要讲了通过何种方式来设置会话的context及userenv context下的client_identifier, 具体应用没说

An application context is a set of name-value pairs that Oracle Database stores in memory. You can then use this information to either permit or prevent the user from accessing data through the application

使用DBMS_SESSION.SET_CONTEXT来设置key-value，另外对于基于客户端会话的application context还可通过OCI设置

To set the values for a secure application context, you must create a PL/SQL package procedure that uses the DBMS_SESSION.SET_CONTEXT procedure. In fact, this is the only way that you can set application context values if the context is not marked INITIALIZED EXTERNALLY or INITIALIZED GLOBALLY. For client-session based application contexts, another way to set the application context is to use Oracle Call Interface (OCI) calls.

对于CDB可以创建common application conntext:

You can create application contexts in a multitenant environment by setting the CONTAINER clause in the CREATE CONTEXT SQL statement.

The PL/SQL package that you create to manage a common application context must be a common PL/SQL package. That is, it must exist in the application root or CDB root.

During an unplug operation, a common application context retains its common semantics, so that later on, if the PDB is plugged into another CDB where a common application context with the same name exists, it would continue to behave like a common object. If a PDB is plugged into an application container or a system container, where the same common application context does not exist, then it behaves like a local object.

There are three general categories of application contexts:

1. Database session-based application contexts. 

会话级别，即每个会话不同，因此存于UGA（dedicate服务器UGA在PGA中），当会话终止时content的生命周期也结束了，与用户认证类似，session-based application context分为三种初始content方法：

Initialized locally：Initializes the application context locally, to the session of the user.

Initialized externally：Initializes the application context from an Oracle Call Interface (OCI) application, a job queue process, or a connected user database link.

Initialized globally：Uses attributes and values from a centralized location, such as an LDAP directory.

2. Global application contexts. 会话共享，因此存于SGA，只要SGA不消亡就一直存在

This type retrieves data that is stored in the System Global Area (SGA) so that it can be used for applications that use a sessionless model, such as middle-tier applications in a three-tiered architecture. A global application context is useful if the session context must be shared across sessions, for example, through connection pool implementations.

1. Client session-based application contexts. 

由客户端OCI设置context，信息仍存于UGA中，相比session-based application context，它不用在服务端进行设置

This type uses Oracle Call Interface functions on the client side to set the user session data, and then to perform the necessary security checks to restrict user access.

Create context语法：

Create or replace context cont_name using package clause;

Clause：

相关视图：

1. DBA_CONTEXT
2. DBA_POLICY_CONTEXTS：Describes all driving contexts in the database that were added by the DBMS_RLS.ADD_POLICY_CONTEXT procedure.
3. SESSION_CONTEXT：Describes the context attributes and their values set for the current session.
4. V$CONTEXT：Lists set attributes in the current PDB session. Users do not have access to this view unless you grant the user the SELECT privilege on it.
5. V$SESSION：Lists detailed information about each current PDB session. Users do not have access to this view unless you grant the user the SELECT privilege on it.

• Using Database Session-Based Application Contexts

This type of application context uses a PL/SQL procedure within Oracle Database to retrieve, set, and secure the data it manages.

Oracle Database sets the values, and then when the user exits the session, automatically clears the application context values stored in cache.

The advantage of having Oracle Database manage the application context is that you can centralize the application context management. Any application that accesses this database will need to use this application context to permit or prevent user access to that application. This provides benefits both in improved performance and stronger security.

下面为Initialized locally方式使用session-based application contexts示例：

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

CONNECT SYS@hrpdb AS SYSDBA

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;

GRANT CREATE SESSION TO LOZER IDENTIFIED BY password;

ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY password;

Step 2: Create the Database Session-Based Application Context

CONNECT sysadmin_ctx@hrpdb

CREATE CONTEXT empno_ctx USING set_empno_ctx_pkg CONTAINER = CURRENT;

set_empno_ctx_pkg is the package (which does not need to exist when you create the context) that sets attributes for the empno_ctx namespace. You cannot create a context called CLIENTCONTEXT. This word is reserved for use with client session-based application contexts.

SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE ='CONTEXT';

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

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

1. USERENV为数据库自带CONTEXT，不能更改只能查询
2. 通过sys_context函数来获取context信息:SYS_CONTEXT ('namespace','parameter'[,length])

Length:默认返回值为varchar,且最大长度为256bytes,你可以设置最大为4000bytes

1. 通过dbms_session.set_context设置context信息：

DBMS_SESSION.SET_CONTEXT (

   namespace VARCHAR2,

   attribute VARCHAR2,

   value     VARCHAR2,

   username  VARCHAR2,

   client_id VARCHAR2);

注：username表示对哪些数据库用户生效，client_id表示会话的identifier属性，这个属性是由应用设置的，详细见下面的Global Application Contexts。对于Database Session-Based Application Context两个值均使用默认null，表示对所有username所有identifier限制即可。

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;

/

如果logon trigger报错则用户将不能登陆，使用exception可以捕获报错：

EXCEPTION ... WHEN_NO_DATA_FOUND adds a WHEN NO_DATA_FOUND system exception to catch any no data found errors that may result from the SELECT statement. 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 a logon trigger for set_empno_ctx_pkg.set_empno package procedure.

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

CONNECT lozer@hrpdb

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

Step 6: Remove the Components of This Tutorial

CONNECT SYS@hrpdb AS SYSDBA

DROP USER sysadmin_ctx CASCADE;

DROP CONTEXT empno_ctx;

（二）Global Application Contexts

Step 1: Create User Accounts

CONNECT SYS@my_pdb AS SYSDBA

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;

CREATE USER apps_user IDENTIFIED BY password CONTAINER = CURRENT;

GRANT CREATE SESSION TO apps_user;

Step 2: Create the Global Application Context

CONNECT sysadmin_ctx@hrpdb

CREATE CONTEXT global_cust_ctx USING set_cust_ctx ACCESSED GLOBALLY;

A global application context 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.

Globally context会存于SGA中

Oracle Database stores the global application context information in the System (sometimes called "Shared") Global Area (SGA) so that it can be used for applications that use a sessionless model, such as middle-tier applications in a three-tiered architecture.

Oracle Database initializes the global application context once, rather than for each user session. This improves performance, because connections are reused from a connection pool. You can clear a global application context value by running the ALTER SYSTEM FLUSH GLOBAL_CONTEXT SQL statement.

In an Oracle RAC environment, whenever a global application context is loaded or changed, it is visible only to the existing active instances.

Be aware that setting a global application context value in an Oracle RAC environment has performance overhead of propagating the context value consistently to all Oracle RAC instances.

If you flush the global application context (using the ALTER SYSTEM FLUSH GLOBAL_CONTEXT SQL statement) in one Oracle RAC instance, then all the global application context is flushed in all other Oracle RAC instances as well.

Step 3: Create a Package for the Global Application Context

CREATE OR REPLACE PROCEDURE set_cust_ctx

 AS

 BEGIN

  DBMS_SESSION.SET_CONTEXT(

    namespace => 'global_cust_ctx',

    attribute => 'responsibility',

    value     => '13',

    username  => 'app_user',

    client_id => '12345' );

 END;

/

exec set_cust_ctx;

关于DBMS_SESSION.SET_CONTEXT的USERNAME与CLIENT_ID参数：

1. username set to NULL, client_id set to NULL

This combination enables all users to access the application context. These settings are also used for database session-based application contexts

1. username set to a value, client_id set to NULL

This combination enables an application context to be accessed by multiple sessions, as long as the username setting is the same throughout. Ensure that the user name specified is a valid database user.

1. username set to NULL,client_id set to a value

This combination enables an application to be accessed by multiple user sessions, as long as the client_id parameter is set to the same value throughout. This enables sessions of all users to see the application context values.

1. username set to a value, client_id set to a value

This combination enables the following two scenarios:

1. Lightweight users: If the user does not have a database account, the username specified is a connection pool owner. The client_id setting is then associated with the nondatabase user who is logging in.
2. Database users:If the user is a database user, this combination can be used for stateless Web sessions.

Setting the username parameter in the SET_CONTEXT procedure to USER calls the Oracle Database-supplied USER function. The USER function specifies the session owner from the application context retrieval process and ensures that only the user who set the application context can access the context.

Step 4: Test the Newly Created Global Application Context

conn app_user@hrpdb

SELECT SYS_CONTEXT('userenv', 'client_identifier') FROM DUAL;    --空

SELECT SYS_CONTEXT('hr_ctx', 'responsibility') FROM DUAL; --空

EXEC DBMS_SESSION.SET_IDENTIFIER('12345');

SELECT SYS_CONTEXT('userenv', 'client_identifier') FROM DUAL;    --12345

SELECT SYS_CONTEXT('global_cust_ctx', 'responsibility') FROM DUAL;  --13

关于DBMS_SESSION.SET_IDENTIFIER：

用于设置USERENV context中的client_identifier值, 查看：

SELECT SYS_CONTEXT('userenv', 'client_identifier') FROM DUAL;

SELECT CLIENT_IDENTIFIER from V$SESSION;

SELECT value FROM session_context WHERE attribute='CLIENT_IDENTIFIER';

client_identifier与数据库无关，它是由应用服务生成的(通过OCI或DBMS_SESSION.SET_IDENTIFIER设置)，这样在使用dbms_session.set_context时使用client_id即可区别对待不同应用

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.

Remember that the client identifier is controlled by the middle-tier application, and once set, it remains open until it is cleared.

清理identifier方法：DBMS_SESSION.CLEAR_IDENTIFIER;

Step 5: Remove the Components of This Tutorial

CONNECT SYS@mypdb AS SYSDBA

DROP CONTEXT global_cust_ctx;

DROP USER sysadmin_ctx CASCADE;

DROP USER apps_user;

（三）Using Client Session-Based Application Contexts

Oracle Call Interface (OCI) functions can set and clear the User Global Area (UGA) user session information.

The advantage of this type of application context in a session-based application context is that an individual application can check for specific nondatabase user session data, rather than having the database perform this task.

Another advantage is that the calls to set the application context value are included in the next call to the server, which improves performance.

However, be aware that application context security is compromised with a client session-based application context: any application user can set the client application context, and no check is performed in the database.

You configure the client session-based application context for the client application only. You do not configure any settings on the database server to which the client connects. Any application context settings in the database server do not affect the client session-based application context.

To configure a client session-based application context, use the OCIAppCtxSet OCI function. A client session-based application context uses the CLIENTCONTEXT namespace, updatable by any OCI client or by the existing DBMS_SESSION package for application context. Oracle Database performs no privilege or package security checks for this type.

The CLIENTCONTEXT namespace enables a single application transaction to both change the user context information and use the same user session handle to service the new user request. You can set or clear individual values for attributes in the CLIENTCONTEXT namespace, or clear all their values.

1. An OCI client uses the OCIAppCtx function to set variable length data for the namespace, called OCISessionHandle. The OCI network single, round-trip transport sends all the information to the server in one round-trip. On the server side, you can query the application context information by using the SYS_CONTEXT SQL function on the namespace.

2) A JDBC client uses the oracle.jdbc.internal.OracleConnection function to achieve the same purposes.

Any user can set, clear, or collect the information in the CLIENTCONTEXT namespace, because it is not protected by package-based security.

1. Setting a Value in the CLIENTCONTEXT Namespace

To set a value in the CLIENTCONTEXT namespace, use the OCIAppCTXSet command, in the following syntax:

err = OCIAppCtxSet((void *) session_handle,(dvoid *)"CLIENTCONTEXT",(ub4) 13,

                   (dvoid *)attribute_name, length_of_attribute_name

                   (dvoid *)attribute_value, length_of_attribute_value, errhp,

                   OCI_DEFAULT);

In this specification:

session_handle represents the OCISessionHandle namespace.

attribute_name is the name of the attribute. For example, responsibility, with a length of 14.

attribute_value is the value of the attribute. For example, manager, with a length of 7.

1. Retrieving the CLIENTCONTEXT Namespace

To retrieve the CLIENTCONTEXT namespace, use the OCIStmtExecute call with either of the following statements:

SELECT SYS_CONTEXT('CLIENTCONTEXT', 'attribute-1') FROM DUAL;

SELECT VALUE FROM SESSION_CONTEXT WHERE NAMESPACE='CLIENTCONTEXT' AND ATTRIBUTE='attribute-1';

The attribute-1 value can be any attribute value that has already been set in the CLIENTCONTEXT namespace. Oracle Database only retrieves the set attribute; otherwise, it returns NULL. Typically, you set the attribute by using the OCIAppCtxSet call. In addition, you can embed a DBMS_SESSION.SET_CONTEXT call in the OCI code to set the attribute value.

1. Example: Retrieving a Client Session ID Value for Client Session-Based Contexts

The OCI OCIStmtExecute call can retrieve client session ID values for client session-based contexts.

oratext    clientid[31];

OCIDefine  *defnp1 = (OCIDefine *) 0;

OCIStmt    *statementhndle;

oratext    *selcid = (oratext *)"SELECT SYS_CONTEXT('CLIENTCONTEXT',

            attribute) 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);

In this example:

oratext, OCIDefine, OCIStmt, and oratext create variables to store the client session ID, reference call for OCIDefine, the statement handle, and the SELECT statement to use.

OCIStmtPrepare prepares the statement selcid for execution.

OCIDefineByPos defines the output variable clientid for client session ID.

OCIStmtExecute executes the statement in the selcid variable.

printf prints the formatted output for the retrieved client session ID.

1. Clearing a Setting in the CLIENTCONTEXT Namespace

To clear a setting in CLIENTCONTEXT, set the value to NULL or to an empty string by using one of the following commands:

The following command sets the empty string to zero:

(void) OCIAppCtxSet((void *) session_handle, (dvoid *)"CLIENTCONTEXT", 13,

                   (dvoid *)attribute_name, length_of_attribute_name,

                   (dvoid *)0, 0,errhp

                   OCI_DEFAULT);

This following command sets the empty string to a blank value:

(void) OCIAppCtxSet((void *) session_handle, (dvoid *)"CLIENTCONTEXT", 13

                   (dvoid *)attribute_name, length_of_attribute_name,

                   (dvoid *)"", 0,errhp,

                   OCI_DEFAULT);

1. Clearing All Settings in the CLIENTCONTEXT Namespace

To clear the namespace, use the OCIAppCtxClearAll command in the following form:

err = OCIAppCtxClearAll((void *) session_handle,

                       (dvoid *)"CLIENTCONTEXT", 13,

                        errhp,                        OCI_DEFAULT);