【SQL】同义词 SYNONYM

目录

定义

创建同义词 CREATE SYNONYM

私有同义词

公有同义词

删除同义词DROP SYNONYM

---

定义

同义词可以是任意表、视图、序列等对象的别名

Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, operator, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym. A synonym places a dependency on its target object and becomes invalid if the target object is changed or dropped.

一般用可以创建私有同义词，仅供自己使用

sys可以给一般用户创建私有同义词，也可以建共有同义词（public）

 

创建同义词 CREATE SYNONYM

创建同义词需要授权SYNONYM

 

创建测试表

SQL> create table XXXXXXXXXXX as select * from dept;   Table created.   SQL> select * from XXXXXXXXXXX;       DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON

给scott用户授权

SQL> conn / as sysdba Connected. SQL> grant create synonym to scott;   Grant succeeded.

 

私有同义词

使用scott用户创建私有同义词

SQL> conn scott/tiger; Connected. SQL> create synonym dept_syn for XXXXXXXXXXX;   Synonym created.

可以象查看表一样查看同义词

SQL> select * from dept_syn;       DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON

创建一个一般用户

SQL> conn / as sysdba Connected. SQL> create user tiger identified by scott;   User created.   SQL> grant connect,resource to tiger;   Grant succeeded.

一般用户如果没有所有者或sys授权的话，是不能访问同义词的

SQL> conn tiger/scott; Connected. SQL> select * from dept_syn; select * from dept_syn               * ERROR at line 1: ORA-00942: table or view does not exist     SQL> select * from scott.dept_syn; select * from scott.dept_syn                     * ERROR at line 1: ORA-00942: table or view does not exist

给tiger用户授权

SQL> conn scott/tiger; Connected. SQL> grant select on XXXXXXXXXXX to tiger;   Grant succeeded.   SQL> conn tiger/scott; Connected. SQL> select * from scott.XXXXXXXXXXX;       DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON   SQL> select * from scott.dept_syn;       DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON

SQL> conn scott/tiger; Connected. SQL> revoke select on XXXXXXXXXXX from tiger;   Revoke succeeded.   SQL>  grant select on dept_syn to tiger;   Grant succeeded.   SQL> conn tiger/scott; Connected. SQL>  select * from scott.XXXXXXXXXXX;       DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON   SQL> select * from scott.dept_syn;       DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON

可以看出来，只要被授权用户有同义词或者表二者之一任意的select权限，就可以使用同义词

查询同义词的信息user_synonyms

SQL> select SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from user_synonyms;   SYNONYM_NAME    TABLE_OWNER     TABLE_NAME --------------- --------------- -------------------- DEPT_SYN        SCOTT           XXXXXXXXXXX

 

公有同义词

使用scott用户创建测试表

SQL> conn scott/tiger; Connected. SQL> create table YYYYYYYYYYY as select * from SALGRADE;   Table created.   SQL> select * from SALGRADE;        GRADE      LOSAL      HISAL ---------- ---------- ----------          1        700       1200          2       1201       1400          3       1401       2000          4       2001       3000          5       3001       9999

使用sys用户创建公有同义词

SQL> conn / as sysdba Connected. SQL> create public synonym salgrade_syn for scott.YYYYYYYYYYY;   Synonym created.

既然是公有同义词，想必tiger用户一定可以访问了

SQL> conn tiger/scott; Connected. SQL> select * from scott.salgrade_syn; select * from scott.salgrade_syn                     * ERROR at line 1: ORA-00942: table or view does not exist   SQL> select * from salgrade_syn; select * from salgrade_syn                     * ERROR at line 1: ORA-00942: table or view does not exist SQL> select * from scott.YYYYYYYYYYY; select * from scott.YYYYYYYYYYY                     * ERROR at line 1: ORA-00942: table or view does not exist

。。。。。

虽然是同义词是公有的，但是依旧需要该表或同义词的select权限

SQL> grant select on salgrade_syn to tiger;   Grant succeeded.   SQL> conn tiger/scott; Connected. SQL> select * from salgrade_syn;        GRADE      LOSAL      HISAL ---------- ---------- ----------          1        700       1200          2       1201       1400          3       1401       2000          4       2001       3000          5       3001       9999   SQL> select * from scott.salgrade_syn;   SQL> conn scott/tiger; Connected. SQL> revoke select on salgrade_syn from tiger;   Revoke succeeded.   SQL> grant select on YYYYYYYYYYY to tiger;   Grant succeeded.   SQL> conn tiger/scott; Connected. SQL> select * from salgrade_syn;        GRADE      LOSAL      HISAL ---------- ---------- ----------          1        700       1200          2       1201       1400          3       1401       2000          4       2001       3000          5       3001       9999

并且公有同义词不可以加用户名，否则啥都不显示

SQL> select * from scott.salgrade_syn;

查询该同义词信息dba_synonym

SQL>  select OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from dba_synonyms where table_name='YYYYYYYYYYY';   OWNER  SYNONYM_NAME    TABLE_OWNER     TABLE_NAME ------ --------------- --------------- -------------------- PUBLIC SALGRADE_SYN    SCOTT           YYYYYYYYYYY

 

删除同义词DROP SYNONYM

删除私有同义词，可以使用sys或者拥有的用户

SQL> conn / as sysdba Connected. SQL> drop synonym scott.dept_syn;   Synonym dropped.

删除公有同义词

SQL> conn / as sysdba Connected. SQL> drop public synonym SALGRADE_SYN;   Synonym dropped.