Oracle SYS_CONTEXT Function

Code often needs to know about the session it runs in: who is logged in, which container and service it uses, which program or module called it, and which language settings apply. SYS_CONTEXT returns these from an application context, a named set of key-value pairs held in the session. The built-in context USERENV describes the session itself, and applications can define their own.

Code for This Guide

The main example is in the examples/environment-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. It runs as NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

It comes from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

sys_context('namespace', 'parameter' [, length])

The namespace and parameter names are not case-sensitive. The result is a VARCHAR2 of up to 256 bytes by default; length raises the limit to as much as 4000.

Frequently Used USERENV Attributes

Describe the Session

Example:

select sys_context('USERENV', 'CURRENT_USER')    as current_user,
       sys_context('USERENV', 'SESSION_USER')    as session_user,
       sys_context('USERENV', 'CON_NAME')        as container,
       sys_context('USERENV', 'DB_NAME')         as db_name,
       sys_context('USERENV', 'SERVICE_NAME')    as service,
       sys_context('USERENV', 'LANGUAGE')        as language
from   dual;

select sys_context('USERENV', 'SID') as sid, sys_context('USERENV', 'MODULE') as module,
       sys_context('USERENV', 'IP_ADDRESS') as ip, sys_context('USERENV', 'ISDBA') as is_dba
from   dual;

Output:

CURRENT_USER    SESSION_USER    CONTAINER    DB_NAME     SERVICE     LANGUAGE
_______________ _______________ ____________ ___________ ___________ ____________________________
NIMBUS          NIMBUS          FREEPDB1     FREEPDB1    freepdb1    AMERICAN_AMERICA.AL32UTF8

SID    MODULE    IP           IS_DBA
______ _________ ____________ _________
70     SQLcl     127.0.0.1    FALSE

SESSION_USER and CURRENT_USER are the same here. Inside a stored procedure with definer's rights, CURRENT_USER becomes the owner of the procedure while SESSION_USER stays the logged-in user, which is how auditing code tells them apart. MODULE shows SQLcl, the client program.

Read Application Tags

Applications tag their sessions with DBMS_APPLICATION_INFO so DBAs can see what each session is doing. SYS_CONTEXT reads the tags back.

Example:

-- application tags set by the session appear in USERENV
exec dbms_application_info.set_module('booking-report', 'monthly totals')
exec dbms_application_info.set_client_info('run by scheduler')

select sys_context('USERENV', 'MODULE')      as module,
       sys_context('USERENV', 'ACTION')      as action,
       sys_context('USERENV', 'CLIENT_INFO') as client_info,
       sys_context('userenv', 'current_schema') as schema_lowercase_args
from   dual;

Output:

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

MODULE            ACTION            CLIENT_INFO         SCHEMA_LOWERCASE_ARGS
_________________ _________________ ___________________ ________________________
booking-report    monthly totals    run by scheduler    NIMBUS

The last column passes the arguments in lowercase, which works because they are not case-sensitive.

Your Own Contexts

CREATE CONTEXT defines a namespace whose values only a named PL/SQL package can set, with DBMS_SESSION.SET_CONTEXT. Because the values cannot be changed by the user directly, they are trusted, which makes them the standard input for row-level security policies: a policy function reads sys_context('app_ctx', 'tenant_id') and filters every query accordingly.

Related Guides

• Row-Level Security in Oracle APEX with VPD: Show Each User Only Their Own Data
• How to Use Pseudocolumns in Oracle

Conclusion

SYS_CONTEXT returns attributes of an application context. The built-in USERENV context describes the user, container, service, client, application tags, and language settings of the session, and your own contexts, set only by trusted code, carry values such as a tenant for row-level security.