Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using database link to connect to two databases

zephyr223Oct 15 2009 — edited Oct 15 2009
Hi guys,
Here's my question. I need to give a contractor Select rights on tables located in two schemas located on two different servers. One server is dev and one is prod. Let's say we have schema user: EMPLOYEE on dev and schema user: DEPARTMENT on prod. The outside contractor needs select rights on all tables in DEPARTMENT schema but only needs select on a few tables ( 4 to be exact) from EMPLOYEE schema.

My initial train of thougth is: CREATE a user named: TEST (random name of course, just used for an example) for contractor on prod. Give user TEST select rights on all tables in DEPARTMENT schema.
Create database link to dev server so user TEST can access EMPLOYEE schema.

How do i go about limiting the user TEST via the database link to only view the 4 tables from EMPLOYEE instead of all teh tables?

Should I also consider doing this scenario in reverse? Creating user TEST on dev, giving TEST rights to the 4 tables in EMPLOYEE, then creating the database link to connect to department schema on prod database??

Any help will be appreciated. Thanks in advance.
This post has been answered by Toon Koppelaars on Oct 15 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 12 2009
Added on Oct 15 2009
4 comments
476 views