RE: change users' default schema

First of all, I want to say thank you to everyone replied or replying. The user is connecting via ODBC and I hope they can easily do "alter session set current_schema=?" in ODBC.

Thanks a lot!

Roger

-----Original Message-----
From: Mladen Gogala [mailto:mgogala_at_verizon.net] Sent: Thursday, November 02, 2006 3:34 PM To: bdbafh_at_gmail.com
Cc: Roger Xu; Oracle-L_at_Freelists. Org (E-mail) Subject: Re: change users' default schema

On 11/02/2006 04:15:42 PM, Paul Drake wrote:
> On 11/2/06, Roger Xu <Roger.Xu_at_dp7upbg.com> wrote:
> >
> > Hi List,
> >
> > Can I change a user's default schema, so he dose not need to type
> > SCOTT.EMP just EMP?
> > (This user does nothing but queries SCOTT's tables.)
> > I know "Synonyms" can be created to bypass the prefix requirement.
> >
> > Thanks,
> >
> > Roger Xu
> >
>
> http://www.oracleadvice.com/Tips/logontrigger.htm
>
> Or one could use "execute immediate" instead.
>

It's by far too complicated. Alter session set current_schema will change name resolution (not the privileges!!!!) to the requested schema:

SQL> connect system
Enter password:
Connected.
SQL> select * from emp;
select * from emp

              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> alter session set current_schema=scott;

Session altered.

SQL> select * from emp
  2 /

     EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30

      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30

      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10

14 rows selected.

SQL> Of course, in order to do that, you must have "BECOME LUSER" privilege.

-- 
Mladen Gogala
http://www.mladen-gogala.com


This e-mail is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Any review, dissemination, copying, printing or other use o
____________________________________________________________________
This email has been scanned for all viruses by the MessageLabs Email Security System.
--
http://www.freelists.org/webpage/oracle-l