Today I had a situation at my client’s place which I need to tackle as soon as possible and I did tackle it nicely.

I think so…….

Any way let me explain the situation and how I tackle it.

Lets assume there are two database one is prod (live one) and another one dev (development).

There came a request from one non oracle developer that he want to have a readonly access to the “production_note” table in prod database as he wanted to access live data of “production_note” table.

In normal situation if it was development environment then I would have done following:

Login into SQL*Plus as SYSTEM in “prod” database

SQL> connect system/system
Connected.

SQL> create user production identified by production default tablespace users;

User created.

SQL> grant connect, resource to production;

Grant succeeded.

SQL> connect actual_user/actual_user
Connected.

SQL> grant select on production_notes to production;

Grant succeeded.

SQL> connect production/production
Connected.

SQL> create synonym production_notes for actual_user.production_notes;

Synonym created.

Then give him access to this new user.

But now here situation is different.I have security by IP addresses in “prod” database. Only Oracle Application Server and “dev” database server can access the  “prod” database and I don’t want to give access to IP address of this developer’s machine. If I do so it might open some loop hole in security infra of my client.

So to tackle this situation I did as follows:

First I did same as above in “Prod” database.

Then I Login into SQL*Plus as SYSTEM in “dev” database

SQL>CREATE PUBLIC DATABASE LINK prod_remote
2     CONNECT TO production IDENTIFIED BY production
3     USING ‘biz11′;

Database link created.

SQL> create user production identified by production default tablespace users;

User created.

SQL> grant connect, resource to production;

Grant succeeded.

SQL> CREATE SYNONYM production_notes
2     FOR production.production_notes;

Synonym created.

Then I just gave him access to this new user in “dev” database without making things complicated for this non oracle user.

Thats it, Mission Accomplished

I hope this post will help somebody and offcourse me as a reference material for future!