It All Depends on the CONTEXT: Using Oracle's SYS_CONTEXT Function

Thursday Jun 26th 2003 by Jim Czuprynski
Share:

The SYS_CONTEXT function is the recommended replacement method for obtaining information about users and environment variables instead of the deprecated USERENV function. This versatile function has been expanded in Oracle 9i to provide a plethora of information about the Oracle database and its processes as well as its users and sessions.

During a recent code review of our production databases' stored procedures and packages, I took the time to convert the objects from the deprecated (but still serviceable) USERENV function to its Oracle 9i replacement, SYS_CONTEXT. The conversion was relatively painless, of course, but it also opened up a new world of possibilities for utilizing the functionalities provided by SYS_CONTEXT.

Here is a quick summary of how SYS_CONTEXT can be put to work, both as a replacement for USERENV as well as gathering other information about the database and its environs:

USERENV vs. SYS_CONTEXT

First, let's consider what information the deprecated USERENV function can provide. In this example, I need to determine whether I am logged on as the DBA; what the instance number of the current database is; what terminal I am currently logged on from; and what the NLS Territory parameters have been set to. Here is an example utilizing USERENV to return this information:

SQL> TTITLE CENTER "Example of USERENV() Function"
SQL> COLUMN amidba FORMAT A9 HEADING "Am I DBA?"
SQL> COLUMN instance FORMAT 99999 HEADING "Inst|ID"
SQL> COLUMN terminal FORMAT A16 HEADING "Terminal"
SQL> COLUMN language FORMAT A32 HEADING "NLS Language Parameters"
SQL> SELECT
  2      USERENV('ISDBA') amidba
  3     ,USERENV('INSTANCE') instance
  4     ,USERENV('TERMINAL') terminal
  5     ,USERENV('LANGUAGE') language
  6    FROM DUAL;
                          Example of USERENV() Function                         
            Inst                                                                
Am I DBA?     ID Terminal         NLS Language Parameters                       
--------- ------ ---------------- --------------------------------              
FALSE          1 MAIN_CONSOLE     AMERICAN_AMERICA.WE8MSWIN1252                 

And here's its counterpart using SYS_CONTEXT against the USERENV namespace:


SQL> TTITLE CENTER "Example of SYS_CONTEXT() Function"
SQL> COLUMN amidba FORMAT A9 HEADING "Am I DBA?"
SQL> COLUMN instance FORMAT A6 HEADING "Inst|ID"
SQL> COLUMN terminal FORMAT A16 HEADING "Terminal"
SQL> COLUMN language FORMAT A32 HEADING "NLS Language Parameters"
SQL> 
SQL> SELECT
  2      SYS_CONTEXT('USERENV', 'ISDBA') amidba
  3     ,SYS_CONTEXT('USERENV', 'INSTANCE') instance
  4     ,SYS_CONTEXT('USERENV', 'TERMINAL') terminal
  5     ,SYS_CONTEXT('USERENV', 'LANGUAGE') language
  6    FROM DUAL;

                        Example of SYS_CONTEXT() Function                       
          Inst                                                                  
Am I DBA? ID     Terminal         NLS Language Parameters                       
--------- ------ ---------------- --------------------------------              
FALSE     1      MAIN_CONSOLE     AMERICAN_AMERICA.WE8MSWIN1252

Expanded Session Information

SYS_CONTEXT is more than just a replacement for USERENV, as the next example shows. There are over two dozen namespace attributes that can be queried. This next example shows how to return:

  • the session's IP address
  • the session's host machine name
  • the network protocol in use
  • the current user and schema
  • whether the session is a foreground task

Here is a sample of some session and user-specific information that can be obtained in one function call:


SQL> TTITLE CENTER "More Session-Level Information from SYS_CONTEXT()"
SQL> COLUMN ipaddr     	FORMAT A15 HEADING "IP Address"
SQL> COLUMN host       	FORMAT A20 HEADING "Host"
SQL> COLUMN netprtc    	FORMAT A8  HEADING "Network|Protocol"
SQL> COLUMN curruser   	FORMAT A8  HEADING "Current|User"
SQL> COLUMN currschema 	FORMAT A8  HEADING "Current|Schema"
SQL> COLUMN fgjob      	FORMAT A4  HEADING "FG|Job?"
SQL> COLUMN bgjob      	FORMAT A4  HEADING "BG|Job?"
SQL> 
SQL> SELECT
  2      SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) ipaddr
  3     ,SYS_CONTEXT('USERENV', 'HOST', 16) host
  4     ,SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL', 8) netprtc
  5     ,SYS_CONTEXT('USERENV', 'CURRENT_USER', 8) curruser
  6     ,SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA', 8) currschema
  7     ,SYS_CONTEXT('USERENV', 'FG_JOB_ID', 4) fgjob
  8     ,SYS_CONTEXT('USERENV', 'BG_JOB_ID', 4) bgjob
  9    FROM DUAL;

                More Session-Level Information from SYS_CONTEXT()
                                     Network  Current  Current  FG   BG 
IP Address      Host                 Protocol User     Schema   Job? Job? 
--------------- -------------------- -------- -------- -------- ---- ---- 
198.63.66.124   WORKGROUP\MYCONSOLE  tcp      HR       HR       0        

