Oracle 查詢權限批量授予：DBMS_SQL 動態游標遍歷 USER_TABLES 實戰

需求：將當前 Schema 下所有表的 `SELECT` 權限批量授予指定使用者 (如 `brokeruser3`)。本文提供一支使用 `DBMS_SQL` 套件的 PL/SQL 存儲過程，動態開啟游標遍歷 `USER_TABLES`，組裝並執行 `GRANT SELECT` 語句，適合在無法使用 `EXECUTE IMMEDIATE` 批量綁定的舊版環境或教學示範 `DBMS_SQL` 低階 API 用法。

完整存儲過程程式碼

CREATE OR REPLACE PROCEDURE grant_select_all_tables (p_grantee IN VARCHAR2) AS
  v_cursor      INTEGER;
  v_sql         VARCHAR2(4000);
  v_table_name  VARCHAR2(128);
  v_rows        INTEGER;
BEGIN
  -- 1. 開啟游標
  v_cursor := DBMS_SQL.OPEN_CURSOR;

  -- 2. 解析查詢 SQL (取得所有表名)
  DBMS_SQL.PARSE(v_cursor,
                 'SELECT table_name FROM user_tables ORDER BY table_name',
                 DBMS_SQL.NATIVE);

  -- 3. 定義輸出欄位
  DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_table_name, 128);

  -- 4. 執行查詢
  v_rows := DBMS_SQL.EXECUTE(v_cursor);

  -- 5. 迴圈提取並授權
  LOOP
    IF DBMS_SQL.FETCH_ROWS(v_cursor) > 0 THEN
      DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_table_name);

      -- 組裝 GRANT 語句
      v_sql := 'GRANT SELECT ON ' || v_table_name || ' TO ' || p_grantee;

      -- 執行授權
      EXECUTE IMMEDIATE v_sql;
      DBMS_OUTPUT.PUT_LINE('GRANTED: ' || v_sql);
    ELSE
      EXIT;
    END IF;
  END LOOP;

  -- 6. 關閉游標
  DBMS_SQL.CLOSE_CURSOR(v_cursor);

EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(v_cursor) THEN
      DBMS_SQL.CLOSE_CURSOR(v_cursor);
    END IF;
    DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);
    RAISE;
END grant_select_all_tables;
/

執行範例

SET SERVEROUTPUT ON
EXEC grant_select_all_tables('brokeruser3');

DBMS_SQL vs EXECUTE IMMEDIATE 對比

現代化改寫 (EXECUTE IMMEDIATE + 隱式游標)

CREATE OR REPLACE PROCEDURE grant_select_all_tables_v2 (p_grantee IN VARCHAR2) AS
BEGIN
  FOR r IN (SELECT table_name FROM user_tables ORDER BY table_name) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'GRANT SELECT ON ' || r.table_name || ' TO ' || p_grantee;
      DBMS_OUTPUT.PUT_LINE('GRANTED: ' || r.table_name);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('FAILED: ' || r.table_name || ' - ' || SQLERRM);
    END;
  END LOOP;
END;
/

優點： 程式碼簡潔 80%、自動游標管理、例外處理粒度細 (單表失敗不影響後續)。

進階增強功能

1. 排除特定表： `WHERE table_name NOT IN ('AUDIT_LOG','TEMP_*')`
2. 支援視圖/物化視圖： `FROM user_tables UNION ALL SELECT view_name FROM user_views`
3. 記錄稽核表： 在例外區塊 `INSERT INTO grant_audit_log ...`
4. 並行授權 (12c+)： 使用 `DBMS_PARALLEL_EXECUTE` 切分表清單並行執行。
5. Schema 參數化： 掃描 `ALL_TABLES WHERE owner = p_owner`，授權 `p_owner.table_name`。

安全性檢查

• 執行者需為表擁有者或擁有 `GRANT ANY OBJECT PRIVILEGE`。
• 目標使用者 `p_grantee` 必須存在。
• 防止 SQL 注入：`p_grantee` 應經 `DBMS_ASSERT.ENQUOTE_NAME` 驗證。

-- 安全版參數處理
v_sql := 'GRANT SELECT ON ' || r.table_name || ' TO ' || DBMS_ASSERT.ENQUOTE_NAME(p_grantee);

雖然 `DBMS_SQL` 展示了動態 SQL 的底層運作，但現代開發首選 `EXECUTE IMMEDIATE` + 隱式游標，程式碼更簡潔、可維護性更高。批量授權腳本建議納入部署自動化流程，並保留稽核日誌。