目录
定义
创建同义词 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. |