同義詞是物件的別名。Oracle同義詞有公和私有兩種。

同義詞不佔用任何實際的儲存空間，只是在Oracle的資料字典中保留其定義描述。

公有同義詞，為一個特殊的使用者組Public所擁有，資料庫中所有使用者都可以使用公有同義詞。公有同義詞一般用來標識一些比較普通的資料庫物件，
這些物件往往大家都需要引用。

私有同義詞，由建立它的使用者所擁有，建立者可以透過授權控制其它使用者是否有權使用該同義詞。

基本語法:
create [public] synonym for [模式.]物件名[@資料庫連結];

例1:建立公有同義詞
SQL> create public synonym books for zhangbin.books;

Synonym created

SQL> create public synonym pubbooks for zhangbin.books;

Synonym created

SQL> select * from all_synonyms where table_owner = 'ZHANGBIN';

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------
PUBLIC PUBBOOKS ZHANGBIN BOOKS

SQL> select * from user_synonyms;

SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------------------ ------------------------------ ------------------------------ --------------------------

結果驗證了，公有同義詞建立到了Public使用者下。使用者私有同義詞檢視下沒有記錄。

例2:建立私有同義詞
SQL> create synonym my_books for zhangbin.books;

Synonym created

SQL> select * from user_synonyms;

SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------------------ ------------------------------ ------------------------------ -------------------------
MY_BOOKS ZHANGBIN BOOKS

--此時使用者私有同義詞下有記錄。

SQL> select * from all_synonyms where table_owner = 'ZHANGBIN';

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------
PUBLIC PUBBOOKS ZHANGBIN BOOKS
ZHANGBIN MY_BOOKS ZHANGBIN BOOKS

可以看到owner列分別為PUBLIC 和使用者。

*注意:使用同義詞只能簡化SQL的書寫，使用同義詞訪問資料庫物件時，仍然需要正常的授權。

例3:
SQL> conn a/a;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as a

SQL> select * from pubbooks;

select * from pubbooks

ORA-00942: 表或檢視不存在

SQL> conn zhangbin/zhangbin;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as zhangbin

SQL> grant select on zhangbin.books to a;

Grant succeeded

SQL> conn a/a
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as a

SQL> select * from pubbooks;

BOOKID BOOKNUM BOOKNAME PUBID BOOKPRICE TYPEID BOOKTIME SALESCOUNT CONTENT IMG
------- ------- -------- ------- ---------- ------- ----------- ---------- -------- ----

結果驗證了需要授權！

例4: 利用私有同義詞訪問
1.grant select on zhangbin.books to a;

2.conn a/a;
create synonym zhangbin_books for zhangbin.books;

select * from zhangbin_books;
==================================================
SQL> grant select on books to a;

Grant succeeded

SQL> conn a/a
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as a

SQL> create synonym zhangbin_books for zhangbin.books;

Synonym created

SQL> select * from zhangbin_books;

BOOKID BOOKNUM BOOKNAME PUBID BOOKPRICE TYPEID BOOKTIME SALESCOUNT CONTENT IMG
------- ------- -------- ------- ---------- ------- ----------- --------------------------------------- --------

同義詞的作用:
1.多使用者協同開發中，可以遮蔽物件的名字及其持有者，從而簡化SQL語句的書寫.
2.同義詞可以指向遠端Oracle資料庫伺服器上的物件，這為訪問分散式資料庫上的遠端物件提供位置透明性。

Oracle資料庫開發——同義詞

相關文章