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

--
http://www.freelists.org/webpage/oracle-l