Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Two DBLINK or not two DBLINK?

Re: Two DBLINK or not two DBLINK?

From: Alberto Dell'Era <>
Date: Fri, 15 Dec 2006 20:27:26 +0100
Message-ID: <>

> My kneejerk for separating out the links is that I think it will help in
> troubleshooting performance issues from the destination database by
> making the connection easy to identify as well as some security
> isolation, since each DBLINK would connect to a different schema in the
> destination DB. One downside I can forsee is that I would increase the
> DBLINK connections as any update would most likely also have read from
> the DB as well.

About the security part - you might as well give the "select" privilege to the "not-read-only" user; if someone can update, it is a trustable user, so why not giving it the right to read also (only in special circumstances it might be worthwhile to let people write and not read, but as far as I can understand, you have no evidence that you have such a special requirement).

For troubleshooting - ie identify who is issuing a particular "bad" statement - you would need to segregate access by application, ie, if you have application a,b,c hitting the local database, you would need to build a from_a, from_b, from_c user on the remote instance, create a db_link_a, db_link_b, db_link_c on the local one, and make it so that each application uses its own db_link_X db-link. Than, you'll grant the least privileges possible to from_x needed for the application X. That way, you'll know which application is responsible for the "bad" statement, and at the same time you'll secure more the remote instance.

But please check the benefits on having multiple db-links against the maintenance burden ... both in the "read only/write" scenario and the "per application" one :)

Alberto Dell'Era
"Per aspera ad astra"
Received on Fri Dec 15 2006 - 13:27:26 CST

Original text of this message