Which Oracle database role allows a person to choose from the table in another schema without indicating the schema identifier? i.e., as user A- Grant choose on the.desk to user B B may then- "Choose * from table" without indicating the 'A'. Our databases enables this, another returns a 'table or view doesn't exist' error.
You may create a synonym with this. Produce a synonym named "CoffeeTable" on object "A.CoffeeTable". You may create a public synonym so everybody sees it such as this, or simply a synonym under user B.
Simply to make sure the schema you're using does not possess a private synonym for that table (or perhaps a view as Leigh indicates) you can the next
SELECT * FROM all_objects WHERE object_name = 'mytablename'
and check out the dog owner and object_type information.
Maybe just the current_schema differs. Try:
alter session set current_schema=A
If there is not a synonym, it is possible to view in schema B that chooses in the table in schema A utilizing the same title because the table? This could seem like in your area recommended table in lots of ways.
Brett is appropriate. Synonyms can be used for this. Actually you will find cases when you don't understand what would be the schema title being produced. You may make use of a for many schema title along with a has already been drawn in some Oracle instance.
Interesting replies. Within our instance, you will find no public synonyms for any.desk. Yet I'm able to choose it in the B schema without needing the schema identifier. The only real difference I can tell in one database towards the other would be the roles. I must disable this feature and don't wish to try shedding roles individually to determine whether it is one. One user has DBA rights, could which do it?
Aha! Performing your statement says there is a public synonym for any.desk! I'm using TOAD software and was depending around the Schema Browser tool to check out the synonyms, and it didn't come in their email list. The SQL stmt lists it as being a Public synonym, yet TOAD isn't seeing it. Err...
The point is, this solves the mystery. I have to remember to not depend on only the tool!
@hamishmcn - Yes, I went in to the filter and located that tables weren't incorporated within the synonym lists, and so i switched them on! Gonna undergo all individuals annoying TOAD preferences and make certain they are set to my specs. As well as your Choose statement is certainly going into my "Named SQL" list like a indication! Thanks.
@erno - most likely why TOAD did not demonstrate the general public synonym happens because it filters the data proven within the list - I do not have toad before me however i think should you right click tab you're going to get towards the blocking options (eg "only show objects possessed through the schema", "show public objects", "show system objects" etc)