VPD and hard parsing
Date: Wed, 1 Jul 2009 15:52:40 -0500
A while back I implemented a VPD scheme to limit user access for reporting purposes. The way that I coded the functions which generated the predicates was such that, for the application they returned NULL, i.e. no restriction, and for specified reporting users, they generated restrictive code to be appended to the where clause. For example:
function probsummarym1_sec(D1 varchar2, d2 varchar2)
return varchar2 is
d_predicate := null; select sys_context('ADHOC_ACCESS','USERNAME') into uname from dual; if (uname is not null ) then begin d_predicate := 'company in (select company fromcustom.t_user_company_access where
end; end if; return d_predicate;
Will this force a hard parse if it returns NULL?
This was on an Oracle 10.2.0.2.0
FWIW, I turned this project over to another DBA about a two weeks after they went production, so I have no direct indication as to whether my VPD implementation was causing performance problems.