Re: how to group synonyms ?

mcstock wrote:

> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1069957611.8448_at_yasure...
> | mcstock wrote:
> |
> | > "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
> |
> | My major concern would be security. If someone doesn't need to know
> | something exists it is a bad habit to allow them information about
> | schema and object names.
> |
> | It is so simple to use non-public synonyms to do the same thing I'd
> | suggest for the few minutes it would take to write a script to create
> | the regular synonyms ... good development practice should win out over
> | the lazyman's approach.
> | --
> | 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)
> |
>
> well that's more specific.
>
> agree with the use of private syns, don't see the need to classify a
> difference of opinion as 'lazy'
>
> now, what about plugging the ALL_USERS hole?
>
> -- Mark Stock

That is, in my opinion, one of the biggest holes in the Oracle security architecture. The system denies knowledge about schemas and objects but not users. Why? No good reason I am sure.

-- 
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)