Oracle创建同义词及dblink

一、定义

oracle的同义词从字面上理解是别名的意思，和视图的功能类似，就是一种映射关系。在使用同义词时，oracle数据库将他翻译成对应方案对象的名字。与视图对象类似，同义词不占用实际存储空间，只有在数据字典中保存了同义词的定义。在oracle数据库中的大部分数据库对象，如表，视图，物化视图，序列，函数，存储过程，包同义词等，数据库管理员都可以根据实际情况为他们定义同义词。

Oracle对象知识：同义词（Synonym）创建删除，同义词(Synonym)是数据库对象的一个别名，Oracle可以为表、视图、序列、过程、函数、程序包等指定一个别名。同义词有两种类型：

私有同义词：拥有CREATE SYNONYM权限的用户(包括非管理员用户)即可创建私有同义词，创建的私有同义词只能由当前用户(创建者用户)使用。

公有同义词：系统管理员可以创建公有同义词，公有同义词可以被所有用户访问。（注：公有同义词在同一实例下的所有用户都是可以访问的）

image.png

二、同义词种类

oracle有两种类型同义词，分别是oracle公用同义词与私有同义词。

普通用户创建的同义词一般是私有同义词，公用同义词一般有DBA(数据库管理员（Database Administrator，简称DBA）)创建，普通用户如果希望创建同义词，则需要create public synonym这个系统权限。

1)、oracle公用同义词：由一个特殊的用户组public所拥有。顾名思义，数据库所有的用户都可以使用公用同义词。公用同义词用来标示一些比较普通的数据库对象，这些对象往往大家都需要引用。

2)、oracle私有同义词：它是跟公用同义词所对应，他是由创建他的用户所有，当然，这个同义词的创建者可以通过授权控制其他用户是否有权使用自己的私有同义词。

三、同义词的好处

1、不占内存空间，节省大量的数据库空间

2、简化了数据库对象的访问

3、提高了数据库对象访问的安全性

4、扩展的数据库的使用范围，能够在不同的数据库用户之间实现无缝交互;同义词可以创建在不同一个数据库服务器上，通过网络实现连接

四、同义词的作用

1. 多用户协同开发中，可以屏蔽对象的名字及其持有者。如果没有同义词，当操作其他用户的表时，必须通过user名.object名的形式，采用了Oracle同义词之后就可以隐蔽掉user名，当然这里要注意的是：public同义词只是为数据库对象定义了一个公共的别名，其他用户能否通过这个别名访问这个数据库对象，还要看是否已经为这个用户授权。

2. 为用户简化sql语句。上面的一条其实就是一种简化sql的体现，同时如果自己建的表的名字很长，可以为这个表创建一个Oracle同义词来简化sql开发。

3)为分布式数据库的远程对象提供位置透明性。

4）Oracle同义词在数据库链接中的作用

数据库链接是一个命名的对象，说明一个数据库到另一个数据库的路径，通过其可以实现不同数据库之间的通信。

五、赋予权限，创建同义词的时候还需要赋于相关的权限：

用sys账号给test账号授予CREATE [PUBLIC] SYNONYM的权限

--授予权限私有同义词：
Grant create synonym to username; --为用户创建同义词的权限
Grant create any synonym to username; --为任意用户创建同义词的权限

--授予权限公有同义词：
Grant create public synonym to username;

--删除私有同义词需要有DROP ANY SYNONYM权限。(如果有drop any table则不用drop any synonym权限也可以删除)
Grant drop any synonym to username;

--删除公有同义词需要有DROP PUBLIC SYNONYM权限。
Grant drop public synonym to username;

--查看当前用户权限的方式
select * from user_sys_privs;

--查看所有用户权限的方式
select * from dba_sys_privs;

--查看当前用户表权限的方式
select * from user_tab_privs;

--查看所有表权限的方式,用于查看同义词对应的表
select * from all_tab_privs;

--查看所有表权限的方式包括,用于查看同义词对应的表
select * from dba_tab_privs;

--oracle查询角色
--1、查看所有角色：
select * from dba_roles;
--2、查看当前用户所拥有的角色：
select * from dba_role_privs;select * from user_role_privs;

六、查看同义词的语法是：

--查看所有同义词：
select * from dba_synonyms;
Select * from all_synonyms;

--查看当前用户的同义词
select * from user_synonyms;

--查询结果及含义Owner,synonym_name,table_owner,table_name,db_link;
(同义词所有者的用户名)，(同义词的名称)，(同义词引用的对象的所有者)，(同义词引用的对象的名称)，(远程同义词中引用的数据库链接的名称)

七、创建同义词的语法是：

--创建公有Oracle同义词的语法：
Create [public] synonym 同义词名称 for [username.]objectName；
例如：create public synonym table_a for user.table_a; 

