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

Question

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

Answer 1

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

Answer 2

As everything is custom-made, here's one option.

• create a database job (using DBMS_JOB or DBMS_SCHEDULER) which will call a procedure that - on a daily basis - checks whether someone's password is going to expire within the next few days. If so, send them an e-mail (using UTL_MAIL or UTL_SMTP), saying that they should change password
• custom login procedure checks whether sysdate is larger that the password expire date. If it is, it'll return false (i.e. won't let the login procedure to end successfully and will reject login attempt)

---

Another option, closer to what you described. In order to notify user about password expiration, you first have to let them in and then somehow redirect to "password change" page.

• create a (stored) function which checks whether someone's password has expired (let it return Boolean and call it f_pwd_exp), e.g.

  function f_pwd_exp (par_app_user)
    return boolean
  is
    l_exp_date date;
  begin
    select exp_date
      into l_exp_date
      from your_users_table
      where username = par_app_user;
  
    return l_exp_date > trunc(sysdate);
  end;
  

• on the initial application page, create a Before Header branch that

  • redirects to "password change" page

  • has server-side condition set to "PL/SQL Function Body" and looks like

    return f_pwd_exp(:APP_USER); 
    

    which means that - if the password has expired, function will return True and branch will fire and redirect user to page which is used to change password

---

Now you have two ways to do it; use either of them, combination of those or produce your own. Good luck!