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';
PASSWORD
------------------------------
33CF630B57549B54

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 !)