同义词是对象的别名。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数据库服务器上的对象，这为访问分布式数据库上的远程对象提供位置透明性。

ITPub博客

Oracle数据库开发——同义词

最新文章