How-to set the current database schema of an application using a global context

0

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.

About Author

Harry Dragstra is Senior Oracle DBA. He is specialized in Oracle RAC, Oracle Virtualization, and has a a strong interest in APEX development and PL/SQL.

Comments are closed.