DB schema split and access to new schemas
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: DB schema split and access to new schemas

  1. #1
    Join Date
    Oct 2005
    Posts
    5

    DB schema split and access to new schemas

    Hello,

    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
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

    Tamil

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

    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