• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » Password Version in oracle

Password Version in oracle

March 5, 2023 by techgoeasy Leave a Comment

  • 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

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Password Version in oracle
  • How to login as user without changing the password in Oracle database(alter user identified by values)
  • How to check encrypted tablespace in the Database
  • How To Export -Import TDE Master Encryption Key
  • How to Configure Auto Login TDE Wallet

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us