同义词是对象的别名。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的数据字典中保留其定义描述。
公有同义词,为一个特殊的用户组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博客 ” ,链接:http://blog.itpub.net/28929558/viewspace-1147632/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28929558/viewspace-1147632/