Oracle 用戶權限複製腳本：從來源用戶批量授權予目標用戶

在 Oracle 環境中，需將某用戶（Grantor）擁有的物件權限完整複製給另一用戶（Grantee）是常見運維需求。本文提供一支健壯的 PL/SQL 匿名區塊，能依物件型別智慧決定授權模式（TABLE→ALL、VIEW→SELECT、其餘→EXECUTE），並內建例外處理與輸出日誌，適合生產環境直接執行。

核心授權邏輯

DECODE(object_type,
  'TABLE', 'ALL',
  'VIEW',  'SELECT',
           'EXECUTE')  -- PROCEDURE/FUNCTION/PACKAGE/SEQUENCE 等

此對應遵循最小權限原則：表需完整 DML、視圖僅查詢、程式單元僅執行。

完整執行腳本

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  p_grantee VARCHAR2(30) := 'TARGET_USER';  -- 修改為目標用戶名
  v_sql     VARCHAR2(500);
  v_cnt     NUMBER := 0;
  v_err     NUMBER := 0;
BEGIN
  FOR rec IN (
    SELECT object_name, object_type,
           DECODE(object_type, 'TABLE', 'ALL', 'VIEW', 'SELECT', 'EXECUTE') AS priv
    FROM   user_objects
    WHERE  object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE','SEQUENCE','TYPE')
  ) LOOP
    v_sql := 'GRANT ' || rec.priv || ' ON ' || rec.object_name || ' TO ' || p_grantee;
    BEGIN
      EXECUTE IMMEDIATE v_sql;
      v_cnt := v_cnt + 1;
      DBMS_OUTPUT.PUT_LINE('[OK] ' || v_sql);
    EXCEPTION
      WHEN OTHERS THEN
        v_err := v_err + 1;
        DBMS_OUTPUT.PUT_LINE('[ERR] ' || v_sql || ' => ' || SQLERRM);
    END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('=== 完成：成功 ' || v_cnt || ' 筆，失敗 ' || v_err || ' 筆 ===');
END;
/

使用步驟與注意事項

1. 以 Grantor 身分登入：CONN source_user/pwd
2. 修改 p_grantee 為目標用戶名
3. 執行腳本：在 SQL*Plus、SQL Developer 或 PL/SQL Developer 中執行
4. 檢視輸出：關注 `[ERR]` 行，常見錯誤含：
   • ORA-01917：用戶不存在 → 先 CREATE USER
   • ORA-01749：Grantor 無 GRANT OPTION → 需系統管理員介入
   • ORA-00942：物件不存在 → 可能為同義詞或已刪除

-- 若需從 ALL_OBJECTS 跨 Schema 授權（需 DBA 權限）
FOR rec IN (
  SELECT owner, object_name, object_type,
         DECODE(object_type, 'TABLE', 'ALL', 'VIEW', 'SELECT', 'EXECUTE') AS priv
  FROM   all_objects
  WHERE  owner = 'SOURCE_SCHEMA'
    AND  object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE','SEQUENCE','TYPE')
) LOOP
  v_sql := 'GRANT ' || rec.priv || ' ON ' || rec.owner || '.' || rec.object_name || ' TO ' || p_grantee;
  -- 其餘邏輯相同
END LOOP;

-- 系統權限
SELECT 'GRANT ' || privilege || ' TO ' || p_grantee || DECODE(admin_option,'YES',' WITH ADMIN OPTION','') || ';'
FROM   dba_sys_privs WHERE grantee = 'SOURCE_USER';

-- 角色
SELECT 'GRANT ' || granted_role || ' TO ' || p_grantee || DECODE(admin_option,'YES',' WITH ADMIN OPTION','') || ';'
FROM   dba_role_privs WHERE grantee = 'SOURCE_USER';