Question:
We have customers who want to work with different database schemas and we want to determine dynamically – depending on a choice during or after login – which database schema the application in a given session should use. Is it possible to set this up in the database?
Answer:
Yes, with a global context, available to all sessions in the instance, and parameter client id to distinguish between customers.
Question:
How?
Answer:
Like in the following example, using a 11gR2 XE database.
CREATE USERS
-- with sys as sysdba: create admin user "sys_gctx" for the global context conn sys/xe@localhost:1521/xe as sysdba CREATE USER SYS_GCTX IDENTIFIED BY xe DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; GRANT CREATE SESSION , CREATE ANY CONTEXT , CREATE PROCEDURE , CREATE TRIGGER , ADMINISTER DATABASE TRIGGER TO SYS_GCTX; GRANT EXECUTE ON DBMS_SESSION TO SYS_GCTX; -- with sys as sysdba: create application user "app_usr" CREATE USER APP_USR IDENTIFIED BY xe DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; GRANT CREATE SESSION, SELECT ANY TABLE TO APP_USR;
CREATE AND FILL GLOBAL CONTEXT
-- with sys_gctx: create the global context conn sys_gctx/xe@localhost:1521/xe CREATE CONTEXT GCTX_SCHEMA USING SET_GCTX_SCHEMA ACCESSED GLOBALLY; -- with sys_gctx: create a procedure to set the global context CREATE OR REPLACE PROCEDURE SET_GCTX_SCHEMA ( P_schema IN VARCHAR2, P_client IN VARCHAR2 ) AS BEGIN DBMS_SESSION.SET_CONTEXT ( NAMESPACE => 'GCTX_SCHEMA', ATTRIBUTE => 'SCHEMA_NAME', VALUE => P_schema, CLIENT_ID => P_client ); END; / -- with sys_gctx: fill the global context ( stays in SGA ) -- at startup of the application, or with -- an "after startup of database" trigger BEGIN SET_GCTX_SCHEMA ('ALTER SESSION SET current_schema=HR','CLIENT_A'); SET_GCTX_SCHEMA ('ALTER SESSION SET current_schema=APEX_040000','CLIENT_B'); END; /
SETUP CLIENT_A, AND CONFIRM THE CHANGE OF CURRENT SCHEMA
-- with app_usr: setup the client as client_a and set the context value for this client conn app_usr/xe@localhost:1521/xe begin dbms_session.set_identifier ('CLIENT_A'); execute immediate sys_context( 'GCTX_SCHEMA', 'SCHEMA_NAME'); end; / -- with app_usr: check if the current schema of this "app_usr" session is indeed HR select sys_context('USERENV', 'session_user') from dual; select sys_context('USERENV', 'current_schema') from dual;
SETUP CLIENT_B, AND CONFIRM THE CHANGE OF CURRENT SCHEMA
-- with app_usr: setup the client as client_b and set the context value for this client begin dbms_session.set_identifier ('CLIENT_B'); execute immediate sys_context( 'GCTX_SCHEMA', 'SCHEMA_NAME'); end; / -- with app_usr: check if the current schema of this "app_usr" session is indeed APEX_040000 select sys_context('USERENV', 'session_user') from dual; select sys_context('USERENV', 'current_schema') from dual;
REMOVE CLUTTER
-- with sys as sysdba -- remove users and context conn sys/xe@localhost:1521/xe as sysdba drop user SYS_GCTX cascade; drop user APP_USR cascade; drop context GCTX_SCHEMA;
With many thanks to Lucas Jellema who posed a very similar question on our Yammer site, with just one distinction… not
“Is it possible to set this up in the database?” but “Is it possible to set this up in ADF BC?“, and who already published on global contexts on this site here.