Geek Answers Handbook

How can I create a read-only database link in oracle

Consider the following scenerio ....

I have a master user MASTER.

I have a test user test.

For both users, the table structure is the same. Both users can be on different oracle servers. A.

then I create the database link as master_link by logging in as a test user in sql plus using the following command

CREATE DATABASE DATABASE master_link CONNECT TO A MASTER IDENTIFIED BY USER USE (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.9.139) (PORT = 1521_NAME)) (PORT = 1521_NAME)) )))

When registering as a test user and using the database link name, I can change the tables in the main user. eg

table update1 @master_link set display_title = 'PONDS';

This query updates the table table1 of the main user.

My requirement: I want to give read-only access to the database link (master_link) so that the test user cannot modify or insert the main user into any table using the database link.

+4
source share
2 answers

In any database where the MASTER schema is located, you will need to create a new user (i.e. MASTER_READ_ONLY). Grant access to SELECT user MASTER_READ_ONLY on all MASTER tables (most likely through a role). If desired, create either public synonyms or private synonyms in the MASTER_READ_ONLY schema that reference objects in MASTER. Then, when you create a link to the database, use the MASTER_READ_ONLY account, not the MASTER account.

Sort of

Like 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;

Like a 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;

Like 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;

In the database where the TEST user was created

 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)))
+4
source

If you connect as the master user, anyone who uses the link has these user privileges in the remote database. To isolate this, you can create a new user in an instance that has a master scheme, give that user select privileges in the (selected) master tables, and build a link to the database using a read-only user.

(I assume that update any table provided by public on the main instance ...)

Alternatively, if you cannot create a new user in the master instance, you can instead create a new user in the test instance. If you create a database link in this new user schema, you can create read-only views using the link available to your user test , without directly exposing the database link. It may be more difficult for those who come later to track what is happening, but this is an option.
+2
source

More articles:


All Articles