Oracle Apex - Password expire after set date based on table and Login validation

I trust you are well. Here's the breakdown of what I got so far. I have:

• Custom authentication schema, referencing username and password from table. (works)

I've added an expire_password column which I would like to set a trigger or function (not sure exactly how to go about it) that will do validation on the login page to check if the user's account is or not expired. If it expires direct to a Change Password page where the user will put in a new password and be able to log in. When the new password has been added reset the timer to expire again password after a set date automatically.

I'd really appreciate the help.

Thanks in advance

Thembani

@littlefoot Covered the basics already. One thing I'll add is that you should not be storing passwords in cleartext, they should be hashed and preferably with a unique salt generated for each password. Doug Gault recently had a related post here: https://blogs.oracle.com/apex/custom-authentication-and-authorization-using-built-in-apex-access-control-a-how-to

Hopefully, you're already doing something like that...

Here's an example Application Process from an old app where I implemented this functionality.

declare

  l_users_rec users%rowtype;

begin

  if :app_page_id != '101' --login
  then
    select *
    into l_users_rec
    from users
    where upper(email) = upper(:APP_USER);

    if l_users_rec.change_password_flag = 'Y' and :app_page_id != '110' --profile
    then
      wwv_flow.g_unrecoverable_error := true;
      owa_util.redirect_url('f?p=' || :APP_ID || ':110:' || :APP_SESSION);
    end if;
  end if;

end;

Some of the APIs have changed. Start with the old ones and then test the new ones:

1. apex_application.stop_apex_engine (instead of wwv_flow.g_unrecoverable_error)
2. apex_util.redirect_url (instead of owa_util.redirect_url)