Umm, I forgot my password, Part 2 ....oracle

In a thread on OTN forums, a poster asked how he could recover a user's password. Naturally he was told that it can't be done because the password itself is not stored, only a hash based on the username and password combination.

After some interesting discussion of password hashing, brute force and rainbow table attacks and the like, a poster makes the following rather novel suggestion:

if you apply the password verify function, yes it is possible to get the password of a user.

Etape 1: edit the utlpwdmg.sql script and add the line which is in bold (insert into...)

-- Check if the password is same as the username

IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;

insert into mytable values ('username','password');

-- Check for the minimum length of the password

Etape 2: run this script as sys

Etape 3: grant the profile to user whom u want to get the password.

u will be able to get the new password by consulting the table mytable (u must create this table)

This had me puzzled at first, and I had to check what $ORACLE_HOME/rdbms/admin/utlpwdmg.sql did. In fact it creates a default password verification function called "verify_function" ("verify_password" might have made a better name, but that's obfuscation for you), and then assigns it to the default profile using ALTER PROFILE DEFAULT ... PASSWORD_VERIFY_FUNCTION verify_function;

This means that any attempt to change the password for a user with the default profile (see ALTER USER examples in the documentation), will automatically execute verify_function(username, password, old_password). The idea is to apply some rules to prevent easily guessed passwords such as your username, but Mouhamadou's ingenious addition is his extra line,

insert into mytable values (username,password);

Now any attempt to change the password for a user with the default profile that successfully passes this extra security step will result in the new password being logged in mytable in clear text.

As we like to say on Oracle WTF, problem solved.

Many thanks to Andrew P. Clarke for submitting this.