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.
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.