With the exception of one parameter for the USERENV namespace - AUTHENTICATION_DATA, which will return the data being used to authenticate the login user - the maximum length of the VARCHAR2 string that SYS_CONTEXT returns is 255 characters, depending upon the parameter chosen. However, note from the previous examples that the SYS_CONTEXT function can also take one other argument after the specified parameter to limit the maximum length of the returned value.

Other Information from the USERENV Namespace

Here is a sample of some other information available from this workspace that might whet your interest.

Purpose

Parameter

Returns

Auditing

AUDITED_CURSORID

The CURSORID of the SQL that triggered the audit

CURRENT_SQL

The SQL the triggered the fine-grain auditing event

ENTRY_ID

Available auditing entry identifier

SESSION_ID

Session ID of current auditing session

Authentication

AUTHENTICATION_DATA

Data used to authenticate the login user

AUTHENTICATION_TYPE

Tells how the user was authentication (DATABASE, OS, NETWORK, or PROXY)

EXTERNAL_NAME

External name of the database user

Initialization

DB_DOMAIN

Value of DB_DOMAIN initialization parameter

DB_NAME

Value of DB_NAME initialization parameter

For a complete list of other parameters and more extensive detail on how the values returned might be used, please review the Oracle 9i SQL Reference Manual.

Building Our Own Namespaces

The USERENV namespace does store extensive information, but the power of SYS_CONTEXT does not stop there. I can also create secured namespaces and store context in them for retrieval within a session or across the instance.

For example, if I create a new namespace via the CREATE CONTEXT command, I can then use SYS_CONTEXT to manage and control access to that namespace. In the example below (executed from the SYSTEM login), I've made the namespace accessible to any session for the database instance by specifying ACCESSED GLOBALLY


SQL> CREATE OR REPLACE CONTEXT hr_security
  2     USING hr.pkg_security
  3     ACCESSED GLOBALLY;

Context created.

Next, I create the corresponding package that will allow me to set parameters in the newly created context via calls to the DBMS_SESSION.SET_CONTEXT procedure:


SQL> CREATE OR REPLACE PACKAGE hr.pkg_security
  2  IS
  3  
  4     PROCEDURE set_security(
  5        a_vcParameter VARCHAR2,
  6        a_vcValue     VARCHAR2
  7     );
  8  
  9     FUNCTION empname
 10     RETURN VARCHAR2;
 11  
 12  END pkg_security;
 13  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY hr.pkg_security
  2  IS
  3  
  4     PROCEDURE set_security(
  5        a_vcParameter VARCHAR2,
  6        a_vcValue     VARCHAR2
  7     )
  8     IS
  9     -- Sets value for specified parameter in HRSECURITY namespace
 10     BEGIN
 11         DBMS_SESSION.SET_CONTEXT(
 12            NAMESPACE => 'HR_SECURITY'
 13           ,ATTRIBUTE => a_vcParameter
 14           ,VALUE => a_vcValue
 15          );
 16  
 17     END set_security;
 18  
 19     FUNCTION empname
 20     RETURN VARCHAR2
 21     IS
 22  	 -- Returns employee's name using employee ID set via SET_SECURITY parameter
 23     vcEmpName VARCHAR2(64) := NULL;
 24     BEGIN
 25        SELECT last_name || ', ' || first_name
 26          INTO vcEmpName
 27          FROM hr.employees
 28         WHERE employee_id = TO_NUMBER(SYS_CONTEXT('HR_SECURITY', 'EMPLOYEE_ID'));
 29     RETURN vcEmpName;
 30     END empname;
 31  
 32  END pkg_security;
 33  /

Package body created.

In this package, I have specified a call to the DBMS_SESSION.SET_CONTEXT procedure to create a new parameter and populate a corresponding value in the HR_SECURITY namespace. I've also built a function that returns a formatted string containing the employee's last and first names based on the value stored in that namespace for EMPLOYEE_ID in the HR_SECURITY namespace.

The script below shows the results of calling the new package to set the value for the EMPLOYEE_ID parameter within the namespace and then using SYS_CONTEXT to retrieve the value from the namespace to get the employee's name:


SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  		lvc_employee_id VARCHAR2(255) := NULL;
  3  		lvc_empname VARCHAR2(64) := NULL;
  4  
  5  BEGIN
  6    	PKG_SECURITY.set_security('employee_id', '302');
  7  
  8      BEGIN
  9      	SELECT SYS_CONTEXT('HR_SECURITY', 'EMPLOYEE_ID')
 10      		INTO lvc_employee_id
 11      		FROM DUAL;
 12      END;
13	DBMS_OUTPUT.PUT_LINE(
'Value for HR_SECURITY.EMPLOYEE_ID via USERENV call is ' || lvc_employee_id);
 14  
 15      lvc_empname:= PKG_SECURITY.EMPNAME;
 16      DBMS_OUTPUT.PUT_LINE('Employee Name: ' || lvc_empname);
 17  
 18  END;
 19  /
Value for HR_SECURITY.EMPLOYEE_ID via USERENV call is 100
Employee Name: King, Steven

PL/SQL procedure successfully completed.

Conclusion

I have not completely explored the myriad ways SYS_CONTEXT can make my life as a DBA and PL/SQL developer easier, but it holds a lot of promise for securing sensitive information when using other namespaces besides USERENV. I'm hoping that this versatile function will be expanded to utilize other
Oracle-populated namespaces in future releases of Oracle.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved