Oracle 唯讀角色自動化建立：授權腳本與同義詞生成雙引擎方案

需求：為指定角色/使用者快速建立「唯讀」存取權限，涵蓋目標 Schema 下所有 TABLE、VIEW、SEQUENCE，並自動建立同義詞簡化存取。本文提供兩支標準 SQL*Plus 自動化腳本：腳本一 掃描 USER_OBJECTS 產生 GRANT SELECT 語句 (去重、跳過已授權)；腳本二 掃描 ALL_TABLES 產生 CREATE SYNONYM 語句 (避免重複)。參數化設計，可直接整合至部署流程。

腳本一：授予 SELECT 權限 (目標 Schema 執行)

-- 參數 &1 = 受授權角色/使用者名稱
-- 用法：@grant_readonly.sql MY_READ_ROLE

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL grant_readonly.sql

SELECT 'GRANT SELECT ON "' || u.object_name || '" TO ' || UPPER('&1') || ';'
FROM   user_objects u
WHERE  u.object_type IN ('TABLE','VIEW','SEQUENCE')
AND    NOT EXISTS (
  SELECT 1
  FROM   all_tab_privs a
  WHERE  a.grantee = UPPER('&1')
  AND    a.privilege = 'SELECT'
  AND    a.table_name = u.object_name
  AND    a.owner = USER  -- 確保是當前 Schema 的物件
);

SPOOL OFF

-- 執行產生的腳本 (取消下行註解可自動執行)
-- @grant_readonly.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

關鍵設計點

• NOT EXISTS 子查詢：避免重複授權導致錯誤或浪費。
• UPPER('&1')：參數自動大寫，符合資料庫內部儲存格式。
• 雙引號包裹物件名："object_name" 保留大小寫/特殊字元 (雖 Oracle 內部通常大寫)。
• SPOOL 輸出檔案，支援審核、版控、分批執行。

腳本二：建立同義詞 (受授權使用者執行)

-- 參數 &1 = 物件擁有者 (目標 Schema)
-- 用法：@create_synonyms.sql TARGET_SCHEMA

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL create_synonyms.sql

SELECT 'CREATE SYNONYM "' || a.table_name || '" FOR "' || a.owner || '"."' || a.table_name || '";'
FROM   all_tables a
WHERE  NOT EXISTS (
  SELECT 1
  FROM   user_synonyms u
  WHERE  u.synonym_name = a.table_name
  AND    u.table_owner = UPPER('&1')
)
AND    a.owner = UPPER('&1');

SPOOL OFF

-- 自動執行 (取消註解)
@create_synonyms.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

同義詞腳本關鍵點

• 來源：ALL_TABLES (包含 TABLE、VIEW、Materialized View)。
• 去重邏輯：檢查 USER_SYNONYMS 已存在同名同義詞則跳過。
• 四段式參照："owner"."table_name" 明確指定來源 Schema。
• 受授權使用者執行：同義詞建立在「受授權者」的 Schema 下。

端到端部署流程

1. DBA/Schema 擁有者 執行腳本一：@grant_readonly.sql REPORT_ROLE
2. 檢查產生的 grant_readonly.sql，確認無誤後執行 (或腳本末尾自動執行)。
3. 受授權使用者** 登入執行腳本二：@create_synonyms.sql SOURCE_SCHEMA
4. 驗證：SELECT * FROM emp; (無需加 Schema 前綴)。