--创建私有Oracle同义词的语法：
Create synonym 同义词名称 for [username.]objectName；
例如：create synonym table_b for user.table_b;

注意事项：如果只创建同义词而没有给指定用户的表或者视图授予select（只读）权限，那么当前用户（同义词所在用户）不会有当前表查询的权限。可通过查看当前用户表权限的方式
select * from user_tab_privs;

给指定用户的表或者视图授予select（只读）权限。
例子一：grant select on 拥有者.表名 to username； —拥有者指原数据库表的拥有者，不用用username给自己赋权限，不然会报：ORA-01749:you may not GRANT/REVOKE privileges to/from yourself。你不可以授予或撤销自己的权限

例子二：grant select on UserB.Table1 to UserA;--UserB用户为表拥有者，UserA为同义词拥有者

问题1：请问SQL中 我创建一个用户后只授予了他一个Select权限可是为什么其他操作还是可以做

解答：可以通过select * from dba_role_privs;查看当前用户所拥有的角色。是否授予了他角色，比如说dba角色之类的

八、同义词的删除语法

因为同义词也是对象 ，删除语法同表一样

Drop [public] synonym 同义词名称-- 删除公有同义词：drop public synonym table_a; -- 删除私有同义词：drop synonym table_b;

九、跨库查询，oracle dblink

1、dblink扩展访问，创建同义词

如果要访问不同数据库下或者不同用户下的表table_a，当然也可以使用同义词，但需要先创建一个Database Link(数据库连接)来扩展访问，然后在使用如下语句创建数据库同义词：

create synonym table_a for table_a@DB_Link;

2、oracle dblink创建事例

oracle在进行跨库访问时，可以通过创建dblink实现，如A库想查B库的表可以在A库上建一个DBLINK（单向），不需要在B上建，除非B库也想查A库的表；

3、创建dblink语法：

CREATE [PUBLIC] DATABASE LINK link
CONNECT TO username IDENTIFIED BY password
USING ‘connectstring’ 

说明：
1) 权限：创建数据库链接的帐号必须有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的系统权限，用来登录到远程数据库的帐号必须有CREATE SESSION权限。这两种权限都包含在CONNECT角色中（CREATE PUBLIC DATABASE LINK权限在DBA中）。一个公用数据库链接对于数据库中的所有用户都是可用的，而一个私有链接仅对创建它的用户可用。由一个用户给另外一个用户授权私有数据库链接是不可能的，一个数据库链接要么是公用的，要么是私有的。

2)link    当GLOBAL_NAME=TRUE时，link名必须与远程数据库的全局数据库名global_name）相同；            否则，可以任意命名。

3)connectstring    连接字符串，tnsnames.ora中定义远程数据库的连接串。

4)username、password：远程数据库的用户名，口令。如果不指定，则使用当前的用户名和口令登录到远程数据库。

4、授予权限

在创建database link之前，我们需要判断，登陆的用户是否具备创建database link 的权限;

database link 分为两种：一种为 public（公有） 一种为 private（个人）。顾名思义，公有dblink即源库所有的用户都可以使用，个人dblink则只有创建者用户可以使用。
创建dblink是要有相应的权限的：

SYS@orcl>select * from user_sys_privs where privilege like upper('%LINK%');
USERNAME             PRIVILEGE               ADM
------------------------------ ---------------------------------    ---
SYS               CREATE DATABASE LINK         NO
SYS               DROP PUBLIC DATABASE LINK      NO
SYS               CREATE PUBLIC DATABASE LINK      NO

5、创建dblink

建立方的服务端配置远程数据库的tnsname配置，当tnsnames文件中有了参数，且有了相应的权限之后，就可以创建dblink，创建方式有以下几种:

创建个人dblink语句为 "CREATE database link "开头：

create database link test1 using 'test';

创建公有dblink语句"CREATE PUBLIC database link"，使用指定的用户和密码创建公有 dblink ：

create public database link test3 connect to scott identified by tiger using 'test';

建一个shared 的数据库连接：

create shared public database link test4 connect to scott identified by "tiger"
authenticated by username identified by "passwd" using 'test';

使用shared方式的 database link是数据库会限制到远程数据库的连接的数量,这样以避免过多的连接对远程数据库造成太大的压力。

还有一种创建方式是不使用tnsname的情况

CREATE database link link_name
CONNECT TO user IDENTIFIED BY password
USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = ip)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = sa)))';

**6、删除dblink **

-- 首先需要查询出来有哪些dblink
select owner,object_name from dba_objects where object_type='DATABASE LINK';

如果为pubilc则删除方式为：drop public database link
链接名如果为个人则删除方式为：drop database link 链接名

十、实例演示

1、同库不同用户演示

如图所示在wst用户下有表V_RPT_INSINFO_TJ

image.png

