Toad World® Forums

Cannot login to 10g database when password is expired

#1

Using toad for oracle 13.1 (2018 R2)

It will fail:

ORA-0107: invalid username/password; logon denied

If I remove expired flag, then the same approach works fine.

Regards
Raul

#2

For me, this is working OK. will you turn on spool sql (main menu -> database -> spool SQL -> Spool to screen), then login to the same database but without the expired password? I am hoping to see the 2nd connection attempt in there and the SQL after it so I can tell where in Toad it is coming from.

Thanks.

#3

Sure.

CREATE USER TESTRAUL
IDENTIFIED BY VALUES 'CA5E949A6248C520'
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE HALDURID
ACCOUNT UNLOCK;

GRANT CONNECT TO TESTRAUL;
ALTER USER TESTRAUL DEFAULT ROLE ALL;

Then logged in using direct connection (host, port, sid).

10g_user_login.sql (29.0 KB)

Raul

#4

I haven't been able to reproduce this. I have tried with various "save password" settings in Toad, but I never get the 2nd message.

Can you zip up and send me your user files folder?

Go to Options -> General.
Click the "Open Folder" button on the right
Close Toad.
Zip up the "User Files" folder.
email it to me.

Thanks.

#5

I sent an email.

I can't seem to get this attachment (zipped up) through because of the security concern.
(gmail and outlook both blocked it)

E: I did send you an email for google drive link instead.
Please let me know, when you have managed to download this file, then I can delete it.

Raul

#6

Thanks Raul.

If anyone is following this thread - it turns out that this is a bug on Oracle 10g. I can reproduce the problem there using both Toad and SQL*Plus, but it works fine in later Oracle versions.

#7

Hi

How did you test this with sqlplus..?

I managed to test it like this, and it works.

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 14 16:32:30 2019

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
With the Real Application Clusters option

SQL>
SQL> create user test123 identified by test123 password expire;
grant connect to test123;
alter user test123 default role all;
User created.

SQL>
Grant succeeded.

SQL>

User altered.

SQL>
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
With the Real Application Clusters option
$ sqlplus test123

SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 14 16:32:43 2019

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Enter password:
ERROR:
ORA-28001: the password has expired

Changing password for test123
New password:
Retype new password:
Password changed

Connected to:
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
With the Real Application Clusters option

SQL> <-- sqlplus login worked

How did you test it..?

Regards
Raul

#8

I did the same thing as you, but I just discovered the following:

If I attempt the login using a 10.2.0.5 client, on the server, it works.
if I attempt the login using a 12c client, remotely, it fails.
I didn't test it, but I suspect if you use Toad with a 10g client, it will work.

This was on the server:

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 14 08:43:22 2019
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user pwexp identified by pwexp;
User created.

SQL> grant create session to pwexp;
Grant succeeded.

SQL> alter user pwexp password expire;
User altered.

SQL> connect pwexp/pwexp
ERROR:
ORA-28001: the password has expired


Changing password for pwexp
New password:
Retype new password:
Password changed
Connected.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 14 08:44:42 2019

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user pwexp password expire;

User altered.

SQL>

This was on a remote client:

C:\Users\QuestAdmin>sqlplus pwexp/pwexp@orcl102

SQL*Plus: Release 12.1.0.1.0 Production on Tue May 14 08:45:20 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-28001: the password has expired


Changing password for pwexp
New password:
Retype new password:
ERROR:
ORA-01017: invalid username/password; logon denied


Password unchanged
Enter user-name: pwexp
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name: ^C
C:\Users\QuestAdmin>sqlplus pwexp/pwexp@orcl102

SQL*Plus: Release 12.1.0.1.0 Production on Tue May 14 08:45:49 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-28001: the password has expired


Changing password for pwexp
New password:
Retype new password:
ERROR:
ORA-01017: invalid username/password; logon denied


Password unchanged
Enter user-name:
#9

Ok, so the problem seems to be with 12c client then, correct..?

But have you managed to try with 18c or 19c client as well..? (if it works, then perhaps I should think about upgrading remote client)

Oh, 18.5 client looks like is the latest, but this is instant client.

I am going off topic, but will Toad also work with instant client or I should install client..?

Raul

#10

Ok, so the problem seems to be with 12c client then, correct..?

I just tested 18c client and that doesn't solve it. I think this is an incompatibility between 10g server and clients that are newer than 10g. Maybe 11g will work, I don't know.

It looks your options are:
a) log in as another user to change your passwords when they expire
b) log into server to change your password when it expires.
c) change your profile so that your password won't expire anymore.
d) upgrade your server
e) install a 10g client to use with your 10g server