ORACLE 同義字（SYNONYM）詳解

ORACLE 同義字（SYNONYM）詳解

以下內容整理自Oracle 官方文檔

一 概念

A synonym is an alias for any table, view,materialized view, sequence, procedure, function, package, type, Java classschema object, user-defined object type, or another synonym. Because a synonymis simply an alias, it requires no storage other than its definition in thedata dictionary.

Oracle中同義字是任何錶、視圖、物化視圖、序列、預存程序、函數、包、類型、JAVA類對象、使用者定義型別,或是其他的同義字的別名。由於其只是一個別名，所以除了在

資料字典中的定義不佔任何空間。

 

Synonyms are often used for security andconvenience. For example, they can do the following:

同義字常用於安全和方便。例如，它們可以做：

1. Mask the name and owner of anobject  偽裝對象名稱和其所有者。

2. Provide location transparency for remoteobjects of a distributed database 為分散式資料庫遠程對象提供位置透明性

3. Simplify SQL statements for databaseusers 簡化資料庫使用者訪問對象SQL語句

4. Enable restricted access similar tospecialized views when exercising fine-grained access control 當行使精細化存取控制時提供類似指定視圖的訪問限制

 

 

You can create both public and privatesynonyms. A public synonym is owned by the special user group named PUBLIC andevery user in a database can access it. A private synonym is in the schema of aspecific user who has control over its availability to others.

你可以建立public和private同義字。public同義字屬於PUBLIC組，每個使用者都可以訪問。private同義字屬於對象所有者，只有其顯式授權後其他使用者才可訪問。

 

Synonyms are very useful in bothdistributed and nondistributed database environments because they hide theidentity of the underlying object, including its location in a distributedsystem. This is advantageous because if the underlying object must be renamedor moved, then only the synonym needs to be redefined. Applications based onthe synonym continue to function without modification.

同義字的優勢體現在如果其底層的對象重新命名或者轉移，那麼只需要重定義該同義字。基於該同義字的應用則無需任何修改。

 

Synonyms can also simplify SQL statementsfor users in a distributed database system. The following example shows how andwhy public synonyms are often created by a database administrator to hide theidentity of a base table and reduce the complexity of SQL statements. Assume thefollowing:

下面舉例說明同義字是如何簡化使用者訪問的：

 

A table called SALES_DATA is in the schemaowned by the user JWARD.

JWARD使用者下有一張表 SALES_DATA

 

The SELECT privilege for the SALES_DATAtable is granted to PUBLIC.

PUBLIC組有SALES_DATA的查詢許可權

 

At this point, you have to query the tableSALES_DATA with a SQL statement similar to the following:

此時，你如果查詢SALES_DATA表則需以下語句：

SELECT * FROM jward.sales_data;

 

Notice how you must include both the schemathat contains the table along with the table name to perform the query.

 

Assume that the database administratorcreates a public synonym with the following SQL statement:

假如資料庫管理員建立了一個public 同義字：

 

CREATE PUBLIC SYNONYM sales FORjward.sales_data;

 

After the public synonym is created, youcan query the table SALES_DATA with a simple SQL statement:

你的語句將簡化為：

 

SELECT * FROM sales;

 

Notice that the public synonym SALES hidesthe name of the table SALES_DATA and the name of the schema that contains thetable.

 

 

二、CREATE SYNONYM 建立同義字

 1、文法結構：

 

2、前提條件：

To create a private synonym in your own schema, you must have the CREATE SYNONYM system privilege.

在自己模式下建立私人同義字需要CREATE SYNONYM許可權。

To create a private synonym in another user's schema, you must have the CREATE ANY SYNONYM system privilege.

在其他使用者模式下建立私人同義字需要CREATE ANY SYNONYM許可權。

To create a PUBLIC synonym, you must have the CREATE PUBLIC SYNONYM system privilege.

建立公有同義字，需要有CREATE PUBLIC SYNONYM許可權。

 

3、樣本

 sqlplus / as sysdba

 

 CREATE SYNONYM offices

  FOR hr.locations;

  

 GRANT SELECT ON hr.locations to SCOTT;

 

 CREATE PUBLIC DATABASE LINK l_hr

  CONNECT TO hr IDENTIFIED BY hr

  USING 'orcl';

 

 CREATE PUBLIC SYNONYM emp_table

  FORHR.employees@l_hr;

 

 GRANT SELECT ON HR.employees to SCOTT;

 

 conn scott/tiger@orcl

 

 SELECT count(*) from sys.offices;

 

 select count(*) from emp_table;

   

  

三、DROP SYNONYM  刪除同義字

  1、文法結構：

 

2、前提條件：

To drop a private synonym, either the synonym must be in your own schema or you must have the DROP ANY SYNONYM system privilege.

刪除私人同義字需要有DROP ANY SYNONYM許可權。

To drop a PUBLIC synonym, you must have the DROP PUBLIC SYNONYM system privilege.

刪除公有同義字需要有DROP PUBLIC SYNONYM許可權。

 

3、樣本

  3.1刪除public同義字，必須加public關鍵字：

         SYS@orcl>DROP SYNONYM emp_table;

         DROPSYNONYM emp_table

                                      *

         第 1 行出現錯誤:

         ORA-01434:要刪除的專用同義字不存在

 

 

         SYS@orcl>DROP PUBLIC SYNONYM emp_table;

 

         同義字已刪除。

 

  3.2刪除private同義字：

 DROP SYNONYM offices;

 

四、Q&A 問答

 

Q: 可以對同義字做INSERT/UPDATE/DELETE操作嗎？

A:

SCOTT@orcl> UPDATE sys.offices t SETt.city='Shanghai' WHERE location_id=1000;

UPDATE sys.offices t SET t.city='Shanghai'WHERE location_id=1000

          *

第 1 行出現錯誤:

ORA-01031: 許可權不足

SYS@orcl> grant update on hr.locationsto scott;

 

授權成功。

SCOTT@orcl> /

 

已更新 1 行。

 

SO: 使用者對同義字的操作許可權都是基於對其底層對象有哪些操作許可權。

 

 

 

 

-------------------------------

Dylan   Presents.