【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

如需转载，请标明出处和链接，谢谢！