I need a help with such scenario:
we need a logical split of schema a into two new schemas A and A1.
They will contain the same objects (tables, views, packages...) but will be having different data - it is a preparation to full hardware split of DB.
However, there are other schemas which will be using both schemas - e.g. view. schema A will have public synonyms, schema A1 private. Is there a way how to access data from schema B through users - e.g. user B will read data from A and user B_1 will get data from A1? How should be created grants for such a situation and how users? Or is there some other way? Note, that I am looking for a solution, which requires no change in schema B objects (e.g. duplication of view with schema owner gefore object).
I would be gratefull for clear explanation or a link to some article which deals with simmilar situation.
Thanks in advance.
"user B will read data from A and user B_1 will get data from A1? "
I'd suggest a database wide LOGON trigger on the database.
Rather than coding in userids into the trigger, one set of users will have a role for accessing A, and the other set a different role for accessing A1. The LOGON trigger would see which role the user had (query user_role_privs) and do the appropriate
ALTER SESSION SET CURRENT_SCHEMA=....