Oracle 登入觸發器初始化應用上下文：SYS_CONTEXT 與 AFTER LOGON 實戰

為何棄用 USERENV 改用 SYS_CONTEXT

SYS_CONTEXT('namespace','attribute') 不僅能讀取舊有 USERENV 參數（Session ID、語言、DBA 權限等），更支援應用自訂屬性（如部門號、角色、租戶 ID），且資料儲於 SGA，避免重複查表，效能與安全性雙贏。

完整實作流程

1. 建立上下文封裝

CREATE OR REPLACE PACKAGE hr_context_pkg AS
    PROCEDURE initialize_hr_context;
END hr_context_pkg;
/

CREATE OR REPLACE PACKAGE BODY hr_context_pkg AS
    PROCEDURE initialize_hr_context IS
        v_dept NUMBER;
    BEGIN
        -- 依當前用戶查詢所屬部門
        SELECT department_id INTO v_dept
        FROM employees WHERE employee_id = SYS_CONTEXT('USERENV','SESSION_USER');
        -- 寫入應用上下文
        DBMS_SESSION.SET_CONTEXT('HR_CONTEXT','DEPT',v_dept);
    EXCEPTION WHEN OTHERS THEN NULL; -- 防止登入失敗
    END;
END hr_context_pkg;
/

2. 建立上下文命名空間

CREATE CONTEXT hr_context USING hr.hr_context_pkg;

3. 建立資料庫級登入觸發器

CREATE OR REPLACE TRIGGER dbt_logon
AFTER LOGON ON DATABASE
BEGIN
    hr.hr_context_pkg.initialize_hr_context;
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

應用端呼叫

-- 任何工具、任何會話皆可即時取得部門號
SELECT SYS_CONTEXT('HR_CONTEXT','DEPT') FROM DUAL;

三大優勢

• 效能：上下文資料駐留 SGA，零磁碟 I/O。
• 安全：僅受信任封裝可修改上下文，觸發器自動執行，防竄改。
• 彈性：可建立無限命名空間與屬性，適應多租戶、多應用場景。