Oracle 欄位權限樹構建實戰：三層視圖實現授權過濾、斷層修復與路徑生成

業務場景

CMS 系統需根據用戶 subject_id 動態生成欄位導航樹，需求：

1. 僅顯示用戶有權限的欄位（action_1 = '1'）
2. 排除權限樹下被明確拒絕的節點（action_1 = '0'）
3. 處理父節點缺失導致的斷層，建立虛擬父子關係
4. 輸出含層級縮排與完整路徑的結果集供前端渲染

分層視圖架構

Layer 1：基礎授權樹 V_NP_CTREE_BS

• 遞迴查詢 CONNECT BY 從授權根節點展開
• LAG() 分析函數標記根節點（RK IS NULL）
• NOT EXISTS 反向排除拒絕授權的子樹
• UNION ALL 補充頂層根節點（parent_id = 0）

Layer 2：虛擬父節點修復 V_NP_CTREE_PA

• 自關聯查詢父節點 FATHER
• CASE WHEN column_id=1 THEN 0 ELSE NVL(FATHER, 1) END 定義虛擬父節點 VFA
• 根節點虛擬父為 0，斷層節點虛擬父預設為 1

Layer 3：最終輸出格式化 V_NP_CTREE_RS

• 依 VFA、disorder、column_id 排序確保層級順序
• SYS_CONNECT_BY_PATH 生成完整路徑（如 /根/子/孫）
• LPAD(' ', 2*LEVEL-1) 產生視覺縮排

關鍵 SQL 片段

-- 授權根節點查詢
START WITH t.column_id IN (
  SELECT t.column_id FROM np_cms_column_security t
  WHERE t.subject_id = 'mazj' AND t.action_1 = '1'
)

-- 拒絕授權子樹排除
AND NOT EXISTS (
  SELECT 0 FROM ... START WITH d.column_id IN (
    SELECT t.column_id FROM np_cms_column_security t
    WHERE t.subject_id = 'mazj' AND t.action_1 = '0'
      AND EXISTS ( ...授權樹內部節點... )
  )
)

-- 路徑生成
LPAD(' ', 2 * LEVEL - 1) || SYS_CONNECT_BY_PATH(D.COLUMN_NAME, '/') "Path"

設計優勢

1. 關注點分離：授權邏輯、結構修復、展示格式化三層解耦
2. 效能可控：基礎視圖可建立物化視圖或索引加速
3. 擴展性強：新增權限維度（角色、部門）只需修改 Layer 1