:

Change Password in Oracle 11g – Password Column Empty

In Oracle 10g it was possible to select the encrypted password from the DBA_USERS view so that you could effectively hack into someones account and then change the password back without them knowing. The way to do this was as follows:

SELECT username, password
FROM DBA_USERS
WHERE username LIKE 'SECRET%';

Once you have the password for the SECRET user, you could change the user’s password to something you knew:

ALTER USER SECRET IDENTIFIED BY temp;

Once you had finished with the account it is easy to change it back to the original value, even though you don’t know what the password is:

ALTER USER SECRET IDENTIFIED BY VALUES '<hashed_password>';

Starting in Oracle 11g they have removed the value for the password column from the DBA_USERS view so the encrypted password is no longer available. In order to be able to achieve the same result now have to use another view and run a slightly different command:

SQL> SELECT password,spare4 FROM user$ WHERE name='SECRET';

PASSWORD          SPARE4
------------------ -------------------------------------------------------------
9DEC0D889E8E9A6B  S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE

SQL> ALTER USER SECRET IDENTIFIED BY VALUES
 2 'S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE;9DEC0D889E8E9A6B';

Here you can see that we’ve had to use the SPARE4 value followed by a semi-colon and then the password value.

Something that you should bear in mind when attempting this process is that if you have upgraded from a previous version of Oracle, like 10g, then this might not work for you. The SPARE4 column in USER$ is only populated if you have changed the user’s password in Oracle 11g. You can check if this is the case or not because the SPARE4 column will be NULL for the user that you are wanting to change it for.

You can also use the PASSWORD_VERSIONS column of the DBA_USERS view to see if it’s possible this approach will work. A value of just 10g means that it won’t work, whereas if it has 10g 11g then it will work.

Like it, share it...

Category: 11g


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *