Take the 2-minute tour ×

Which Oracle database role will allow a user to select from a table in another schema without specifying the schema identifier? i.e., as user A- Grant select on A.table to user B; B can then- "Select * from table" without specifying the 'A'. One of our databases allows this, the other returns a 'table or view does not exist' error.

share|improve this question

6 Answers 6

You can create a synonym for this. Create a synonym named "CoffeeTable" on object "A.CoffeeTable". You can create a public synonym so everyone sees it like this, or just a synonym under user B.

share|improve this answer

Just to double check that the schema you are using doesn't have a private synonym for the table (or a view as Leigh suggests) you could the following

SELECT * FROM all_objects WHERE object_name = 'mytablename'

and look at the owner and object_type information.

share|improve this answer

Maybe only the current_schema is different. Try:

alter session set current_schema=A

share|improve this answer

If there isn't a synonym, is there a view in schema B that selects from the table in schema A using the same name as the table? This would appear to be a locally referenced table in many ways.

share|improve this answer

Brett is right. Synonyms are used for this. In fact there are cases where you do not know what will be the schema name in production. Maybe you use A for some schema name and A is already taken in some Oracle instance.

share|improve this answer

@erno - probably the reason that TOAD didn't show you the public synonym is because it filters the information shown in the list - I don't have toad in front of me but I think if you right click on tab you will get to the filtering options (eg "only show objects owned by the schema", "show public objects", "show system objects" etc)

share|improve this answer

Your Answer

