Seems this has been answered already [many times] on AskTom. Try
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:7452932520496
Cheers
--
"Chris O" <itoys1 -a t- optusnet -d o t- com -d o t- au> wrote in message
news:3f836ace$0$20475$afc38c87_at_news.optusnet.com.au...
> "Holger Peine" <peine_at_iese.fraunhofer.de.this-is-junk> wrote in message
> news:3f82bb52_at_news.fhg.de...
> > Hello everyone,
> >
> > you will see from my question that I'm not an Oracle expert.
> >
> > How can I make many DB users access the same set of tables
> > without qualifying each access with the name of the schema
> > containing the tables?
> >
> > I have one user MASTER which creates all the tables (thus all tables
> > end up in the MASTER schema), and many other users (some of them
> > created later at run-time) accessing the tables (sometimes via DML
> > statements, sometimes via stored procedures). The users maintain no
> > other data besides that in the MASTER schema's tables, i.e. there
> > are no tables in all those user schemas. The users need to prefix
> > the table names, like MASTER.sometable. I feel that this is not the
proper
> > way to do that, but what is the recommended way?
> >
> > Should I create a public synonym (or one private synonym in each user
> > schema) for every table name?
> >
> > Is it possible to create the other users in the MASTER schema in the
first
> > place (which might create security problems - would GRANTs still provide
> > protection within one common schema?)?
> >
> > - And one more related question about packages: Do packages exist within
> > a schema or besides the schemas? I.e. does the
package_name.package_member
> > access syntax have to be prefixed with a schema name when used from
> > another schema?
> >
> > Thanks for your help,
> >
> > --
> > Dr. Holger Peine
> > Fraunhofer IESE, Kaiserslautern, Germany
> > Phone +49-6301-707-134, Fax -209 (shared)
> > www.iese.fraunhofer.de/Staff/peine -- PGP key on request or via
> pgp.mit.edu
> >
> Hi All. I'll add my 1/2 cents worth...
>
> The answers by Daniel, Ed and Pete only partly answer this question. It
> always seems that no one wants to give a definitive answer to this
question.
> Is this because there is no definitive answer?
>
> Use of PUBLIC synonyms is always going to be less flexible than using
> PRIVATE synonyms. This is due to the potential for namespace clashes.
> Usually this is not an issue.
>
> However, I believe that I can recall a posting to this group that stated
> that PUBLIC synonyms were significantly slower than PRIVATE synonyms. If
> this is true then presumably it is due to the explosion in the number of
> PUBLIC synonyms used by Java etc. If this assertion is true than this
> would provide a valid reason for avoiding PUBLIC synonyms.
>
> So this begs the question, how to best use PRIVATE synonyms? The
simplistic
> approach is to create all required PRIVATE synonyms in each users schema.
> If there are more than a few users this quickly becomes a nightmare! I do
> not believe this is practical for more than a few users.
>
> Another approach is to create a small number of schemas containing the
> required PRIVATE synonyms. There are then two alternatives for making one
> of these schemas your current schema. These are:
>
> 1. Have the application connect as one of these schema using some
technique
> to obtain its [secret] password.. This is basically what Oracle Apps used
> to do in Version 9 [and probably still does]. That is, the app provides
the
> authentication and then re-connects the user [silently] as the owner of
the
> target schema.
>
> 2. Have the application set a role [password protected] which grants it
> privileges to do what it needs and then use the command
>
> ALTER SESSION SET CURRENT_SCHEMA = <target-schema>
>
> to change the default schema to the required one. Unfortunately, this
does
> not always work as expected. For example, the following potential
problems
> exist:
>
> i) The pseudo column USER returns the actual connected user and not
> <target-schema>. One might argue either way as to what this should
return!
>
> ii) Queries against USER_xxxx catalogs return objects owned by the
connected
> user and not those owned by <target-schema>. It could easily be argued
that
> this is the correct [and desirable] behaviour.
>
> iii) Some DDL statements do not work as expected. For example, the RENAME
> command fails no matter what your underlying privileges are.
>
>
> Q. Does anyone know whether the SET CURRENT_SCHEMA method has been used
> successfully? Is there any known problems with it [e.g. such as being not
> recommended by Oracle]?
>
> Q. Any other suggestions for how to implement PRIVATE synonyms?
>
> Cheers
>
>
>
>
>
Received on Wed Oct 08 2003 - 00:12:37 CDT
