Within Oracle databases passwords get hashed. When, for example, you create a new account or change the password of an account, the specified clear-text password gets hashed. This hash value is stored in the database with the account. Once hashed it is not possible to get the original clear-text password from the hash value (one-way hash algorithms) again. The case-insentive passwords (still generated in Oracle 11g) gets hashed by Oracle using the 3DES (Triple-DES) encryption algorithm, while the case-sensitive passwords (as of 11g Release 1) get hashed using the SHA1 hashing algorithm. This means that for each account there are two different password hashes available as of Oracle 11g (in pre 11g databases there is only one password hash available per account).
Note: the case-insensitive hash (pre 11g and 11g) is actually not a hash but an encrypted username/password combination, which becomes a one-way hash by encrypting the result of the first run (after removing some bytes) again.
How does a case-insensitive password hash look like (pre 11g and 11g)?
How does a case-sensitive password hash look like (as of 11g Release 1)?
To make life a little easier in this article, when I talk about a 10g hash value this means the hash value for case-insensitive passwords. When I talk about an 11g hash value this means the hash value for case-sensitive passwords that appeared as of 11gR1.
The procedure used for generating a 10g hash
- Convert username to uppercase version of username (username sys becomes SYS)
- Convert password to uppercase version of password (password test becomes TEST)
- Capatilized username and password gets concatinated (username SYS with password TEST becomes SYSTEST)
- Encrypt (using 3DES algorithm) concatinated value with a (permanent – always the same) secret key
- Encrypt (using 3DES algorithm) concatinated value with a secret key (this key are the last 8 bytes of the first encryption)
- The actual password hash value will be the last 8 bytes of the second encryption round, stored in a readable hex representation of these 8 bytes – so 16 characters)
The procedure for generating a 11g hash
- An 10 bytes SALT gets generated by Oracle (looks random)
- Password (case-sensitive) and SALT (10 bytes) value become concatinated
- A SHA1 hash gets generated for the concatinated value
- 11g password hash becomes: “S:” plus <SHA1 hash – readable hex representation> plus <SALT – readable hex representation, 20 characters>
Copy password hashes
Sometimes it can be convenient to copy passwords between databases or to temporarily change a password, without actually knowing the clear-text password. You can (as you probably already know) use the undocumented CREATE USER / ALTER USER clause IDENTIFIED BY VALUES <hash value> to specify a hash value instead of a clear-text password. Next to the fact that as of Oracle database 11g Release 1 passwords are case-sensitive, they are also not bounded to a username anymore, as with the pre 11g case-insensitive password hashes is the case. If you take a look again at the described procedure for generating 11g password hashes, you can see that only the password (concatinated with a SALT) is hashed and the username is not. This means you can use an 11g password hash of one user and apply this password hash to another user, making the passwords for both users the same! With 10g password hashes you can only apply a copied password hash to a user with the same username.
To copy a password hash (or temporarily change it) you first have to query for it.
Queries (version dependent)
10g Release 2 (and previous versions) get 10g password hashes:
SELECT username, password FROM dba_users WHERE username='<username>';
11g Release 1 (and later versions) get both 10g and 11g password hashes:
SELECT name, password, spare4 FROM sys.user$ WHERE name='<username>';
Changing a password hash (version dependent)
10g Release 2 (and previous versions)
ALTER USER username IDENTIFIED BY VALUES '<10g password hash>';
11g Release 1 (and later versions)
ALTER USER username IDENTIFIED BY VALUES '<11g password hash>';
Note that by using the above IDENTIFIED BY VALUES clause in 11g, setting either the 10g or 11g password hash, will make the other hash value disappear. So if in 11g you specify a 10g password hash for a user, Oracle will remove the 11g hash value and vice versa.
It is possible to specify both the 10g and 11g password hash value in the same IDENTIFIED BY VALUES clause to be able to set both hashes
and thus without losing any one of them.
ALTER USER username IDENTIFIED BY VALUES '<11g password hash>;<10g password hash>';
Case-sensitive passwords as of 11g Release 1
Until Oracle database 11g Release 1, passwords within an oracle database where case-insensitive. As of 11g Release 1 the database uses case-sensitive passwords as a standard. It is possible however to disable this new functionality by changing an initialization parameter.
The initialization parameter to enable/disable the use of case-sensitive passwords is sec_case_sensitive_logon
The SQL statement for changing (in this example disable – not recommended) this initialization parameter:
ALTER SYSTEM SET sec_case_sensitive_logon=false;