源碼-Oracle資料庫管理-第八章-序列和同義詞-Part 2（同義詞）

同義詞：方案對象的别名，它不占用存儲的空間，目的是在Oracle中為表、視圖、序列、PL/SQL程式單元、使用者自定義對象或其他的同義詞建立友好的名稱。

主要作用：

1. 安全性——防止暴露太多的資訊給開發人員（無需使用類似[email protected]的格式）

2. 降低複雜環境的出錯率

[email protected]> CREATE PUBLIC SYNONYM scottemp FOR scott.emp;
CREATE PUBLIC SYNONYM scottemp FOR scott.emp
*
第 1 行出現錯誤:
ORA-01031: 權限不足


[email protected]> conn sys as sysdba
已連接配接。

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
[email protected]

[email protected]> CREATE PUBLIC SYNONYM scottemp FOR scott.emp;

同義詞已建立。

[email protected]> conn scott/tiger
已連接配接。

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
[email protected]

[email protected]> selecc count(*) from scottemp;
SP2-0734: 未知的指令開頭 "selecc cou..." - 忽略了剩餘的行。
[email protected]> select count(*) from scottemp;

  COUNT(*)
----------
        23

[email protected]> conn sys as sysdba
已連接配接。

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
[email protected]

[email protected]> grant scott create pubic synonym;
grant scott create pubic synonym
      *
第 1 行出現錯誤:
ORA-00990: 權限缺失或無效


[email protected]> grant create pubic synonym to scott;
grant create pubic synonym to scott
      *
第 1 行出現錯誤:
ORA-00990: 權限缺失或無效


[email protected]> grant create public synonym to scott;

授權成功。

[email protected]> grant drop public synoym to scott;
grant drop public synoym to scott
      *
第 1 行出現錯誤:
ORA-00990: 權限缺失或無效


[email protected]> grant drop public synonym to scott;

授權成功。

[email protected]> conn scott/tiger;
已連接配接。

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
[email protected]

[email protected]> drop public synonym scottemp;

同義詞已删除。

[email protected]> select count(*) from scottemp;
select count(*) from scottemp
                     *
第 1 行出現錯誤:
ORA-00942: 表或視圖不存在


[email protected]> CREATE PUBLIC SYNONYM scottemp FOR scott.emp;

同義詞已建立。

[email protected]> desc user_synonyms;
 名稱                                                  是否為空? 類型
 ----------------------------------------------------- -------- ------------------------------------
 SYNONYM_NAME                                          NOT NULL VARCHAR2(30)
 TABLE_OWNER                                                    VARCHAR2(30)
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 DB_LINK                                                        VARCHAR2(128)

[email protected]> select synonym_name, table_name, table_owner from all_synonyms where table_owner='SCOTT' and table_name='EMP';

SYNONYM_NAME
------------------------------------------------------------
TABLE_NAME
------------------------------------------------------------
TABLE_OWNER
------------------------------------------------------------
SCOTTEMP
EMP
SCOTT


[email protected]> col synonym_name, table_name, table_owner for a20';
SP2-0735: 未知的 COLUMN 選項開頭 "table_name..."
[email protected]> col synonym_name for a20';
SP2-0246: 非法的 FORMAT 字元串"a20'"
[email protected]> col synonym_name, table_name, table_owner for a20;
SP2-0735: 未知的 COLUMN 選項開頭 "table_name..."
[email protected]> col synonym_name for a20;
[email protected]> col table_name for a20;
[email protected]> col table_owner for a20;
[email protected]> select synonym_name, table_name, table_owner from all_synonyms where table_owner='SCOTT' and table_name='EMP';

SYNONYM_NAME         TABLE_NAME           TABLE_OWNER
-------------------- -------------------- --------------------
SCOTTEMP             EMP                  SCOTT

[email protected]> commit;

送出完成。

[email protected]> spool out