但是在test用户没有这张表，在test用户下如图不能访问表V_RPT_INSINFO_TJ；

image.png

在test用户下创建私有同义词：

-- 创建公有同义词
create public synonym V_RPT_INSINFO_TJ for wst.V_RPT_INSINFO_TJ;

-- 创建私有同义词
create synonym V_RPT_INSINFO_TJ for wst.V_RPT_INSINFO_TJ;

创建好后就可以在test用户下访问wst用户的表V_RPT_INSINFO_TJ

image.png

查看同义词

在有dba权限的用户查询（sys）：

SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME IN ( 'V_RPT_INSINFO_TJ','V_WEB_FEE_INSINFO'); 
OWNER        SYNONYM_NAME      TABLE_OWNER    TABLE_NAME         DB_LINK
--------      ------------------   --------------   ---------------   ---------------
PUBLIC        V_RPT_INSINFO_TJ    WST        V_RPT_INSINFO_TJ       null
TEST        V_WEB_FEE_INSINFO   WST        V_WEB_FEE_INSINFO      null

查询当前用户(test):

SELECT * FROM USER_SYNONYMS; 

SYNONYM_NAME           TABLE_OWNER       TABLE_NAME          DB_LINK
-------------------    ------------------   ------------------------  -------------------
V_WEB_FEE_INSINFO        WST           V_WEB_FEE_INSINFO      null

2、跨库同义词演示

在192.168.81.10库下用户cw中并不能访问V_RPT_INSINFO_TJ，如下图所示。

image.png

赋予权限语句

-- 在cw用户下查看些当前用户是否具备创建database link权限
select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='CW';

如果查询有返回行，则表示具备创建database link权限，否则，则需要使用sys登陆orcl为CW用户赋予创建权限

-- 给cw用户授予创建dblink的权限
grant create public database link to cw; grant create database link to cw; 

-- 给cw用户删除dblink权限
grant drop public database link to cw;

此时，以cw用户登录再执行上面查看是否具备权限的sql语句，会发现有返回行，表示CW这个用户已经具备创建database link的权限

image.png

sql创建dblink语句（CW用户下）：

注意一点，如果密码是数字开头，用“”括起来（因192.168.81.10服务器cw用户下是没有tnsname文件，所以采用不使用tnsname情况创建方式）

-- 创建公有dblink
create public database link CWLINK connect to wst identified by "12" USING '192.168.81.10:1521/gbksl';

-- 查看cw用户有公用的dblink名称为"CWLINK"
select * from SYS.ALL_DB_LINKS where db_link='CWLINK';

公有dblink图示：

image.png image.png

-- 创建个人dblink
create database link CWSLINK  
  connect to wst identified by "12"  
    using '(DESCRIPTION =    
    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.81.10)(PORT = 1521))    
)   
 (CONNECT_DATA =      (SERVICE_NAME = orcl)    )  )';  

-- 查看cw用户有私人的dblink名称为"CWSLINK"，如下图：
select * from SYS.ALL_DB_LINKS where db_link='CWSLINK';

私人dblink图示：

image.png

这样，就完成了简单database简单的创建。cw用户就可以跨库查询的。

创建同义词

从上一步我们可以看到cw用户查询wst用户的V_RPT_INSINFO_TJ视图的sql查询写起来比较复杂，因为里面要以@dblink方式才能查询成功。幸好oracle提供了同义词的概念，我们可以将"V_RPT_INSINFO_TJ@CWSLINK"这个长串用一个更简单的词(如V_RP，只要不跟cw用户中的表名冲突就行)来代替，这样我们就能像查询普通表/视图一样的对V_RP视图进行查询了

create synonym V_RP for V_RPT_INSINFO_TJ@CWSLINK

dblink备注说明：

在上面的dblink描述中我们看到，A(cw)用户想访问B(wst)用户的V_RPT_INSINFO_TJ表或视图，我们直接通过创建与B（wst）用户的dblink方式成功在A（cw）用户中查询到V_RPT_INSINFO_TJ表或视图了，但是这样一来B(wst)用户中的所有表或视图都可以被A(cw)用户通过上面创建的dblink来访问了，而这点是对于B(wst)用户来说是不安全的，很多情况下远程数据库是不会开放整个用户B(wst)来给A(cw)用户访问的，所以这里我们需要引入C用户的来解决这个问题，C用户是新增的拥有B用户部分数据访问权限的远程用户。

在B用户中创建C用户并授权。既然需求是实现A用户能成功访问B用户的V_RPT_INSINFO_TJ表或视图，那么我们就在B用户中执行创建C用户并给C用户授权B用户V_RPT_INSINFO_TJ表或视图的相关权限，当然connect权限是必须要有的。C用户创建完成后我们同样可以上面讲的方式来查看C用户的权限并授权，变换成A用户中创建与C用户连接的dblink。