您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 批量授權實戰：建立唯讀使用者與同義詞自動化腳本

編輯：Oracle 資料庫基礎

需求：使用者 A 無自有物件，需透過同義詞存取使用者 B 的所有表/視圖/序列/程式，且僅具備 SELECT 權限 (不可見 B 的結構定義)。本文提供完整的六步驟自動化流程：建立使用者、授予基本權限、產生同義詞腳本、切換身分建立同義詞、產生物件授權腳本、執行授權，全程可腳本化、可版控。

前置條件

擁有 DBA 權限或 CREATE USER、GRANT ANY PRIVILEGE、CREATE ANY SYNONYM 權限。
目標 Schema B 已存在且擁有完整物件。

六步驟自動化流程

步驟 1：建立唯讀使用者 A


CREATE USER a
  IDENTIFIED BY "password"
  DEFAULT TABLESPACE lmis
  TEMPORARY TABLESPACE lmis
  PROFILE DEFAULT;

GRANT CREATE SESSION TO a;
GRANT CREATE SYNONYM TO a;  -- 允許自建同義詞

步驟 2：以擁有者 B 身分產生「建立同義詞」腳本


-- 在 B 的 Session 中執行 (SQL*Plus 環境)
SET HEADING OFF
SET FEEDBACK OFF
SPOOL c:\temp\create_synonyms.sql

SELECT 'CREATE SYNONYM ' || tname || ' FOR b.' || tname || ';'
FROM   tab;  -- 或 user_tables/user_views/user_sequences...

SPOOL OFF

TAB 視圖包含 TABLE、VIEW、SYNONYM、SEQUENCE 等，產出的腳本範例：


CREATE SYNONYM emp FOR b.emp;
CREATE SYNONYM dept FOR b.dept;
CREATE SYNONYM emp_seq FOR b.emp_seq;

步驟 3：切換至使用者 A 執行同義詞腳本


CONN a/password
@c:\temp\create_synonyms.sql

此時 A 可直接用 SELECT * FROM emp (實際查詢 b.emp)。

步驟 4：以擁有者 B 身分產生「物件授權」腳本


CONN b/password
SET HEADING OFF
SET FEEDBACK OFF
SPOOL c:\grant_a.sql

-- 表/視圖/同義詞：SELECT, INSERT, UPDATE, DELETE, REFERENCES, INDEX
SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, INDEX ON ' || tname || ' TO a;'
FROM   tab
WHERE  tabtype <> 'VIEW'

UNION ALL

-- 視圖：僅 SELECT, INSERT, UPDATE, DELETE (無 INDEX/REFERENCES)
SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || object_name || ' TO a;'
FROM   user_objects
WHERE  object_type = 'VIEW'

UNION ALL

-- Procedure/Function/Package：EXECUTE
SELECT 'GRANT EXECUTE ON ' || object_name || ' TO a;'
FROM   user_objects
WHERE  object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')

UNION ALL

-- Sequence：SELECT
SELECT 'GRANT SELECT ON ' || object_name || ' TO a;'
FROM   user_objects
WHERE  object_type = 'SEQUENCE';

SPOOL OFF

步驟 5：執行授權腳本 (仍在 B Session)


@c:\grant_a.sql

步驟 6：驗證


CONN a/password
SELECT * FROM emp;           -- 成功
SELECT * FROM user_tab_privs; -- 確認權限
INSERT INTO emp ...;         -- 失敗 (ORA-01031) 若步驟4未給 INSERT

關鍵技術細節

環節	關鍵點
SPOOL 檔案路徑	使用資料庫伺服器可寫入路徑 (非 Client 端)，或改用 `UTL_FILE` 寫入伺服器目錄
TAB vs USER_OBJECTS	`TAB` 相容舊版，新版建議用 `USER_OBJECTS` 並過濾 `OBJECT_TYPE`
視圖授權差異	視圖無 `INDEX`/`REFERENCES` 權限，需分開產生
大小寫敏感	腳本中物件名建議加雙引號 `"emp"` 避免保留字衝突
Public Synonym 替代	若多用戶共用，可改建 `PUBLIC SYNONYM` (需 `CREATE PUBLIC SYNONYM` 權限)

現代化改寫建議 (11g+)


-- 使用 PL/SQL 迴圈直接執行，免去 SPOOL/檔案中轉
BEGIN
  FOR r IN (SELECT object_name, object_type FROM user_objects
            WHERE object_type IN ('TABLE','VIEW','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE'))
  LOOP
    DECLARE
      v_sql VARCHAR2(4000);
    BEGIN
      CASE r.object_type
        WHEN 'TABLE' THEN
          v_sql := 'GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, INDEX ON ' || r.object_name || ' TO a';
        WHEN 'VIEW' THEN
          v_sql := 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || r.object_name || ' TO a';
        WHEN 'SEQUENCE' THEN
          v_sql := 'GRANT SELECT ON ' || r.object_name || ' TO a';
        ELSE -- PROCEDURE, FUNCTION, PACKAGE
          v_sql := 'GRANT EXECUTE ON ' || r.object_name || ' TO a';
      END CASE;
      EXECUTE IMMEDIATE v_sql;
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('FAIL: ' || v_sql || ' - ' || SQLERRM);
    END;
  END LOOP;
END;
/

此流程實現了「結構隱藏、權限最小化、自動化部署」三大目標，是跨 Schema 唯讀/讀寫分離的經典範本。