Global Context + Session Client Identifier = Strange Behaviour or Documentation BUG ?

Here I’ll describe some strange/interesting/ behaviour of Global Application Context connected with usage of Session Client Identifier, that is quite usually met in Application Express, J2EE Agent, Oracle Data Integrator starting from release 11.1.1.7 and many other applications that uses JDBC Connection Pooling in conjunction with setting Session Client Identifier.

So what is the problem ?

The problem is that Global Application Context doesn’t work as expected and described in documentation – attributes’ VALUES ARE NOT VISIBLEIn all mentioned applications accessing attributes’ values of Global Application Context may require additional coding, which is not mentioned in Documentation.

According to Oracle Documentation for Database releases 11.2 or 12.1 we have next comments on DBMS_SESSION.SET_CONTEXT(…)

“… the final two parameters are optional, used only in globally accessed contexts

this parameters are:
username  Database username attribute of the application context.  Default: NULL
client_id Application-specific client_id attribute of the application context (64-byte maximum).  Default: NULL

If the namespace parameter is a global context namespace, then the username parameter is matched against the current database user name in the session, and the client_id parameter is matched against the current client_id in the session. If these parameters are not set, NULL is assumed, enabling any user to see the context values.

So, according to Documentation, if we don’t specify last two parameter than user in any database session may see the context values.

Lets see what is going in the Real Life.

CREATE OR REPLACE PACKAGE my_ctx_pkg
AS
PROCEDURE set_context( p_namespace VARCHAR2,
p_attribute VARCHAR2,
p_value     VARCHAR2,
p_username  VARCHAR2 DEFAULT NULL,
p_client_id VARCHAR2 DEFAULT NULL);
END;
/
CREATE OR REPLACE PACKAGE BODY my_ctx_pkg
AS
PROCEDURE set_context( p_namespace VARCHAR2,
p_attribute VARCHAR2,
p_value     VARCHAR2,
p_username  VARCHAR2 DEFAULT NULL,
p_client_id VARCHAR2 DEFAULT NULL)
AS
BEGIN
DBMS_SESSION.SET_CONTEXT(    p_namespace,    p_attribute,    p_value,    p_username,    p_client_id);
END set_context;
END;
/
CREATE CONTEXT TEST_CONTEXT USING my_ctx_pkg ACCESSED GLOBALLY;

COL CLIENT_IDENTIFIER FOR A20
COL ATTR_VAL          FOR A20
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;
CLIENT_IDENTIFIER    ATTR_VAL
-------------------- --------------------
-- NOTHING AT THIS TIME

exec my_ctx_pkg.set_context('TEST_CONTEXT','ATTR','GLOBAL',NULL,NULL);
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;
 CLIENT_IDENTIFIER    ATTR_VAL
 -------------------- --------------------
.                     GLOBAL
--EXPECTED!

--LETS SET SESSION CLIENT IDENTIFIER
exec DBMS_SESSION.SET_IDENTIFIER(123);
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;
CLIENT_IDENTIFIER    ATTR_VAL
-------------------- --------------------
123
--INTERESTING! WHERE IS OUR VALUE ? WHICH HAVE TO BE AVAILABLE TO ALL USERS...

exec my_ctx_pkg.set_context('TEST_CONTEXT','ATTR','CLIENT_VALUE');
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;
CLIENT_IDENTIFIER    ATTR_VAL
-------------------- --------------------
123
--STOP! WHERE IS OUR VALUE ?
--LETS TRY ONCE MORE

exec my_ctx_pkg.set_context('TEST_CONTEXT','ATTR','CLIENT_VALUE+IND',NULL,p_client_id=>123);
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;
CLIENT_IDENTIFIER    ATTR_VAL
-------------------- --------------------
123                  CLIENT_VALUE+IND
--WE CAUGHT IT

--LETS CLEAR CLIENT IDENTIFIER
exec DBMS_SESSION.CLEAR_IDENTIFIER();
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;
 CLIENT_IDENTIFIER    ATTR_VAL
 -------------------- --------------------
.                     CLIENT_VALUE
--FUNNY! NOW WE SEE VALUE THAT WE HAVE SET WHEN WE HAD Client_Identifier=123
--  BUT WHICH WE WASN'T ABLE TO SEE UNTIL Cleared Identifier

Conclusion:

  • after setting Session Client Identifier(DBMS_SESSION.SET_IDENTIFIER) behaviour of Global Context becomes UNEXPECTED, almost looks like mix of Global and non-Global Context – tightly bound to the value of Session Client Identifier
  • if You have set Client Identifier and don’t use client_id during calling SET_CONTEXT, than you will modify Global Context, which is not seen until you RESET Client Identifier with DBMS_SESSION.CLEAR_IDENTIFIER,  so use client_id when calling SET_CONTEXT.
  • to access values of Global Context inside JDBC pooled connection that use DBMS_SESSION.SET_IDENTIFIER you have to: save current value of Client Identifier -> call DBMS_SESSION.CLEAR_IDENTIFIER -> access Global Context -> return Client Identifier back with DBMS_SESSION.SET_IDENTIFIER
  • if some part of application depends on values of Global Context, for example: VPD(Virtual Private Database) which RLS(Row Level Security) policies are based on values of Global Context, than when you use Apex, ODI 11.1.1.7, or other client that uses DBMS_SESSION.SET_IDENTIFIER possible in conjunction with JDBC Connection Pooling, then you may face troubleshooting nightmare…

References:

  • 413976.1 Query Using Global Context In Application Express Returns Incorrect Result
  • 1618599.1 ODI Standalone And J2EE Agents Do Not Return Data When Oracle Database Context (SYS_CONTEXT) Is Used
    • BUG: 18089930 DATA NOT SELECTED FROM VIEWS USING DB CONTEXTS WHEN EXECUTED WITH J2EE AGENT

Status: Closed, Not a Bug

Advertisements

3 thoughts on “Global Context + Session Client Identifier = Strange Behaviour or Documentation BUG ?

  1. how does the above example behaves if we DO NOT create a globally accessible context like :

    CREATE CONTEXT TEST_CONTEXT USING my_ctx_pkg;

    only.

    2ndly, if i create a test_context do i need it to create once like other database objects does it stays consistent ?

  2. ashifzubair,
    sorry, it’s not Q&A forum
    1) you have to test it yourself, especially if you want to understand what is going on…
    2) you have to read documentation, understand it and practice

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s