[大数据]Oracle数据库管理（14）——同义词

同义词是现有对象的别名，可以简化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