Implementing session timeout in your ApEx app – Part 1

This entry talks about one method
for causing users to timeout of your
application after a period of inactivity.

STEP 1 – Create your USERS table

From SQL Workshop, run the following:

CREATE TABLE users (id NUMBER, username VARCHAR2(30), last_active_date DATE);

And then, once the table is created, populate it with a user:

INSERT INTO users (id
,username
,last_active_date)
VALUES (1
,'ANDY'
,'01-JAN-2008');

(Change the value of ‘ANDY’ to be the username of a user that exists. For example, if you are using ApEx Standard Authentication then change it to be the username of an ApEx user.)

STEP 2 – Create the APP_USERS package

From SQL Workshop, run the following to create the package specification:

CREATE OR REPLACE PACKAGE app_users
IS
c_session_timeout_minutes CONSTANT number(2,0) := 20;
PROCEDURE set_last_activity_date;
FUNCTION is_session_valid
RETURN boolean;
END app_users;

From SQLWorkshop, run the following to create the package body:

CREATE OR REPLACE PACKAGE BODY app_users
IS
PROCEDURE Set_Last_Activity_Date
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_current_user VARCHAR2(30);
BEGIN
l_current_user := v('APP_USER');
UPDATE users
SET last_active_date = sysdate
WHERE username = l_current_user;
COMMIT;
END;

FUNCTION Is_Session_Valid
RETURN boolean
IS
l_current_user VARCHAR2(30);
l_is_session_valid VARCHAR2(1);
BEGIN
l_current_user := v('APP_USER');
SELECT CASE
WHEN (last_active_date + APP_USERS.c_session_timeout_minutes/1440) < sysdate THEN
'N'
ELSE
'Y'
END
INTO l_is_session_valid
FROM users
WHERE username = l_current_user;
IF l_is_session_valid = 'N' THEN
return false;
ELSE
return true;
END IF;
END;
END app_users;


Step 4 – Create a user-friendly “Session timed out” page

Create an ApEx page and set its “Authentication” Security Setting to “Page is Public”. This page should contain a helpful message for the user indicating that their session has timed out. Make a note of the ID of this page (we’ll assume this is page 3 for this example).

Step 5 – Modify Authentication Scheme

Click “Shared Components > Security > Authentication Schemes” in Application Builder and select your authentication scheme.

Enter the following into the “Post-Authentication Process” field:

APP_USERS.set_last_activity_date;

Also, within this screen, set the Session Verify Function to be:

BEGIN
IF APP_USERS.is_session_valid THEN
APP_USERS.set_last_activity_date;
return true;
ELSE
OWA_UTIL.redirect_url(
owa_util.get_cgi_env('request_protocol')||
'://'||
owa_util.get_cgi_env('server_name')||
':'||
owa_util.get_cgi_env('server_port')||
owa_util.get_cgi_env('script_name')||
'/wwv_flow_custom_auth_std.logout?p_this_flow='||
:APP_ID||'&p_next_flow_page_sess='||:APP_ID||':3');
RETURN false;
END IF;
END;

In the above code, replace 3 with whatever the ID of your helpful “Your session has timed out” page is.

Step 6 – Finished, have a cup of tea

Now, whenever a user has been inactive for more than 20 minutes and they try to access another page in your application, they will be logged out and taken to a page indicating that there session has timed out.

Next time I’ll write about how we can make an ApEx application popup a message just before a user’s session is about to time out, giving them the option to remain logged in. You can now see the second part here.