熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

userenv和sys

2013-11-13 15:30:26  來源: Oracle 

  這兩個函數用來記錄連接的session信息經常用於觸發器中記錄客戶端的連接信息(比如IP)我比較關注的是sessionid和ip但是 select userenv(sessionid)得到的並不是用戶的sid而是v$session中的audsidsid根據audsid再得到

  SQL> select sid from v$session where audsid=userenv(sessionid);

  SID

  

  

  至於sid和audsid的區別自己也沒太搞明白網上摘了一段

  from asktom

  the audsid column is populated via a sequence and for normal

  sessions is the same as userenv(sessionid) but for some background sessions it is not set

  (it comes back as making the view not work

  So Id just ignore audsid for now and use SID

  下面記錄了兩個函數的具體用法

   USERENV(OPTION)

  返回當前的會話信息

  OPTION=ISDBA若當前是DBA角色則為TRUE否則FALSE

  OPTION=LANGUAGE返回數據庫的字符集

  OPTION=SESSIONID為當前會話標識符

  OPTION=ENTRYID返回可審計的會話標識符

  OPTION=LANG返回會話語言名稱的ISO簡記

  OPTION=INSTANCE返回當前的實例

  OPTION=terminal返回當前計算機名

  SELECT USERENV(LANGUAGE) FROM DUAL;

  返回系統會話信息sys_context

  select

  SYS_CONTEXT(USERENVTERMINAL) terminal

  SYS_CONTEXT(USERENVLANGUAGE) language

  SYS_CONTEXT(USERENVSESSIONID) sessionid

  SYS_CONTEXT(USERENVINSTANCE) instance

  SYS_CONTEXT(USERENVENTRYID) entryid

  SYS_CONTEXT(USERENVISDBA) isdba

  SYS_CONTEXT(USERENVNLS_TERRITORY) nls_territory

  SYS_CONTEXT(USERENVNLS_CURRENCY) nls_currency

  SYS_CONTEXT(USERENVNLS_CALENDAR) nls_calendar

  SYS_CONTEXT(USERENVNLS_DATE_FORMAT) nls_date_format

  SYS_CONTEXT(USERENVNLS_DATE_LANGUAGE) nls_date_language

  SYS_CONTEXT(USERENVNLS_SORT) nls_sort

  SYS_CONTEXT(USERENVCURRENT_USER) current_user

  SYS_CONTEXT(USERENVCURRENT_USERID) current_userid

  SYS_CONTEXT(USERENVSESSION_USER) session_user

  SYS_CONTEXT(USERENVSESSION_USERID) session_userid

  SYS_CONTEXT(USERENVPROXY_USER) proxy_user

  SYS_CONTEXT(USERENVPROXY_USERID) proxy_userid

  SYS_CONTEXT(USERENVDB_DOMAIN) db_domain

  SYS_CONTEXT(USERENVDB_NAME) db_name

  SYS_CONTEXT(USERENVHOST) host

  SYS_CONTEXT(USERENVOS_USER) os_user

  SYS_CONTEXT(USERENVEXTERNAL_NAME) external_name

  SYS_CONTEXT(USERENVIP_ADDRESS) ip_address

  SYS_CONTEXT(USERENVNETWORK_PROTOCOL) network_protocol

  SYS_CONTEXT(USERENVBG_JOB_ID) bg_job_id

  SYS_CONTEXT(USERENVFG_JOB_ID) fg_job_id

  SYS_CONTEXT(USERENVAUTHENTICATION_TYPE) authentication_type

  SYS_CONTEXT(USERENVAUTHENTICATION_DATA) authentication_data

  from dual ;

  其中第二個參數的可選值如下

  AUTHENTICATION_DATA

  Data being used to authenticate the login user For X certificate authenticated sessions this field returns the context of the certificate in HEX format

  Note: You can change the return value of the AUTHENTICATION_DATA attribute using the length parameter of the syntax Values of up to are accepted This is the only attribute of USERENV for which Oracle implements such a change

  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

  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_INFO

  Returns up to bytes of user session information that can be stored by an application using the DBMS_APPLICATION_INFO package

  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

  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

  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

  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

  EXTERNAL_NAME

  External name of the database user For SSL authenticated sessions using v certificates this field returns the distinguished name (DN) stored in the user certificate

  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

  HOST

  Name of the host machine from which the client has connected

  INSTANCE

  The instance identification number of the current instance

  IP_ADDRESS

  IP address of the machine from which the client is connected

  ISDBA

  TRUE if you are logged on as SYS

  LANG

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

  LANGUAGE

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

  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_CURRENCY

  The currency 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

  NLS_TERRITORY

  The territory of the current session

  OS_USER

  Operating system username of the client process that initiated the database 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

  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

  SESSIONID

  The auditing session identifier You cannot use this option in distributed SQL statements

  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)


From:http://tw.wingwit.com/Article/program/Oracle/201311/16831.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.