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.
Support for expired passwords
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?
I guess in such cases the user should get some prompt, not the main window, and the ability to set a password, or?
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.
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.
This is how the commandline client behaves:
This is what Workbench does:
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:
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
... 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?
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
Please login to leave a reply, or register at first.