Re: Different ways of setting up a multi user database?

reidlai_at_hk.super.net wrote:
>
> In article <362AE3E9.33F8_at_expohire.com.au>,
> info_at_expohire.com.au wrote:
> > 1. Create synonyms for each table in the database in each user account,
> > thus not requiring to include the schema name for every object
> > referenced in the client code
>
> Why don't you use public synonym? Is it difficult to use in your >project?

Public synonyms solve the problem of accessing database objects without including the schema name.
However, public synonyms still leave me with problems because of the following reasons:

1. When a user calls stored procedures, triggers or functions via synonyms Oracle executes these procedures from the context of the user who created the procedures, not the current user session. ie. If stored procedures modify any table, the table that belongs to the schema which created the procedure will be modified. THis means that I have to prefix the tables being modified in the stored procedure, function etc... with the schema name.

Does this mean that I have to create an identical procedures functions for each schema? Also, if a table in a schema has a trigger, can I have to create that same trigger in each schema? Sorry...one more question. Is it possible to create tables, procedures and functions in the public schema??

>
> >
> > 2. Make all client code accessing the database include the schema name.
> > (Thus not requiring the use of synonyms).
> >
>
> If you really care synonym, you should use public synonym or a trick as
> follow:
>
> Before executing any queries, ALTER SESSION SET CURRENT_SCHEMA=
> your_target_schema (no quote and no space).
>
> E.g. ALTER SESSION SET CURRENT_SCHEMA=SYS.
>
> Please be careful to use the above statement which is not recommended by
> Oracle.
>
> Best Regards,
>
> Reid Lai
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Wed Oct 21 1998 - 04:07:11 CDT