Re: Changing the default schema for a user

Hello!

You can set current schema in the after logon trigger

example

CREATE OR REPLACE TRIGGER afterlogon
AFTER LOGON ON DATABASE
   BEGIN
   IF USER ='OPERATOR' THEN
            EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=ADMIN';    END IF;
END; Note that current schema is "admin"
  select sys_context( 'userenv', 'current_schema' ) from dual;

but you are still the user "operator"
select sys_context( 'userenv', 'username' ) from dual;

You can also solve it with synonyms as alkos said but i personally avoid using them.

Regards,
Janne!

Thomas Lam wrote:

> 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
Received on Wed Nov 19 2003 - 06:06:24 CST