同义词

oracle210

---

---

同义词

定义：同义词是现有对象的一个别名。

q简化SQL语句

q隐藏对象的名称和所有者

q提供对对象的公共访问

同义词：分为共有同义词和私有同义词

公有同义词：就是对于所有用户都可见的

私有同义词：只有当前用户可见；私有同义词只能在其模式内访问，且不能与当前模式的对象同名。

SQL> revoke create synonym from hr; Revoke succeeded SQL> create user test_syn identified by test_syn; User created//1创建对象 SQL> grant create session to test_syn; Grant succeeded//3.赋予登录的系统权限 SQL> grant create table to test_syn; Grant succeeded //6.赋予创建表的系统权限 SQL> grant create synonym to test_syn; Grant succeeded //11.赋予创建私有同义词权限 SQL> select * from syn_private_test ; select * from syn_private_test ORA-00942: 表或视图不存在 //13.私有同义词只对相同模式下可见 SQL> grant create public synonym to test_syn; Grant succeeded SQL> select * from syn_pub_test; ID NAME ----------- -------------------- SQL> create public syn_pub_test for test_syn.test_syn; SQL> SQL> create public synonym syn_pub_test for test_syn.test_syn; create public synonym syn_pub_test for test_syn.test_syn ORA-00955: 名称已由现有对象使用 //15.赋予创建公有同义词权限 SQL> SQL> create public synonym syn_pub_test for dept; create public synonym syn_pub_test for dept ORA-00955: 名称已由现有对象使用 //17.说明公有同义词是针对所有用户的 SQL> create or replace public synonym syn_pub_test for test_syn.test_syn; Synonym created SQL> SQL> grant drop public synonym to test_syn; Grant succeeded SQL> grant drop synonym to test_syn; grant drop synonym to test_syn ORA-00990: 权限缺失或无效 //没有回收私有删除同义词权限，只有公有删除同义词权限 SQL>

[oracle@localhost ~]$ sqlplus test_syn/test_syn@jiagulun //2登录不成功 SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 13 18:37:57 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. ERROR: ORA-01045: user TEST_SYN lacks CREATE SESSION privilege; logon denied Enter user-name: ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: ERROR: ORA-01017: invalid username/password; logon denied SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus [oracle@localhost ~]$ sqlplus test_syn/test_syn@jiagulun //4.登录成功 SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 13 18:39:20 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table test_syn(id number(20),name varchar2(20)); create table test_syn(id number(20),name varchar2(20)) *//5.创建表失败，没有创建表的系统权限 ERROR at line 1: ORA-01031: insufficient privileges SQL> create table test_syn(id number(20),name varchar2(20)); //7.创建表成功 Table created. SQL> select * from test_syn; //8.查是对象权限已有的 no rows selected SQL> alter table test_syn modify id number(10); //9.也是对象权限 Table altered. SQL> create synonym syn_pri_test for test_syn; create synonym syn_pri_test for test_syn *//10.创建私有同义词没有该系统权限 ERROR at line 1: ORA-01031: insufficient privileges SQL> create synonym syn_pri_test for test_syn; Synonym created.//12.创建私有同义词成功 SQL> select * from syn_pri_test; no rows selected SQL> commit; Commit complete. SQL> create public synonym syn_pub_test for test_syn; create public synonym syn_pub_test for test_syn *//14.创建公有同义词没有权限 ERROR at line 1: ORA-01031: insufficient privileges SQL> create public synonym syn_pub_test for test_syn; Synonym created.//16.创建公有同义词 SQL> commit; Commit complete. SQL> SQL> drop public synonym syn_pub_test; drop public synonym syn_pub_test * ERROR at line 1: ORA-01031: insufficient privileges SQL> drop public synonym syn_pub_test; Synonym dropped. //赋予了公有的就有了私有的删除权限了 SQL> drop synonym syn_pri_test; Synonym dropped. SQL> SQL> create synonym syn_pri_test for test_syn; create synonym syn_pri_test for test_syn ORA-00955: 名称已由现有对象使用 SQL>这里是另外一个会话，相同的用户说明私有是针对模式有效，而不是会话

看下tab这个玩意儿

SQL> select av.owner,av.view_name,av.view_type from all_views av where av.view_name='TAB';

OWNER VIEW_NAME VIEW_TYPE

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

SYS TAB

SQL> select at.owner,at.table_name,at.tablespace_name from all_tables at where at.table_name='TAB';

OWNER TABLE_NAME TABLESPACE_NAME

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

SQL> select * from user_synonyms us where us.synonym_name='TAB';

SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK

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

SQL> select * from all_synonyms ass where ass.synonym_name='TAB';

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK

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

PUBLIC TAB SYS TAB

SYSTEM TAB SYS TAB

SQL> select ao.owner,ao.object_name,ao.object_id,ao.namespace ,ao.object_type from all_objects ao where ao.object_name='TAB';

OWNER OBJECT_NAME OBJECT_ID NAMESPACE OBJECT_TYPE

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

SYS TAB 3090 1 VIEW

PUBLIC TAB 3092 1 SYNONYM//这个是我们平时查询的公共同义词TAB

SYSTEM TAB 3091 1 SYNONYM//system用户查询的

SQL>

SQL> /

SQL>

SQL> / //--->/可以执行上次执行的代码

SQL> select ao.owner,ao.object_name,ao.object_id,ao.namespace ,ao.object_type from all_objects ao where ao.object_name='TAB';

OWNER OBJECT_NAME OBJECT_ID NAMESPACE OBJECT_TYPE

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

SYS TAB 3090 1 VIEW

PUBLIC TAB 3092 1 SYNONYM

SYSTEM TAB 3091 1 SYNONYM

SQL>

SQL> select ao.object_name,ao.object_id,ao.namespace from user_objects ao where ao.object_name='TAB';

OBJECT_NAME OBJECT_ID NAMESPACE

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

TAB 3090 1

SQL>