Re: multiple schemas per user - possible?

On Tue, 17 Sep 2002 16:28:35 GMT, roger <rsr_at_rogerwrae.com> wrote:

...
>To me, it seems like a user should be able to create multiple
>"schemas", add objects to the various schemas, and specify a
>default schema which would be used in the absence of an explicit
>schema reference, etc...

...

Firstly, USER en SCHEMA are referring to two aspects of the same entity. If you log on and perform things you do this as a user. Objects you create in a schema. If you use the CREATE USER command you create a user and also a schema, be it empty.

To answer your question: as user A you can create objects in schema B, if you have enough system privileges granted to you, for instance to create a table in schema B you need the CREATE ANY TABLE privilege. Immediately after this table (say TBL) is created you can only reference it with B.TBL. There are ways point to B.TBL by referencing TBL, one you mentioned is creating a private or public synonym TBL pointing to B.TBL.

But what you are apparently looking for is

ALTER SESSION SET CURRENT_SCHEMA=B. Now reference to TBL will point to B.TBL.

You will remain user A though, only the unqualified references will be resolved differently. Therefore object privileges are still those of user A.

Jaap. Received on Tue Sep 17 2002 - 13:48:57 CDT