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?

Improve this question
3
  • ALTER SESSION SET CURRENT_SCHEMA will work, but you still need to do all of the grants.
    – Philᵀᴹ
    Oct 29, 2014 at 23:33
  • @Phil - you mean X needs all rights on Schema Y ?
    – Martin
    Oct 30, 2014 at 8:40
  • see if proxy authentication will be useful for you.
    – Raj
    Nov 5, 2014 at 15:31

1 Answer 1

Reset to default
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 and acknowledge that you have read and understand our privacy policy and code of conduct.

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