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

advertisements

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
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

  1. 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.

_____________________________________________________________________________________________________________________

A visitor from Maryland viewed 'How to create and remove soft link (symbolic) in S' 41 mins ago
A visitor from Florida viewed 'How to disable all constraints on a table or schem' 49 mins ago
A visitor from Lublin viewed 'Script to Find and Cleanup Orphaned Datapump Jobs' 1 hr 9 mins ago
A visitor from Henan viewed 'ORA-00059: maximum number of DB_FILES exceeded - F' 1 hr 33 mins ago
A visitor from Lublin viewed 'Script to Find and Cleanup Orphaned Datapump Jobs' 1 hr 34 mins ago

0 comments:

Post a Comment

Labels

Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2)
 

acehints.com Copyright 2011-25 All Rights Reserved | Site Map | Contact | Disclaimer