同义词是现有对象的别名,可以简化SQL语句、隐藏对象的名称和所有者、提供对对象的公共访问。
同义词有2种类型:私有同义词和公有同义词。
一、私有同义词
私有同义词只能在其模式内访问,且不能与当前模式的对象重名。
创建私有同义词需要有“create synonym”权限。
//先用SCOTT用户创建一个表
SQL> conn scott/scott @orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott@ORCL
SQL> show user;
User is "SCOTT"
SQL> create table test1(id number);
Table created
//用其他用户访问scott用户的test1表,需要用scott.test1。这样很麻烦,而且会暴漏表的用户,很不安全
//先换个用户登录
SQL> conn system/system @orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system@ORCL
SQL> show user;
User is "SYSTEM"
SQL> select * from scott.test1;
ID
----------
//为scott.test1创建一个同义词
SQL> create synonym syname1 for scott.test1;
Synonym created
//可以使用新创建的同义词访问scott.test1
SQL> select * from syname1;
ID
----------
//这里创建的是私有同义词,只能由创建用户即system用户使用,scott用户就不能使用该同义词
//先换scott用户登录
SQL> conn scott/scott @orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott@ORCL
SQL> show user;
User is "SCOTT"
//scott用户不能使用system用户创建的同义词
SQL> select * from syname1;
select * from syname1
ORA-00942: table or view does not exist
//scott用户目前不能创建同义词,因为没有“create synonym”权限
SQL> create synonym syn_02 for test1;
create synonym syn_02 for test1
ORA-01031: insufficient privileges
//切换到system用户,给scott用户赋予“create synonym”权限
SQL> conn system/system @orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system@ORCL
SQL> grant create synonym to scott;
Grant succeeded
//切换回SCOTT用户
SQL> conn scott/scott @orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott@ORCL
//此时可以创建同义词,并可以正常访问
SQL> create synonym syn_02 for test1;
Synonym created
SQL> select * from sysn_02;
SQL> select * from syn_02;
ID
----------
//删除私有同义词
SQL> conn scott/scott@orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott@ORCL
SQL> drop synonym syn_02;
Synonym dropped
二、公有同义词
可以被数据库的所有用户访问。
创建公有同义词需要“create public synonym”权限,若只有“create synonym”权限则不能创建公有同义词。
若其他用户不具有访问某个对象的权限,则即使该用户能访问公有同义词也不能访问该对象。
一般情况,普通用户不能删除自己创建的公有同义词,需要用system用户删除公有同义词。
//创建一个测试表
SQL> create table test2(id number);
Table created
//因为没有“create public synonym”权限,不能创建公有同义词
SQL> create public synonym synp_01 for test2;
create public synonym synp_01 for test2
ORA-01031: insufficient privileges
//切换到system用户,给SCOTT用户赋予“create public synonym”权限
SQL> conn system/system @orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system@ORCL
SQL> grant create public synonym to scott;
Grant succeeded
//切换回scott用户,此时scott用户可以创建公有同义词
SQL> conn scott/scott @orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott@ORCL
SQL> create public synonym synp_01 for test2;
Synonym created
//切换用户测试公有同义词
SQL> conn system/abc123 @orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system@ORCL
SQL> select * from synp_01;
ID
----------
//用system用户创建一个用户testuser01
SQL> create user testuser01 identified by testuser01;
User created
SQL> grant connect to testuser01;
Grant succeeded
SQL> conn testuser01/testuser01 @orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as testuser01@ORCL
//访问公有同义词,提示对象不存在
SQL> select * from synp_01;
select * from synp_01
ORA-00942: table or view does not exist
//原因是testuser01用户没有访问scott.test2的权限
SQL> select * from scott.test2;
select * from scott.test2
ORA-00942: table or view does not exist
//使用scott用户给testuser01用户赋予访问test2的权限
SQL> conn scott/scott @orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott@ORCL
SQL> grant select on test2 to testuser01;
Grant succeeded
//切换回testuser01用户,此时该用户已可以通过公有同义词访问scott.test2
SQL> conn testuser01/testuser01@orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as testuser01@ORCL
SQL> select * from scott.test2;
ID
----------
SQL> select * from synp_01;
ID
----------
//切换到scott用户,尝试删除synp_01,发现权限不足
SQL> conn scott/scott@orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott@ORCL
SQL> drop public synonym synp_01;
drop public synonym synp_01
ORA-01031: insufficient privileges
//切换到system用户,删除公有同义词
SQL> conn system/system@orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system@ORCL
SQL> drop public synonym synp_01;
Synonym dropped