From: Henry Poras
Sent: Monday, July 09, 2001 4:52 PM
To: '[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 PM
To: Multiple recipients of list ORACLE-L
Subject:
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]
