User restrication by tablespace or partition
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: User restrication by tablespace or partition

  1. #1
    Join Date
    May 2013
    Posts
    2

    User restrication by tablespace or partition

    I have searched all around, DBASupport as well as google. No luck. This is pretty much my requirement.

    1: Schema ABC
    2: Two table spaces: 1 - ABC_US 2 - ABC_CA
    3: Tables in ABC will be partitioned (2 partitions) on Country_ID: 1 - PRTN_US 2 - PRTN_CA
    4: Partition PRTN_US goes to TS ABC_US and partition PRTN_CA goes to TS ABC_CA
    5: Data with Country_ID = 'USA' goes to PRTN_US and data with Country_ID = 'CA' goes to PRTN_CA

    The main requirement is to create roles ROLE_READ_US and ROLE_READ_CA.
    Users ABC_RDR_US and ABC_RDR_CA.

    Now, these users have to be restricted to their specific country data. For ex: user ABC_RDR_US should only see the data in table space ABC_US.

    How can this be achieved? There is no option to assign roles by table space.

    Thanks, in advance

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    So you want someone to show you how to create a list partitioned table and use the sys_context to filter for records from one country. For each partition you can specify a table space. Now what do I get for helping you to get a good grade?

    Code:
    WHERE country_id =  CASE sys_context('USERENV', 'CURRENT_SCHEMA')
                        WHEN 'PRTN_US' THEN 'USA'
                        WHEN 'PRTN_CA' THEN 'CA'
                        ELSE 'WTF' END;
    http://docs.oracle.com/cd/E11882_01/...1.htm#i1006532

    http://psoug.org/reference/sys_context.html
    this space intentionally left blank

  3. #3
    Join Date
    May 2013
    Posts
    2
    How about couple of Buds? ; )

    Thanks, gandolf989. But where and how do I implement this? Can a role be created with this WHERE clause?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    A second option would be VPD (Virtual Private Database) where access is granted at row level.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    The original poster can also create a view with the filter I mentioned above and a synonym for each schema.
    Another option would be to create a view for each schema that filtered on country and then create a synonym
    for each view for its respective schema. There are a dozen ways to do this, just pick one.

    It's not that I don't like bud, but you can't ship that kind of thing through the mail if you catch my drift...
    Actually, I only drink stout beer... Where is Mr. Hanky when you need him, we are venturing into his turf.
    this space intentionally left blank

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