Home > ApEx > Implementing session timeout in your ApEx app – Part 1

Implementing session timeout in your ApEx app – Part 1

stopwatch.jpg

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.)

image1.jpg

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;

image2.jpg

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;

image3.jpg

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).

image4.jpg

Step 5 – Modify Authentication Scheme

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

image5.jpg

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

APP_USERS.set_last_activity_date;

image6.jpg
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.

 

 

About these ads
Categories: ApEx
  1. TJ
    March 6, 2013 at 7:13 am | #1

    HELLO ANDY:
    Quick question: It works fine but the flow does not go to the session time-out page I indicated in the code. I replaced 3 above with my page, but it goes to a generic “webpage not-found” type of error… Can you help?
    TJ

  2. GTA
    August 1, 2011 at 9:24 am | #2

    Hi Andy,

    Where you have been so long??? i couldn’t able to see in the Apex forum for the past 3 months(from March 2011 to July…). Really we r missing u Andy.

    All izz Well
    GTA

  3. July 28, 2010 at 11:18 pm | #3

    Hi andy,

    I am working on an application in 3.0 that want to control the inaction of the user session but I have a question, make all the points but when I add Session Verify Function, returns me the following error Error ERR-10 740 Unable to run custom post-auth process.

    It did that long ago that may help you post.

    Thank you very much

  4. March 14, 2010 at 5:20 am | #4

    Hi,

    Interesting post. With the new idle session settings in 3.2, is there a way to easily implement something similar?

    Karl

  5. Andrew Tulley
    October 30, 2008 at 7:50 pm | #5

    Jerry,

    I have just retested the package spec and package body code to make sure it compiles and indeed it does. See below a copy of the SQL*Plus session I just ran to compile it. If you typed the code manually into SQL*Plus or SQL Workshop, you might like to try copying and pasting instead.

    SQL> create user andytest identified by andytest;

    User created.

    SQL> grant connect, resource to andytest;

    Grant succeeded.

    SQL> conn andytest/andytest
    Connected.
    SQL> CREATE TABLE users (id NUMBER, username VARCHAR2(30), last_active_date DATE);

    Table created.

    SQL> CREATE OR REPLACE PACKAGE app_users
    2 IS
    3 c_session_timeout_minutes CONSTANT number(2,0) := 20;
    4 PROCEDURE set_last_activity_date;
    5 FUNCTION is_session_valid
    6 RETURN boolean;
    7 END app_users;
    8 /

    Package created.

    SQL> CREATE OR REPLACE PACKAGE BODY app_users
    2 IS
    3 PROCEDURE Set_Last_Activity_Date
    4 IS
    5 PRAGMA AUTONOMOUS_TRANSACTION;
    6 l_current_user VARCHAR2(30);
    7 BEGIN
    8 l_current_user := v(‘APP_USER’);
    9 UPDATE users
    10 SET last_active_date = sysdate
    11 WHERE username = l_current_user;
    12 COMMIT;
    13 END;
    14
    15 FUNCTION Is_Session_Valid
    16 RETURN boolean
    17 IS
    18 l_current_user VARCHAR2(30);
    19 l_is_session_valid VARCHAR2(1);
    20 BEGIN
    21 l_current_user := v(‘APP_USER’);
    22 SELECT CASE
    23 WHEN (last_active_date + APP_USERS.c_session_timeout_minutes/1440) < sysdate THEN
    24 ‘N’
    25 ELSE
    26 ‘Y’
    27 END
    28 INTO l_is_session_valid
    29 FROM users
    30 WHERE username = l_current_user;
    31 IF l_is_session_valid = ‘N’ THEN
    32 return false;
    33 ELSE
    34 return true;
    35 END IF;
    36 END;
    37 END app_users;
    38 /

    Package body created.

  6. October 9, 2008 at 2:24 pm | #6

    Your STEP 2 code does not compile!

    Error at line 15: PLS-00103: Encountered the symbol “’” when expecting one of the following:

    ( ) – + case mod new not null others

    table avg count current exists max min prior sql stddev sum
    variance execute multiset the both leading trailing forall
    merge year month DAY_ hour minute second timezone_hour
    timezone_minute timezone_region timezone_abbr time timestamp
    interval date

    1. CREATE OR REPLACE PACKAGE BODY app_users IS
    2. PROCEDURE Set_Last_Activity_Date IS
    3. PRAGMA AUTONOMOUS_TRANSACTION;

  7. July 28, 2008 at 8:36 pm | #7

    Thanks for this post. I need to implement this in my app and was wondering the best way to achieve it.

  8. April 3, 2008 at 5:27 am | #8

    Nice post Andy

  1. April 4, 2008 at 11:01 am | #1
  2. October 30, 2008 at 8:43 pm | #2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: