使用sys_context找出會話資訊

we6100發表於2015-11-16
select SYS_CONTEXT('USERENV', 'TERMINAL') terminal,
       SYS_CONTEXT('USERENV', 'LANGUAGE') language,
       SYS_CONTEXT('USERENV', 'SESSIONID') sessionid,
       SYS_CONTEXT('USERENV', 'INSTANCE') instance,
       SYS_CONTEXT('USERENV', 'ENTRYID') entryid,
       SYS_CONTEXT('USERENV', 'ISDBA') isdba,
       SYS_CONTEXT('USERENV', 'NLS_TERRITORY') nls_territory,
       SYS_CONTEXT('USERENV', 'NLS_CURRENCY') nls_currency,
       SYS_CONTEXT('USERENV', 'NLS_CALENDAR') nls_calendar,
       SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') nls_date_format,
       SYS_CONTEXT('USERENV', 'NLS_DATE_LANGUAGE') nls_date_language,
       SYS_CONTEXT('USERENV', 'NLS_SORT') nls_sort,
       SYS_CONTEXT('USERENV', 'CURRENT_USER') current_user,
       SYS_CONTEXT('USERENV', 'CURRENT_USERID') current_userid,
       SYS_CONTEXT('USERENV', 'SESSION_USER') session_user,
       SYS_CONTEXT('USERENV', 'SESSION_USERID') session_userid,
       SYS_CONTEXT('USERENV', 'PROXY_USER') proxy_user,
       SYS_CONTEXT('USERENV', 'PROXY_USERID') proxy_userid,
       SYS_CONTEXT('USERENV', 'DB_DOMAIN') db_domain,
       SYS_CONTEXT('USERENV', 'DB_NAME') db_name,
       SYS_CONTEXT('USERENV', 'HOST') host,
       SYS_CONTEXT('USERENV', 'OS_USER') os_user,
       SYS_CONTEXT('USERENV', 'EXTERNAL_NAME') external_name,
       SYS_CONTEXT('USERENV', 'IP_ADDRESS') ip_address,
       SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') network_protocol,
       SYS_CONTEXT('USERENV', 'BG_JOB_ID') bg_job_id,
       SYS_CONTEXT('USERENV', 'FG_JOB_ID') fg_job_id,
       SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') authentication_type,
       SYS_CONTEXT('USERENV', 'AUTHENTICATION_DATA') authentication_data
  from dual

sys_context函式是Oracle提供的一個獲取環境上下文資訊的預定義函式。該函式用來返回一個指定namespace下的parameter值。該函式可以在SQL和PL/SQL語言中使用。

 

 

sys_context實際上就是一個Oracle儲存和傳遞引數的容器訪問函式。我們登入Oracle伺服器,是帶有會話資訊session_info和其他一些屬性資訊。其中,有一些是Oracle預定義的,登入系統的時候自動填入到指定的變數中。還有一些是我們自己定義到其中,用於傳遞值使用的。

 

 

下面是sys_context函式的使用格式:

 

sys_context(‘namespace’,’parameter’{,length});

 

其中,namespace是儲存資訊的一個組group單位,namespace是按照類別進行分類的。一個namespace下可以有多個引數值,通過不同的parameter進行區分。namespace是預先定義好的SQL識別符號,而parameter是可以任意大小寫非敏感的字串,不超過30位長度。

 

 

函式返回值為varchar2型別,長度預設為256位。如果需要限制這個預設值,可以資料length引數作為新的返回長度值。

 

設定namespace指定parameter值,可以使用dbms_session.set_context方法進行。

//自定義一個namespace,並且規定的設定的方法控制程式碼;…………………….step 1

SQL> create context Test using set_test_context;

 

Context created

 

//定義方法…………………….step 2

create or replace procedure set_test_context

(

  vc_value in varchar2

)

is

begin

 dbms_session.set_context('Test','a1',vc_value);

end set_test_context;

 

//設定上值…………………….step 3

SQL> exec set_test_context('m');

 

PL/SQL procedure successfully completed

 

//獲取這個值

SQL> select sys_context('Test','a1') from dual;

 

SYS_CONTEXT('TEST','A1')

------------------------------------

m

 

 

step1-3很重要,因為Test namespace為自定義的namespace,所以需要這樣的設定,以確定許可權所屬。

 

 

sys_context函式最常用的就是userenv名稱空間下的系列引數。下面是引數列表,摘自

http://hi.baidu.com/edeed/blog/item/28cba0ecaa6c8e3e269791bb.html

 

Attribute

Return Value

ACTION

Identifies the position in the module (application name) and is set through the DBMS_APPLICATION_INFO package or OCI.

SELECTsys_context('USERENV', 'ACTION') FROM dual;

exec dbms_application_info.set_action('INSERTING');

SELECTsys_context('USERENV', 'ACTION') FROM dual;

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.

SELECTsys_context('USERENV', 'CLIENT_IDENTIFIER') FROM dual;

exec dbms_session.set_identifier(USER || ' ' || SYSTIMESTAMP);

SELECTsys_context('USERENV', 'CLIENT_IDENTIFIER') FROM dual;

CLIENT_INFO

Returns user session information that can be stored by an application using the DBMS_APPLICATION_INFO package.

