DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005

    DB schema split and access to new schemas


    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.

  2. #2
    Join Date
    May 2000
    I would like to use qualifier on each table and views.
    Example, scheam_1.table_a , scheam_2.table_a etc.
    With this, I will not be confused with public or private synonym of an object.


  3. #3
    Join Date
    Feb 2005
    "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

    Again, no public or private synonyms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.