Reset the Password for an Oracle Expired Login or Username to Old or Previous Value

advertisements

_____________________________________________________________________________________________________________________

Sometimes we don’t remember the passwords for some application user/logins which we hard coded in the application. These users/logins password get expired by the aging. Even though the account password is expired the encrypted password values will be stored in the database. There are two ways to find out the encrypted values and reset it to the old value.

SQL> SELECT USERNAME, ACCOUNT_STATUS FROM dba_users where username ='TEST';

USERNAME                       ACCOUNT_STATUS

------------------------------ -----------------------------

TEST                           EXPIRED

1. From sys.user$ table

SQL> select name, password from sys.user$ where name ='TEST';

NAME                           PASSWORD

------------------------------ ---------------------------

TEST                           92C5B0023D792E06

2. From DBA_USERS table

SQL>  select extract( xmltype (dbms_metadata.get_xml('USER', username)), '//USER_T/PASSWORD/text()').getStringVal() OldPass

  2  from dba_users where username ='TEST';

OLDPASS

----------------

92C5B0023D792E06

Now you can easily reset the password by following command and connect the application.

    SQL> alter user "TEST" identified by values ‘92C5B0023D792E06';

User altered.

SQL> SELECT USERNAME, ACCOUNT_STATUS FROM dba_users where username ='TEST';

USERNAME                       ACCOUNT_STATUS

------------------------------ ---------------------------

TEST                           OPEN

Please try out and let me know your comments.

_____________________________________________________________________________________________________________________

Website Stats