Re: user-schema what is it ?

Comments embedded.

s Lehaire wrote:
> Hi,
> I have some table created under a user and is attached to the schema of the
> user.
> I would like to move this table to an other schema is-it possible ?
>

Short answer: Yes. You might have trouble with FK constraints. You might have trouble if you do this when others are updating dependent tables (might break any FKs).

> second question about user-schema
> I've got an user which can alter, create table modify data and so on...
> I want to limit it to change the data (INSERT, DELETE, UPDATE, SELECT) and
> that's all.
> but I want to create a new user which can do the changes of the structure of
> the tables (CREATE, ALTER ..) and make request on it (INSERT, DELETE,
> UPDATE, SELECT)
>

Look in the docs for the concept of ROLES (see at http://tahiti.oracle.com). Assign all the rights to two different roles, then assign one of those roles to newly-limited user and the other to newly-created user. Of course, you have to strip directly assigned roles too.

> last question :
> what is the difference between USER and SCHEMA? I see that my schema have
> the same name of my user but if i create a new user which can create tables,
> those tables would be on which schema ??
> must I do a thing like this create table MY_SCHEMA."THE_TABLE" (...) ??
> thx for responses
>

Simple answer: a user becomes a schema as soon as they obtain objects (like tables, etc). In traditional terms, a 'schema' is simply the layout and structure of a database ... and that's what a user becomes when they have objects that define that layout and structure. Received on Wed Nov 20 2002 - 12:11:07 CST