<FONT face=Tahoma
size=2>-----Original Message-----From: Henry Poras Sent:
Monday, July 09, 2001 4:52 PMTo:
'ORACLE-L_at_fatcity.com'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 [<A
href="mailto:ThomasJe_at_tce.com">mailto:ThomasJe_at_tce.com]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 Tthomasje_at_tce.com
Received on Mon Jul 09 2001 - 16:06:47 CDT
