RE: Advice for dblinks between two prod DBs.
- From: "Jesse, Rich" <Rich.Jesse@xxxxxx>
- Date: Wed, 22 Nov 2006 12:59:17 -0600
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@xxxxxxxxxxxxxxxxx]
Sent: Wednesday, November 22, 2006 12:53 PM
To: oracledba.williams@xxxxxxxxx; Jesse, Rich
Cc: Hemant K Chitale; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Dennis Williams
Sent: Wednesday, November 22, 2006 1:44 PM
To: Rich.Jesse@xxxxxx
Cc: Hemant K Chitale; oracle-l@xxxxxxxxxxxxx
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
Other related posts:
