An interesting but undocumented Oracle feature lets you temporarily log in as another user. Logging in as another user is helpful when troubleshooting privilege-related problems. To use this technique you do not even need to know the other user's password.
For example, say that you are the
SYSTEM user, and that you need to log in as the
The first thing to do is to retrieve the
COIN_ADMIN user's password. This example shows how to do that:
The value that you get for the password when you query
is the hexadecimal
representation of the encrypted password. You cannot use it to log in. Just save this value so that later you can restore it. The next task is to temporarily change the
SQL> ALTER USER coin_admin IDENTIFIED BY temp;
Now you can log in as
SQL> CONNECT coin_admin/temp@coin
Once you are logged in as the
user, you can change the password back to its original value by issuing this command:
hexadecimal: A base-16 numbering system often used to display an exact representation of data in memory. Hexadecimal numbering goes from 1-9, and then from A-F. The hexadecimal A is equivilant to the decimal value 10. After F, the next hexadecimal value is 10, which is equivilant to the decimal value 16. Hexadecimal digits each represent exactly four binary bits, making hexadecimal a convenient alternative to writing long strings of 1s and 0s.
Notice that the hexadecimal string that you saved earlier was used to reset the password back to its original state. The keyword
VALUES is an undocumented feature that allows you to store an already encrypted password directly.