It’s probably Monday morning. The caffeine from your first cup of coffee has not quite worked it’s way into your system.
The cold sweat running down the back of your neck provides an unpleasant contrast to the warm blast of panicked users as they call up to inform you that the Application is down.
APEX, which has been behaving impeccibly all this time, has suddenly decided to respond to all requests with :
503 – Service Unavailable.
The database is up. The APEX Listener is up. But something else is up. APEX just doesn’t want to play.
Better still, the person who set up the APEX in the first place has long-departed the company. You have no idea how the Apex Listener was configured.
Out of sympathy with your current predicament, what follows is :
- How to confirm that this problem is related to the APEX_PUBLIC_USER (the most likely cause)
- A quick and fairly dirty way of getting things back up and running again
- How to stop this happening again
Note: These steps were tested Oracle Developer Day VM with a 12c database running on Oracle Linux 6.5. In this environment, APEX is configured to run with the APEX Listener.
Confirming the APEX User name
First of all, we want to make sure that APEX is connecting to the database as APEX_PUBLIC_USER. To do this, we need to check the default.xml file.
Assuming you’re on a Linux box :
cd /u01/oracle/apexListener/apex cat default.xml
If you don’t see an entry for db.username then APEX_PUBLIC_USER is the one that’s being used.
If there is an entry for db.username then that is the name of the database user you need to check in the following steps.
For now, I’ll assume that it’s set to the default.
Incidentally, there will also be an entry for db.password. This will almost certainly be encrypted so is unlikely to be of use to you here.
Confirming the status of the APEX_PUBLIC_USER
The most likely reason for your current troubles is that the APEX_PUBLIC_USER’s database password has expired.
To verify this – and get the information we’ll need to fix it, connect to the database and run the query :
select account_status, profile from dba_users where username = 'APEX_PUBLIC_USER' /
If the account_status is EXPIRED, then the issue you are facing is that the APEX_PUBLIC_USER is expired and therefore APEX can’t connect to the database.
The other item of interest here is the PROFILE assigned to the user.
We need to check this to make sure that there is no PASSWORD_VERIFY_FUNCTION assigned to the profile. If there is then you need to supply the existing password in order to change it, which is a bit of a problem if you don’t know what it is.
Whilst we’re at it, we need to check whether there is any restriction in place as to the length of time or number of password changes that must take place before a password can be reused.
In my case, APEX_PUBLIC_USER has been assigned the DEFAULT profile.
select resource_name, limit from dba_profiles where profile = 'DEFAULT' and resource_name in ( 'PASSWORD_REUSE_TIME', 'PASSWORD_REUSE_MAX', 'PASSWORD_VERIFY_FUNCTION' ) /
When I ran this, I was lucky and got :
RESOURCE_NAME LIMIT ------------------------------ -------------------- PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED PASSWORD_VERIFY_FUNCTION NULL
So, there are no restrictions on password reuse for this profile. Neither is there any verify function.
If your APEX_PUBLIC_USER is attached to a profile that has these restrictions, then you’ll want to change this before re-setting the password.
As we’re going to have to assign this user to another profile anyway, we may as well get it out of the way now.
The New Profile for the APEX_PUBLIC_USER
Oracle’s advice for the APEX_PUBLIC_USER is to set the PASSWORD_LIFE_TIME to UNLIMITED.
Whilst it’s only these four parameters we need to set in the profile for us to get out of our current predicament, it’s worth also including a limitation on the maxiumum number of failed login attempts, if only to provide some limited protection against brute-forcing.
In fact, I’ve just decided to use the settings from the DEFAULT profile for the attributes that I don’t need to change :
create profile apex_public limit failed_login_attempts 10 password_life_time unlimited password_reuse_time unlimited password_reuse_max unlimited password_lock_time 1 composite_limit unlimited sessions_per_user unlimited cpu_per_session unlimited cpu_per_call unlimited logical_reads_per_session unlimited logical_reads_per_call unlimited idle_time unlimited connect_time unlimited private_sga unlimited /
As we don’t specify a PASSWORD_VERIFY_FUNCTION, none is assigned to the new profile.
NOTE – it’s best to check the settings in your own default profile as they may well differ from those listed here.
Next, we assign this profile to APEX_PUBLIC_USER…
alter user apex_public_user profile apex_public /
The next step is to reset the APEX_PUBLIC_USER password, which is the only way to unexpire the user.
No password, no problem
Remember, in this scenario, we don’t know the current password for APEX_PUBLIC_USER. We don’t want to reset the password to just anything because we’re not sure how to set the password in the DAD used by the Apex Listener.
First of all, we need to get the password hash for the current password. To do this :
select password from sys.user$ where name = 'APEX_PUBLIC_USER' /
You’ll get back a hex string – let’s say something like ‘DF37145AF23CCA4’.
Next step is to re-set the APEX_PUBLIC_USER password :
alter user apex_public_user identified by sometemporarypassword /
We now immediately set it back to it’s original value using IDENTIFIED BY VALUES :
alter user apex_public_user identified by values 'DF37145AF23CCA4' /
At this point, APEX should be back up and running.
Once the dust settles…
Whilst your APEX installation may now be back up and running, you now have a database user for which the password never changes.
Although the APEX_PUBLIC_USER has only limited system and table privilges, it also has access to any database objects that are available to PUBLIC.
Whilst this is in-line with Oracle’s currently documented recommendations, you may consider that this is a situation that you want to address from a security perspective.
If there is a sensible way of changing the APEX_PUBLIC_USER password without breaking anything, then you may consider it preferable to simply setup some kind of reminder mechanism so that you know when the password is due to expire and can change it ahead of time.
You would then be able to set the password to expire as normal.
If you’re wondering why I’m being a bit vague here, it’s simply because I don’t currently know of a sensible way of doing this.
If you do, it would be really helpful if you could let me know 🙂
Thanks a million. This article was very useful
LikeLike
Thank you very much. You save my holiday 🙂
LikeLike
This article was an absolutely perfect solution for our error. Thank you very much! It’s so rare to find an oracle blog post that actually fixes your problem. You are a star!
LikeLike
Thanks, very useful
LikeLike
impressive!
LikeLike
Thanks so much!!!
LikeLike
“… we want to make sure APEX is connecting to the database as APEX_PUBLIC_USER…” Don’t you mean that “… we want to make sure users (via ORDS) are connecting to the database as APEX_PUBLIC_USER…”
LikeLike
apex_ton,
thanks for the observation. On the environment that I was working on at the time I wrote this, ORDs was not being used, hence why it’s not mentioned.
Mike
LikeLike
No problem. Btw – I wasn’t trying to be “picky’. I just wanted to make sure I understood it correctly — that is, if not defined otherwise, ORDS will connect to the database via the user APEX_PUBLIC_USER. Am I correct in saying that or am I mistaken?
Thanks for a great blog!
LikeLike
apex_ton,
I’m always pleased to get comments that ask/about challenge what I’ve written.
If I’ve got something wrong, I ( and probably anyone else reading it) would like to know.
In answer to your question – I’m not sure since I haven’t had a play with ORDS to date.
The comments in the apex_rest_config.sql script ( for APEX 5.1) seem to suggest that the default is to connect as APEX_LISTENER.
It’s probably worth taking a look at the Oracle Docs for APEX 5.1 here.
HTH
Mike
LikeLike
Mike Smithers, you are a godsend!! The DBA reloaded our APEX stuff, and I couldn’t figure out how in God’s creation I could log in, and THIS WORKED. I _had_ a different instance that had the correct password (and got that via hash), and once I reset it in the “bad” instance, I could log in again!!!! THANK YOU!!!
LikeLike
Thanks for the feedback. It’s a couple of years since I wrote this so it’s good to know that it still works 🙂
LikeLike
Great Article, it helped me. thanks!
LikeLike
Thank you SO MUCH!!!
LikeLike
Genial , fantastico.
Cool
LikeLike