Note that instead of storing the hashed password values directly in the DBA_USERS table, they are from 11gR1 and onwards both stored in the table USER$, column PASSWORD for the 10G style hash value, and column SPARE4 for the 11G SHA-1 style hash value.
A NULL value indicates that the password has not been changed since the migration and the user still has the old case insensitive password.
In my query below, USER1 through USER4 have been migrated and will now take advantage of 11g password case-sensitivity, if enabled.
USER5 is still using 10G style case insensitive passwords.
USER6 is created after migration, and will also take advantage of the 11g password case-sensitivity, if available.
SELECT U.NAME "Name",
NVL(REGEXP_REPLACE(U.SPARE4,'^.+', 'Password changed since migration',1,0), 'Password unchanged since migration') "Action undertaken",
DU.PASSWORD_VERSIONS "Password Version"
FROM USER$ U LEFT OUTER JOIN DBA_USERS DU
ON U.NAME = DU.USERNAME
WHERE DU.USERNAME NOT IN (SELECT ROLE FROM DBA_ROLES)
ORDER BY NAME ASC;
| Name | Action undertaken | Password Version |
|---|---|---|
| USER1 | Password changed since migration | 10G 11G |
| USER2 | Password changed since migration | 10G 11G |
| USER3 | Password changed since migration | 10G 11G |
| USER4 | Password changed since migration | 10G 11G |
| USER5 | Password unchanged since migration | 10g |
| USER6 | Password changed since migration | 11G |
Users that are imported from an earlier release into an 11g database, will remain case-insensitive until the password is changed.
To generate "alter user identified by values" statements, use the following:
SELECT 'alter user ' || NAME || ' identified by values ' || '''' || SPARE4 ||';' || PASSWORD ||''';'
FROM USER$
WHERE NAME IN ('USER1','USER2');
Sources: Oracle Documentation
