RE: Advice for dblinks between two prod DBs.

From: Mercadante, Thomas F \(LABOR\) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Wed, 22 Nov 2006 14:02:36 -0500
Message-ID: <ABB9D76E187C5146AB5683F5A07336FFE088C5@EXCNYSM0A1AJ.nysemail.nyenet>

"SELECT FROM ANY TABLE" LOL!

This transmission may contain confidential, proprietary, or privileged information which is intended solely for use by the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, dissemination, copying or distribution of this transmission or its attachments is strictly prohibited. In addition, unauthorized access to this transmission may violate federal or State law, including the Electronic Communications Privacy Act of 1985. If you have received this transmission in error, please notify the sender immediately by return e-mail and delete the transmission and its attachments.

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jesse, Rich
Sent: Wednesday, November 22, 2006 1:59 PM Cc: oracle-l_at_freelists.org
Subject: RE: Advice for dblinks between two prod DBs.

Yep! The remote DB is our ERP. The API to the ERP data is via views in the app schema. I plan on creating a new user in the ERP DB for each link and granting it only the access to the views that are required.

Going from a shop where one app account has "SELECT FROM ANY TABLE" to this scenario has it's hurdles. And, yes, I'll take the blame for the former. Amazing what a DBA can learn in the 9 years since... :)

Thanks guys!

Rich

From: Mercadante, Thomas F (LABOR)
[mailto:Thomas.Mercadante_at_labor.state.ny.us]
Sent: Wednesday, November 22, 2006 12:53 PM To: oracledba.williams_at_gmail.com; Jesse, Rich Cc: Hemant K Chitale; oracle-l_at_freelists.org Subject: RE: Advice for dblinks between two prod DBs.

This is great advice. Create a read-only user in the remote database. Only grant access to the items it needs to see. And again, I would create views in the remote database that are granted to this user that give only what is needed. And pre-joining tables in this view aids querying immensely - the joins happen on the remote database. This speeds things up tremendously!

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dennis Williams
Sent: Wednesday, November 22, 2006 1:44 PM To: Rich.Jesse_at_qg.com
Cc: Hemant K Chitale; oracle-l_at_freelists.org Subject: Re: Advice for dblinks between two prod DBs.

Jesse,

From reading your posting and the replies, I don't see one aspect mentioned.

One of the drawbacks of a database link is that it gives access to the entire schema, particularly troubling where ERP systems are involved. A practice I've used is to create a special username on each end of the database link. On one end, that username owns the database link. At the other end, that username is granted select only privilege to the needed tables. Synonyms can simplify a complicated syntax.

Just a tip.

Dennis Williams

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 22 2006 - 13:02:36 CST