Oracle-L: RE: Row level security and latch waits

From: Madhavan Amruthur <mad5698_at_fastmail.fm>
Date: Wed, 20 Aug 2003 15:29:25 -0800
Message-ID: <F001.005CBD25.20030820152925@fatcity.com>

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

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)

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(*)

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(*)

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: mad5698_at_fastmail.fm

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: ListGuru_at_fatcity.com (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).

Received on Wed Aug 20 2003 - 18:29:25 CDT

RE: Row level security and latch waits - LONG email...