How to Keep Same Password for Oracle User when Expiry Time is Reached ?

Since Oracle 11g has introduced new Password policies like PASSWORD_LIFE_TIME=180 days, PASSWORD_VERIFY_FUNCTION= VERIFY_FUNCTION_11G which was not there in Oracle 10g.

This has made few of remote DBA's life easy and for few DBA's tougher. Let's take a scenario where you have a window based application running on 100 systems. For each system there is a separate file for database user name and password.

Now, you have reached  PASSWORD_LIFE_TIME value and getting below error.

$ sqlplus remote_dba/remote_dba
SQL*Plus: Release Production on Thu Apr 11 03:01:11 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 ORA-28001: the password has expired

Or you are getting below error.

SQL> connect remote_dba/remote_dba 
ORA-28002: the password will expire within 2 days

In both of the above case, remote DBA doesn't have any option other than changing password of users. Now think about situation 

1. when you need to change this at 100 places in window based application if you change the password.
2. you are not allowed to change password, but you are bound to change the password from Database side. Now this has become a challenging situation for a DBA.

Here, you are giving new password as an existing password to keep same password and hitting below error.
$ sqlplus remote_dba/remote_dba

ORA-28001: the password has expired

Changing password for remote_dba
Old password: remote_dba
New password: remote_dba
Retype new password: remote_dba
ORA-28003: password verification for the specified password failed
ORA-20002: Password length less than 10
Password unchanged
Enter user-name:  

To make remote DBA life easy, below is the solution for all these problems using which you
can keep same Password for Oracle User when Expiry Time is Reached.

1. Remove the verify function from the profile assigned to the user, To find profile assigned to user use below query:

SQL> SELECT USERNAME, PROFILE from dba_users where username like 'REMOTE_DBA%';

USERNAME                       PROFILE
------------------------------ ------------------------------
REMOTE_DBA                  DEFAULT

In this case use has default profile. Suppose, use has profile_test then use below command:

SQL> alter profile profile_test limit password_verify_function null;

If user has default profile then remove the verify function from DEFAULT profile

SQL> alter profile DEFAULT limit password_verify_function null;

Profile altered.

This will disables the password checking feature.

2. Now change the password by keeping the same value.

SQL> alter user remote_dba identified by remote_dba;

If you forgot user password, you can user below query.

SQL> select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c
from dba_users d, sys.user$ u
where d.username = upper('&&username')
and u.user# = d.user_id;  
Enter value for username: remote_dba
old   3: where d.username = upper('&&username')
new   3: where d.username = upper('remote_dba')

alter user "REMOTE_DBA" identified by values 'F894844C34402B67';

SQL> alter user "REMOTE_DBA" identified by values 'F894844C34402B67';

Please share, if this post helps you to solve your problem or you like this post.

1 comment: