Oracle 將用戶 A 的權限批量授予用戶 B：PL/SQL 動態腳本

需求：將某用戶 (Grantor) 擁有的所有物件權限 (SELECT/INSERT/UPDATE/DELETE/EXECUTE) 批量授予另一用戶 (Grantee)。提供一段通用 PL/SQL 區塊動態產生並執行 GRANT 指令。

PL/SQL 腳本

DECLARE
  p_grantee VARCHAR2(30) := 'YWXT_MODULE_ADMIN';  -- 目標用戶
BEGIN
  FOR rec IN (
    SELECT object_name, object_type,
           DECODE(object_type, 'TABLE', 'ALL', 'VIEW', 'SELECT', 'EXECUTE') AS grant_type
    FROM user_objects
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'GRANT ' || rec.grant_type || ' ON ' || rec.object_name || ' TO ' || p_grantee;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 240));
    END;
  END LOOP;
END;
/

邏輯說明

• 以 USER_OBJECTS 為來源，遍歷當前用戶擁有的所有物件。
• 依物件類型決定授權類型：TABLE → ALL、VIEW → SELECT、其餘 (PROCEDURE/FUNCTION/PACKAGE) → EXECUTE。
• EXECUTE IMMEDIATE 動態執行 GRANT，單條失敗不影響後續 (捕獲例外並輸出錯誤)。
• 以 Grantor 身分登入執行 (需具備 GRANT ANY OBJECT PRIVILEGE 或為物件擁有者)。

擴充建議

• 加入 WHERE object_type IN ('TABLE','VIEW','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE') 過濾不需授權的類型。
• 若需授予 WITH GRANT OPTION，在動態 SQL 中附加。
• 大量物件時建議分批提交，避免長交易鎖定字典。

此腳本適用於開發/測試環境快速同步權限；生產環境建議採用角色 (ROLE) 統一管理，避免逐個物件授權產生維護負債。