Re: Changing the default schema for a user

According to Oracle ...
"This setting changes the current schema, but it does not change the session user or the current user, nor does it give you any additional system or object privileges for the session. "

So you will still need to grant appropriate object privileges.

You may also be interested in
http://www.ixora.com.au/q+a/0105/05154451.htm Mike

Jan Korecki <Jan.Korecki_at_contactor.se> wrote in message news:<3fbb5cf3$0$97839$57c3e1d3_at_news3.bahnhof.se>...
> 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 Mon Dec 01 2003 - 18:24:20 CST