Oracle SYS_CONTEXT Function

时间：2022-03-14 01:46


Actions As SYS
Note: USERENV is an Oracle provided namespace that describes the current session.
Syntax	sys_context(‘<namespace>‘, ‘<parameter>‘, <length>); ;

Attribute	Return Value
ACTION	Identifies the position in the module (application name) and is set through the DBMS_APPLICATION_INFO package or OCI.
ACTION	sys_context(‘USERENV‘, ‘ACTION‘) ; exec dbms_application_info.set_action(‘ING‘); sys_context(‘USERENV‘, ‘ACTION‘) ;
AUDITED_CURSORID	Returns the cursor ID of the SQL that triggered the audit. This parameter is not valid in a fine-grained auditing environment. If you specify it in such an environment, Oracle Database always returns NULL.
AUTHENTICATED_IDENTITY	Returns the identity used in authentication. In the list that follows, the type of user is followed by the value returned: Kerberos-authenticated enterprise user: kerberos principal name Kerberos-authenticated external user : kerberos principal name; same as the schema name SSL-authenticated enterprise user: the DN in the user‘s PKI certificate SSL-authenticated external user: the DN in the user‘s PKI certificate Password-authenticated enterprise user: nickname; same as the login name Password-authenticated database user: the database username; same as the schema name OS-authenticated external user: the external operating system user name Radius/DCE-authenticated external user: the schema name Proxy with DN : Oracle Internet Directory DN of the client Proxy with certificate: certificate DN of the client Proxy with username: database user name if client is a local database user; nickname if client is an enterprise user SYSDBA/SYSOPER using Password File: login name SYSDBA/SYSOPER using OS authentication: operating system user name
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.
AUTHENTICATION_METHOD	Returns the method of authentication. In the list that follows, the type of user is followed by the method returned. Password-authenticated enterprise user, local database user, or SYSDBA/SYSOPER using Password File; proxy with username using password: PASSWORD Kerberos-authenticated enterprise or external user: KERBEROS SSL-authenticated enterprise or external user: SSL Radius-authenticated external user: RADIUS OS-authenticated external user or SYSDBA/SYSOPER: OS DCE-authenticated external user: DCE Proxy with certificate, DN, or username without using password: NONE
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.
CLIENT_IDENTIFIER	Returns an identifier that is set by the application through the DBMS_SESSION.SET_IDENTIFIER procedure, the OCI attribute OCI_ATTR_CLIENT_IDENTIFIER, or the Java class Oracle.jdbc.OracleConnection.setClientIdentifier. This attribute is used by various database components to identify lightweight application users who authenticate as the same user.
CLIENT_IDENTIFIER	sys_context(‘USERENV‘, ‘CLIENT_IDENTIFIER‘) ; exec dbms_session.set_identifier(USER \|\| ‘ ‘ \|\| ); sys_context(‘USERENV‘, ‘CLIENT_IDENTIFIER‘) ;
CLIENT_INFO	Returns user session information that can be stored by an application using the DBMS_APPLICATION_INFO package.
CLIENT_INFO	sys_context(‘USERENV‘, ‘CLIENT_INFO‘) ; exec dbms_application_info.set_client_info(‘TEST‘); sys_context(‘USERENV‘, ‘CLIENT_INFO‘) ;
CURRENT_BIND	The bind variables for fine-grained auditing
CURRENT_EDITION_ID	The numeric identifier of the current edition
CURRENT_EDITION_ID	sys_context(‘USERENV‘, ‘CURRENT_EDITION_ID‘) ;
CURRENT_EDITION_NAME	The name of the current edition
CURRENT_EDITION_NAME	sys_context(‘USERENV‘, ‘CURRENT_EDITION_NAME‘) ;
CURRENT_SCHEMA	Name of the default schema being used in the current schema. This value can be changed during the session with an SESSION SET CURRENT_SCHEMA statement.
CURRENT_SCHEMA	sys_context(‘USERENV‘, ‘CURRENT_SCHEMA‘) ;
CURRENT_SCHEMAID	Identifier of the default schema being used in the current session.
CURRENT_SCHEMAID	sys_context(‘USERENV‘, ‘CURRENT_SCHEMAID‘) ; user# sys.user$ name = USER;
CURRENT_SQL	Returns the first 4K bytes of the current SQL that triggered the fine-grained auditing event.
CURRENT_SQLn	CURRENT_SQLn attributes return subsequent 4K-byte increments, where n can be an integer from 1 to 7, inclusive. CURRENT_SQL1 returns bytes 4K to 8K; CURRENT_SQL2 returns bytes 8K to 12K, and so forth. You can specify these attributes only inside the event handler for the fine-grained auditing feature.
CURRENT_SQL_LENGTH	The length of the current SQL statement that triggers fine-grained audit or row-level security (RLS) policy functions or event handlers. Valid only inside the function or event handler.
DB_DOMAIN	Domain of the database as specified in the DB_DOMAIN initialization parameter.
DB_DOMAIN	sys_context(‘USERENV‘, ‘DB_DOMAIN‘) ;
DB_NAME	Name of the database as specified in the DB_NAME initialization parameter.
DB_NAME	sys_context(‘USERENV‘, ‘DB_NAME‘) ; name, value gv$parameter name ‘db%name‘;
DB_UNIQUE NAME	Name of the database as specified in the DB_UNIQUE_NAME initialization parameter.
DB_UNIQUE NAME	sys_context(‘USERENV‘, ‘DB_UNIQUE_NAME‘) ; name, value gv$parameter name ‘db%name‘;
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.
ENTERPRISE_IDENTITY	Returns the user‘s enterprise-wide identity: For enterprise users: the Oracle Internet Directory DN. For external users: the external identity (Kerberos principal name, Radius and DCE schema names, OS user name, Certificate DN). For local users and SYSDBA/SYSOPER logins: NULL. The value of the attribute differs by proxy method: For a proxy with DN: the Oracle Internet Directory DN of the client For a proxy with certificate: the certificate DN of the client for external users; the Oracle Internet Directory DN for global users For a proxy with username: the Oracle Internet Directory DN if the client is an enterprise users; NULL if the client is a local database user.
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.
GLOBAL_CONTEXT_MEMORY	The number used in the System Global Area by the globally accessed context.
GLOBAL_CONTEXT_MEMORY	sys_context(‘USERENV‘, ‘GLOBAL_CONTEXT_MEMORY‘) ;
GLOBAL_UID	Returns the global user ID from Oracle Internet Directory for Enterprise User Security (EUS) logins; returns null for all other logins.
HOST	Name of the host machine from which the client has connected.
HOST	sys_context(‘USERENV‘, ‘HOST‘) ;
IDENTIFICATION_TYPE	Returns the way the user‘s schema was created in the database. Specifically, it reflects the IDENTIFIED clause in the CREATE/ALTER USER syntax. In the list that follows, the syntax used during schema creation is followed by the identification type returned: IDENTIFIED BY password: LOCAL IDENTIFIED EXTERNALLY: EXTERNAL IDENTIFIED GLOBALLY: GLOBAL SHARED IDENTIFIED GLOBALLY DN: GLOBAL PRIVATE
IDENTIFICATION_TYPE	sys_context(‘USERENV‘, ‘IDENTIFICATION_TYPE‘) ;
INSTANCE	The instance identification number of the current instance.
INSTANCE	sys_context(‘USERENV‘, ‘INSTANCE‘) ;
INSTANCE_NAME	The name of the instance.
INSTANCE_NAME	sys_context(‘USERENV‘, ‘INSTANCE_NAME‘) ;
IP_ADDRESS	IP address of the machine from which the client is connected.
ISDBA	TRUE if the session is SYS
ISDBA	sys_context(‘USERENV‘, ‘ISDBA‘) ;
LANG	The ISO abbreviation for the language name, a shorter form than the existing ‘LANGUAGE‘ parameter.
LANG	sys_context(‘USERENV‘, ‘LANG‘) ;
LANGUAGE	The language and territory currently used by your session, along with the database character set, in the form: language_territory.characterset.
LANGUAGE	sys_context(‘USERENV‘, ‘LANGUAGE‘) ;
MODULE	The application name (module) set through the DBMS_APPLICATION_INFO package or OCI.
MODULE	sys_context(‘USERENV‘, ‘MODULE‘) ;
NETWORK_PROTOCOL	Network protocol being used for communication, as specified in the ‘PROTOCOL=protocol‘ portion of the connect string.
NLS_CALENDAR	The current calendar of the current session.
NLS_CALENDAR	sys_context(‘USERENV‘, ‘NLS_CALENDAR‘) ;
NLS_CURRENCY	The currency of the current session.
NLS_CURRENCY	sys_context(‘USERENV‘, ‘NLS_CURRENCY‘) ;
	The date format for the session.
	sys_context(‘USERENV‘, ‘‘) ;
