Home » RDBMS Server » Security » Why does ORACLE calls VPD function twice on every DML?
Why does ORACLE calls VPD function twice on every DML? [message #268142] Mon, 17 September 2007 14:27 Go to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, I have a table called NEWS that contains an VPD policy enabled for INSERT,DELETE,SELECT and UPDATE statements.

I started saving logs in a table whenever it calls the VPD function and it surprised me that for each INSERT,DELETE,SELECT or UPDATE it uses the VPD function twice. Do you know why is that happening?.

Thanks in advance.
Re: Why does ORACLE calls VPD function twice on every DML? [message #268151 is a reply to message #268142] Mon, 17 September 2007 14:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) It depends on Oracle version and refresh policy you give on your VPD policy
2) This is Oracle internal stuff and not specific to VPD but to all functions use in SQL. Search on AskTom there is a thread on this (in short nobody knows).

Regards
Michel
Re: Why does ORACLE calls VPD function twice on every DML? [message #268153 is a reply to message #268142] Mon, 17 September 2007 14:56 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Thank you for answering.
Im working on a 10.2.0.2 database, it's a dbms_rls.dynamic policy. Do you have the URL of that thread?.
Re: Why does ORACLE calls VPD function twice on every DML? [message #268275 is a reply to message #268153] Tue, 18 September 2007 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it's a dbms_rls.dynamic policy

Quote:
DYNAMIC: The default policy type. Server assumes the predicate may be affected by any system or session environment at any time, and so always reexecutes the policy function on each statement parsing or execution. Applies to only one object.

So function is called each time Oracle encounters it in its access path (code plan execution).

No I can't give you the link, I should have to do what you can: search in AskTom. Try something like "how many times a function is called" or a variant.

Regards
Michel

[Updated on: Tue, 18 September 2007 01:55]

Report message to a moderator

Re: Why does ORACLE calls VPD function twice on every DML? [message #268420 is a reply to message #268275] Tue, 18 September 2007 09:17 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, thanks Michel for answering. My context(and the predicate) changes when an user sets it's client identifier, is there any way to cache the predicate of the user until the client identifier of the session changes?. I've read that SHARED_CONTEXT_SENSITIVE doesn't work with client identifier changing, so it wouldn't cache the new predicate for the user.
Re: Why does ORACLE calls VPD function twice on every DML? [message #268434 is a reply to message #268420] Tue, 18 September 2007 10:39 Go to previous message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know an option/way there immediatly.
I should have to make research on this.

Regards
Michel
Previous Topic: Change of password
Next Topic: Password problem on Oracle 10g (merged)
Goto Forum:
  


Current Time: Fri Oct 31 21:39:00 CDT 2014

Total time taken to generate the page: 0.05980 seconds