Oracle Password Hacks

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: , , , ,

Oracle Web Resources

Below are some of my favorite Oracle related web resources. Most have information on databases and Oracle Application Server. There are, of course, countless sites out there on the topic. These are just a few of the ones I prefer.

tahiti.oracle.com – This quick URL gives you direct access to the current Oracle documentation (with a free OTN account.) It’s a good quick back-door way into the oracle docs.

asktom.oracle.com – I don’t know the whole story on this guy, but he seems to work for (or closely with) Oracle answering user submitted questions. There are thousands of questions and answers on all things Oracle.

otn.oracle.com – Oracle’s Technology Network is the official source for tech news from Oracle. It is to the techies what www.oracle.com is to the business folks.

orafaq.com – More Oracle question and answer listings. Also a very useful and comprehensive glossary.

rittman.net – Mark Rittman’s Oracle Weblog is a fantastic resource with a focus on data warehousing and business intelligence. One of the best sites by an individual.

Technorati tags: , , , ,