Querying data from remote databases in Oracle

No, this article is not yet another database links tutorial. Sometimes you can have two databases, one being the live production database, and the other being a clone so that users can query the latter one (for security and load balancing reasons). So what does it involve to allow for example the finance unit access a particular schema on the live database through the clone database?

First of all, a database link needs to be set up. Avoid public database links unless you explicitly want everyone to have access to them. The database link can be created as a fixed user and this user, who exists on the live database, must have access to the tables in question:

CREATE DATABASE LINK link_name
CONNECT TO user_name
IDENTIFIED BY password
USING 'service_name';

Once a database link is setup, you need to create a synonym which points to a particular object on the remote database through this database link:

CREATE SYNONYM synonym_name
FOR "object_name"@database_link

Now comes the tricky part...A user cannot query the synonym directly as 'SELECT * from synonym_name'. A view needs to be created:

CREATE VIEW view_name AS SELECT * from synonym_name;

The view serves as an interface between the remote data and the user. Now to be a good DBA, you may need to create a role and assign select grants on this view to this role only. You will then put the 'finance' users in this role. So eventually you will have total control on who can access data from the live database.