How can i create read only database link in oracle

Question

Consider the following scenerio....

I have a master user MASTER.

I have a test user TEST.

For both users the table structure are same. Both user can be on different oracle servers.

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

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)))

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

This query updates the table table1 of master user.

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.

Answer 1

If you connect as user master anyone 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 master schema, give that user select privs on (selected) master's tables, and build your database link using the read-only user.

(I'm assuming you don't have update any table granted to public on the master instance...)

---

Alternatively, if you aren't able to create a new user on the master instance, you could create a new user on the test instance instead. If you create the database link in that new user's schema, you could create read-only views using the link which your test user could access without exposing the database link itself. This may be more complicated for someone coming along later to trace what's happening, but it's an option.

Answer 2

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.

Something like

As a 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

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

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)))