TOAD 10.5.0.41,
database 1 Oracle 9.2.0.8
database 2 Oracle 10.2.0.3
Something strange happening. I.e. I can’t explain it.
Situation 1:
In database 1 we have user A, who owns a db link to user B in database
2. User B doesn’t own tables, but has select privs on a lot of tables
belonging to another user.
User A can select AND describe tables over this db link.
Situation 2:
Same databases, but because of something special that needs to be tested
I created an extra user C in database 2. This user C owns 5 tables.
In database 1 I created an additional db link, connecting to user C in
database 2.
This time user A can select from these 5 tables, but as soon as I try to
describe one of the tables across the db link I get an error message,
telling me that table A.
doesn’t exists.
Which in itself is correct, but not what I asked.
Obviously I’m overlooking something, but what?
Anybody, please?
regards,
Peter
The message is clearly wrong, but I wonder if Toad is not finding the table
because user A doesn’t have access, or because Toad is sending the wrong
query. To answer that you’ll have to go to Database -> Spool SQL ->
Spool to screen, then try to describe the table.
If you send me the DDL for your DB Links (feel free to change the
usernames/databases to something generic, of course) I’ll try to reproduce
the situation.
-John
Is it possible that a priv granted via a role does not work across a db link?
Sort of like how PL/SQL code requires direct grant rather than via a role? Just
thinking out loud J
Something important I forgot:
Describing in sqlplus works!
I'm at home now, almost time to cook dinner. 
But tomorrow I'll cook you a reproducible case (I hope)
Peter
Op 27-5-2010 15:15, John Dorlon schreef:
The message is clearly wrong, but I wonder if Toad is not finding the
table because user A doesn’t have access, or because Toad is sending
the wrong query. To answer that you’ll have to go to Database -> Spool
SQL -> Spool to screen, then try to describe the table.
If you send me the DDL for your DB Links (feel free to change the
usernames/databases to something generic, of course) I’ll try to
reproduce the situation.
-John
From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] *On Behalf
Of *Peter van Rijn
Sent: Thursday, May 27, 2010 7:35 AM
To: toad@yahoogroups.com
Subject: [toad] Describe over DB link
TOAD 10.5.0.41,
database 1 Oracle 9.2.0.8
database 2 Oracle 10.2.0.3
Something strange happening. I.e. I can't explain it.
Situation 1:
In database 1 we have user A, who owns a db link to user B in database
2. User B doesn't own tables, but has select privs on a lot of tables
belonging to another user.
User A can select AND describe tables over this db link.
Situation 2:
Same databases, but because of something special that needs to be tested
I created an extra user C in database 2. This user C owns 5 tables.
In database 1 I created an additional db link, connecting to user C in
database 2.
This time user A can select from these 5 tables, but as soon as I try to
describe one of the tables across the db link I get an error message,
telling me that table A.
doesn't exists.
Which in itself is correct, but not what I asked.
Obviously I'm overlooking something, but what?
Anybody, please?
regards,
Peter
I think I found the culprit.
One of the spooled statements was:
Select username from sys.all_db_links
where upper(db_link) like 'DECX.%'
Mind the dot!
Since the name of the database link is "DECX" this statement yields no
result.
If I change the name of the database link to one with a dot in it the
describe works okay.
So, workaround available, but not really acceptable.
regards,
Peter
Op 27-05-2010 15:15, John Dorlon schreef:
The message is clearly wrong, but I wonder if Toad is not finding the
table because user A doesn’t have access, or because Toad is sending the
wrong query. To answer that you’ll have to go to Database -> Spool SQL
-> Spool to screen, then try to describe the table.
If you send me the DDL for your DB Links (feel free to change the
usernames/databases to something generic, of course) I’ll try to
reproduce the situation.
-John
From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] *On Behalf Of
*Peter van Rijn
Sent: Thursday, May 27, 2010 7:35 AM
To: toad@yahoogroups.com
Subject: [toad] Describe over DB link
TOAD 10.5.0.41,
database 1 Oracle 9.2.0.8
database 2 Oracle 10.2.0.3
Something strange happening. I.e. I can't explain it.
Situation 1:
In database 1 we have user A, who owns a db link to user B in database
2. User B doesn't own tables, but has select privs on a lot of tables
belonging to another user.
User A can select AND describe tables over this db link.
Situation 2:
Same databases, but because of something special that needs to be tested
I created an extra user C in database 2. This user C owns 5 tables.
In database 1 I created an additional db link, connecting to user C in
database 2.
This time user A can select from these 5 tables, but as soon as I try to
describe one of the tables across the db link I get an error message,
telling me that table A.
doesn't exists.
Which in itself is correct, but not what I asked.
Obviously I'm overlooking something, but what?
Anybody, please?
regards,
Peter
Thanks Peter.
It took me a few tries, but I was able to set up a scenario to reproduce it. It will be fixed next beta.
-John
Hi John,
Just tested in 10.6.0.11: fix is approved.
Thanks,
Peter
Op 28-05-2010 20:16, John Dorlon schreef:
Thanks Peter.
It took me a few tries, but I was able to set up a scenario to reproduce
it. It will be fixed next beta.
-John