sql 获取 session 客户端的 IP 地址

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

在 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


 col "My IP Address" for a30
 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 地址：


 conn scott/tiger@prod



 col sys_context('userenv','sid') for a40
 select sys_context('userenv','sid') from dual;


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

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

col client_info for a30

col CLIENT_IDENTIFIER for a30
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



 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