Simplest way to log in as Oracle user different from schema user and still see all tables,... of schema? What is the simplest way to log in as user X and see all tables,... of schema Y as if they belonged to X?

With an Oracle 11g database, I would like to log into the database using user X but all the data (tables, views, procedures, ...) would be stored in Schema Y.

All the code that accesses the tables is guaranteed to use unqualified names (TBL_NAME, not Y.TBL_NAME)

Searching for "schema alias" points to CREATE SYNONYM, but that would have to be done for each DML object, right? And since I only have unqualified calls, it seems a login trigger with ALTER SESSION SET CURRENT_SCHEMA = ...; should do the trick?

Any other options?
ALTER SESSION SET CURRENT_SCHEMA 


will work, but you still need to do all of the grants.

–  Phil Oct 29 at 23:33