dblink实现一个数据库用户只能查另一个库用户的数据

最新推荐文章于 2023-11-25 10:50:22 发布

逝鸿

最新推荐文章于 2023-11-25 10:50:22 发布

阅读量3.6k

点赞数

CC 4.0 BY-SA版权

文章标签： dblink 只能查询

本文链接：https://blog.youkuaiyun.com/qq_21127313/article/details/79280783

本文介绍如何配置Oracle数据库之间的连接，并实现跨库查询的功能。通过设置tnsnames.ora文件及创建用户与数据库链接，实现从一个数据库（a库）查询另一个数据库（b库）的数据，同时演示了权限控制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--a库：10.2.0.5 csdb  
--b库：11.2.0.4 orcl  

用a库的用户查询b库用户的数据
a库tnsnames.ora文件中添加对方内容  
ORCL =  
  (DESCRIPTION =  
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))  
    (CONNECT_DATA =  
      (SERVER = DEDICATED)  
      (SERVICE_NAME = orcl)  
    )  
  )  
 
a端 tnsping orcl  

a端创建用来查询b端test下表的用户
create user testa identified by oracle_4U
default tablespace abctest
temporary tablespace temp; 

grant create session,create database link to testa;

b端创建用来查询test下表的用户
create user testb identified by oracle_4U
default tablespace abctest
temporary tablespace temp; 

grant create session to testb;

test用户下授权查询所有表和视图、序列给testb用户，执行查询结果
test用户执行： 	select 'grant select on test.' || tname || ' to testb;' from tab;
sys用户执行：  	select 'grant select on ' || sequence_name || ' to testb;' from dba_sequences where sequence_owner='TEST';

a端testa用户创建dblink,只能查询，无法修改
[oracle@10g admin]$ sqlplus  testa/oracle_4U

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 7 02:16:29 2018

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create database link selectlink connect to testb identified by oracle_4U using 'ORCL'; 

Database link created.

SQL> select * from test.worker@selectlink;

WNO       WNAME
--------- --------------------
001       adam
002       lilith

SQL> insert into test.worker@selectlink values('003','eva');
insert into test.worker@selectlink values('003','eva')
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-02063: preceding line from SELECTLINK