Password Version in oracle

• Password version is a new column in DBA_USERS starting 11g.
• The PASSWORD_VERSIONS column shows the list of password versions that exist for the account.
• 10G refers to the earlier case-insensitive Oracle password DES-based version
• 11G refers to the SHA-1 version, and 12C refers to the SHA-2-based SHA-512 version.

It is defined as below in 11G

decode(length(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL)

We can find the password version using the below sql

SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS
USERNAME                       PASSWORD_VERSIONS
------------------------------ -----------------
TEST1                          10G 11G 12C 
TEST2                          10G 11G
TEST3                          10G 11G
TEST4                          11G
TEST5                          10G

• The value of Password versions while resetting the password is determined by the parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER in the sqlnet.ora on the database side
• If SQLNET.ALLOWED_LOGON_VERSION_SERVER is 10 or 11, Password version generated in 10G 11G 12C. This mode is called permissive mode
• If SQLNET.ALLOWED_LOGON_VERSION_SERVER is 12, Password version was generated in 11G 12C. This is called the exclusive mode
• If SQLNET.ALLOWED_LOGON_VERSION_SERVER is 12a, Password version generated in 12C. This is the highest security mode

If it is required to use Case insensitive password, we need to have SQLNET.ALLOWED_LOGON_VERSION_SERVER set to 10 or lower and Parameter sec_case_sensitive_logon should be false

How to find users having 10G version only

SELECT USERNAME FROM DBA_USERS 
WHERE ( PASSWORD_VERSIONS = '10G '
OR PASSWORD_VERSIONS = '10G HTTP ')
AND USERNAME <> 'ANONYMOUS';

You can change the password with the correct SQLNET.ALLOWED_LOGON_VERSION_SERVER settings and password version will be modified

How to find users having 10G version also

SELECT USERNAME FROM DBA_USERS
WHERE PASSWORD_VERSIONS LIKE '%10G%' 
AND USERNAME <> 'ANONYMOUS';

Again if we have to remove 10G version at all, we need to set SQLNET.ALLOWED_LOGON_VERSION_SERVER to 12 or 12a and change the password

Related Articles
ORA-01017: invalid username/password; logon denied : Learn the possible resolution of ORA-01017 invalid username/password; logon denied. How to resolve it quickly without much efforts
Oracle Database Interactive Quick Reference Guide | 11g & 12c : Useful Reference guide for 11g/12c for DBA,developers and administrator
Oracle Database Security Best Practices : This page contains awesome Oracle Database Security Tips to help DBA,developers and administrator
oracle dba interview questions and answers : You should not miss these 60 awesome oracle apps dba interview questions.Must read to succeed in interviews and jobs.Download also available
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm