-
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
-
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
-
How about couple of Buds? ; )
Thanks, gandolf989. But where and how do I implement this? Can a role be created with this WHERE clause?
-
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|