Oracle 跨 Schema 與 Database Link 使用同義詞實現透明訪問

場景背景

在企業級 Oracle 環境中，常見的需求是讓不同實例、不同 Schema 的用戶透明地訪問彼此的資料庫物件。本文記錄一個典型案例：兩個 SID（Asid、Bsid），Asid 下有 Auser1、Auser2 兩個用戶，Bsid 下有 Buser1。Auser2 與 Buser1 之間已建立 Database Link。目標是讓 Buser1 通過同義詞直接查詢 Auser1 下的三張表（table1、table2、table3）與一個函數（function1），無需在 SQL 中硬編碼遠端物件名稱。

實施步驟

第一步：在物件擁有者授權

以 Auser1 登錄，將所需物件的權限授予 Auser2，使其成為中轉跳板：

GRANT INSERT, SELECT, UPDATE ON table1 TO Auser2;
GRANT INSERT, SELECT, UPDATE ON table2 TO Auser2;
GRANT INSERT, SELECT, UPDATE ON table3 TO Auser2;
GRANT EXECUTE ON function1 TO Auser2;

提示：若需刪除權限，可在 GRANT 後補充 DELETE。

第二步：在中轉用戶建立本地同義詞

以 Auser2 登錄，為 Auser1 的物件建立同義詞，隱藏 Schema 前綴：

CREATE SYNONYM table1 FOR Auser1.table1;
CREATE SYNONYM table2 FOR Auser1.table2;
CREATE SYNONYM table3 FOR Auser1.table3;
CREATE SYNONYM function1 FOR Auser1.function1;

注意： 原文中 table2、table3 的 FOR 子句誤寫為 Auser1.table1，此處已修正。

第三步：確認遠端 Database Link 名稱

以 Buser1 登錄，查詢可用的 DB Link：

SELECT username, db_link FROM user_db_links;

假設查得 DB Link 名稱為 mydblink1。

第四步：在目標用戶建立跨 DB Link 同義詞

以 Buser1 登錄，指向 Auser2 的同義詞並附加 DB Link：

CREATE SYNONYM table1 FOR Auser2.table1@mydblink1;
CREATE SYNONYM table2 FOR Auser2.table2@mydblink1;
CREATE SYNONYM table3 FOR Auser2.table3@mydblink1;
CREATE SYNONYM function1 FOR Auser2.function1@mydblink1;

第五步：驗證測試

此時 Buser1 即可直接執行：

SELECT * FROM table1;

即可透明獲取 Auser1.table1 的數據，同理適用於其他表與函數。

進階補充：Procedure 同義詞的事務限制

同樣可以為預存程序建立同義詞，但需注意：透過 DB Link 執行遠端 Procedure 時，不允許在遠端執行 COMMIT。若業務需要提交，應在客戶端（如 JDBC 中調用 connection.commit()）統一控制事務邊界。

最佳實踐小結

• 採用「授權→本地同義詞→遠端同義詞」三層鏈路，實現物理位置與邏輯訪問的解耦。
• 同義詞命名建議與基礎物件同名，減少應用改造成本。
• 定期審核 DB Link 與同義詞鏈路，避免因底層物件變更導致失效。