The Oracle (tm) Users' Co-Operative FAQ

How do I make people change their password every 90 days ?


Author's name: Connor McDonald

Author's Email: connor_mcdonald@yahoo.com

Date written: August 22, 2001

Oracle version(s): 8.0+

How do I make people change their password every 90 days ?

Back to index of questions


Before Oracle 8.0, unfortunately you are left to your own devices when it comes to password management. Its typical for sites to have an additional table in the database which contains the last time someone changed their password, and have explicit coding within their applications to cater for password expiry.

In Oracle 8.0 and above, this can be done with profiles, for example:

SQL> CREATE PROFILE myprofile LIMIT
  2     FAILED_LOGIN_ATTEMPTS 5
  3     PASSWORD_LIFE_TIME 60
  4     PASSWORD_REUSE_TIME 60
  5     PASSWORD_REUSE_MAX UNLIMITED
  6     PASSWORD_LOCK_TIME 1/24
  7     PASSWORD_GRACE_TIME 10;

Profile created.

where the fields are described as below:

FAILED_LOGIN_ATTEMPTS Specify the number of failed attempts to log in to the user account before the account is locked.
PASSWORD_LIFE_TIME Specify the number of days the same password can be used for authentication. The password expires if it is not changed within this period, and further connections are rejected.
PASSWORD_REUSE_TIME Specify the number of days before which a password cannot be reused. If you set PASSWORD_REUSE_TIME to an integer value, then you must set PASSWORD_REUSE_MAX to UNLIMITED.
PASSWORD_REUSE_MAX Specify the number of password changes required before the current password can be reused. If you set PASSWORD_REUSE_MAX to an integer value, then you must set PASSWORD_REUSE_TIME to UNLIMITED.
PASSWORD_LOCK_TIME Specify the number of days an account will be locked after the specified number of consecutive failed login attempts.
PASSWORD_GRACE_TIME Specify the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.

Once the profile is created, you assign it to users

SQL> alter user SCOTT profile myprofile;

User altered.

Most Oracle tools will now trap password expiry et al but many other tools (and your own applications) will not. You will need to trap the following errors:

ORA-28000 the account is locked
ORA-28001 the password has expired
ORA-28002 the password will expire within string days
ORA-28003 password verification for the specified password failed

and take the appropriate actions


Further reading: N/A


Back to top

Back to index of questions