Oracle Apex - Forcing Password Change in Custom Authentication

Introduction

If you've created you own authentication scheme in APEX, you may be aware that there's no mechanism to hook into that enforces a password change. Typically you might want your users to change password after 90 days, or if it's just been reset by an admin.

The example in this blog uses a redirect on a global page region to switch to the change password page. It's not a full authentication solution and assumes the following:-

• You have a table called system_users
• The table has a column called "expired" which is either '1' or '0'.
• You have already created a change password page (in my example this is page 106).
• Page 106 has a process to change your password and reset the "expired" flag.
• You already have a working authentication and password change function.

Instructions

1 - Create a function to check if your user account is expired.

(My example below assumes you have a "system_users" table with a column called "expired". But it's only a simple example. Write something better!)

Function check_expired(p_app_user varchar2)
 RETURN NUMBER AS
 l_return NUMBER;
BEGIN
  BEGIN
    SELECT 1
    INTO   l_return
    FROM   system_users
    WHERE  expired = 1
    AND    upper(username) = upper(p_app_user));
  EXCEPTION
    WHEN no_data_found THEN
      l_return := 0;
  END;
  RETURN l_return;
END check_expired;


2 - Create an application item called "G_PASSWORD_EXPIRED" and ensure its scope is 'Global'.

3 - Create an application computation for this item, set the computation point to After Authetication and the type to 'PL/SQL Expression'.

The compution will be:-

  check_expired(:APP_USER);

Create an Application Compution for G_PASSWORD_EXPIRED

4 -  Create a global page and add a region called "Redirect".

5 - Set the region type to 'PL/SQL Dynamic Content' and add the following code:-

IF :G_PASSWORD_EXPIRED = 1 THEN
   APEX_UTIL.REDIRECT_URL('F?P=&APP_ID.:106:&APP_SESSION.::&DEBUG.');
END IF;

(NB. Page 106 is the change password page)

6 - Set the Server-side Condition to 'Current Page is NOT in comma delimited list', and list pages you don't want the redirect to be affected by.

(NB. This should be the login page, and the change password page.)

7 - On your change password page, ensure your ChangePassword process sets the G_PASSWORD_EXPIRED item to zero, or you will be stuck in a loop.

Change Password Page Should also Reset G_PASSWORD_EXPIRED

That's all you need... the global page adds a page redirect and no attempt to hack an alternative page allowed me to get around it.