The reason was simple; the context is a database level object and does not have any owner and is not associated to one scheme.
The second issue (also obvious) is that a DROP USER... CASCADE... does not remove a context, simply because it is not associated with a specific user so it is not dropped.
Let me create a simple example. First create a user and a package to control the context:
SQL> create user context1 identified by context1;
User created.
SQL> grant create session, create any context, create procedure to context1;
Grant succeeded.
SQL>
Note that we must grant CREATE ANY CONTEXT as there is no CREATE CONTEXT system privilege because contexts are global? Next connect as the context1 user and create the simple package that will be associated with the context:
SQL> connect context1/context1@//192.168.56.33:1539/xepdb1
Connected.
SQL> create or replace package contextset as
2 procedure set_user(
3 pv_username varchar2
4 );
5 end;
6 /
Package created.
SQL>
SQL> create or replace package body contextset as
2
3 procedure set_user(
4 pv_username varchar2
5 ) is
6 begin
7 dbms_session.set_context(
8 namespace => 'appcontext',
9 attribute => 'username',
10 value => pv_username
11 );
12 end;
13
14 end;
15 /
Package body created.
SQL>
Now we can create context for this user:
SQL> create context appcontext using contextset;
Context created.
SQL>
Now, finally we can set the context and retrieve it:
SQL> exec contextset.set_user('CONTEXT1');
PL/SQL procedure successfully completed.
SQL> select sys_context('appcontext','username') from dual;
SYS_CONTEXT('APPCONTEXT','USERNAME')
--------------------------------------------------------------------------------
CONTEXT1
SQL>
This works as expected. What if we then create a user CONTEXT2 and create the same context using CONTEXT2 version of the package:
SQL> create user context2 identified by context2;
User created.
SQL> grant create session, create any context, create procedure to context2;
Grant succeeded.
SQL>
Connect to context2 and create the context2 version of the package for the context:
SQL> connect context2/context2@//192.168.56.33:1539/xepdb1
Connected.
SQL> create or replace package contextset as
2 procedure set_user(
3 pv_username varchar2
4 );
5 end;
6 /
Package created.
SQL>
SQL> create or replace package body contextset as
2
3 procedure set_user(
4 pv_username varchar2
5 ) is
6 begin
7 dbms_session.set_context(
8 namespace => 'appcontext',
9 attribute => 'username',
10 value => pv_username
11 );
12 end;
13
14 end;
15 /
Package body created.
SQL>
Create the context and set and read it:
SQL> create context appcontext using contextset;
create context appcontext using contextset
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL>
And if we try and set the context we get:
SQL> exec contextset.set_user('CONTEXT2');
BEGIN contextset.set_user('CONTEXT2'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 141
ORA-06512: at "CONTEXT2.CONTEXTSET", line 7
ORA-06512: at line 1
SQL>
A context is global in the database and we cannot create the same context per user. We have a number of possible solutions and which to use / choose depends on what is needed in the application.
The first is that each user can have their own contexts but they need unique names. The second is that we can create the context as one user and its global but then create the access package as one user and allow users to execute it and then when setting the context qualify the package and use it from the second user.
Connect back to the first user and grant execute to context2 and test again:
SQL> connect context1/context1@//192.168.56.33:1539/xepdb1
Connected.
SQL> grant execute on contextset to context2;
Grant succeeded.
SQL>
Connect back to Context2 and try again to set it:
SQL> connect context2/context2@//192.168.56.33:1539/xepdb1
Connected.
SQL> exec context1.contextset.set_user('CONTEXT2');
PL/SQL procedure successfully completed.
SQL> select sys_context('appcontext','username') from dual;
SYS_CONTEXT('APPCONTEXT','USERNAME')
--------------------------------------------------------------------------------
CONTEXT2
SQL>
It now works for both users. The key is to understand that a context is at the database level and not the schema level. Each user/schema can now log in and use the same context to store their username in that context. I have created two command windows and these are here showing that it works:
SQL> -- context1
SQL> connect context1/context1@//192.168.56.33:1539/xepdb1
Connected.
SQL> select sys_context('appcontext','username') from dual;
SYS_CONTEXT('APPCONTEXT','USERNAME')
--------------------------------------------------------------------------------
SQL> exec context1.contextset.set_user('CONTEXT1');
PL/SQL procedure successfully completed.
SQL> select sys_context('appcontext','username') from dual;
SYS_CONTEXT('APPCONTEXT','USERNAME')
--------------------------------------------------------------------------------
CONTEXT1
SQL> --context2
C:\Users\pete>sqlplus context2/context2@//192.168.56.33:1539/xepdb1
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 2 09:58:57 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: Wed Sep 02 2026 09:22:28 +01:00
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> exec context1.contextset.set_user('CONTEXT2');
PL/SQL procedure successfully completed.
SQL> select sys_context('appcontext','username') from dual;
SYS_CONTEXT('APPCONTEXT','USERNAME')
--------------------------------------------------------------------------------
CONTEXT2
SQL>
Let us see whats stored in the meta data for the context:
SQL> @sc_print 'select * from dba_context where schema like ''''%CONTEXT%'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from dba_context where schema like ''%CONTEXT%''','''','''''');
Executing Query [select * from dba_context where schema like '%CONTEXT%']
NAMESPACE : APPCONTEXT
SCHEMA : CONTEXT1
PACKAGE : CONTEXTSET
TYPE : ACCESSED LOCALLY
ORIGIN_CON_ID : 3
TRACKING : YES
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
As we can see the context is not assigned to a specific user/schema. It does however specify the schema that owns the PL/SQL that allows the context to be set.
If we drop context1 cascade does the context get removed?
SQL> drop user context1 cascade;
User dropped.
SQL> set serveroutput on
SQL> @sc_print 'select * from dba_context where schema like ''''%CONTEXT%'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from dba_context where schema like ''%CONTEXT%''','''','''''');
Executing Query [select * from dba_context where schema like '%CONTEXT%']
NAMESPACE : APPCONTEXT
SCHEMA : CONTEXT1
PACKAGE : CONTEXTSET
TYPE : ACCESSED LOCALLY
ORIGIN_CON_ID : 3
TRACKING : YES
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
No, the context is at the database level and not the schema level so even though CONTEXT1 created it, it is not removed when CONTEXT1 is dropped cascade. It will not work now as the package has gone.
We are interested in contexts as they are used extensively in Oracle security solutions such as VPD, RAS, Deep Security (new in 26ai) and our own solutions. we can see this by the quantity of contexts available in a default database:
SQL> set lines 220
SQL> col namespace for a30
SQL> col schema for a20
SQL> col package for a30
SQL> col type for a20
SQL> select namespace,schema,package,type from dba_context;
NAMESPACE SCHEMA PACKAGE TYPE
------------------------------ -------------------- ------------------------------ --------------------
LSBY_APPLY_CONTEXT SYS DBMS_LOGSTDBY_CONTEXT ACCESSED LOCALLY
GLOBAL_AQCLNTDB_CTX SYS DBMS_AQJMS ACCESSED GLOBALLY
DBFS_CONTEXT SYS DBMS_DBFS_CONTENT_ADMIN ACCESSED GLOBALLY
REGISTRY$CTX SYS DBMS_REGISTRY_SYS ACCESSED LOCALLY
SHARD_CTX GSMADMIN_INTERNAL DBMS_GSM_POOLADMIN ACCESSED LOCALLY
SHARD_CTX2 GSMADMIN_INTERNAL DBMS_GSM_UTILITY ACCESSED LOCALLY
LT_CTX WMSYS LT_CTX_PKG ACCESSED LOCALLY
DR$APPCTX CTXSYS DRIXMD ACCESSED LOCALLY
SDO_SEM_HTTP_CTX MDSYS SDO_SEM_HTTP_CTX ACCESSED LOCALLY
SDO_SEM_CTX MDSYS SDO_SEM_CTX ACCESSED GLOBALLY
SDO_SEM_CTX_SESSION MDSYS SDO_SEM_CTX_SESSION ACCESSED LOCALLY
NAMESPACE SCHEMA PACKAGE TYPE
------------------------------ -------------------- ------------------------------ --------------------
SDO_SEM_UPDATE_CTX MDSYS SDO_SEM_UPDATE_CTX ACCESSED LOCALLY
OPG_CTX MDSYS OPG_CTX ACCESSED GLOBALLY
OPG_CTX_SESSION MDSYS OPG_CTX_SESSION ACCESSED LOCALLY
LBAC_CTX LBACSYS LBAC_CACHE ACCESSED LOCALLY
LBAC$LABELS LBACSYS LBAC_CACHE ACCESSED LOCALLY
ORA_OLS_SESSION_LABELS LBACSYS SA_AUDIT_ADMIN ACCESSED LOCALLY
MAC$FACTOR DVSYS DBMS_MACSEC ACCESSED LOCALLY
FLAG_LOCK ORABLOG WRITE_LOG ACCESSED GLOBALLY
APPCONTEXT CONTEXT1 CONTEXTSET ACCESSED LOCALLY
20 rows selected.
SQL>
Be aware that contexts are global and the same one cannot be created by two or more users/schemas and also be aware that they need to be dropped separately
#oracleace #oracleacepro #sym_42 #oracle #database #security #context
