Monday, June 05, 2017

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.

1 comment:

Laurent Schneider said...

also you cannot change a 11.2.0.3 password with a 11.2.0.4 client