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

We 21 oct 98 (info_at_expohire.com.au) wrote:

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

Stored procedures works by same way always, even if You call it directly ant it uses direct (without public or private alias) table references. I.e. stored procedures/packages works with priveleges granted to it owner. There is some exceptions from it rule, DBMS_SQL package for example.

> Does this mean that I have to create an identical procedures functions
> for each schema?

Yes, If You wish to make Your stored proc works with different tables with same names from different schemas.

> Also, if a table in a schema has a trigger, can I have
> to create that same trigger in each schema?

Same as stored procs. Just note that two tables with same names in different schemas does not have _nothing_ common one with another.

> Sorry...one more question. Is it possible to create tables, procedures
> and functions in the public schema??

No. PUBLIC is not schema I think. Seems like PUBLIC is a role that granted to any Oracle user. But there is some difference - You can grant priveleges to PUBLIC, but You can't create <some_role> synonyms.

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

Why ?

--
Is There A God Or Any Kind Of Justice Under The Sky... (Queen'91)

Igor V. Podolsky (igoryok_at_soft-review.kiev.ua) Received on Fri Oct 23 1998 - 13:31:05 CDT