Re: how to group synonyms ?

good feedback, billy

the focus of my original post was synonyms vs hard-coded schema names -- so it we agree on the basic concept but disagree on PUBLIC vs PRIVATE -- neither view being wrong, certainly worth debating ;-)

good point on privileges needed to create public synonyms -- but that probably is only a strong point if we limit the discussion to allowing oracle schemas that own application objects to create private synonyms; and even then, it's (usually) no big deal to have the DBA create the public synonyms

if users are connecting to oracle via schemas other than the app object owning schema (boy, i sure hope they are!) then actual the advantages/disadvantages of maintaining public vs private syns deserve comparison

in using PUBLIC, the following are required to make it work reasonable well and be reasonably secure:

1. standardize on application or sub-system prefixes and use these in the public synonyms -- that way similarly named tables are always differentiated, i.e. HR_LOCATIONS & SO_LOCATIONS
2. require the DBA to run a generalized or custom script to create the public synonyms for the app/subsys (so we don't give CREATE PUBLIC SYNONYM to anybody)
3. when necessary to exp/imp an app/subsys, require the DBA to rerun the generalized or custom pub syn script for that app/subsys

advantage -- less maintenance
disadvantage -- exposing object existence when no privileges exist (which oracle could fix with two simple alterations to the ALL_SYNONYMS view)

for PRIVATE synonyms, the following would be required

1. have _each_ login schema create private synonyms for _each_ application/sub-system
2. standardize on application/sub-system prefixes and use these in private synonyms -- that way similarly named tables can each have their own private synonym, i.e. HR_LOCATIONS & SO_LOCATIONS
3. provide each login schema the CREATE SYNONYM privilege (which allows spoofing the application by recreating the syns to point elsewhere) -- or 3a) require the DBA to run a generalized or custom script to create private synonyms for each login schema, for each app/subsys used by the user (which replaces #1)
4. when necessary to exp/imp an app/subsys, require all login schemas to be exp/imp'd, or require the DBA to run the private syn script for each login schema

advantage -- existence of objects with no privileges is not exposed disadvantage -- somewhat more maintenance advantage -- lose a synonym, smaller impact disadvantage -- more synonyms to validate

of course, the scenario changes depending on exactly how you're authenticating users

working as a consultant (implying job activity not status or rank) for a number of different organizations with a number of different standards (or lack there-of) i've had to deal with both situations -- including DBAs that will not allow a plethora of private syns, under the theory 'the more objects you have the more that could go wrong' (which is valid even for syns, as they could be recreated incorrectly or get dropped)

so i still see a good case for PUBLIC syns

regarding :

| Apps using & creating synonyms, also need to drop & re-create them
| (think about using synonyms like current_billing_month,
| prev_billing_month). Would you feel comfortable granting an app schema
| the priv to drop public synonyms?

sound like there's another issue here -- if an app is dropping and creating synonyms in production to point to different objects, that sounds like deficient design.. someone needs to learn about views and SYSDATE

| Think SQL-Server and Oracle. SQL-Server database = Oracle Schema.

wasn't address SQL-Server in this thread, not applicable

| Scope definition. You can have two similar schemas on the same
| database using the same table names. And btw, there is *no* valid
| reason why this should not be allowed. I have seen this numerous times
| in many Oracle instances though the years.
|
| What now when it comes to public synonyms? Consider the danger where
| the schema that does need/use the public synonym looses its local
| synonym/table - and its app code still work as the scope of the object
| name is resolved at public synonym level.

seems you're making a point in favor of PUBLIC -- if relying on public syns (ie SO_LOC, HR_LOC) there are no private syns to lose, so there is no danger

you seem to be describing a potential danger of missing public and private, where the private is overriding the public. however, if you're mixing private and public syns, and you don't have good standards for app prefixes, then you've got a problem -- but it's with standards, not with public vs private.

if using private, use private (lose synonym, one schema breaks), if using public, use public (lose synonym, all schemas break)

| Export an app schema. Import that app schema in another database.
| Where are the public synonyms? So much for a self-contained app
| schema.
|

where are the private synonyms for the login schemas? see above

| > from a project development and testing standpoint, i've seen lots of
| > problems with explicitly naming the schema owner --
|
| Agree! Fully!! But that does NOT mean that you need *public* synonyms.
| Just synonyms.
|

glad to see your hackles are lowered, at least a little ;-)

• mcs