Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » VPD and ApEx
VPD and ApEx [message #255944] Thu, 02 August 2007 08:47 Go to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
My intention is to create an application - report using Oracle ApEx
a) The login should be validated against ebusiness suite authentication for which I created a function under APPS schema

create or replace function custom_ebiz_suite_auth (p_username IN VARCHAR2, p_password IN VARCHAR2) 
return boolean 
as 
userID NUMBER;
begin 
   if fnd_web_sec.validate_login(p_username, p_password) = 'Y'  then 
   	  SELECT USER_ID INTO userID FROM FND_USER WHERE USER_NAME= p_username;
	  fnd_global.APPS_INITIALIZE (userID,0,0);  --FND_GLOBAL.EMPLOYEE_ID is set
      return true; 
   else 
   	   fnd_global.APPS_INITIALIZE (0,0,0);
      return false; 
   end if; 
end;
/


b) I created a schema called myApex and then did - GRANT EXECUTE ON custom_ebiz_suite_auth TO myApex

c) I used the above function in the Authentication scheme inside ApEx and login works based on EBusiness Suite Login.

d) In my myApex schema I have a table EmployeeMaster (which I used in creating report) which uses VPD (I hope I am making sense)
and I used FND_GLOBAL.EMPLOYEE_ID

d) Soon after login to Application (created by ApEx), I am authenticated but I don't get records from EmployeeMaster
I disabled VPD by:
execute DBMS_RLS.ENABLE_POLICY('MYAPEX', 'EMPLOYEEMASTER', 'USER_SEL_POLICY', FALSE);

now I get all the records.

How to use the VPD concept in ApEx by using the above steps?



Thank you in advance.
Anand
Re: VPD and ApEx [message #259510 is a reply to message #255944] Wed, 15 August 2007 15:12 Go to previous message
patrick_wolf
Messages: 11
Registered: August 2007
Location: Vienna, Austria
Junior Member
Hi Anand,

I think the problem is due to the session/connection handling of Oracle APEX.

I assume that the call to the fnd_global.APPS_INITIALIZE package in your authentication code stores your userID in a static package variable. The only problem with that is that this static variable will not be available the next time you issue a request from your browser.

Oracle APEX uses a connection pool, so you don't have a dedicated database session. So you can't rely on any static variables you have set in a previous request, because the chances that you get the same session are low. Independent from that, APEX/mod_plsql issues a command to reset the package state (clear all static variables) each time it gets a connection, so your static variable will always be lost. That's some background information.

What what can you do to solve this problem?

1) Create an application item which will store your userID
2) Use the Shared Components>Authentication Schemes>Edit Authentication Scheme\Post-Authentication Process property to assign the userID to your application item
3) Use the Shared Components>Edit Security Attributes>Security \Virtual Private Database PL/SQL call to set security context property to re-initialize your context. Eg:
BEGIN
    fnd_global.APPS_INITIALIZE(:YOUR_APPLICATION_ITEM,0,0);
END;


The code in the "VPD" property is always called when APEX grabs a session from the connection pool.

Hope that helps to solve your problem.
Patrick
----------------------------------------------------------------------------------------------------
My APEX Blog: http://inside-apex.blogspot.com
The ApexLib Framework: http://apexlib.sourceforge.net
The APEX Builder Plugin: http://sourceforge.net/projects/apexplugin/
Previous Topic: How to make field grayed out?
Next Topic: how use the "tooltip" in Oracle Application Express
Goto Forum:
  


Current Time: Mon Dec 05 19:00:58 CST 2016

Total time taken to generate the page: 0.14820 seconds