Password Expire

If a user forgets his password, he may ask you to reset it for him. You will then know his new password, which you may see as a security issue. By including the password expire clause in the alter user command, you can force the user to change his password the next time he logs in. After this, you will no longer know his password. The examples which follow show a DBA changing a password in red and a user logging in afterwards in green.

 

The first example shows a DBA using an Oracle 11 version of SQL*Plus to change a password in an Oracle 11 database:

TEST11 > sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:03:51 2015

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> alter user a identified by b

  2  password expire

  3  /

 

User altered.

 

SQL>

The user then logs in with the same Oracle 11 version of SQL*Plus and is prompted to change his password. After doing this, he reconnects to the database. This is not necessary, it is just to show that the password change has taken effect:

TEST11 > sqlplus a/b

 

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:11:51 2015

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

ERROR:

ORA-28001: the password has expired

 

Changing password for a

New password:

Retype new password:

Password changed

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> conn a/c

Connected.

SQL>

The DBA then resets and expires the password again using the same Oracle 11 version of SQL*Plus:

TEST11 > sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:56:10 2015

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> alter user a identified by b

  2  password expire

  3  /

 

User altered.

 

SQL>

The user logs in using an Oracle 10 version of SQL*Plus this time. He is prompted to change his password but is unable to do so:

TEST10 > sqlplus a/b@test11

 

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Aug 26 11:59:46 2015

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

ERROR:

ORA-28001: the password has expired

 

Changing password for a

New password:

Retype new password:

ERROR:

ORA-01017: invalid username/password; logon denied

 

Password unchanged

Enter user-name: 

So, if you want to expire a password in an Oracle 11 database, you need to check that the person who will be logging in to that user afterwards is using an Oracle 11 version of SQL*Plus, not an Oracle 10 one.