Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Something strange - am puzzled.

Something strange - am puzzled.

From: Paul <paul_at_see.my.sig.com>
Date: Sat, 23 Jul 2005 13:53:55 +0100
Message-ID: <mhb4e1tu2pko0d5aklcmhjppc2hfj0tlk0@4ax.com>

Was experimenting this morning and have found something puzzling.

XP Pro SP2, Oracle 9.2.0.1.0 (don't ask - broadband's a comin!).

Log in as scott/tiger - run select count(*) from all_tables, and get the result 35. So, I think to myself that there's the scott schema, and then tables to which scott has access as a lowly user of the system, such as dual and a few others.

Then I created a system DSN in the admin tools for XP, using the "Oracle in OraHome92" driver, which I presume is supplied by Oracle itself.

I have the username as scott/tiger in the DSN!

Then I open Excel, New Workbook, then Data -> Get External Data ->

I then choose the Oracle datasource, and am prompted for the password which I enter.

Then I am presented with a query wizard and low and behold, I can see a whole load of tables, much more than 33 - approx. 250, which is less than the 834 I can count as sys/xxxx as sysdba.

Now, if try and place data from the db into the Excel spreadsheet and I choose one of the tables visible to scott in SQLPlus, I can do it.

If I choose one of the tables that appears in the query wizard, but not in the list visible to scott in SQLPlus, then I am given the error message ORA-00903 invalid table name.

*_BUT_*, when I go back into SQLPlus (as scott) I *_can_* select from these tables.

So, obviously scott can access 250 odd tables, but I can't see these using select owner, table_name from all_tables.

How can I see *_all_* the tables to which scott has access?

TIA. Paul...

-- 

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2, 

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters 
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.
 
Furthermore, as a courtesy to those who spend 
time analysing and attempting to help, please 
do not top post.
Received on Sat Jul 23 2005 - 07:53:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US