ORA-01017: invalid username/password; logon denied

Today I witnessed this weird issue:

From one host (hostA) I can connect to database :

sqlplus PD1_MDS/xxxxx@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.9.27)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=BIAPPPD)))"


But using the same connect string and username/pwd , I cannot connect from another machine (hostB)

The error was : ORA-01017: invalid username/password; logon denied


However when I use a different username(PD1_MDDDD) and password from host B, I can still conect

So , what was it , that was blocking a particular user(P1_MDS), from a particular machine (hostB) to connect to database.

Obviously, the password, is correct, since, I am able to connect using the same password, from a different machine.

Upon researching, I find the following:

SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM dba_users where username like ‘%PD1%’;

USERNAME                       PASSWORD

------------------------------ --------

PD1_MDS                 11G

PD1_MDDDD           10G 11G

From this, it seemed to be a client version issue. Meaning PD1_MDDDD  user will accept both 10g and 11g client connections, but PD1_MDS will only accept 11G and higher connections and cannot authenticate, 10G or lower client connections. Hence we get an error message ORA-01017: invalid username/password; logon denied

I had to reset the password to the same value again, to make it accept connections from both 10g,11g clients

alter user PD1_MDS identified by xxxxx;

When I rerun the query to check password versions, I see the following

SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM dba_users where username like ‘%PD1%’;

USERNAME                       PASSWORD

------------------------------ --------

PD1_MDDDD                 10G 11G

PD1_MDS                        10G 11G

Please note that in this case, i knew the pwd, but if you do not know the password, and try to set it to the old password, using byvalue clause, it will still be a 11G version password. So it will not work.

After resetting the above password again to the same value , it accepted , both 10G and 11G client connections.

Hope this helps some one.