同义词学习 （和兜兜一起学习官档）

1、 Overview of Synonyms

A synonym is an alias for a schema object.Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.

【同义词就是对象的别名，不需要存储，只是在数据字典定义】

2、 权限

a)        用户创建 同义词需要有 create synonym 权限

b)        用户创建 私有同义词在其他模式下需要有 create any synonym 权限

c)        用户创建 公有同义词需要有 create public synonym 权限

d)        用户删除公有同义词需要有 drop public synonym 权限

sys@TEST> grant create synonym to test;

Grant succeeded.

sys@TEST> grant create any synonym to test;

Grant succeeded.

sys@TEST> grant create public synonym to test;

Grant succeeded

3、   建立同义词范例 （ test 用户建立）

test@TEST> create synonym t_synonym for t;

Synonym created.

test@TEST> create synonym t_synonym_others for doudou.t1;

Synonym created.

test@TEST> create public synonym t_synonym_public for doudou.test;

Synonym created.

【推荐建立 synonym 最好指定 schemas name 】

4、   删除同义词范例 （删除公有同义词需要有 drop public synonym 权限）

test@TEST> drop synonym t_synonym;

Synonym dropped.

test@TEST> drop synonym t_synonym_others;

Synonym dropped.

test@TEST> drop public synonym t_synonym_public;

drop public synonym t_synonym_public

                    *

ERROR at line 1:

ORA-01031: insufficient privileges

用户 test 没有 drop public synonym 权限，所以报错；授权后可以删除

sys@TEST> grant drop public synonym to test;

Grant succeeded.

                   test@TEST> drop public synonym t_synonym_public;

Synonym dropped.

5 、Using Synonyms in DML Statements

因为 test 没有对 doudou.t5 表 insert 权限，所以报错

test@TEST> insert into t5a_sm values (1,'1',1);

insert into t5a_sm values (1,'1',1)

            *

ERROR at line 1:

ORA-01031: insufficient privileges

授权后，成功插入

sys@TEST> grant insert on doudou.t5 to test;

Grant succeeded

test@TEST> insert into t5a_sm values (1,'1',1);

1 row created.

test@TEST> commit;

Commit complete.

test@TEST> select * from t5a_sm;

   EQUIPNO EQUIPNAME                                                              PRICE

---------- ---------------------------------------------------------------- ----------

         1 1                                                                          1

doudou@TEST> select * from t5;

   EQUIPNO EQUIPNAME                                                              PRICE

---------- ---------------------------------------------------------------- ----------

         1 1                                                                          1

附表

一、 同义词与对象的关系

a)        同义词仅仅是对象的一个别名，只要数据字典中没有 synonym name 的记录，就可以创建同义词，无论对象是否存在，因为同义词只是把定义写入数据字典中。

b)        没有对象的同义词可以建立成功，但没有任何意义；数据库会把这个同义词视为无效的同义词。

c)        同义词创建后，但是没有对象的相应权限，那就不能对同义词进行相应的操作，等待授权后，一切就都可以了。

二、实验

1 、 DOUDOU 用户有表 T5 ， TEST 用户没有表 T5 ；用户 test 建立 t5 同义词 t5_sm 、 t5a_sm

doudou@TEST> select * from tab;

TNAME                                                         TABTYPE          CLUSTERID

------------------------------------------------------------ -------------- ----------

T5                                                            TABLE

test@TEST> create synonym t5_sm for t5;

Synonym created.

test@TEST> create synonym t5a_sm for doudou.t5;  

Synonym created.

test@TEST> select * from tab;

TNAME                                                         TABTYPE          CLUSTERID

------------------------------------------------------------ -------------- ----------

T5_SM                                                         SYNONYM

T5A_SM                                                        SYNONYM

sys@TEST>   select owner,synonym_name,table_owner,table_name from dba_synonyms where synonym_name='T5_SM';

OWNER                 SYNONYM_NAME                     TABLE_OWNER           TABLE_NAME

-------------------- ------------------------------ -------------------- ------------------------------

TEST                  T5_SM                            TEST                   T5

sys@TEST>   select owner,synonym_name,table_owner,table_name from dba_synonyms where synonym_name='T5A_SM';

OWNER                 SYNONYM_NAME                    TABLE_OWNER           TABLE_NAME

-------------------- ------------------------------ -------------------- ------------------------------

TEST                  T5A_SM                          DOUDOU                T5

查看同义词 t5_sm,t5a_sm 报错是不同的。

         T5_sm 同义词无效

         T5a_sm 同义词是有效的，但是表或试图不存在，很简单 test 没有权限去看 doudou 的 t5 表

test@TEST> select count(*) from t5_sm;

select count(*) from t5_sm

                     *

ERROR at line 1:

ORA-00980: synonym translation is no longer valid

test@TEST> select count(*) from t5a_sm;

select count(*) from t5a_sm

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

给 test 用户权限，让它可以看到 t5 ，这样同义词也可以看到数据了

sys@TEST> grant select on doudou.t5 to test;

Grant succeeded.

test@TEST> select count(*) from t5a_sm;

  COUNT(*)

----------

         0

2 、对象没有相应的权限，同义词就不能相应的操作，授权后即可

因为 test 没有对 doudou.t5 表 insert 权限，所以报错

test@TEST> insert into t5a_sm values (1,'1',1);

insert into t5a_sm values (1,'1',1)

            *

ERROR at line 1:

ORA-01031: insufficient privileges

sys@TEST> grant insert on doudou.t5 to test;

Grant succeeded

test@TEST> insert into t5a_sm values (1,'1',1);

1 row created.

test@TEST> commit;

Commit complete.

test@TEST> select * from t5a_sm;

   EQUIPNO EQUIPNAME                                                              PRICE

---------- ---------------------------------------------------------------- ----------

         1 1                                                                          1

doudou@TEST> select * from t5;

   EQUIPNO EQUIPNAME                                                              PRICE

---------- ---------------------------------------------------------------- ----------

         1 1                                                                          1

三、知识点

同义词介绍

http://docs.oracle.com/cd/E11882_01/server.112/e25789/schemaob.htm#autoId25

同义词管理

http://docs.oracle.com/cd/E11882_01/server.112/e25494/views003.htm#i1106663