PQ & FGAC
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: PQ & FGAC

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi peeps

    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?

    Cheers

    Burps

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    The business policy that gets defined for a user ultimately gets converted into a WHERE clause of the query and the actual query the gets executed has this WHERE clause.

    Whats the confusion ?
    svk

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    The problem is with Parallel Query, FGAC only detects the actual session not the slave session spawned for PQ, I was wondering if anyone know a way to avoid this or we cant use PQ for FGAC?

  4. #4
    Join Date
    Jul 2000
    Posts
    521
    Can you please explain the problem with an example ?
    svk

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  6. #6
    Join Date
    Nov 2001
    Posts
    15
    hi there,

    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.

    have fun

    nick


  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    do you use a logon trigger to activate the FGAC?

  8. #8
    Join Date
    Nov 2001
    Posts
    15
    hi,

    no, i don't use a trigger.

    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.

    have fun

    nick

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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!) =(

  10. #10
    Join Date
    Nov 2001
    Posts
    15
    hi pando,

    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.

    nick

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