Oracle database role - select from table across schemas without schema identifier

Question

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.

Solution 1

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.

Solution 2

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.

Solution 3

Maybe only the current_schema is different. Try:

alter session set current_schema=A

Solution 4

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.

View more solutions