Anyone used Fine Grained Access Control? It works pretty ok as far as I have tested it but I have a doubt, since FGAC works with the session context in the current session what happens in Parallel query? The slave sessions?
For example I query a table with 1000000 rows (seen by my user) with parallel degree 4, in my SQL*PLUS session I run a select * from table
my session will fetch the correct results but the other 3 PQ slave process wont because the policy only applies in my SQL*PLUS session not the slave session, i.e Oracle doesnt know that those three slave sessions are spawned by my user, it will treat as any other user and start fecthing wrong results
if it makes you feel any better, i am currently implementing fine-grained access on a worldwide enterprise financial data warehouse and have not yet experienced any problems.
however, i am not using session contexts to contain the FGAC predicates. i actually just keep all FGAC predicates in a table. the pl/sql packages that my policies implement simply select the appropriate predicate from this table. when new users are added to the system, we simply populate this table with the appropriate predicate.
i hope this makes sense. if not, please let me know and i will provide more detailed descriptions.
the table that contains all the predicates is owned by the application owner schema. i grant select on this table to PUBLIC. then i create the PL/SQL procedures that will obtain the correct predicates from this table. after that, i simply use the dbms_rls package to add a policy for each table implementing FGAC.
i do not need a trigger since there is no need to initialize a user's application context at logon (i'm guessing this is the type of trigger you were referring to). if you are, however, using application contexts to implement FGAC at the session level, i do recommend that you use this trigger as it will ensure that a user's context gets applied before accessing any data.
i hope i wasn't too vague. let me know if you would like examples or further clarifications.
Hm Have you tried Parallel Query? How would the slave processes get the right predicate? I dont know how slave processes works well, I dont know if they logon to a session like a normal session so thaa is the reason of my doubts. Also I read from Oracle Documentation regarding PQ issue and FGAC as well which indicates that in PQ enviroment most probably someone will get the wrong result set because of the slave processes not getting correct predicate... I cant tried it, still in the pahse of programming... and I hate programming! And I have to do all this alone with ZeRo help.. do all these PL/SQL thingies (I have done like 6 or 7 procedures, 20 triggers, 2 functions and 0 packages in my whole life!) =(
yes, my db does implement both FGAC and Parallel Execution (PX) and everything works just fine.
with PX, i believe the user's original slave process now takes on the role of a parallel coordinator, dividing up tasks into smaller units that will be taken on by parallel processes. since, these parallel processes are coordinated by the user's original slave process, and are not themselves individual 'free' processes, they will inherently have no more global of a 'scope' than the parallel coordinator; i.e. the parallel processes will inherit the FGAC predicates that were tacked on the original SQL statement.
and don't worry about the PL/SQL thingie...i'm sure you'll get the hang of it.