I have two schemes with multiple tables. The queries in packages heavily reference schema names. Is it possible to remove schema names from the queries and oracle to identify the tables based on roles or privileges??
Select * from schema1.app_table1 ap1 inner join schema2.app_table2 ap2
The query would be run from oracle user app_user who has access to both the tables - app_table1 and app_table2.
It is possible to run the query without schema prefixes or synonyms just by modifying the user app_user.
Thanks for your help.