一、概念
ORACLE的同义词分为两种:私有同义词和公有同义词
私有同义词:普通用户创建,只有创建该同义词的用户才能访问;其他用户获得授权后也可
以访问
公有同义词:一般由dba建立,所有用户都可以访问。
二、创建同义词及所需权限
1、创建私有同义词(需要有 create synonym权限)
Create synonym synonym_name for object_name
例:
XSH1>create synonym ep for t2;
Synonym created.
2、穿件公有同义词 (需要 create public synonym 权限)
Create public synonym synonym_name for object_name
例:
XSH>create public synonym ep2 for t1;
Synonym created.
3、在a用户下 创建b用户的同义词 (需要create any synonym权限 )
XSH>conn xsh1/xsh1
Connected.
XSH1>create synonym xsh.em8 for t2;
create synonym xsh.em8 for t2
*
ERROR at line 1:
ORA-01031: insufficient privileges
XSH1>conn / as sysdba
Connected.
SYS>grant create any synonym to xsh1;
Grant succeeded.
SYS>conn xsh1/xsh1
Connected.
XSH1>create synonym xsh.em8 for t2;
Synonym created.
三、授权及访问
公有同义词及私有同义词,建好之后,其他用户需获得该同义词的访问权限,或获得该同义词对象的访问权限之后,其他用户才能访问该同义词:
私有同义词: 其他用户获得私有同义词的访问权限后,需在同义词前加schema:
XSH>create synonym em5 for custer_1;
Synonym created.
XSH1>select * from em5;
select * from em5
*
ERROR at line 1:
ORA-00942: table or view does not exist --synonym前没加schema
XSH1>select * from xsh.em5; --加schema 后正常
ID SALER
---------- ----------
6 350
7 400
公有同义词:授权后无需加schema:
XSH>create public synonym em6 for custer_1;
Synonym created.
XSH>conn xsh1/xsh1
Connected.
XSH1>select * from em6;
ID SALER
---------- ----------
6 350
7 400
同义词授权:grant select on em5 to xsh1;
收回权限:revoke select on em5 from xsh1
四、查看同义词
当前用户下私有同义词 user_synonyms
查看所有的同义词 dba_synonyms
五、删除同义词:
XSH>drop synonym em5;
Synonym dropped.
XSH>drop public synonym em6;
Synonym dropped.
六、同义词编译
当前用户只能编译该用户下的私有同义词(E5是私有同义词,EM6是公有同义词),
XSH>alter synonym em5 compile;
Synonym altered.
XSH>alter public synonym em6 compile;
alter public synonym em6 compile
*
ERROR at line 1:
ORA-01031: insufficient privileges
Sys用户才能编译公有同义词:
XSH>conn / as sysdba
Connected.
SYS>alter public synonym em6 compile;
Synonym altered.
SYS>show user
USER is "SYS"