PQ & FGAC

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

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 ?

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?

Can you please explain the problem with an example ?

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

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

do you use a logon trigger to activate the FGAC?

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

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!) =(

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 :)

oh yeah, one more thing. i wanted to suggest a good reference book for topics on Oracle PX and the Parallel Server:

ORACLE PARALLEL PROCESSING by Tushar Mahapatra and Sanjay Mishra, O'Reilly Publishing

nick :)

I think the problem only happens in Sys Context:

Extracted from doco:

Parallel Query Not Recommended for Use with SYS_CONTEXT

If SYS_CONTEXT is used inside a SQL function which is embedded in a parallel query, the function will not be able to pick up the application context. The application context exists only in the user session.

Consider, for example, a user-defined function within a SQL statement, which sets the user's ID to 5:

CREATE FUNC proc1 AS RETURN NUMBER;
BEGIN
IF SYS_CONTEXT ('hr', 'id') = 5
THEN RETURN 1; ELSE RETURN 2;
END
END;


Now consider the statement:

SELECT * FROM EMP WHERE proc1( ) = 1;


If this statement is run as a single query (that is, if one process is used to run the entire query), there will be no problem.

However, if this statement is run as a parallel query, the parallel execution servers (query slave processes) do not have access to the user session which contains the application context information. The query will not produce the expected results.

By contrast, if you use the SYS_CONTEXT function within a query, there is no problem. For example,

SELECT * FROM EMP WHERE SYS_CONTEXT ('hr', 'id') = 5


In this way, it works like a bind variable: the query coordinator can access the application context information and pass it on to the parallel execution servers.

Versioning in Application Context

When you execute a statement, Oracle8i takes a snapshot of the entire application context being set up by SYS_CONTEXT. Within the duration of a query, the context remains the same for all fetches of the query.

If you (or a function) attempt to change the context within a query, the change will not take effect in the current query. In this way, SYS_CONTEXT enables you to store variables in a session.


Do you have an exmaple of doing it without context :-?

hi pando,

hmmm...you bring up a very interesting point. i've never considered the idea that a parallel slave process would not be able to access the original user's session application context. however, i don't believe this applies in a PX/FGAC situation; i would bet that in a PX/FGAC situation the parallel slave processes are able to execute the correct sql, with the correct predicate.

the doc excerpt you provided states that a "function will not be able to pick up the the application context". FGAC, it seems to me*, uses a function call ONCE, during the policy implementation to 'rewrite' a user's sql, which is then passed on to subsequent parallel slave processes. the function call is NOT part of the parsed sql and therefore does not need access to the user's application context.

also, the fact that Oracle Corporation seems to push the notion of using its PX feature along with its FGAC feature so heavily, leads me to believe that these two features would work in harmony. i mentioned that i do not use application contexts to store user's predicates. i do, however, use application contexts to retrieve the current user's username within my FGAC functions. and stating once again, I use both PX and FGAC quite heavily and have never had a problem. i am also very much sure that all my parallel slave process are executing correct sql statements.

i would suggest testing your current FGAC setup to see if all parallel slave processes are indeed implementing the correct sql. and regarding your request for an example of FGAC without contexts, if you still want them, let me know and i can provide some sql for you.

* the basis for my claims is simply the culmination of my personal experiences and observations. i have not found direct statements to substantiate or unsubstantiate my personal hypothesis concerning these matters in official Oracle documentation or other sources.

nick :)

Actually I extracted these paragraph from FGAC section in Oracle Doco so basically Oracle is suggesting not use FGAC with PQ!