URGENT SYS_CONTEXT

With Oracle8i, a new SQL function was introduced: SYS_CONTEXT. This function returns the value of an attribute associated with the context namespace. A context namespace is a set of application-defined attributes that can be set by a package by associating the namespace with itself. Please see Oracle8i Concepts for an example application context: the chapter on "Application Context in Part Data Protection." Most of the default context-related/session-related values that can be obtained through the use of SYS_CONTEXT, can also be obtained through the direct use of USERENV (SELECT USERENV( ) FROM DUAL). CAUTION: Some commercial applications may be using the userenv context value. Check the applicable documentation for those applications to determine what restrictions they may impose on use of this context area. Oracle recommends that you use the application context feature or the SYS_CONTEXT function with the USERENV option. These alternatives are more secure and flexible. See the Oracle8i SQL Reference, Chapter 4 Functions: USERENV.

IP-ADDRESS: ----------- To obtain the client IP-address of the currently connected, you use the default context namespace:
SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') "My IP Address" from dual;
My IP Address --------------- 141.143.51.105

NLS_LANG: --------- To obtain the nls_lang:
SQL> SELECT SYS_CONTEXT('USERENV','LANGUAGE') "nls_lang" from dual;

nls_lang ---------------------------------------------------------------------- AMERICAN_AMERICA.WE8ISO8859P1

SESSION & CURRENT USER: ----------------------- To obtain the session user and current user when using a definer right procedure: SQL> connect system/manager Connected. SQL> CREATE or REPLACE PROCEDURE myproc 2 AUTHID definer 3 AS 4 v_current_user varchar2(30); 5 v_session_user varchar2(30); 6 BEGIN 7 SELECT sys_context('USERENV','SESSION_USER'), 8 sys_context('USERENV','CURRENT_USER') 9 INTO v_session_user, 10 v_current_user 11 FROM dual; 12 dbms_output.put_line('User calling procedure: '||v_session_user); 13 dbms_output.put_line('User-context for procedure: '||v_current_user); 14 END; SQL> / Procedure created. SQL> set serveroutput on SQL> exec myproc User calling procedure: SYSTEM User-context for procedure: SYSTEM SQL> connect sys/change_on_install Connected. SQL> set serveroutput on SQL> exec system.myproc; User-context for procedure: SYSTEM User calling procedure: SYS Possible values that can be used for the USERENV Namespace: ----------------------------------------------------------- (Extracted from SQL Reference Documentation) TERMINAL The operating system identifier for the client of the current session. In distributed SQL statements, this option returns the identifier for your local session. In a distributed environment, this is supported only for remote SELECTs, not for remote INSERTs, UPDATEs, or DELETEs. (The return length of this parameter may vary by operating system.) LANGUAGE The language and territory currently used by your session, along with the database character set, in this form: language_territory.characterset LANG The ISO abbreviation for the language name, a shorter form than the existing 'LANGUAGE' parameter. SESSIONID The auditing session identifier. You cannot use this option in distributed SQL statements. INSTANCE The instance identification number of the current instance. ENTRYID The available auditing entry identifier. You cannot use this option in distributed SQL statements. To use this keyword in USERENV, the initialization parameter AUDIT_TRAIL must be set to TRUE. ISDBA TRUE if you currently have the DBA role enabled and FALSE if you do not. CLIENT_INFO Returns up to 64 bytes of user session information that can be stored by an application using the DBMS_APPLICATION_INFO package. NLS_TERRITORY The territory of the current session. NLS_CURRENCY The currency of the current session. NLS_CALENDAR The current calendar of the current session. NLS_DATE_FORMAT The date format for the session. NLS_DATE_LANGUAGE The language used for expressing dates. NLS_SORT BINARY or the linguistic sort basis. CURRENT_USER The name of the user whose privilege the current session is under. CURRENT_USERID User ID of the user whose privilege the current session is under. SESSION_USER Database user name by which the current user is authenticated. This value remains the same throughout the duration of the session. SESSION_USERID Identifier of the database user name by which the current user is authenticated. CURRENT_SCHEMA Name of the default schema being used in the current schema. This value can be changed during the session with an ALTER SESSION SET CURRENT_SCHEMA statement. CURRENT_SCHEMAID Identifier of the default schema being used in the current session. PROXY_USER Name of the database user who opened the current session on behalf of SESSION_USER. PROXY_USERID Identifier of the database user who opened the current session on behalf of SESSION_USER. DB_DOMAIN Domain of the database as specified in the DB_DOMAIN initialization parameter. DB_NAME Name of the database as specified in the DB_NAME initialization parameter. HOST Name of the host machine from which the client has connected. OS_USER Operating system username of the client process that initiated the database session. EXTERNAL_NAME External name of the database user. For SSL authenticated sessions using V.503 certificates, this field returns the distinguished name (DN) stored in the user certificate. IP_ADDRESS IP address of the machine from which the client is connected. NETWORK_PROTOCOL Network protocol being used for communication, as specified in the 'PROTOCOL=protocol' portion of the connect string. BG_JOB_ID Job ID of the current session if it was established by an Oracle background process. Null if the session was not established by a background process. FG_JOB_ID Job ID of the current session if it was established by a client foreground process. Null if the session was not established by a foreground process. AUTHENTICATION_TYPE How the user was authenticated: DATABASE: Username/password authentication. OS: Operating system external user authentication. NETWORK: Network protocol or ANO authentication. PROXY: OCI proxy connection authentication. AUTHENTICATION_DATA Data being used to authenticate the login user. For X.503 certificate authenticated sessions, this field returns the context of the certificate in HEX2 format. Note: You can change the return value of the AUTHENTICATION_DATA attribute using the length parameter of the syntax. Values of up to 4000 are accepted. This is the only attribute of USERENV for which Oracle implements such a change.

Regards,
Rohit Nirkhe,Oracle/Apps DBA,OCP 8i