- Password version in Oracle 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 the 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
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