NLS_DATE_LANGUAGE	The language used for expressing dates.
NLS_DATE_LANGUAGE	sys_context(‘USERENV‘, ‘NLS_DATE_LANGUAGE‘) ;
NLS_SORT	BINARY or the linguistic sort basis.
NLS_SORT	sys_context(‘USERENV‘, ‘NLS_SORT‘) ;
NLS_TERRITORY	The territory of the current session.
NLS_TERRITORY	sys_context(‘USERENV‘, ‘NLS_TERRITORY‘) ;
OS_USER	Operating system username of the client process that initiated the database session.
OS_USER	sys_context(‘USERENV‘, ‘OS_USER‘) ;
POLICY_INVOKER	The invoker of row-level security (RLS) policy functions.
PROXY_ENTERPRISE_IDENTITY	Returns the Oracle Internet Directory DN when the proxy user is an enterprise user.
PROXY_GLOBAL_UID	Returns the global user ID from Oracle Internet Directory for Enterprise User Security (EUS) proxy users; returns NULL for all other proxy users.
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.
SERVER_HOST	The host name of the machine on which the instance is running.
SERVER_HOST	sys_context(‘USERENV‘, ‘SERVER_HOST‘) ;
SERVICE_NAME	The name of the service to which a given session is connected.
SERVICE_NAME	sys_context(‘USERENV‘, ‘SERVICE_NAME‘) ;
SESSION_USER	Database user name by which the current user is authenticated. This value remains the same throughout the duration of the session.
SESSION_USER	sys_context(‘USERENV‘, ‘SESSION_USER‘) ;
SESSION_USERID	Identifier of the database user name by which the current user is authenticated.
SESSION_USERID	sys_context(‘USERENV‘, ‘SESSION_USERID‘) ;
SESSIONID	The auditing session identifier. You cannot use this option in distributed SQL statements. This is the equivalent to the AUDSID column in gv$session.
SESSIONID	sys_context(‘USERENV‘, ‘SESSIONID‘) ;
SID	The session number (different from the session ID).
SID	sys_context(‘USERENV‘, ‘SID‘) ;
STATEMENTID	The auditing statement identifier. STATEMENTID represents the number of SQL statements audited in a given session.
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 statements, not for remote , , or DELETE operations. (The return length of this parameter may vary by operating system.)
TERMINAL	sys_context(‘USERENV‘, ‘TERMINAL‘) ;

