The computer has forgotten my password !

The quote in the title of this entry is from my all-time favourite support call, made to be by a very irate and hassled user.
Taking pity on their plight, I dealt with the call and refrained from pointing out the flaws in this assertion.

Several years later, I had cause to remember this incident, although the circumstances were very different.

The place I was working at had gone in for outsourcing…in a big way. Whilst we still had control of the application owner account, our DBAs were in Madrid; Unix admins in Switzerland and other development teams in Poland.
As the application owners, we were in the process of rolling out a major enhancement to the app.
All was going well, we’d rehearsed the rollout several times previously and most of the 300-odd scripts had been completed without a hitch.
We then came to a small change on one of the other applications on the machine, required to account for the changes we were making to the main application.
At this point, several things became apparent :-
the password we had for the user we needed to run these scripts as wasn’t correct
the person or persons who had this information were definitely unavailable ( it was Saturday afternoon)
changing the password on the account could potentially cause issues with batch jobs connecting as this user ( although we had no idea what these could be as we didn’t know the application in question).
Faced with the prospect of having to rollback the release and come back the following Saturday ( F.A. Cup Final day, no less), we opted to “cheat”.

Utilizing that useful little dodge known as “identified by values” we were able to change the relevant password, connect as the user and run the scripts and then change the password back to it’s original value, without ever having to know what that value was.

For an illustration of this technique, I used an account which had CREATE USER, ALTER USER, and SELECT ANY DICTIONARY privileges.

Let’s start by creating a user :-

SQL> grant connect to mystery identified by secret;
Grant succeeded.

Now, we can look at DBA_USERS to find a hash value for the password :-

SQL> select password
2 from dba_users
3 where username = 'MYSTERY';

To change the password for this user, it’s simply a matter of :-

SQL> alter user mystery identified by iknowurpwd;
User altered.

Once you’ve done what you need to as this user and need to reset the password to it’s original value :-

SQL> alter user mystery identified by values '33CF630B57549B54';
User altered.

And hey presto, off to the pub ( and next weekend is still free to watch the footie !)


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.