Oracle同義詞概念

Oracle同義詞

Oracle的同義詞（synonyms）跟View功能類似，但更強大。在Oracle數據庫中的大部分數據庫對象，如表、視圖、物化視圖、序列、函數、存儲過程、包、同義詞等等都可以做為同義詞。其目的主要有2個：用于簡化對象訪問和提高對象訪問的安全性。

下面簡單做個演示：

SQL> conn scott/tiger

Connected.

SQL> create table test1 (id number);

Table created.

SQL> insert into test1 values(1);

1 row created.

SQL> commit;

1、創(chuàng)建連接用戶：

SQL> conn /as sysdba;

Connected.

SQL> create user zsl identified by zsl;

User created.

2、創(chuàng)建profile：

SQL> create profile zsl_test1 limitsessions_per_user 5;

Profile created.

3、設置profile綁定用戶：

SQL> alter user zsl profile zsl_test1;

User altered.

4、賦予權限

SQL> grant create session to zsl;

Grant succeeded.

SQL> grant select on scott.test1  to  zsl;

Grant succeeded.

5、創(chuàng)建同義詞

SQL> create synonym zsl.test1 forscott.test1;

Synonym created.

6、切換到zsl用戶下測試

SQL> conn zsl/zsl

Connected.

SQL> select * from test1;

     ID

----------

      1

7、查找屬主

SQL> conn /as sysdba;

Connected.

SQL> set line 150

SQL> select owner,SYNONYM_NAME,TABLE_OWNER, TABLE_NAME from dba_synonyms where SYNONYM_NAME=upper('test1');

OWNER               SYNONYM_NAME         TABLE_OWNER         TABLE_NAME

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

ZSL    　          TEST1            SCOTT                TEST1