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

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