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 ? |
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
