Re: Changing the default schema for a user

Comments and answers are inline.

"Thomas Lam" <thomaszic_at_hotmail.com> a écrit dans le message news: c9a1ad67.0311190040.f6af89b_at_posting.google.com...
> Greetings All,
>
> I have created a oracle instance in ORACLE 9i.
> I have created a user "admin" and create a lot of tables and object
> belongs to user "admin". And now, I need to create another user
> "operator" who just only can SELECT, INSERT, UPDATE and DELETE on all
> tables of schema "admin".

Good practice.

> Therefore, I grant SELECT, INSERT, UPDATE and DELETE of all objects
> under "admin" to "operator" and using the following command when
> "operator" login:
>
> ALTER SESSION SET CURRENT_SCHEMA= admin;
>
> it is successfully the "operator" operate on all tables of "admin" but
> cannot ALTER tables, but it is very trouble that everyone login as
> "operator" needs to ALTER SESSION. Can I change the "operator" login's
> default schema as "admin"!?
> or is there any other methods to solve my scenario, e.g. synonym (I
> don't know how to use synonym) !?

> Thomas Lam

That's it ! Use synonyms.
The way to use them is fairly simple :
log as operator
CREATE SYNONYM my_admin_table for admin.my_admin_table;

This way operator can refer to admin's tables as if they were in is own schema
with the security policy demanded (only DML, no DDL)

If multiple users have to refer to admin's tables, either you create PUBLIC synonyms (logged as admin
and CREATE PUBLIC SYNONYM, take a look a SQL reference) or you create a set of synonyms for each user as you did for operator.

Hope this helps.

Alkos Received on Wed Nov 19 2003 - 03:14:30 CST