How to create Read-Only database link in oracle

In oracle in few cases as a DBA we have to create the (read-only) only select privilege database link as per the request of developer or tester or QA request. Cause is anything our responsibility is create the select privilege dblink.

Using below steps we can create Read Only DB Link in Oracle Database:

How to create Select privilege DBLINK:

Step 1: Create dummy user in source database.

Sql> create user dummy identified by dummy default tablespace users quota 100m on users;

Sql> grant connect, resource, select any table to dummy;

On Target DB side:

Step1: Copy the tnsnames of source database to target database.

Step2:

Sql> create database link readonly connect to dummy identified by dummy using ‘orcl’;

Now you can test the readonly dblink:

Syntax: Sql> select count(*) from <schema_name>.<table_name>@readonly;

Sql> select * from rak.test@readonly;