Password History – Reusing a password

By setting either one or both the profile limits PASSWORD_REUSE_MAX or PASSWORD_REUSE_TIME are set to anything other than UNLIMITED, Oracle somewhere keeps a history of passwords used by a user. This password history is stored in a table with the name user_history$ which is part of the SYS schema.

Whenever the users password is changed, either by the user of by a DBA, the case-insensitive password hash (3DES – 10g) is stored in this table. Even in 11g, where by default SHA1 password hashes (case-sensitive) are used, oracle stores the 3DES hash in the user_history$ table.

The sys.user_history$ table contains 3 columns; user#, password and password_date. Because the password column is of datatype VARCHAR2 with a maximum length of 30 characters, its not able to store the SHA1 hash value. When however you use an “alter user … identified by values” specifying only the SHA1 hash (effectively removing the 3DES hash), oracle stores the last 30 characters of the SHA1 hash value in the password field of the sys.user_history$ table instead.

Oracle only adds passwords for a user to the user_history$ table when password reusing is bounded by the profile enabled for the user. As soon as the “reuse” limits in a profile are set the UNLIMITED password changes don’t get tracked anymore, but any passwords that already exist in the history table won’t get deleted.

This entry was posted in Database and tagged , , , , , . Bookmark the permalink.

3 Responses to Password History – Reusing a password

  1. hossein golestsan says:

    hi , thanks so much for this post .
    i have a question in this regards . could you please tell me how i can restore old password a user .

    • marcel says:

      Hi,

      The SYS.USER_HISTORY$ table only contains the 3DES (pre 11g) password hashes for the users password history. This hash can be used
      in an ALTER USER xxxx IDENTIFIED BY VALUES’<10 passwordhash>‘ but by altering the user this way you will remove the 11g hash value,
      so disabling the usage of the O5LOGON (or higher) protocol.

      So in short: if you use Oracle 10gr2 or lower then you can use the password hashes from the USER_HISTORY$ table, if not that I wouldn’t
      use this hash because you will disable Oracle’s new authentication protocol this way.

      Regards,

      Marcel

  2. DBA says:

    Here I found a custom method to track the oracle password history
    http://www.acehints.com/2014/06/method-to-track-oracle-schema-user.html

Leave a Reply

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

Blue Captcha Image
Refresh

*