Friday, April 3, 2009

Forms-Apex Integration (2)



(PS: I am using SyntaxHighlighter for the first time. It works for IE, but not for Firefox, yet.
PS2: I fixed the Firefox issue. Apparently Firefox cannot interpret a css file on googlecode. The trick is to include the css in the blogger template.)

Yesterday I posted a simple solution for viewing stored PDF documents in a Forms6i client/server application using Apex. There was one catch: it was not secured in any way yet.

The security I have added relies on a simple principle: every call made to the Apex URL needs a unique id that can be used only once. The form generates this unique id, stores it in the database and passes it to Apex in the URL.

The Apex page checks if the unique id exists in the database. If so, it displays the page. If not, it returns an error message.
The Apex page immediately deletes the id from the database, so it cannot be reused in a modified URL.

Here is what I did.
First, create table user_sessions and a new procedure:


create or replace procedure check_session(i_user_id in varchar2
,i_session_id in varchar2)
is
v_dummy varchar2(1);
begin
select 'x'
into v_dummy
from user_sessions
where user_id = i_user_id
and session_id = i_session_id;

-- Session is valid. Delete the one time session id
delete
from user_sessions
where user_id = i_user_id
and session_id = i_session_id;
commit;
exception
when others then
raise_application_error(-20000,'Permission denied');
end;

Next, add two application items to the Apex application to hold the user and session id. E.g. AI_USERID and AI_SESSIONID.

Now modify the when-button-pressed trigger:


declare
v_sessionid varchar2(32);
begin
if :id is not null then
v_sessionid := dbms_random.string('x', 32);
insert into user_sessions (user_id, session_id)
values(user,v_sessionid);
commit;
host('cmd /c start http://server:port/apex/'
||'f?p=111:100:::::P100_ID,AI_USERID,AI_SESSIONID:'
||:id||','||user||','||v_sessionid);
end if;
end;

The last step is to modify the process:

check_session(v('AI_USERID'), v('AI_SESSIONID'));
showReport(nv('P100_ID'));

Add #SQLERRM_TEXT# to the Process Error Message.

Actually I do not really need the user id in this example, but you could use this parameter for other purposes, like logging who visited the page.

1 comment:

  1. i am new in apex and i need .to manage forms to call apex.but i cant do it right.

    i use forms 10G m it will be kind of you if you explain me how to manage formsweb.cfg to call apex or steps to manage forms to call apex

    ReplyDelete