Oracle 權限查詢利器：TABLE FUNCTION 封裝 DBMS_METADATA 實戰包

設計思路

利用 PIPELINED TABLE FUNCTION 將系統視圖 (DBA_SYS_PRIVS、DBA_ROLE_PRIVS、ROLE_SYS_PRIVS 等) 封裝為可直接 SELECT * FROM TABLE(PKG.FUNC(...)) 查詢的介面，支援參數過濾、生成授權/回收 DDL。

核心對象類型

CREATE TYPE PRIVS_LIST IS TABLE OF VARCHAR2(4000);

包規格 (PKG_MAN_PRIVS)

CREATE OR REPLACE PACKAGE MAN_PRIVS AS
  -- 1. 對象權限查詢 (支援多 Owner、多對象、多權限、受益者過濾)
  FUNCTION LIST_TAB_PRIVS (
    OBJ_OWNER VARCHAR2, OBJ_NAME VARCHAR2 DEFAULT NULL,
    PRIVS VARCHAR2 DEFAULT NULL, IS_REVOKE VARCHAR2 DEFAULT 'N',
    GRANTEE VARCHAR2 DEFAULT NULL
  ) RETURN PRIVS_LIST PIPELINED;

  -- 2. 系統權限查詢
  FUNCTION LIST_SYS_PRIVS (OBJ_OWNER VARCHAR2 DEFAULT NULL)
    RETURN PRIVS_LIST PIPELINED;

  -- 3. 角色權限查詢
  FUNCTION LIST_ROLE_PRIVS (OBJ_OWNER VARCHAR2 DEFAULT NULL)
    RETURN PRIVS_LIST PIPELINED;

  -- 4. 角色擁有的系統權限
  FUNCTION LIST_ROLE_SYS_PRIVS (OBJ_OWNER VARCHAR2 DEFAULT NULL)
    RETURN PRIVS_LIST PIPELINED;

  -- 5. 角色擁有的對象權限
  FUNCTION LIST_ROLE_TAB_PRIVS (OBJ_OWNER VARCHAR2 DEFAULT NULL)
    RETURN PRIVS_LIST PIPELINED;

  -- 6. 角色嵌套關係
  FUNCTION LIST_ROLE_ROLE_PRIVS (OBJ_OWNER VARCHAR2 DEFAULT NULL)
    RETURN PRIVS_LIST PIPELINED;

  -- 7. 回收對象權限 (生成 REVOKE DDL)
  PROCEDURE REVOKE_TAB_PRIVS (
    OBJ_OWNER VARCHAR2, OBJ_NAME VARCHAR2 DEFAULT NULL,
    PRIVS VARCHAR2 DEFAULT NULL, GRANTEE VARCHAR2 DEFAULT NULL);

  -- 8. 授予對象權限
  PROCEDURE GRANT_TAB_PRIVS (
    OBJ_OWNER VARCHAR2, OBJ_NAME VARCHAR2, PRIVS VARCHAR2, GRANTEE VARCHAR2);
END MAN_PRIVS;

調用示例

-- 查詢 SH、HR 兩用戶對象的 SELECT/UPDATE/DELETE/INSERT 權限 (含 PUBLIC)
SELECT * FROM TABLE(MAN_PRIVS.LIST_TAB_PRIVS('SH,HR', NULL, 'SELECT,UPDATE,DELETE,INSERT', 'N', 'PUBLIC'));

-- 查詢用戶系統權限
SELECT * FROM TABLE(MAN_PRIVS.LIST_SYS_PRIVS('SH,HR'));

-- 查詢角色權限
SELECT * FROM TABLE(MAN_PRIVS.LIST_ROLE_PRIVS('SH,HR'));

-- 授予權限
EXEC MAN_PRIVS.GRANT_TAB_PRIVS('HR', 'JOBS', 'SELECT,UPDATE,INSERT', 'PUBLIC');

-- 回收權限
EXEC MAN_PRIVS.REVOKE_TAB_PRIVS('HR', NULL, NULL, 'PUBLIC');

部署與權限要求

• 建議建立在 SYS 或擁有 DBA 權限的用戶下
• 執行者需對以下視圖有 SELECT 權限：DBA_SYS_PRIVS、DBA_ROLE_PRIVS、ROLE_SYS_PRIVS、ROLE_TAB_PRIVS、DBA_TABLES
• 授權/回收操作需 GRANT ANY OBJECT PRIVILEGE 或對象擁有者權限

優勢

1. SQL 介面調用，無需 PL/SQL 塊，便於報表工具/BI 直接接入
2. 參數化過濾，避免全量掃描系統視圖
3. 生成標準 DDL，可直接審計/執行/存檔
4. 統一入口管理系統權限、角色權限、對象權限、角色嵌套