Access tables from multiple schemas

Question

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??

Query -

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.

Answer 1

If possible. What you need to do is create public synonyms for the tables, grant GRANT SELECT to the user who performs the query.

CREATE PUBLIC SYNONYM app_table1 FOR schema1.app_table1;

CREATE PUBLIC SYNONYM app_table2 FOR schema2.app_table2;

GRANT SELECT ON app_table1 TO app_user;

GRANT SELECT ON app_table2 TO app_user;

Your query will be like this:

Select * from app_table1 ap1 inner join app_table2 ap2