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 …

SELECT resource_name, limit
FROM dba_profiles
WHERE profile = 'DEFAULT'
AND resource_type = 'PASSWORD';

The results should be something like :

RESOURCE_NAME			            LIMIT
--------------------------------    ----------------------------------------
FAILED_LOGIN_ATTEMPTS                            10
PASSWORD_LIFE_TIME		                180
PASSWORD_REUSE_TIME		                UNLIMITED
PASSWORD_REUSE_MAX		                UNLIMITED
PASSWORD_VERIFY_FUNCTION                        NULL
PASSWORD_LOCK_TIME		                 1
PASSWORD_GRACE_TIME		                 7

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.

First off, we need to logon as the apex admin….

Point your browser at http://localhost:8080/apex

In the logon screen specify :

Workspace : Internal
Username : admin
Password : the admin password

NOTE – if you don’t know ( or have forgotten) your admin password, you can use the aforementioned apxchpwd.sql script to change it.
On the default install, the script can be found at :

/u01/app/oracle/product/11.2.0/xe/apex/apxchpwd.sql

If you’ve upgraded to a later version of APEX since installing the DB then you’ll need to use the version of this script that is with the code for the new APEX version.

In the Manage Instance dropdown menu, select …Manage Password Complexity

Password Complexity Management

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.

Set :
Require User Account Expiration and Locking to No
Account Password Lifetime (days) : 99999

Password Lifetime settings

If you want to turn off the password complexity, then go to the next section – Workspace Password Policy and amend the settings as shown below

Password Complexity Settings

Finally, if you want to lift these restrictions for the Admin User as well…

Go to the Service Administration Password Policy section of the page and select :
Use policy specified in Workspace Password Policy

Click Apply Changes.

Admin Password Restrictions

If you want to now change the Admin password free of the default restrictions, you need to do this from within the tool itself :

Select Manage Workspaces from the menu bar and choose Manage Developers and Users

Manage Developers and Users drop-down

This will give you a list of all the users setup in Apex.

Look for the ADMIN user for the INTERNAL workspace.
Click on the Pencil Button

Edit Admin user

Type in the new password in the password field
Set Require Change of Password on First Use to No
and click Apply Changes

Change Admin Password

Having once again failed to convince Deb that a cat-skin rug would look good in the living room, I’m off to explore the joys of nature, armed only with a pooper scooper and a supply of plastic bags.


Filed under: APEX, Oracle Tagged: APEX admin password, APEX password settings, apxchpwd.sql, dba_profiles, default profile, oracle xe, password_life_time