Logging In As a Different User – When you don’t know the Password

You may need to sometimes log in as another DBA to perform certain actions. However, even the Oracle DBA doesn’t have access to users’ passwords, which are stored in an encrypted form. You could use the ALTER USER statement to change the user’s password, but you might not want to inconvenience the user by changing the password for good.

In a case like this, you can change the password of a user temporarily and use the new password to get in as that user. Before you change a user’s password, get the original encrypted password, which you can use to reset the user’s password back after you’re done. Here’s an example:

SQL> SELECT 'alter user tester identified by values 'password';'
2 FROM dba_users
3* WHERE username='TESTER';

'ALTERUSERTESTERIDENTIFIED
---------------------------------------------------------
alter user tester identified by values 1825ACAA229030F1;

SQL>
Now change the password of user tester so you can log in as that user:

SQL> ALTER USER tester IDENTIFIED BY newpassword;
When you’re done using the tester user account, use the ALTER USER statement again to change user tester’s password back to its original value. Make sure you enclose the encrypted password in single quotes.

SQL> ALTER USER tester IDENTIFIED BY VALUES '1825ACAA229030F1';
User altered.