Re: Changing the default schema for a user

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

Additionally -besides the two mentioned methods- you can of course qualify the tables with the schema.object notation like so:

select * from admin.table_name where bla='foo';

Also, operator is a reserved word and I'd refrain from using it as a user's name:

select * from v$reserved_words where keyword='OPERATOR'

hth,
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch