Oracle 跨 Schema 存取資料表：同義詞與權限完整指南

在 Oracle 資料庫中，若使用者 A 需存取使用者 B 擁有的資料表，標準寫法為 SELECT * FROM B.TABLENAME。但能否透過設定讓 A 直接使用 SELECT * FROM TABLENAME 即可存取？答案是肯定的，核心在於建立同義詞並授予權限。

核心步驟

1. 建立同義詞（以 SYSTEM 或 DBA 身分執行）：
   CREATE SYNONYM tablename FOR B.TABLENAME;
2. 授予存取權限：
   GRANT SELECT ON B.TABLENAME TO A;
   或授予同義詞權限：
   GRANT SELECT ON tablename TO A;

批次建立同義詞技巧

若需為大量資料表建立同義詞，可利用動態 SQL 生成腳本：

SELECT 'CREATE SYNONYM ' || table_name || ' FOR B.' || table_name
FROM dba_tables WHERE owner = 'B';

將查詢結果匯出並執行，即可一次完成。亦可封裝為預存程序自動化。

替代方案：SELECT ANY TABLE 權限

授予 GRANT SELECT ANY TABLE TO A; 可讓 A 存取所有 Schema 的資料表，但權限過大，生產環境建議採用同義詞方式精細控制。

常見誤區

• 僅建立同義詞未授權 → 仍報 ORA-00942。
• 同義詞名稱與本地資料表衝突 → 需先 DROP SYNONYM。

善用同義詞可簡化 SQL 撰寫、解耦 Schema 依賴，是跨 Schema 存取的最佳實踐。