distal-attribute
distal-attribute
distal-attribute
distal-attribute

Support for expired passwords

dveeden posted 2 years ago in General
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 posted 2 years ago
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?
dveeden posted 2 years ago
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 posted 2 years ago
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?
dveeden posted 2 years ago
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 posted 2 years ago
Ok, thanks. I just wonder why Workbench asks for the old password, and what it does with it.
dveeden posted 2 years ago
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 posted 1 year ago
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?
ansgar posted 7 months ago

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.

dveeden posted 7 months ago

This looks very good. Thanks for implementing this!

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