Re: how to group synonyms ?

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1069919723.866398_at_yasure...
| Billy Verreynne wrote:
|
| > "mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in
| >
| >
| >>i highly recommend public synonyms for applications
| >
| >
| > Arrgghhh!!! No!! Never!!
| >
| >
| >>-- coding the schema
| >>name with the object creates maintenance and portability problems
| >
| >
| > BULL!!
| >
| > Do *NOT* use public synonyms as a crutch like that. It is nothing but
| > a quick and dirty method that lacks. Period.
| >
| > Take security, access control and scope into proper consideration and
| > then DO IT CORRECTLY!!
| >
| > --
| > Billy
|
| I'm with Billy on this one. There is no valid reason I can think of for
| public synonyms beyond DUAL and others created by Oracle. And I'd even
| take issue with a few of theirs if I thought anyone would listen.
|
| --
| Daniel Morgan
| http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
| http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
| damorgan_at_x.washington.edu
| (replace 'x' with a 'u' to reply)
|

thanks for the feedback, polite and otherwise...

i can see that there may be some valid points behind Billy's, shall we say, observation, but a little more articulation on specific problems would be helpful

i've never ran into problems with my recommended approach -- and i don't see any security issues except the ability for adhoc users to read the public synonym definitions for objects that they would otherwise not know to exist. please elaborate if visibility of these object is the issue

scope is considered in this approach (standard application prefixes covers that).

basically, i view public synonyms in the same light as OS, Java, and Apache path and virtual directory settings -- all in pretty common usage

it seems like the perceived issues are actually addressed by proper use of roles

from a project development and testing standpoint, i've seen lots of problems with explicitly naming the schema owner -- from limitations in testing (all test environments must use the same schema names, hence each test environment requires its own instance) to customer DBAs insisting on changing schema names (which i don't advocate, but it happens in the real world all too often), to code reusability (can't plug code and tables from one project into another project)

daniel's argument (basically) in favor of oracle's usage of public synonyms actually supports the use of non-oracle public synonyms. if i'm developing with good standards, i will quickly have common code and objects (i.e. subsystems) used across multiple applications and/or subsystems (i.e., called from code existing in, or running under, multiple schemas). if it's not (totally) evil for oracle to create public synonyms for widely used objects, why object to the same practice for commonly used non-oracle objects?

if public synonyms are that troubling, or if some actual problems can be identified, then an alternate approach would be requiring each user to have appropriate private synonyms in order to run the application -- but that seems a bit overkill, yet it preserves the schema-independence that i've found to be extremely useful

• Mark Stock