Oracle 11g 数据库对象-同义词

SQL> remark 同义词：Oracle为了方便对数据的 操作，允许用户创建对象的别名，用于简化查询的语句，这个别名称为同义词；
SQL> remark 同义词的分类：公有同义词 私有同义词
SQL> remark 公有同义词：所有的对象都可以进行访问
SQL> remark 私有同义词：只有创建者能访问；
SQL> remark 创建同义词的权限：create any synonym drop any synonym;
SQL> remark 创建一个表空间
SQL> create tablespace HOPESPACE
2 datafile 'd:\hopespace.dbf'
3 size 30m
4 autoextend on;

Tablespace created.

SQL> remark 创建一个用户 hope
SQL> create user hope
2 identified by hope123
3 default tablespace hopespace
4 temporary tablespace temp;

User created.

SQL> remark 给新建的用户进行系统权限授权
SQL> grant connect to hope;

Grant succeeded.

SQL> grant resource to hope;

Grant succeeded.

SQL> remark 现在：新建的用户操作系统缺省(默认)用户scott；
SQL> remark 系统给用户授予创建和删除同义词的权限
SQL> grant create any synonym to hope;

Grant succeeded.

SQL> grant drop any synonym to hope;

Grant succeeded.

SQL> remark 连接scott 用户 ，]
SQL> conn scott/tiger;
Connected.
SQL> remark scott 用户给hope用户授予emp表的使用权限
SQL> grant all on emp to hope;

Grant succeeded.

SQL> remark 连接hope账户，并创建同义词
SQL> conn hope/hope123;
Connected.
SQL> create synonym myemp for scott.emp;

Synonym created.

SQL> remark 通过同义词查询表的信息
SQL> select empno,job,sal from myemp;

EMPNO JOB SAL
---------- --------- ----------
7369 CLERK 800
7499 SALESMAN 1600
7521 SALESMAN 1250
7566 MANAGER 2975
7654 SALESMAN 1250
7698 MANAGER 2850
7782 MANAGER 2450
7788 ANALYST 3000
7839 PRESIDENT 5000
7844 SALESMAN 1500
7876 CLERK 1100

EMPNO JOB SAL
---------- --------- ----------
7900 CLERK 950
7902 ANALYST 3000
7934 CLERK 1300

14 rows selected.

SQL> remark 注：用户在使用同义词对象的时候，对同义词操作的权限等同于所依赖的对象的权限；即：使用同义词是，依赖的对象必须是授权过的；
SQL> remark ..............公有同义词.........................
SQL> remark 权限：create public synonym drop public synonym
SQL> remark 创建公有同义词的用户必须拥有上面的两个权限
SQL> remark 其他的用户操作同义词的时候要依赖同义词所关联的对象；
SQL> remark 给hope 用户授予创建公有同义词的权限
SQL> conn system/manager;
Connected.
SQL> grant create public synonym to hope;

Grant succeeded.

SQL> grant drop public synonym to hope;

Grant succeeded.

SQL> reamrk 登陆hope用户，并创建公有同义词
SP2-0734: unknown command beginning "reamrk 登..." - rest of line ignored.
SQL> edit
Wrote file afiedt.buf

1* grant drop public synonym to hope
SQL> remark 登陆hope用户，并创建公有同义词
SQL> conn hope/hope123;
Connected.
SQL> create public synonym pubemp for scott.emp;

Synonym created.

SQL> remark 另外创建一个用户，并使用这个公有同义词
SQL> conn system/manager;
Connected.
SQL>
1 create user hope1
2* identified by hope1123
3 /

User created.

SQL> remark 给新建的用户授予系统权限
SQL> grant connect to hope1;

Grant succeeded.

SQL> grant resource to hope1;

Grant succeeded.

SQL> remark 连接hope1用户使用公有同义词
SQL> conn hope1/hope1123;
Connected.
SQL> remark 由于scott并没有授权给hope1这个用户相关对象的权限，所有hope1并不能使用该公有同义词
SQL> remark 给hope1进行授权
SQL> conn scott/tiger;
Connected.
SQL> grant all on emp to hope1;

Grant succeeded.

SQL> conn hope1/hope1123;
Connected.
SQL> select empno,ename,sal from pubemp;

SQL> remark 连接hope用户进行使用公有同义词
SQL> conn hope/hope123;
Connected.
SQL> select empno,ename from pubemp;
SQL> remark ..................................................................
SQL> remark ..................................................................
SQL> remark 创建公有同义词
SQL> create public synonym pubemp for scott.emp;
create public synonym pubemp for scott.emp
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> remark ..................................................................
SQL> remark 删除同义词
SQL> drop synonym myemp;

Synonym dropped.

SQL> drop synonym pubemp;
drop synonym pubemp
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist


SQL> spool off;