FW: Database Links standards
Title:
-Original Message-From: Henry Poras Sent:
Monday, July 09, 2001 4:52 PMTo:
'[EMAIL PROTECTED]'Subject: RE: Database Links
standards
Here is our stuff. Some of this arose because for a long time
each application team worked as an independent entity.Up to this
point, most of our database links have been private (owned by a particular
schema) and connected via a given user/password determined during the creation
of the link. I would like to move to public database links without embedded
usernames and passwords. This will entail slightly more administration on the
remote database (creating a user) but allow greater security and
flexibility.The difficulties with database links as we have been using
them are· Private database
links are an administrative nightmare (for exports, compiling objects,
.)· The user defined in the
link often was the schema owner of most objects in the remote database. The
security of the remote database was thus dependent on the security of the local
database.· If the remote
database needs to change the user password, there is no good way to know which
applications and database links will be affected.Points 2 and 3 can be
avoided by creating a new user on the remote database, but since all that is
needed when creating the link is a single entry in the local database, this is
often not done. (The phone call can be "can you tell me the username and
password on your database?" "sure")The advantages with the newer method
are· Better communication
between the local and remote databases is
necessary· The remote
database will need to create a user to match the username on the local database.
Thus it can easily control the rights assigned to this user. The remote database
is in control of its own security. The username chosen should reflect the source
application which will help in times of password changes.We no longer have
to worry about private database links.
HTH
Henry-Original Message-From: Thomas Jeff [mailto:[EMAIL PROTECTED]]Sent: Friday,
July 06, 2001 4:51 PMTo: Multiple recipients of list ORACLE-LSubject:
Database Links standardsWe came up with the below standards with
respect to database links (heavilyused in our
environment). The result has been a billion complaints byour
developers, stating that the standards are unnecessarily complex.
I'mcurious as to what others might think, if they *are* indeed too
complex.Also what kind of naming/adminstrative standards that other shops
employ.1. To access remote data across a DB LINK, the
standard implementation consists of four pieces:a) A private database link
owned by the schema owner of the table objects being
accessed. The CONNECT TO and IDENTIFIED BY clauses are
required - Naming Standard: {remote
schema}_{database name}b) A standard PUBLIC SYNONYM created for the remote
table being accessed across the link. - Naming
Standard: {remote schema}_{remote table}. The purpose of
this synonym is both to allow portability and also
to provide documentation of the remote
connection.c) A VIEW created as a SELECT * from the PUBLIC SYNONYM above
(1.b). - Naming Standard: {remote
table}_VWd) A PUBLIC SYNONYM on the VIEW above(1.c.) -
Naming Standard: {remote table}Thanks,Jeff T[EMAIL PROTECTED]
Database Links standards
Title: Database Links standards
We came up with the below standards with respect to database links (heavily
used in our environment). The result has been a billion complaints by
our developers, stating that the standards are unnecessarily complex. I'm
curious as to what others might think, if they *are* indeed too complex.
Also what kind of naming/adminstrative standards that other shops employ.
1. To access remote data across a DB LINK, the
standard implementation consists of four pieces:
a) A private database link owned by the schema owner of the table
objects being accessed. The CONNECT TO and IDENTIFIED BY clauses are
required
- Naming Standard: {remote schema}_{database name}
b) A standard PUBLIC SYNONYM created for the remote table being
accessed across the link.
- Naming Standard: {remote schema}_{remote table}. The purpose of this
synonym is both to allow portability and also to provide documentation
of the remote connection.
c) A VIEW created as a SELECT * from the PUBLIC SYNONYM above (1.b).
- Naming Standard: {remote table}_VW
d) A PUBLIC SYNONYM on the VIEW above(1.c.)
- Naming Standard: {remote table}
Thanks,
Jeff T
[EMAIL PROTECTED]
RE: Database Links standards
Title: Database Links standards
Jeff,
Looks
ok, except that I don't think you need step b) - the PUBLIC SYNONYM for the
remote table.
I
always create a private synonym within my DBA account (which happens to own the
tables in the local database) and then create the VIEW and a PUBLIC SYNONYM on
the view to hide everything from the user. The view will say
:
select
* from table@dblink.
The
developers then get access to the view only - never to the base db-linked
tables.
Your
approaches works for me, though. Tell the developers to use it as
delivered and to like it! :)
hope
this helps.
Tom Mercadante Oracle Certified Professional
-Original Message-From: Thomas Jeff
[mailto:[EMAIL PROTECTED]]Sent: Friday, July 06, 2001 4:51
PMTo: Multiple recipients of list ORACLE-LSubject:
Database Links standards
We came up with the below standards with respect to
database links (heavily used in our
environment). The result has been a billion complaints by
our developers, stating that the standards are unnecessarily
complex. I'm curious as to what others might
think, if they *are* indeed too complex. Also what
kind of naming/adminstrative standards that other shops employ.
1. To access remote data across a DB LINK, the
standard implementation consists of four
pieces:
a) A private database link owned by the schema owner of the
table objects being accessed. The
CONNECT TO and IDENTIFIED BY clauses are
required - Naming
Standard: {remote schema}_{database name}
b) A standard PUBLIC SYNONYM created for the remote table
being accessed across the link.
- Naming Standard: {remote
schema}_{remote table}. The purpose of this synonym is both to allow portability and also
to provide documentation of
the remote connection.
c) A VIEW created as a SELECT * from the PUBLIC SYNONYM above
(1.b). - Naming
Standard: {remote table}_VW
d) A PUBLIC SYNONYM on the VIEW above(1.c.) - Naming Standard: {remote table}
Thanks, Jeff T [EMAIL PROTECTED]
RE: Database Links standards
Title: Database Links standards
this
is fine.
your
problem may be in marketing the technology, not the technology itself.
-Original Message-From: Thomas Jeff
[mailto:[EMAIL PROTECTED]]Sent: Friday, July 06, 2001 4:51
PMTo: Multiple recipients of list ORACLE-LSubject:
Database Links standards
We came up with the below standards with respect to
database links (heavily used in our
environment). The result has been a billion complaints by
our developers, stating that the standards are unnecessarily
complex. I'm curious as to what others might
think, if they *are* indeed too complex. Also what
kind of naming/adminstrative standards that other shops employ.
1. To access remote data across a DB LINK, the
standard implementation consists of four
pieces:
a) A private database link owned by the schema owner of the
table objects being accessed. The
CONNECT TO and IDENTIFIED BY clauses are
required - Naming
Standard: {remote schema}_{database name}
b) A standard PUBLIC SYNONYM created for the remote table
being accessed across the link.
- Naming Standard: {remote
schema}_{remote table}. The purpose of this synonym is both to allow portability and also
to provide documentation of
the remote connection.
c) A VIEW created as a SELECT * from the PUBLIC SYNONYM above
(1.b). - Naming
Standard: {remote table}_VW
d) A PUBLIC SYNONYM on the VIEW above(1.c.) - Naming Standard: {remote table}
Thanks, Jeff T [EMAIL PROTECTED]
Re: Database Links standards
Looks good to me, except for step b.
You don't really need that.
I've used the same setup several
times successfully.
Jared
On Friday 06 July 2001 13:51, Thomas Jeff wrote:
We came up with the below standards with respect to database links
(heavily
used in our environment). The result has been a billion complaints by
our developers, stating that the standards are unnecessarily complex. I'm
curious as to what others might think, if they *are* indeed too complex.
Also what kind of naming/adminstrative standards that other shops employ.
1. To access remote data across a DB LINK, the
standard implementation consists of four pieces:
a) A private database link owned by the schema owner of the table
objects being accessed. The CONNECT TO and IDENTIFIED BY clauses are
required
- Naming Standard: {remote schema}_{database name}
b) A standard PUBLIC SYNONYM created for the remote table being
accessed across the link.
- Naming Standard: {remote schema}_{remote table}. The purpose of this
synonym is both to allow portability and also to provide documentation
of the remote connection.
c) A VIEW created as a SELECT * from the PUBLIC SYNONYM above (1.b).
- Naming Standard: {remote table}_VW
d) A PUBLIC SYNONYM on the VIEW above(1.c.)
- Naming Standard: {remote table}
Thanks,
Jeff T
[EMAIL PROTECTED]
Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1
Content-Transfer-Encoding: 7bit
Content-Description:
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
