如何在 oracle 中创建只读数据库链接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3888123/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can i create read only database link in oracle
提问by ponds
Consider the following scenerio....
考虑以下场景......
I have a master user MASTER.
我有一个主用户 MASTER。
I have a test user TEST.
我有一个测试用户 TEST。
For both users the table structure are same. Both user can be on different oracle servers.
对于两个用户，表结构是相同的。两个用户都可以在不同的 oracle 服务器上。
then I create a database link as master_link by logging in as test user to sql plus using the following command
然后我通过使用以下命令以 test 用户身份登录到 sql plus 来创建一个作为 master_link 的数据库链接
CREATE DATABASE LINK master_link CONNECT TO MASTER IDENTIFIED BY password USING (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST =192.168.9.139)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
CREATE DATABASE LINK master_link CONNECT TO MASTER IDENTIFIED BY password USING (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST =192.168.9.139)(PORT = 1521))) (CONNECT_DATA = (SERVICE)NAME) = or
By loggin in as test user and using the database link name i can modify the tables in master user. for example
通过以测试用户身份登录并使用数据库链接名称，我可以修改主用户中的表。例如
update table1@master_link set display_title = 'PONDS' ;
更新 table1@master_link set display_title = 'PONDS' ;
This query updates the table table1 of master user.
此查询更新主用户的表 table1。
My requirement is i want to give read only permission to database link (master_link) so that test user can't modify or insert into any table in master user by using database link.
我的要求是我想授予数据库链接（master_link）只读权限，以便测试用户无法使用数据库链接修改或插入主用户中的任何表。
回答by Justin Cave
On whatever database the MASTER schema resides, you would need to create a new user (i.e. MASTER_READ_ONLY). Grant the MASTER_READ_ONLY user SELECT access on all of MASTER's tables (most likely via a role). Optionally, create either public synonyms or private synonyms in the MASTER_READ_ONLY schema that reference the objects in MASTER. Then, when you create the database link, use the MASTER_READ_ONLY account rather than the MASTER account.
在 MASTER 模式所在的任何数据库上，您都需要创建一个新用户（即 MASTER_READ_ONLY）。授予 MASTER_READ_ONLY 用户对所有 MASTER 表的 SELECT 访问权限（很可能通过角色）。（可选）在 MASTER_READ_ONLY 架构中创建公共同义词或私有同义词，以引用 MASTER 中的对象。然后，在创建数据库链接时，使用 MASTER_READ_ONLY 帐户而不是 MASTER 帐户。
Something like
就像是
As a DBA
作为一名 DBA
CREATE USER master_read_only
IDENTIFIED BY password2;
GRANT create session, create synonym
TO master_read_only;
CREATE ROLE master_ro_role;
GRANT master_ro_role
TO master_read_only;
As MASTER
作为大师
BEGIN
FOR x IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE
'GRANT SELECT ON master.' || x.table_name ||
' TO master_ro_role';
END LOOP;
END;
As MASTER_READ_ONLY
作为 MASTER_READ_ONLY
BEGIN
FOR x IN (SELECT * FROM all_tables WHERE owner='MASTER')
LOOP
EXECUTE IMMEDIATE
'CREATE SYNONYM ' || x.table_name ||
' FOR master.' || x.table_name;
END LOOP;
END;
On the database where the TEST user has been created
在创建 TEST 用户的数据库上
CREATE DATABASE LINK master_link
CONNECT TO master_read_only
IDENTIFIED BY password2
USING (DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST =192.168.9.139)
(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = orcl)))
回答by Alex Poole
If you connect as user masteranyone using the link has that user's privileges on the remote database. To isolate this you can create a new user on the instance that has the masterschema, give that user select privs on (selected) master's tables, and build your database link using the read-only user.
如果您以用户身份连接，则master使用该链接的任何人都拥有该用户在远程数据库上的权限。为了隔离这一点，您可以在具有master架构的实例上创建一个新用户，为该用户提供对 (selected)master表的选择权限，并使用只读用户构建您的数据库链接。
(I'm assuming you don't have update any tablegranted to publicon the master instance...)
（我假设您没有在主实例上update any table授予public...）
或者，如果您无法在主实例上创建新用户，则可以改为在测试实例上创建新用户。如果您在该新用户的架构中创建数据库链接，您可以使用您的
testtest用户可以访问的链接创建只读视图，而不会暴露数据库链接本身。对于稍后来跟踪正在发生的事情的人来说，这可能会更复杂，但这是一种选择。