Oracle 記錄登入用戶 IP 與登入失敗 IP 完整實作

Oracle 記錄登入用戶 IP 與登入失敗 IP 完整實作

需求：資料庫登入成功時記錄客戶端 IP，登入失敗（如密碼錯誤）時同樣記錄 IP 供稽核。利用 SYS_CONTEXT('USERENV','IP_ADDRESS') 取得 IP，結合 AFTER LOGON 與 AFTER SERVERERROR 觸發器實現。

1. 登入成功記錄 IP

V$SESSION 預設無 IP 欄位，需先呼叫 DBMS_SESSION.SET_IDENTIFIER 將 IP 寫入 CLIENT_INFO，再由登入觸發器固化。

-- 步驟 1：設定識別碼（可在應用連線池初始化時執行）
BEGIN
  DBMS_SESSION.SET_IDENTIFIER(SYS_CONTEXT('USERENV','IP_ADDRESS'));
END;
/

-- 步驟 2：建立資料庫級登入觸發器
CREATE OR REPLACE TRIGGER on_logon_trigger
AFTER LOGON ON DATABASE
BEGIN
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV','IP_ADDRESS'));
END;
/

需 DBA 權限執行。觸發器啟動後，所有新 Session 的 V$SESSION.CLIENT_INFO 將自動帶入 IP。

2. 查詢當前連線 IP 資訊

SELECT username,
       program,
       machine,
       client_info,
       SYS_CONTEXT('USERENV','IP_ADDRESS') AS ipadd
FROM v$session
WHERE username IS NOT NULL
ORDER BY username, program, machine;

結果範例：

3. 登入失敗記錄至 Alert Log

利用 AFTER SERVERERROR ON DATABASE 捕捉 ORA-01017（密碼錯誤），將 IP、OS User、Process、Program 等寫入 Alert Log。

CREATE OR REPLACE TRIGGER logon_denied_to_alert
AFTER SERVERERROR ON DATABASE
DECLARE
  message     VARCHAR2(168);
  ip          VARCHAR2(15);
  v_os_user   VARCHAR2(80);
  v_module    VARCHAR2(50);
  v_action    VARCHAR2(50);
  v_pid       VARCHAR2(10);
  v_sid       NUMBER;
  v_program   VARCHAR2(48);
BEGIN
  IF ORA_IS_SERVERERROR(1017) THEN
    -- 取得遠端連線 IP
    IF UPPER(SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')) = 'TCP' THEN
      ip := SYS_CONTEXT('USERENV','IP_ADDRESS');
    END IF;

    -- 取得當前 Session 對應的 Process 資訊
    SELECT sid INTO v_sid FROM sys.v_$mystat WHERE rownum < 2;
    SELECT p.spid, v.program
      INTO v_pid, v_program
      FROM v$process p, v$session v
     WHERE p.addr = v.paddr AND v.sid = v_sid;

    v_os_user := SYS_CONTEXT('USERENV','OS_USER');
    DBMS_APPLICATION_INFO.READ_MODULE(v_module, v_action);

    message := TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS') || ' logon denied from ' ||
               NVL(ip,'localhost') || ' ' || v_pid || ' ' || v_os_user ||
               ' with ' || v_program || ' – ' || v_module || ' ' || v_action;

    -- 寫入 Alert Log (2 代表 ALERT FILE)
    SYS.DBMS_SYSTEM.KSDWRT(2, message);
  END IF;
END;
/

4. 權限授予（否則編譯報錯 PLS-00201）

GRANT EXECUTE ON SYS.DBMS_SYSTEM TO <你的用戶>;
-- 例如：GRANT EXECUTE ON SYS.DBMS_SYSTEM TO POWERDESK;

5. Alert Log 記錄範例

Fri May 15 19:11:09 2015
20150515 191109 logon denied from 192.168.120.169 20934 Administrator with plsqldev.exe – plsqldev.exe
20150515 191109 logon denied from 192.168.120.169 20934 Administrator with plsqldev.exe – plsqldev.exe
Fri May 15 19:11:18 2015
20150515 191118 logon denied from 192.168.120.169 20958 Administrator with plsqldev.exe – plsqldev.exe

關鍵點總結

• SYS_CONTEXT('USERENV','IP_ADDRESS') 只在 TCP 協議下有值，Bequeath 連線回傳 NULL。
• 登入觸發器屬於系統級，影響所有用戶，謹慎部署，建議先在測試環境驗證。
• DBMS_SYSTEM.KSDWRT 需額外授權，且僅寫入 Alert Log，不進入資料表，適合輕量級稽核。