Re: Simple Q re schemas and users

"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 Tue Oct 07 2003 - 20:39:18 CDT