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.