Hi,
With the way we have implemented FGAC, we do not have any parses
occurring with application context.
The application context does act like bind variables and I will try to
illustrate that with an example.
We set the context of the users logging in to a particular group/role
which gets executed from a logon trigger
POLICY_NAME FUNCTION
------------------------------ ------------------------------
DPR70_CURRENCY_D_PLCY DP_PREDICATE_FUNCTION
The above is the function defined on the object
Login as a user
SQL> select sys_context('dp_comp_group_id_ctx','comp_group_id')
2 from dual;
SYS_CONTEXT('DP_COMP_GROUP_ID_CTX','COMP_GROUP_ID')
--------------------------------------------------------------------------------
1012
SQL> select count(sql_text) from v$sql where sql_text like '%sys_cont%';
COUNT(SQL_TEXT)
---------------
12
In v$sql
this is the sql that generates the predicate clause vis the function
SELECT text from dp_security_text where object_name = :b1
and comp_group_id = sys_context('dp_comp_group_id_ctx','comp_group_id')
SELECT sys_context(:b2,:b1) from sys.dual
Notice how the context gets converted into bind variables
Also notice the parse_calls and executions
6 16 SELECT text from dp_security_text where
object_name = :b1 and comp_group_id =
sys_context('dp_comp_group_id_ctx','comp_group_id')
Now lets login as a different user
SQL> select sys_context('dp_comp_group_id_ctx','comp_group_id')
2 from dual;
SYS_CONTEXT('DP_COMP_GROUP_ID_CTX','COMP_GROUP_ID')
--------------------------------------------------------------------------------
1011
1* select count(*) from v$sql where sql_Text like '%sys_cont%'
SQL> /
COUNT(*)
----------
12
There is a parse because the user_id is different
7 18 SELECT text from dp_security_text where
object_name = :b1 and comp_group_id =
sys_context('dp_comp_group_id_ctx','comp_group_id')
No lets execute queries as this user
SQL> select count(*) from dpr70_currency_d;
COUNT(*)
----------
3
from V$sql
SQL_TEXT
--------------------------------------------------------------------------------
PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LOADS LAST_LOAD_TIME
----------- ---------- ------------------- ---------- -------------------
SELECT text from dp_security_text where object_name = :b1
and
comp_group_id = sys_context('dp_comp_group_id_ctx','comp_group_id')
7 20 2003-08-20/09:29:34 2 2003-08-20/10:39:44
Notice parse_calls does not change
lets execute another query
SQL> select count(*) from dpr70_gl_acct_balance_f;
COUNT(*)
----------
2974
from v$SQL
SQL_TEXT
--------------------------------------------------------------------------------
PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LOADS LAST_LOAD_TIME
----------- ---------- ------------------- ---------- -------------------
SELECT text from dp_security_text where object_name = :b1
and
comp_group_id = sys_context('dp_comp_group_id_ctx','comp_group_id')
7 24 2003-08-20/09:29:34 2 2003-08-20/10:39:44
Notice again parse_calls does not change
The above SQL is the predicate clause being generated every time
Hope this helps.
Also from Tom Kyte
/QUOTE
Sys_context is treated like a bind variable in a query -- its value is
BOUND in
just like any other value would be.
it rewrites the query which is then sent to the optimizer rewrote the
SYS_CONTEXT calls as bind variables.
/END QUOTE
URL
http://tinyurl.com/knrg
As for the doc below...
> I'll have to do more investigation, since those paragraphs don't clearly
> explain (at least, to me) what is meant by "the same predicate". Does
> that
> mean that predicates with Context Variables included as bind variables
> will
> not be reparsed if their Policy Function was created with
> Static_Policy=True, and yet different sessions can reuse the parsed SQL
> with their own Context values? ...or not?
what this means is in 9i, the policy function will be executed every time
a query is issued against the object.
But if the predicate being is generated is the same everytime then the
policy can be generated with static_policy=true
and if needs to be refreshed, then dbms_policy.refresh needs to be used.
If the predicate generated is different in different database sessions
but they are the same within a session, then
_dynamic_rls_policies=FALSE will prevent it being executed within the
session.
Hope this helps.
Regards,
Sorry for the long winding email
Thanks
Madhavan
http://www.dpapps.com
--
Madhavan Amruthur
DecisionPoint Applications
--
http://www.fastmail.fm - mmm... Fastmail...
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Madhavan Amruthur
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
