Oracle APEX 20.1 - Require Change of Password on First Use (even post PSE (30990551) )

Require Change of Password on First Use issue

404 Not Found - The procedure named null could not be accessed...

In case you are using Application Express Accounts in your applications for authentication this post might be of interest. Especially if it is used in production.

I was not able to find anything on this issue on forum so decided to share this as potential workaround.

How process would normally work we would log into Application Builder as Admins and create a user in this case let's call it TEST. 

Since we want it to reset its password on first login we will leave Require Change of Password on First Use as default - On.

What happens next is user tries to login and gets familiar screen to us all.

User changes its password and our job is done. Application comes up life continues.........

Well there would not be for this post then would it?

What you may encounter in APEX 20.1 is that you instead get this error

The procedure named null could not be accessed, it may not be declared or the user executing this request may not have been granted execute privilege on the procedure. Check the spelling of the procedure and check that the execute privilege has been granted to the caller

Where is this now coming from? To be honest I was quit caught up with this as I wasn't expecting it. I would call this a bug.

If this setting is turned off things work as expected. Users can normally login but with setting set to ON error comes back.

Digging deeper we did find some Tomcat log errors:

which were not that helpful.

To be fair I was able to reproduce this on 3 out of 5 APEX 20 instances available to me which makes it even more interesting.

Looking at apex.oracle.com seems like it is completely ignoring this flag and it never gives us an option to reset user passwords.

I would bet this functionality worked there before but I can't point to when exactly this ignore flag started to happen to narrow it down to when problems showed up. So in a way we can say it does not happen there but you also can't update your password on first login.

Is there a workaround? Yes but it still leaves a bad taste.

At internal and workspace level there is a setting Account Expiration and Locking which we can disable but this sorts only one part of the problem.

On top of this we need to run additional script - code you can find here.

This is to cater for existing users as newly created users will work perfectly fine even if we create them with Require Change of Password on First Use set to YES.

Great. Small note here setting Account Expiration and Locking to Disable will prevent users of being able to reset their passwords as reset password screen will not be shown anymore. Until this problem is fixed in APEX 20.1 how can we reset user passwords?

One way would be to let user login into the Builder itself which would lead them to the screen where they can reset their passwords. We probably do not want to do this in production environments.

Second way perhaps is to maintain and reset user passwords manually.

There might be a better way of handling the situation so if you happened to have similar issue and solution for it I would love to hear back. Please leave a comment or reach out to me directly.

In conclusion except for hoping these tips will be of help I will encourage you to reconsider your current process and look for alternative credential management solutions.

Please do not use APEX users in production at all. There are plenty of more sophisticated/secure built in authentications available to us in APEX like LDAP, AD etc...... even a custom table would be an option too that is more suitable for production systems. ;)

 

With this said leaving it in your good hands now.

-----------------------------

P.S. Update on 16/07/2020 I love how these things twist and turn on you. :D

I just found out from @dani3lSun that this bug was fixed with patch in the 20.1 PSE (30990551)

which even more contributes to my story that some servers had this error where some did not.

Then I went in and I checked if this patch was already installed on the server and it was but issue is still there. How!!!? I had a feeling we had it installed already but it does not hurt to double check.

Looks like workaround might still hold its in place. Thanks Daniel 

P.S. please check comments bellow. With latest PSE applied - ORA error goes away but we still do not get reset password screen when this flag is on.

Happy APEXing,
Lino