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;
/
CLIENT_IDENTIFIER)。NOT LIKE '192.168.219.%' 示範 C 類網段放行;實務上可建立白名單設定表,觸發器動態查詢,避免硬編碼修改觸發器。[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。
[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
✅ 符合預期:正常登入。
[oracle@lxdb1 ~]$ sqlplus epay_user
...
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
✅ 符合預期:本地連線通常不受此觸發器限制(視 IP_ADDRESS 回傳值而定,若為本地回環或實體 IP 需納入白名單)。
避免頻繁修改觸發器程式碼,建立白名單表:
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;
觸發器開頭加入排除清單,防止鎖死 SYS/SYSTEM/DBA 帳號:
IF v_user IN ('SYS','SYSTEM','DBA_ADMIN','MONITOR_USER') THEN
RETURN; -- 直接放行
END IF;
在 RAISE_APPLICATION_ERROR 前寫入稽核表,記錄被阻擋的登入嘗試(IP、時間、使用者、程式名稱 SYS_CONTEXT('USERENV','MODULE')),供安全稽核追蹤。
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 Standby | Standby 為 Read-Only 或 Apply 狀態時不會觸發 AFTER LOGON;Primary 切換為 Standby 後觸發器仍存在,需確認邏輯適用性。 |
透過 AFTER LOGON ON DATABASE 觸發器搭配 SYS_CONTEXT 取得 Session 資訊,即可在 Oracle 實現靈活的使用者級 IP 存取控制。配合白名單表外部化、稽核記錄與排除管理帳號機制,可構建生產級的登入安全防線。部署前務必完整測試所有應用連線路徑,避免誤阻合法業務存取。