Scenarios which require password Change:
1. Its recommended to change passwords periodically for security concerns.
2. User has forgotten/lost the password and unable to login to the system.
3. User wants to change password for any operational Purpose
Oracle database allows user to change database user’s password using the below queries
+Logging in as the user
SQL> ALTER USER ME IDENTIFIED BY <NEW_PASSWORD>;
+Logging in as SYS/SYSTEM User
SQL> ALTER USER <USERNAME> IDENTIFIED BY <PASSWORD>;
There can be some cases when the DBA need to change password and revert back to older value after the dba action is completed.
Below test case helps in understanding how to change db user password and restore to older password value in Oracle 10g.
1. Change oracle user password using alter command
SQL> conn system/manager
Connected.
SQL> alter user TESTUSER identified by password1
User altered.
2. Test the password is working
SQL> conn TESTUSER/password1;
Connected.
3.Retreive the encrypted password for user
SQL> conn system/manager;
Connected.
SQL> alter user TESTUSER identified by values ‘6057000499B128C3’;
User altered.
select username, password from dba_users where username = ‘TESTUSER’;
USERNAME PASSWORD
—————————— ——————————
TESTUSER DB78866145D4E1C3
4. Change the value of TESTUSER password to a new value
SQL> conn system/manager
Connected.
SQL>
SQL> alter user TESTUSER identified by password2;
User altered.
5. Verify the new password is working
SQL> conn TESTUSER/password2;
Connected.
6. Restore the older password using the encrypted password which we retreived from dba_users;
SQL> conn system/manager@dev;
Connected.
SQL> alter user TESTUSER identified by values ‘DB78866145D4E1C3’;
User altered.
7. Verify that you are able to connect using old password.
SQL> conn TESTUSER/password2@dev;
ORA-1017 invalid username/password ;logon denied
SQL> conn TESTUSER/password1@dev;
Connected.