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.
3 Responses to Password History – Reusing a password