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.

Answer 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.

Answer 2

Maybe only the current_schema is different. Try:

alter session set current_schema=A

Answer 3

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.

Answer 4

Thanks for your replies. In our instance, there are no public synonyms for A.table. Yet I can select it from the B schema without using the schema identifier. The only difference I can see from one database to the other are the roles. I would like to disable this feature and don't want to try dropping roles one at a time to figure out if it is one of them. One user has DBA privileges, could that do it?

Answer 5

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.

Answer 6

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.

Answer 7

Aha! Executing your statement revealed that there is a public synonym for A.table! I am using TOAD software and was relying on the Schema Browser tool to look at the synonyms, and it did not appear in the list. The SQL stmt lists it as a Public synonym, yet TOAD is not seeing it. Hmmm...

In any event, this solves the mystery. I need to remember not to rely on just the tool!

Thanks for your help!

Answer 8

@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)

Answer 9

@hamishmcn - Yes, I went into the filter and found that tables were not included in the synonym lists, so I switched them on! Gonna go through all those pesky TOAD preferences and make sure they're set to my specs. And your SELECT statement is going into my "Named SQL" list as a reminder! Thanks.

Answer 10

how can i selected multi table from multi users users name : table name colum name 1- clnuser cln pat_id 2-arch3 f3 pat_id 3-arch4 f4 pat_id