Support for expired passwords

[expired user #8759]'s profile image [expired user #8759] posted 9 years ago in General Permalink
If I create an user with Oracle MySQL 5.6:
CREATE USER 'foo'@'%' IDENTIFIED BY 'foobar';
ALTER USER 'foo'@'%' PASSWORD EXPIRE;

And then the users tries to login with HeidiSQL, but get's an error message that HeidiSQL doesn't support expired passwords.

See also:
https://dev.mysql.com/doc/refman/5.6/en/password-expiration-sandbox-mode.html

Please make it possible to login so the user can set a password.
ansgar's profile image ansgar posted 9 years ago Permalink
I will need to add new constants for connecting options from mysql.h to HeidiSQL. And that will require a newer libmysql.dll, as the MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS and CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS constants require libmysql.dll 5.6.10+ (according to the docs), and HeidiSQL currently uses 5.6.6 from MariaDB.

I guess in such cases the user should get some prompt, not the main window, and the ability to set a password, or?
[expired user #8759]'s profile image [expired user #8759] posted 9 years ago Permalink
By default disconnect_on_expired_password is set. This means that a client which can't handle expire passwords will be disconnected. This is what now happens for HeidiSQL. The reason for this is that many application correctly handle connection errors, but fail to handle expired passwords/wrong permissions.

If disconnect_on_expired_password is disabled or the client has the MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS flag set then the user is put into an sandbox environment. Then the application can't do anything except an SET PASSWORD statement.
So when HeidiSQL will send this flag nothing else should be needed. However some connection initialization statments may fail.

I've tested this with a server with disconnect_on_expired_password disabled and then I get this error:
"SQL Error (1820): You must SET PASSWORD before executing this statement."
So it looks like there are initialization statments which are ran by HeidiSQL. I couldn't find an option to disable the initialization statements.

The initialization commands from HeidiSQL:
SET NAMES utf8mb4;
SHOW STATUS;

The show status is the one that results in an error.

Note that in 5.7 password expiration might become more common as it introductes password lifetimes. (e.g. set lifetime to 90 days and MySQL will set the account as expired).

So what I think should be done:
- Add the flag to indicate the client can handle expired passwords
- Don't issue SHOW STATUS or continue if it gives an error.
- Let the user execute SET PASSWORD or provide a prompt for it.

ansgar's profile image ansgar posted 9 years ago Permalink
What I am unclear about is what happens after the client has fired the "SET PASSWORD ...". Is the connection closed then, or does the server let the user proceed to do anything what he can do with his privileges?
[expired user #8759]'s profile image [expired user #8759] posted 9 years ago Permalink
This is how the commandline client behaves:
mysql> select version();
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> set password = password('newpassword');
Query OK, 0 rows affected (0.00 sec)
mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.6.22-debug-log |
+------------------+
1 row in set (0.00 sec)


This is what Workbench does:


ansgar's profile image ansgar posted 9 years ago Permalink
Ok, thanks. I just wonder why Workbench asks for the old password, and what it does with it.
[expired user #8759]'s profile image [expired user #8759] posted 9 years ago Permalink
I think workbench uses a new connection to set the password. This looks also more like the password change dialog from windows.

You might also want to change the saved password for the connection.
ansgar's profile image ansgar posted 8 years ago Permalink
I would like to go on here with the implementation of expired passwords in HeidiSQL.

I have the CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS constant set in mysql_real_connect(). Further I have a libmysql.dll from the MariaDB 10.1.8 release which can handle expired passwords and the mentioned constant.

Now, after connecting through HeidiSQL, I only get a disconnect after the first init statements. If I don't want to run into this
SQL Error (1820): You must SET PASSWORD before executing this statement

... then how can I detect an expired password? I guess I have to run into the error to see the error code 1820, then trigger a "reset password" dialog?
Code modification/commit 75fecfd from ansgarbecker, 8 years ago, revision 9.3.0.5094
MySQL/MariaDB: Implement support for expired passwords. Show a change-password dialog after the very first query of a connection when it returns "Error 1820: You must SET PASSWORD before executing this statement". See http://www.heidisql.com/forum.php?t=17921
ansgar's profile image ansgar posted 8 years ago Permalink

r5094 now adds a change-password dialog like in the screenshot, when the very first query of a connection returns error 1820.

This implementation only works on servers with disabled disconnect_on_expired_password variable.

[expired user #8759]'s profile image [expired user #8759] posted 8 years ago Permalink

This looks very good. Thanks for implementing this!

Please login to leave a reply, or register at first.