As a DBA it is often useful to change a user’s database password for testing, but what if you don’t know the user’s original password so you can change it back when you’re done?

There is no easy way to decipher the encrypted password, but you can view it. What you can do is copy the user’s encrypted password, change the password to a known value for testing with the normal alter user command, then replace the original password with a special alter user command.

In my case I am actually using this to synchronize passwords between two databases to assure database links work properly. This will only work if the usernames are identical.

Here is an example of how I am using this technique to synchronize passwords:

First we want to set up a user with a known password

In the original database:

SYS:TEST> alter user jemmons identified by copyme;

User altered.

SYS:TEST> select username, password from dba_users where username='JEMMONS';

USERNAME PASSWORD
------------------------------ ------------------------------
JEMMONS EAEC44107194EBC6

Now we connect up to the database we want to clone the password to. Note the first attempt to connect as jemmons fails as that is not the assigned password.

In the database you want to copy the password to:

nolog> conn jemmons/copyme;
ERROR:
ORA-01017: invalid username/password; logon denied

nolog> conn / as sysdba
Connected.
nolog> alter user jemmons identified by values 'EAEC44107194EBC6';

User altered.

Elapsed: 00:00:00.01
nolog> conn jemmons/copyme;
Connected.

Now we see that this only works if the usernames are identical. This is because the hashed password is based on a combination of the password provided and the username.


nolog> conn / as sysdba
Connected.
nolog> alter user ken identified by values 'EAEC44107194EBC6';

User altered.

nolog> conn ken/copyme;
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

If you want to know more on this, check out article from red-database-security.com

Note: This was done on a 9i database. This may or may not work across versions.

Technorati tags: , , , ,