| VPD and ApEx [message #255944] |
Thu, 02 August 2007 08:47  |
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  |
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/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
