在 Oracle 中如何確定遠程 session 用戶端的 IP 位址

在 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