ORACLE SYNONYM详解

版权声明：本文为博主原创文章，未经博主允许不得转载。

目录(?)[+]

1. 一 概念
2. 二CREATE SYNONYM 创建同义词
3. 三DROP SYNONYM 删除同义词
4. 四QA 问答

以下内容整理自Oracle 官方文档

一 概念

A synonym is an alias for any table,view,materialized view,sequence,procedure,function,package,type,Java classschema object,user-defined object type,or another synonym. Because a synonymis simply an alias,it requires no storage other than its definition in thedata dictionary.

Oracle中同义词是任何表、视图、物化视图、序列、存储过程、函数、包、类型、JAVA类对象、用户定义类型,或是其他的同义词的别名。由于其只是一个别名，所以除了在

数据字典中的定义不占任何空间。

Synonyms are often used for security andconvenience. For example,they can do the following:

同义词常用于安全和方便。例如，它们可以做：

1. Mask the name and owner of anobject 伪装对象名称和其所有者。

2. Provide location transparency for remoteobjects of a distributed database 为分布式数据库远程对象提供位置透明性

3. Simplify SQL statements for databaseusers 简化数据库用户访问对象SQL语句

4. Enable restricted access similar tospecialized views when exercising fine-grained access control 当行使精细化访问控制时提供类似指定视图的访问限制

You can create both public and privatesynonyms. A public synonym is owned by the special user group named PUBLIC andevery user in a database can access it. A private synonym is in the schema of aspecific user who has control over its availability to others.

你可以创建public和private同义词。public同义词属于PUBLIC组，每个用户都可以访问。private同义词属于对象所有者，只有其显式授权后其他用户才可访问。

Synonyms are very useful in bothdistributed and nondistributed database environments because they hide theidentity of the underlying object,including its location in a distributedsystem. This is advantageous because if the underlying object must be renamedor moved,then only the synonym needs to be redefined. Applications based onthe synonym continue to function without modification.

同义词的优势体现在如果其底层的对象重命名或者转移，那么只需要重定义该同义词。基于该同义词的应用则无需任何修改。

Synonyms can also simplify SQL statementsfor users in a distributed database system. The following example shows how andwhy public synonyms are often created by a database administrator to hide theidentity of a base table and reduce the complexity of SQL statements. Assume thefollowing:

下面举例说明同义词是如何简化用户访问的：

A table called SALES_DATA is in the schemaowned by the user JWARD.

JWARD用户下有一张表 SALES_DATA

The SELECT privilege for the SALES_DATAtable is granted to PUBLIC.

PUBLIC组有SALES_DATA的查询权限

At this point,you have to query the tableSALES_DATA with a SQL statement similar to the following:

此时，你如果查询SALES_DATA表则需以下语句：

SELECT * FROM jward.sales_data;

Notice how you must include both the schemathat contains the table along with the table name to perform the query.

Assume that the database administratorcreates a public synonym with the following SQL statement:

假如数据库管理员创建了一个public 同义词：

CREATE PUBLIC SYNONYM sales FORjward.sales_data;

After the public synonym is created,youcan query the table SALES_DATA with a simple SQL statement:

你的语句将简化为：

SELECT * FROM sales;

Notice that the public synonym SALES hidesthe name of the table SALES_DATA and the name of the schema that contains thetable.

二、CREATE SYNONYM 创建同义词

1、语法结构：

2、前提条件：

To create a private synonym in your own schema,you must have theCREATESYNONYMsystem privilege.

在自己模式下创建私有同义词需要CREATE SYNONYM权限。

To create a private synonym in another user's schema,you must have theCREATEANYSYNONYMsystem privilege.

在其他用户模式下创建私有同义词需要CREATE ANY SYNONYM权限。

To create aPUBLICsynonym,you must have theCREATEPUBLICSYNONYMsystem privilege.

创建公有同义词，需要有CREATE PUBLIC SYNONYM权限。

3、示例

sqlplus / as sysdba

CREATE SYNONYM offices

FOR hr.locations;

GRANT SELECT ON hr.locations to SCOTT;

CREATE PUBLIC DATABASE LINK l_hr

CONNECT TO hr IDENTIFIED BY hr

USING 'orcl';

CREATE PUBLIC SYNONYM emp_table

FORHR.employees@l_hr;

GRANT SELECT ON HR.employees to SCOTT;

conn scott/tiger@orcl

SELECT count(*) from sys.offices;

select count(*) from emp_table;

三、DROP SYNONYM 删除同义词

1、语法结构：

2、前提条件：

To drop a private synonym,either the synonym must be in your own schema or you must have theDROPANYSYNONYMsystem privilege.

删除私有同义词需要有DROP ANY SYNONYM权限。

To drop aPUBLICsynonym,you must have theDROPPUBLICSYNONYMsystem privilege.

删除公有同义词需要有DROP PUBLIC SYNONYM权限。

3、示例

3.1删除public同义词，必须加public关键字：

SYS@orcl>DROP SYNONYM emp_table;

DROPSYNONYM emp_table

*

第 1 行出现错误:

ORA-01434:要删除的专用同义词不存在

SYS@orcl>DROP PUBLIC SYNONYM emp_table;

同义词已删除。

3.2删除private同义词：

DROP SYNONYM offices;

四、Q&A 问答

Q: 可以对同义词做INSERT/UPDATE/DELETE操作吗？

A:

SCOTT@orcl> UPDATE sys.offices t SETt.city='Shanghai' WHERE location_id=1000;

UPDATE sys.offices t SET t.city='Shanghai'WHERE location_id=1000

*

第 1 行出现错误:

ORA-01031: 权限不足

SYS@orcl> grant update on hr.locationsto scott;

授权成功。

SCOTT@orcl> /

已更新 1 行。

SO: 用户对同义词的操作权限都是基于对其底层对象有哪些操作权限。

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

DylanPresents.

• 上一篇Oracle PUP（PRODUCT_USER_PROFILE）配置和使用
• 下一篇Linux命令之kill

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!