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;