【client_ip】通過v$session查詢用戶端的IP資訊

我們想要查看串連資料庫的用戶端資訊（主要是IP地址）可以通過v$session視圖，其中有幾個與用戶端資訊相關的欄位：

OSUSER	VARCHAR2(30)	Operating system client user name
PROCESS	VARCHAR2(12)	Operating system client process ID
MACHINE	VARCHAR2(64)	Operating system machine name
TERMINAL	VARCHAR2(30)	Operating system terminal name
PROGRAM	VARCHAR2(48)	Operating system program name
MODULE	VARCHAR2(48)	Name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure
ACTION	VARCHAR2(32)	Name of the currently executing action as set by calling the DBMS_APPLICATION_INFO.SET_ACTION procedure
CLIENT_INFO	VARCHAR2(64)	Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure
CLIENT_IDENTIFIER	VARCHAR2(64)	Client identifier of the session

下面看一個pl/sql developer串連的會話中，各欄位的值為多少（展示中去掉了部分無關欄位）：

SID                                          140

USERNAME                   SYSTEM

COMMAND                   3

OWNERID                    2147483644

TADDR

LOCKWAIT      

STATUS                         ACTIVE

SERVER                                    DEDICATED

SCHEMA#                     5

SCHEMANAME  SYSTEM

OSUSER           ballontt

PROCESS                                 5864:4884

MACHINE                     WORKGROUP\BALLONTT-PC

TERMINAL                    BALLONTT-PC

PROGRAM                    plsqldev.exe

TYPE                          USER

MODULE                                PL/SQL Developer

MODULE_HASH            1190136663

ACTION                                    SQL視窗 -建立

ACTION_HASH              2127054360

CLIENT_INFO  

CLIENT_IDENTIFIER   

有一個通過pl/sql developer工具串連的SID為140的會話，我開啟另一視窗建立一個會話，通過v$session視圖查看140會話的用戶端資訊，通過各個欄位的值可以知道用戶端的所在主機的機器名、OS名、用戶端是什麼樣的應用程式，但是client_info欄位為空白值，並沒有IP資訊。在尋找會話是屬於哪台用戶端時非常不方便。而有的時候，該欄位就會有用戶端的IP資訊。這樣一來，問題就產生了。什麼時候該欄位有用戶端IP，什麼時候沒有呢？

 

Problem

V$session視圖中的client_info什麼時候有用戶端的IP地址資訊呢？

 

Solution

1. dbms_application_info.set_client_info

在上面列表中，CLIENT_INFO欄位的描述是：

Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFOprocedure。

就是說，該欄位的值是通過“DBMS_APPLICATION_INFO.SET_CLIENT_INFO”預存程序來設定的。用戶端在開始一個會話時，首先執行一遍該預存程序，用IP做為該預存程序的參數（即用戶端的資訊）。此時通過v$session視圖中的client_info欄位就可以看到預存程序中定義的IP資訊。

 

1）首先在遠程用戶端的sql*plus上登陸一個會話

C:\Users\ballontt>sqlplus system/oracle@ballontt

 

SQL> select userenv('sid') from dual;

USERENV('SID')

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

           139

SQL> select client_infofrom v$session where sid=139;

CLIENT_INFO

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

 

此時SID為139的遠端工作階段的client_info欄位為空白。

 

2）在SID為139的會話中執行一次DBMS_APPLICATION_INFO.SET_CLIENT_INFO預存程序，然後查詢v$session試圖中的client_inf欄位o

SQL> begin

  2  dbms_application_info.set_client_info('192.68.10.10');

  3  end;

  4  /

PL/SQL 過程已成功完成。

 

SQL> select client_info from v$session where sid=139;

CLIENT_INFO

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

192.68.10.10

所以說，當用戶端的應用在會話中使用了dbms_application_info包定義了IP資訊時，我們可以就可以查到該會話在v$session試圖中的client_info欄位資訊。否則，v$session視圖中就沒有相應的IP資訊。（dbms_application_info包中有還有類型功能的其它過程：set_action/set_module/set_session_longops）

 

