I have a database that I just implemented a simple FGAC where a current user only can see rows they own. What I'm looking for is a way that users can see all rows they have rights to in addition to the rows they own.
My schema is as follows:
ADAS_POC table - contains user_ID#, login, user_name
Access_Restrict table - ID #, Access Identifier - 1) everyone 2) division 3) owner only
ADAS_Library table - Table where data needs to be secured.
If the row in the library contains a 1 for the Access_Restrict column everyone would have access. If it contains a 2 only the user that belongs to that specified division can view the data. If it contains a 3 then owner only. What is the best way to set up my users to restrict their access? Should I create separate roles or simply add it to the POC table for each user?
Does anyone have example of code for context_package and the security_package that would help with my coding? Ive used a couple of examples on VPD and from Tom Kytes Oracle Expert one-on-one book. They helped somewhat.
Thanks for the response,
We have implemented fgac in a different way bcos of our requirements.
If I have understood your requirements properly:
but as u say that the levels are hierarchical why dont you have a table that has the parent_level and use a hierarchical query and include the list in the dynamic where clause. say if a user has level 3 which is admin and its child_levels are 2,1 the where clause will be level in (3,2,1).
If you have 9i, all these are provided by oracle's OLS and you dont have to worry abt anything.
Click Here to Expand Forum to Full Width