Turning off Password expiration on Oracle XE and Apex

It is a strange time in the UK.
I’m not referring to England being 2-0 up after 2 tests in the Ashes (something that happens about as often as a Briton winnng the Men’s Singles at Wimbledon), nor the fact that a Briton has won the Tour de France for the second year running.
Stranger even than that is the bright yellow ball in the sky which has replaced the traditional warm rain of the British Summer.

This phenomenon has had a strange effect on the cat. Her animal instincts obviously alerted by the unfamiliar change in the climate, she currently spends almost all of her time out in the garden.
Unfortunately, she seems to have decided to treat said garden as something of a litter tray. This will necessitate something of a mine-sweeping exercise before I next mow the lawn.

It’s frustrating when you’re anxious to start something but then hit an unpleasant roadblock.
For example, you may have decided to have a play with that Oracle XE/Apex installation on your laptop that you haven’t used for a little while. However, when you come to connect, you realise that you can’t remember the password.

Health Warning – Before I go any further, I should point out that doing this is not something I’d recommend on anything other than a system that’s being used simply as a playground for you to experiment and which contains no sensitive data.

Right, now that’s out of the way, let’s start with…

The database

All database users are assigned to something called a PROFILE.
The profile controls two aspects of the users database access – the system resources available to the user and the rules around the user’s password.
There are two created by default when you install XE, however all users are assigned to the default profile which is called, appropriately enough, DEFAULT.
You can find details of any profile in the DBA_PROFILES view.
Let’s have a look at the settings for the password properties in the DEFAULT profile …

In order to prevent the pain of password resets, we just need to change the PASSWORD_LIFE_TIME…

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED
/

That’s all there is to it. You no longer need to change any database account passwords…unless you want to.

APEX

With APEX, things are a little different.
The admin user’s password can be set via the apxchpwd.sql. However, changes made to the password rules in APEX itself do not appear to be picked up in this script.

Go to the Workspace Login Control Section.
Here, we can do – more-or-less the equivalent of what we’ve just done on the database – i.e. set the password not to expire.
I say more-or-less, because the setting we need to change will only accept a positive non-zero integer value up to 99999.