Oracle SYNONYM案例学习

CREATE SYNONYM
使用 CREATE SYNONYM 语句创建同义词，它是表、视图、序列、运算符、过程、存储函数、包、物化视图、Java 类模式对象、用户定义的对象类型或其他同义词的替代名称。同义词依赖于它的目标对象，如果目标对象被更改或删除，同义词就会变得无效。

创建同义词的先决条件：

1. 要在您自己的模式中创建私有同义词，您必须具有 CREATE SYNONYM 系统权限
2. 要在另一个用户的模式中创建私有同义词，您必须具有 CREATE ANY SYNONYM 系统特权
3. 要创建 PUBLIC 同义词，您必须具有 CREATE PUBLIC SYNONYM 系统特权

在自己的模式中创建私有同义词案例

--sysdba会话
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 13 14:41:41 2023
Version 19.17.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected.
SQL> select * from hr.emp;

        ID NAME
---------- --------------------------------
         1 bob
         2 fox
SQL> create user ct5432 identified by "ct5432";

User created.

SQL> grant create session to ct5432;

Grant succeeded.

SQL> grant create table to ct5432;

Grant succeeded.

SQL> grant create view to ct5432;

Grant succeeded.

SQL> grant create synonym to ct5432;

Grant succeeded.

SQL> grant select any table to ct5432;

Grant succeeded.

SQL> grant insert any table to ct5432;

Grant succeeded.

SQL> grant update any table to ct5432;

Grant succeeded.

SQL> grant delete any table to ct5432;

Grant succeeded.

--ct5432会话
[oracle@localhost ~]$ sqlplus ct5432/ct5432

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 13 14:52:17 2023
Version 19.17.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL> create or replace synonym ct5432.hremp for hr.emp;
Synonym created.

SQL> select * from ct5432.hremp;

        ID NAME
---------- --------------------------------
         1 bob
         2 fox

SQL> update ct5432.hremp set name = 'bob_x' where id = 1;

1 row updated.

SQL> select * from ct5432.hremp;

        ID NAME
---------- --------------------------------
         1 bob_x
         2 fox

SQL> create or replace view ct5432.empview as select id,name from hr.emp;

View created.

SQL> create or replace synonym ct5432.hremp for ct5432.empview;

Synonym created.

SQL> select * from ct5432.hremp;

        ID NAME
---------- --------------------------------
         1 bob_x
         2 fox

SQL> update ct5432.hremp set name = 'bob' where id = 1;

1 row updated.

SQL> select * from ct5432.hremp;

        ID NAME
---------- --------------------------------
         1 bob
         2 fox

SQL> drop synonym ct5432.hremp;

Synonym dropped.

在另一个用户的模式中创建私有同义词案例

--sysdba会话
SQL> grant create any synonym to ct5432;

Grant succeeded.

SQL> grant drop any synonym to ct5432;

Grant succeeded.
--ct5432会话
SQL> create or replace synonym test.hremp for hr.emp;

Synonym created.

SQL> select * from test.hremp;

        ID NAME
---------- --------------------------------
         1 bob
         2 fox
SQL> update hremp set name = 'bob_x' where id = 1;

1 row updated.

SQL> select * from test.hremp;

        ID NAME
---------- --------------------------------
         1 bob_x
         2 fox

SQL> drop synonym test.hremp;

Synonym dropped.

创建 PUBLIC 同义词案例

--sysdba会话
SQL> grant create public synonym to ct5432;

Grant succeeded.

SQL> grant drop public synonym to ct5432;

Grant succeeded.
--ct5432会话
SQL> create or replace public synonym hremp for hr.emp;

Synonym created.

SQL> select * from hremp;

        ID NAME
---------- --------------------------------
         1 bob_x
         2 fox
--sysdba会话
SQL> revoke update any table from ct5432;

Revoke succeeded.

SQL> grant update on hremp to ct5432;

Grant succeeded.

SQL> revoke update on hr.emp from ct5432;

Revoke succeeded.
--ct5432会话
SQL> update hremp set name = 'bob' where id = 1;
update hremp set name = 'bob' where id = 1
       *
ERROR at line 1:
ORA-01031: insufficient privileges
--sysdba会话
SQL> grant update on hremp to ct5432;

Grant succeeded.
--ct5432会话
SQL> update hremp set name = 'bob' where id = 1;

1 row updated.

SQL> select * from hremp;

        ID NAME
---------- --------------------------------
         1 bob
         2 fox

SQL> drop public synonym hremp force;

Synonym dropped.

综上
同义词提供数据独立性和位置透明性。同义词允许应用程序在不修改的情况下运行，无论哪个用户拥有表或视图，也无论哪个数据库拥有表或视图。但是，同义词不能替代数据库对象的特权。必须先向用户授予适当的权限，然后用户才能使用同义词。