The Dba of our company has created views for the tables for all the users. We have 230 users, he has created 230 views for each tables! I want to replace this by public synonym and grant select to the users. Example: Create public synonym emp to scott.emp grant select on scott.emp to public; When I query synony after droping view it works. Without dropping the view If I query emp oracle does the select on the view. When we have a view and synony in the same name, what is the hirarchy between this? Is there a workaround for this problem. Because when I modify a table I have to correct 230 view scripts!
The resolution order is described in the documentation:
Oracle Database attempts to qualify the first piece of the name referenced in the SQL statement. For example, in scott.emp, scott is the first piece. If there is only one piece, the one piece is considered the first piece.
A) In the current schema, the database searches for an object whose name matches the first piece of the object name. If it does not find such an object, it continues with step b.
B) The database searches for a public synonym that matches the first piece of the name. If it does not find one, it continues with step c.
C) The database searches for a schema whose name matches the first piece of the object name. If it finds one, it returns to step b, now using the second piece of the name as the object to find in the qualified schema. If the second piece does not correspond to an object in the previously qualified schema or there is not a second piece, the database returns an error.
If no schema is found in step c, the object cannot be qualified and the database returns an error.