SELECTsys_context('USERENV', 'CLIENT_INFO') FROM dual;

exec dbms_application_info.set_client_info('TEST');

SELECTsys_context('USERENV', 'CLIENT_INFO') FROM dual;

CURRENT_BIND

The bind variables for fine-grained auditing

CURRENT_EDITION_ID

The name of the current edition

SELECTsys_context('USERENV', 'CURRENT_EDITION_ID') FROM dual;

CURRENT_EDITION_NAME

The name of the current edition

SELECTsys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

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.

SELECTsys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

CURRENT_SCHEMAID

Identifier of the default schema being used in the current session.

SELECTsys_context('USERENV', 'CURRENT_SCHEMAID') FROM dual;

SELECT user#
FROM sys.user$
WHERE name = USER;

CURRENT_SQL

Returns the first 4K bytes of the current SQL that triggered the fine-grained auditing event.

CURRENT_SQLn

CURRENT_SQLnattributes 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.

SELECTsys_context('USERENV', 'DB_DOMAIN') FROM dual;

DB_NAME

Name of the database as specified in the DB_NAME initialization parameter.

SELECTsys_context('USERENV', 'DB_NAME') FROM dual;

SELECT name, value
FROM gv$parameter
where name LIKE 'db%name';

DB_UNIQUE NAME

Name of the database as specified in the DB_UNIQUE_NAME initialization parameter.

SELECTsys_context('USERENV', 'DB_UNIQUE_NAME') FROM dual;

SELECT name, value
FROM gv$parameter
where name LIKE '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.

SELECTsys_context('USERENV', 'GLOBAL_CONTEXT_MEMORY') FROM dual;

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.

SELECTsys_context('USERENV', 'HOST') FROM dual;

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 AS DN: GLOBAL PRIVATE

SELECTsys_context('USERENV', 'IDENTIFICATION_TYPE') FROM dual;

INSTANCE

The instance identification number of the current instance.

SELECTsys_context('USERENV', 'INSTANCE') FROM dual;

INSTANCE_NAME

The name of the instance.

SELECTsys_context('USERENV', 'INSTANCE_NAME') FROM dual;

IP_ADDRESS

IP address of the machine from which the client is connected.

ISDBA

TRUE if the session is SYS

SELECTsys_context('USERENV', 'ISDBA') FROM dual;

LANG

The ISO abbreviation for the language name, a shorter form. than the existing 'LANGUAGE' parameter.

SELECTsys_context('USERENV', 'LANG') FROM dual;

LANGUAGE

The language and territory currently used by your session, along with the database character set, in the form.:

language_territory.characterset.

SELECTsys_context('USERENV', 'LANGUAGE') FROM dual;

MODULE

The application name (module) set through the DBMS_APPLICATION_INFO package or OCI.

SELECTsys_context('USERENV', 'MODULE') FROM dual;

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.

SELECTsys_context('USERENV', 'NLS_CALENDAR') FROM dual;

NLS_CURRENCY

The currency of the current session.

SELECTsys_context('USERENV', 'NLS_CURRENCY') FROM dual;

NLS_DATE_FORMAT

The date format for the session.

SELECTsys_context('USERENV', 'NLS_DATE_FORMAT') FROM dual;

NLS_DATE_LANGUAGE

The language used for expressing dates.

SELECTsys_context('USERENV', 'NLS_DATE_LANGUAGE') FROM dual;

NLS_SORT

BINARY or the linguistic sort basis.

SELECTsys_context('USERENV', 'NLS_SORT') FROM dual;

NLS_TERRITORY

The territory of the current session.

SELECTsys_context('USERENV', 'NLS_TERRITORY') FROM dual;

OS_USER

Operating system username of the client process that initiated the database session.

SELECTsys_context('USERENV', 'OS_USER') FROM dual;

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.

SELECTsys_context('USERENV', 'SERVER_HOST') FROM dual;

SERVICE_NAME

The name of the service to which a given session is connected.

SELECTsys_context('USERENV', 'SERVICE_NAME') FROM dual;

SESSION_USER

Database user name by which the current user is authenticated. This value remains the same throughout the duration of the session.

SELECTsys_context('USERENV', 'SESSION_USER') FROM dual;

SESSION_USERID

Identifier of the database user name by which the current user is authenticated.

SELECTsys_context('USERENV', 'SESSION_USERID') FROM dual;

SESSIONID

The auditing session identifier. You cannot use this option in distributed SQL statements. This is the equivalent to the AUDSID column ingv$session.

SELECTsys_context('USERENV', 'SESSIONID') FROM dual;

SID

The session number (different from the session ID).

SELECTsys_context('USERENV', 'SID') FROM dual;

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 SELECT statements, not for remote INSERT, UPDATE, or DELETE operations. (The return length of this parameter may vary by operating system.)

SELECTsys_context('USERENV', 'TERMINAL') FROM dual;

 

 

例子:

 

SQL> select sys_context('userenv','ip_address') from dual;

 

SYS_CONTEXT('USERENV','IP_ADDR

-----------------------------------

169.254.94.86

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/27500440/viewspace-1839620/,如需轉載,請註明出處,否則將追究法律責任。

相關文章