2. 在伺服器端建立觸發器

如果說1中的方法是在用戶端使用了dbms_application_info.set_client_info預存程序，我們也可以利用該預存程序在伺服器端建立一個使用者登入時觸發的觸發器。

 

1）使用sys使用者建立觸發器

SQL> create or replace triggerlogon_on_database after logon on database

 2  begin

 3 dbms_application_info.set_client_info(sys_context('userenv','ip_address'));

 4  end;

 5  /

該觸發器在使用者登入時（即一個會話產生時），將該會話的的SID、IP地址寫進v$session;

 

2）建立成功後，在遠程用戶端新開啟一個會話，然後查詢v$session種的client_info欄位資訊

C:\Users\ballontt>sqlplussystem/oracle@ballontt

SQL> select userenv('sid') from dual;

USERENV('SID')

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

       138

 

SQL> select sid,client_info fromv$session where sid=138;

      SID CLIENT_INFO

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

       138 192.168.10.1

我的遠程用戶端的IP地址就是192.168.10.1。

 

3. utl_inaddr預存程序

在網上查看資訊時有人提到utl_inaddr包中的兩個預存程序可以分別根據主機名稱查詢到IP，或根據Ip查詢到主機名稱。

 

SQL>desc utl_inaddr;

FUNCTION  GET_HOST_ADDRESS RETURNS VARCHAR2

 Argument Name                  Type                    In/Out     Default?

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

 HOST                           VARCHAR2                IN        DEFAULT

 

FUNCTION  GET_HOST_NAME  RETURNS VARCHAR2

 Argument Name                  Type                    In/Out     Default?

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

 IP                             VARCHAR2                IN         DEFAULT

 

 

SQL>select utl_inaddr.get_host_address('ballontt01') from dual;

UTL_INADDR.GET_HOST_ADDRESS('BALLONTT01')

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

192.168.1.11

我們可以看到主機名稱為ballontt01機器的IP地址為：192.168.1.11

 

甚至如果伺服器可以連網，我們可以查詢互連網上主機名稱對應的IP

SQL>select utl_inaddr.get_host_address('www.baidu.com') from dual;

UTL_INADDR.GET_HOST_ADDRESS('WWW.BAIDU.COM')

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

220.181.111.148

 

其工作原理：執行該過程時，首先擷取網域名稱解析伺服器（resolv.conf），在根據host.conf檔案確定解析順序，因為預設是hosts檔案優先解析，這個時候會又繼續讀取/etc/hosts檔案。如果Hosts檔案存在解析關係，則返回資訊；如果不存在，則繼續詢問DNS伺服器獲得解析地址，如果不能解析，則會報錯。

SQL> selectutl_inaddr.get_host_address('ballontt001') from dual;

selectutl_inaddr.get_host_address('ballontt001') from dual

       *

ERROR at line 1:

ORA-29257: host ballontt001 unknown

ORA-06512: at"SYS.UTL_INADDR", line 19

ORA-06512: at"SYS.UTL_INADDR", line 40

ORA-06512: at line 1

 

如果在/etc/hosts加入ballontt001對應的IP後重新查詢：

100.100.100.100 ballontt001

SQL> selectutl_inaddr.get_host_address('ballontt001') from dual;

 

UTL_INADDR.GET_HOST_ADDRESS('BALLONTT001')

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

100.100.100.100

所以有些生產環境中，試圖使用utl_inaddr包結合v$session視圖中的machine欄位（會話的主機名稱）來查詢會話的IP時，因為hosts檔案和DNS伺服器中沒有machine欄位的資訊而無法解析，進而導致報錯，無法得到我們想要的結果。

 

綜上所述，做為一名DBA，如果要想獲得會話的IP，我們能做的就是方法2中的建立一個觸發器。

ballontt
2014/02/25---The End--- 微博：weibo.com/ballontt 如需轉載，請標明出處和連結，謝謝！