在 Oracle 中如何确定远程 session 客户端的 IP 地址

在 Oracle 中如何确定远程 session 客户端的 IP 地址 前几天，Daniel 所在的环境多个数据库发生了一件惨案：我们把所有数据库主机、数据库的密码更改后， 硬件总集成的一台 windows 主机安装了一个 Oracle数据库监控工具，不断尝试利用错误的密码（有错有对

在 Oracle 中如何确定远程 session 客户端的 IP 地址


前几天，Daniel 所在的环境多个数据库发生了一件惨案：我们把所有数据库主机、数据库的密码更改后，
硬件总集成的一台 windows 主机安装了一个 Oracle数据库监控工具，不断尝试利用错误的密码（有错有对）
连接到我们的数据库中，导致生产环境多个数据库发生大量的 library cache lock 等待事件，虽说对应用
没带来影响，但是给数据库运维带来了巨*烦，所有的登录尝试都失败。十几个人、几家厂商查了一天多
最后还是凭感觉找到了那台windows主机和机主。这次事故给了我们惨痛的教训，使我不得不研究一下怎样
获得远程 session 的客户端 IP 地址，快速定位这类问题。Oracle 中获得 session 的客户端 IP 地址主要
分为当前 session 和非当前 session 的 IP 地址。


1、如何当前 session 的客户端 IP 地址


使用 SYS_CONTEXT namespace 可以获得当前 session 的客户端 IP 地址


[oracle@prod admin]$ sqlplus system/oracle123@prod


SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 19 20:48:47 2013


Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


20:48:51 system@PROD> col "My IP Address" for a30
20:48:59 system@PROD> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') "My IP Address"  from dual;


My IP Address
------------------------------
192.168.8.160


注意：要想测试此方法不能通过操作系统认证方式连接到数据库，必须通过TNS，因为前者是通过IPC协议
连接，后者才是通过 TCP/IP 协议连接。


2、如何获得所有远程 Session 的客户端 IP 地址
在 Oracle 数据库中没有直接获取远程 session 客户端 IP 地址的方法。但是存在直接获取当前
session 客户端 IP 地址的方法。通过在数据库中创建 after logon 触发器，再结合使用 
DBMS_APPLICATION_INFO.SET_CLIENT_INFO 存储过程，我们可以通过查询 v$session 来获取所有
远程 session 的客户端 IP 地址。


首先，创建如下触发器：




CREATE OR REPLACE TRIGGER login_on AFTER LOGON ON DATABASE
DECLARE
ipinfo VARCHAR2(30);
BEGIN
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') INTO ipinfo FROM DUAL;
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(ipinfo);
END;
/


然后使用任意的用户远程(TNS)连接到数据库中,然后通过查询以下语句便可查看该 session 的客户端 IP 地址：


21:03:35 system@PROD> conn scott/tiger@prod
Connected.


21:04:05 scott@PROD> col sys_context('userenv','sid') for a40
21:04:18 scott@PROD> select sys_context('userenv','sid') from dual;


SYS_CONTEXT('USERENV','SID')
----------------------------------------
125



用 system 用户创建一个 session 连接，在该 session 中来查询 scott 用户创建的 session 的客户端 IP 地址:


21:07:47 sys@PROD> col client_info for a30
21:08:04 sys@PROD> col CLIENT_IDENTIFIER for a30



21:08:17 sys@PROD> select username,sid,serial#,client_info,client_identifier from v$session where sid=125;


USERNAME                              SID    SERIAL# CLIENT_INFO                    CLIENT_IDENTIFIER
------------------------------ ---------- ---------- ------------------------------ ------------------------------
SCOTT                                 125         35 192.168.8.160



21:08:43 sys@PROD> select username,sid,serial#,client_info,client_identifier from v$session where sid=18;


USERNAME                              SID    SERIAL# CLIENT_INFO                    CLIENT_IDENTIFIER
------------------------------ ---------- ---------- ------------------------------ ------------------------------
SCOTT                                  18         17 192.168.8.100



[oracle@dss admin]$ sqlplus scott/tiger@prod
SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 19 21:20:16 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


21:21:18 scott@PROD> col  sys_context('userenv','sid') for a30
21:21:24 scott@PROD> select sys_context('userenv','sid') from dual;


SYS_CONTEXT('USERENV','SID')
------------------------------
139



21:08:59 sys@PROD> select username,sid,serial#,client_info,client_identifier from v$session where sid=139;


USERNAME                              SID    SERIAL# CLIENT_INFO                    CLIENT_IDENTIFIER

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

SCOTT                                 139        163 192.168.8.180

http://blog.csdn.net/xiangsir/article/details/8693734