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
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.
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
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.
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
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.
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 optionSQL>
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 test123SQL*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 expiredChanging password for test123
New password:
Retype new password:
Password changedConnected to:
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
With the Real Application Clusters optionSQL> <-- sqlplus login worked
How did you test it..?
Regards
Raul
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: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
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