Context Demo
User Created Contexts	OR CONTEXT App_Ctx using My_pkg ACCESSED GLOBALLY; OR my_pkg set_session_id(p_session_id ); set_ctx(p_name , p_value ); close_session(p_session_id ); ; / OR my_pkg g_session_id ; set_session_id(p_session_id ) g_session_id := p_session_id; dbms_session.set_identifier(p_session_id); end set_session_id; --=============================================== set_ctx(p_name , p_value ) dbms_session.set_context(‘App_Ctx‘,p_name,p_value,USER,g_session_id); set_ctx; --=============================================== close_session(p_session_id ) dbms_session.set_identifier(p_session_id); dbms_session.clear_identifier; close_session; --=============================================== ; / col var1 format a10 col var2 format a10 exec my_pkg.set_session_id(1234); exec my_pkg.set_ctx(‘Var1‘, ‘Val1‘); exec my_pkg.set_ctx(‘Var2‘, ‘Val2‘); sys_context(‘app_ctx‘, ‘var1‘) var1, sys_context(‘app_ctx‘, ‘var2‘) var2 ; -- Now we‘ll log out/log in -- At first, the context is empty-but we rejoin the session & there it is disconnect connect uwclass/uwclass sys_context(‘app_ctx‘, ‘var1‘) var1, sys_context(‘app_ctx‘, ‘var2‘) var2 ; exec my_pkg.set_session_id(1234); sys_context(‘app_ctx‘, ‘var1‘) var1, sys_context(‘app_ctx‘, ‘var2‘) var2 ; -- Now we‘ll show that this context is tied to our user (we specified -- USER above, if we used null anyone can join this session). grant execute on my_pkg to scott; conn scott/tiger exec uwclass.my_pkg.set_session_id(1234); sys_context(‘app_ctx‘, ‘var1‘) var1, sys_context(‘app_ctx‘, ‘var2‘) var2 ; -- Return to the set context again and clear it conn uwclass/uwclass exec my_pkg.set_session_id(1234); sys_context(‘app_ctx‘, ‘var1‘) var1, sys_context(‘app_ctx‘, ‘var2‘) var2 ; exec my_pkg.close_session(1234); sys_context(‘app_ctx‘, ‘var1‘) var1, sys_context(‘app_ctx‘, ‘var2‘) var2 ;