SYS_CONTEXT ile Session Bilgilerini Almak-USERENV
SYS_CONTEXT ile Aktif oturuma ait bilgileri alabiliriz.PLSQL içinde aldığınız bu bilgileri kontrol veya loglama için kullanabiliriz.
Genel kullanımı:
sys_context(namespace_string,parameter_string)
sys_context(namespace_string,parameter_string,len) 3. parametre olarak STRING uzunluğu belirtebiliriz.
SELECT SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE',5) FROM DUAL
——–
DATAB
Örnek aşağıdaki gibidir.
SELECT 'AUDITED_CURSORID' AS Parameter, SYS_CONTEXT('USERENV','AUDITED_CURSORID') AS Value, 'Returns the cursor ID of the SQL that triggered the audit' AS Description FROM Dual
UNION ALL
SELECT 'AUTHENTICATION_DATA' AS Parameter, SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') AS Value, 'Authentication data' AS Description FROM Dual
UNION ALL
SELECT 'AUTHENTICATION_TYPE' AS Parameter, SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') AS Value, 'Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy' AS Description FROM Dual
UNION ALL
SELECT 'BG_JOB_ID' AS Parameter, SYS_CONTEXT('USERENV','BG_JOB_ID') AS Value, 'If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL.' AS Description FROM Dual
UNION ALL
SELECT 'CLIENT_IDENTIFIER' AS Parameter, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') AS Value, 'Returns the client identifier (global context)' AS Description FROM Dual
UNION ALL
SELECT 'CLIENT_INFO' AS Parameter, SYS_CONTEXT('USERENV','CLIENT_INFO') AS Value, 'User session information' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_SCHEMA' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS Value, 'Returns the default schema used in the current schema' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_SCHEMAID' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SCHEMAID') AS Value, 'Returns the identifier of the default schema used in the current schema' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_SQL' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SQL') AS Value, 'Returns the SQL that triggered the audit event' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_USER' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_USER') AS Value, 'Name of the current user' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_USERID' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_USERID') AS Value, 'Userid of the current user' AS Description FROM Dual
UNION ALL
SELECT 'DB_DOMAIN' AS Parameter, SYS_CONTEXT('USERENV','DB_DOMAIN') AS Value, 'Domain of the database from the DB_DOMAIN initialization parameter' AS Description FROM Dual
UNION ALL
SELECT 'DB_NAME' AS Parameter, SYS_CONTEXT('USERENV','DB_NAME') AS Value, 'Name of the database from the DB_NAME initialization parameter' AS Description FROM Dual
UNION ALL
SELECT 'ENTRYID' AS Parameter, SYS_CONTEXT('USERENV','ENTRYID') AS Value, 'Available auditing entry identifier' AS Description FROM Dual
UNION ALL
SELECT 'EXTERNAL_NAME' AS Parameter, SYS_CONTEXT('USERENV','EXTERNAL_NAME') AS Value, 'External of the database user' AS Description FROM Dual
UNION ALL
SELECT 'FG_JOB_ID' AS Parameter, SYS_CONTEXT('USERENV','FG_JOB_ID') AS Value, 'If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL.' AS Description FROM Dual
UNION ALL
SELECT 'GLOBAL_CONTEXT_MEMORY' AS Parameter, SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY') AS Value, 'The number used in the System Global Area by the globally accessed context' AS Description FROM Dual
UNION ALL
SELECT 'HOST' AS Parameter, SYS_CONTEXT('USERENV','HOST') AS Value, 'Name of the host machine from which the client has connected' AS Description FROM Dual
UNION ALL
SELECT 'INSTANCE' AS Parameter, SYS_CONTEXT('USERENV','INSTANCE') AS Value, 'The identifier number of the current instance' AS Description FROM Dual
UNION ALL
SELECT 'IP_ADDRESS' AS Parameter, SYS_CONTEXT('USERENV','IP_ADDRESS') AS Value, 'IP address of the machine from which the client has connected' AS Description FROM Dual
UNION ALL
SELECT 'ISDBA' AS Parameter, SYS_CONTEXT('USERENV','ISDBA') AS Value, 'Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.' AS Description FROM Dual
UNION ALL
SELECT 'LANG' AS Parameter, SYS_CONTEXT('USERENV','LANG') AS Value, 'The ISO abbreviate for the language' AS Description FROM Dual
UNION ALL
SELECT 'LANGUAGE' AS Parameter, SYS_CONTEXT('USERENV','LANGUAGE') AS Value, 'The language, territory, and character of the session. In the following format:language_territory.characterset' AS Description FROM Dual
UNION ALL
SELECT 'NETWORK_PROTOCOL' AS Parameter, SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') AS Value, 'Network protocol used' AS Description FROM Dual
UNION ALL
SELECT 'NLS_CALENDAR' AS Parameter, SYS_CONTEXT('USERENV','NLS_CALENDAR') AS Value, 'The calendar of the current session' AS Description FROM Dual
UNION ALL
SELECT 'NLS_CURRENCY' AS Parameter, SYS_CONTEXT('USERENV','NLS_CURRENCY') AS Value, 'The currency of the current session' AS Description FROM Dual
UNION ALL
SELECT 'NLS_DATE_FORMAT' AS Parameter, SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') AS Value, 'The date format for the current session' AS Description FROM Dual
UNION ALL
SELECT 'NLS_DATE_LANGUAGE' AS Parameter, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') AS Value, 'The language used for dates' AS Description FROM Dual
UNION ALL
SELECT 'NLS_SORT' AS Parameter, SYS_CONTEXT('USERENV','NLS_SORT') AS Value, 'BINARY or the linguistic sort basis' AS Description FROM Dual
UNION ALL
SELECT 'NLS_TERRITORY' AS Parameter, SYS_CONTEXT('USERENV','NLS_TERRITORY') AS Value, 'The territory of the current session' AS Description FROM Dual
UNION ALL
SELECT 'OS_USER' AS Parameter, SYS_CONTEXT('USERENV','OS_USER') AS Value, 'The OS username for the user logged in' AS Description FROM Dual
UNION ALL
SELECT 'PROXY_USER' AS Parameter, SYS_CONTEXT('USERENV','PROXY_USER') AS Value, 'The name of the user who opened the current session on behalf of SESSION_USER' AS Description FROM Dual
UNION ALL
SELECT 'PROXY_USERID' AS Parameter, SYS_CONTEXT('USERENV','PROXY_USERID') AS Value, 'The identifier of the user who opened the current session on behalf of SESSION_USER' AS Description FROM Dual
UNION ALL
SELECT 'SESSION_USER' AS Parameter, SYS_CONTEXT('USERENV','SESSION_USER') AS Value, 'The database user name of the user logged in' AS Description FROM Dual
UNION ALL
SELECT 'SESSION_USERID' AS Parameter, SYS_CONTEXT('USERENV','SESSION_USERID') AS Value, 'The database identifier of the user logged in' AS Description FROM Dual
UNION ALL
SELECT 'SESSIONID' AS Parameter, SYS_CONTEXT('USERENV','SESSIONID') AS Value, 'The identifier of the auditing session' AS Description FROM Dual
UNION ALL
SELECT 'TERMINAL' AS Parameter, SYS_CONTEXT('USERENV','TERMINAL') AS Value, 'terminal' as description from dual;
SQL sonucu:
PARAMETER | VALUE | DESCRIPTION | ||
AUDITED_CURSORID | Returns the cursor ID of the SQL that triggered the audit | |||
AUTHENTICATION_DATA | Authentication data | |||
AUTHENTICATION_TYPE | DATABASE | Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy | ||
BG_JOB_ID | If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL. | |||
CLIENT_IDENTIFIER | Returns the client identifier (global context) | |||
CLIENT_INFO | User session information | |||
CURRENT_SCHEMA | IFSAPP | Returns the default schema used in the current schema | ||
CURRENT_SCHEMAID | 66 | Returns the identifier of the default schema used in the current schema | ||
CURRENT_SQL | Returns the SQL that triggered the audit event | |||
CURRENT_USER | IFSAPP | Name of the current user | ||
CURRENT_USERID | 66 | Userid of the current user | ||
DB_DOMAIN | Domain of the database from the DB_DOMAIN initialization parameter | |||
DB_NAME | TEST | Name of the database from the DB_NAME initialization parameter | ||
ENTRYID | Available auditing entry identifier | |||
EXTERNAL_NAME | External of the database user | |||
FG_JOB_ID | 0 | If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL. | ||
GLOBAL_CONTEXT_MEMORY | 6334136 | The number used in the System Global Area by the globally accessed context | ||
HOST | WORKGROUPOMERPC | Name of the host machine from which the client has connected | ||
INSTANCE | 1 | The identifier number of the current instance | ||
IP_ADDRESS | 192.168.2.83 | IP address of the machine from which the client has connected | ||
ISDBA | FALSE | Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE. | ||
LANG | US | The ISO abbreviate for the language | ||
LANGUAGE | AMERICAN_AMERICA.AL32UTF8 | The language, territory, and character of the session. In the following format:language_territory.characterset | ||
NETWORK_PROTOCOL | tcp | Network protocol used | ||
NLS_CALENDAR | GREGORIAN | The calendar of the current session | ||
NLS_CURRENCY | $ | The currency of the current session | ||
NLS_DATE_FORMAT | DD-MON-RR | The date format for the current session | ||
NLS_DATE_LANGUAGE | AMERICAN | The language used for dates | ||
NLS_SORT | BINARY | BINARY or the linguistic sort basis | ||
NLS_TERRITORY | AMERICA | The territory of the current session | ||
OS_USER | omer | The OS username for the user logged in | ||
PROXY_USER | The name of the user who opened the current session on behalf of SESSION_USER | |||
PROXY_USERID | The identifier of the user who opened the current session on behalf of SESSION_USER | |||
SESSION_USER | IFSAPP | The database user name of the user logged in | ||
SESSION_USERID | 66 | The database identifier of the user logged in | ||
SESSIONID | 10740647 | The identifier of the auditing session | ||
TERMINAL | OMERPC | terminal |
Çok basit bir örnek olarak:
select sid from v$session where audsid=sys_context(‘userenv’,’SESSIONID’);