Re: Desparate Oracle Help...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 6 Nov 2001 05:46:37 -0800
Message-ID: <9s8pjt01b6p@drn.newsguy.com>

In article <Xns9151ADF189615craznar_at_130.102.2.1>, Christopher says...
>
>OK, I have spent 8 hours on this one ... anyone help?
>
>I have a user 'joe' which has a whole stack of tables, I have a user 'bob'
>which can access those tables.
>
>I need to make it so that 'bob' can access the tables using the same syntax
>as 'joe'.
>
>That is 'joe' can go 'select * from blah', I need 'bob' to be able to do
>that as well instead of 'select * from joe.blah'.
>

when logged in as BOB issue:

SQL> alter session set current_schema = joe; SQL> select * from blah;

that'll be as if they entered select * from joe.blah.

Consider:

ops$tkyte_at_ORA717DEV.US.ORACLE.COM> desc emp ERROR:
ORA-04043: object emp does not exist

ops$tkyte_at_ORA717DEV.US.ORACLE.COM> select ename from emp where rownum = 1; select ename from emp where rownum = 1

                  *

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

ops$tkyte_at_ORA717DEV.US.ORACLE.COM> alter session set current_schema=scott;

Session altered.

ops$tkyte_at_ORA717DEV.US.ORACLE.COM> desc emp

 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 EMPNO                               NOT NULL NUMBER(4)
 ENAME                                        VARCHAR2(10)
 JOB                                          VARCHAR2(9)
 MGR                                          NUMBER(4)
 HIREDATE                                     DATE
 SAL                                          NUMBER(7,2)
 COMM                                         NUMBER(7,2)
 DEPTNO                                       NUMBER(2)

ops$tkyte_at_ORA717DEV.US.ORACLE.COM> select ename from emp where rownum = 1;

ENAME

---

KING it does not change your PRIVS, just the default schema name used to resolve object references.

>I cannot use VIEWs or SYNONYMS ... they have to be tables when looked at
>from the 'bob' account ... just as they are when 'joe' accesses them.
>
>With VIEWs and SYNONYMs - they are not tables for 'bob', but they are for
>'joe'.
>
>Any ideas ?
>
>--
>---
>/* Christopher Burke - Spam Mail to craznar_at_hotmail.com
>|*
>\* Real mail to cburke(at)craznar(dot)com

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Nov 06 2001 - 07:46:37 CST