1

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 '14 at 23:33
  • @Phil - you mean X needs all rights on Schema Y ? – Martin Oct 30 '14 at 8:40
  • see if proxy authentication will be useful for you. – Raj Nov 5 '14 at 15:31
2
ALTER SESSION SET CURRENT_SCHEMA 

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

– Phil Oct 29 at 23:33

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.