官檔同義詞學習

同義詞學習（和兜兜一起學習官檔）

 

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

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-753423/，如需轉載，請註明出處，否則將追究法律責任。