萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 利用登入觸發器實現 IP 限制登入的實作教學

前言

Oracle 資料庫本身不像 MySQL 那樣支援在使用者定義中直接指定允許連線的 Host/IP(如 CREATE USER 'user'@'192.168.1.%')。若需實現使用者級別的 IP 存取控制,常見做法是建立 AFTER LOGON ON DATABASE 觸發器,在登入瞬間判斷 Client IP 與使用者名稱,不符合白名單時拋出例外阻擋連線。本文提供完整觸發器範例、IP 白名單設定技巧、實測驗證案例,以及部署注意事項。

一、觸發器完整範例程式碼

CREATE OR REPLACE TRIGGER logon_ip_control
AFTER LOGON ON DATABASE
DECLARE
    v_ip   VARCHAR2(30);
    v_user VARCHAR2(30);
BEGIN
    -- 取得目前 Session 的使用者與 Client IP
    SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') INTO v_user FROM DUAL;
    SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO v_ip FROM DUAL;

    -- 僅針對特定使用者進行 IP 限制
    IF v_user = 'EPAY_USER' THEN
        -- 白名單判斷:支援完整 IP 或網段(使用 LIKE 模糊比對)
        IF v_ip NOT IN ('192.168.219.20', '192.168.219.22')
           AND v_ip NOT LIKE '192.168.219.%'  -- 如需開放整個 C 類網段
        THEN
            RAISE_APPLICATION_ERROR(-20001,
                'User ' || v_user || ' is not allowed to connect from ' || v_ip);
        END IF;
    END IF;
END;
/

關鍵說明

  • SYS_CONTEXT('USERENV','SESSION_USER'):取得實際登入的資料庫使用者名稱(非 Proxy User 時與 CURRENT_USER 相同)。
  • SYS_CONTEXT('USERENV','IP_ADDRESS'):取得 Client 端 IP 位址(IPv4 格式)。若透過 Connection Pool 或 Proxy,可能為中介層 IP,需配合應用層傳遞真實 IP(如 CLIENT_IDENTIFIER)。
  • RAISE_APPLICATION_ERROR(-20001, ...):拋出自訂錯誤,導致登入失敗,錯誤訊息會回傳給 Client。
  • 網段支援:範例使用 NOT LIKE '192.168.219.%' 示範 C 類網段放行;實務上可建立白名單設定表,觸發器動態查詢,避免硬編碼修改觸發器。

二、實測驗證案例

案例 1:非白名單 IP(192.168.219.21)連線 —— 應被阻擋

[oracle@lxdb2 ~]$ sqlplus epay_user@pri
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 3 19:23:48 2013
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: User EPAY_USER is not allowed to connect from 192.168.219.21
ORA-06512: at line 10

✅ 符合預期:觸發器攔截,回傳自訂錯誤碼 -20001。

案例 2:白名單 IP(192.168.219.22)連線 —— 應成功

[oracle@lxdb1 ~]$ sqlplus epay_user
SQL*Plus: Release 11.2.0.3.0 Production
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

✅ 符合預期:正常登入。

案例 3:本地連線(192.168.219.23,同主機)連線 —— 應成功

[oracle@lxdb1 ~]$ sqlplus epay_user
...
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

✅ 符合預期:本地連線通常不受此觸發器限制(視 IP_ADDRESS 回傳值而定,若為本地回環或實體 IP 需納入白名單)。

三、進階擴充與最佳實務

1. 白名單外部化至設定表

避免頻繁修改觸發器程式碼,建立白名單表:

CREATE TABLE admin.ip_whitelist (
    username   VARCHAR2(30) NOT NULL,
    ip_pattern VARCHAR2(50) NOT NULL,  -- 支援完整 IP 或 '192.168.1.%' 格式
    enabled    CHAR(1) DEFAULT 'Y' CHECK (enabled IN ('Y','N')),
    PRIMARY KEY (username, ip_pattern)
);

-- 觸發器改為動態查詢
SELECT COUNT(*) INTO v_cnt
FROM admin.ip_whitelist
WHERE username = v_user
  AND enabled = 'Y'
  AND (ip_pattern = v_ip OR v_ip LIKE ip_pattern);

IF v_cnt = 0 THEN
    RAISE_APPLICATION_ERROR(...);
END IF;

2. 排除系統/管理帳號

觸發器開頭加入排除清單,防止鎖死 SYS/SYSTEM/DBA 帳號:

IF v_user IN ('SYS','SYSTEM','DBA_ADMIN','MONITOR_USER') THEN
    RETURN;  -- 直接放行
END IF;

3. 稽核記錄

在 RAISE_APPLICATION_ERROR 前寫入稽核表,記錄被阻擋的登入嘗試(IP、時間、使用者、程式名稱 SYS_CONTEXT('USERENV','MODULE')),供安全稽核追蹤。

4. 部署與測試建議

  • 先在測試環境完整驗證所有應用連線來源 IP。
  • 生產部署時,建議先建立 DISABLE 狀態的觸發器,確認無誤後再 ALTER TRIGGER logon_ip_control ENABLE。
  • 保留緊急回滾方案:DROP TRIGGER logon_ip_control; 或以 SYSDBA 連線停用。

四、常見問題與限制

問題說明與對策
Connection Pool / Middleware IP應用伺服器使用連線池時,資料庫看到的 IP 為中介層 IP。對策:應用層設定 DBMS_SESSION.SET_IDENTIFIER 傳遞真實 Client IP,觸發器改查 SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')。
IPv6 格式IP_ADDRESS 可能回傳 IPv6 格式(如 ::ffff:192.168.1.1),白名單比對需同時支援 IPv4/IPv6 或正規化處理。
效能影響觸發器極輕量(單次 SELECT + 簡單判斷),對登入延遲影響微乎其微(通常 < 1ms)。
Data Guard StandbyStandby 為 Read-Only 或 Apply 狀態時不會觸發 AFTER LOGON;Primary 切換為 Standby 後觸發器仍存在,需確認邏輯適用性。

結語

透過 AFTER LOGON ON DATABASE 觸發器搭配 SYS_CONTEXT 取得 Session 資訊,即可在 Oracle 實現靈活的使用者級 IP 存取控制。配合白名單表外部化、稽核記錄與排除管理帳號機制,可構建生產級的登入安全防線。部署前務必完整測試所有應用連線路徑,避免誤阻合法業務存取。

copyright © 萬盛學電腦網 all rights reserved