Disabled SQL accounts

jameson's profile image jameson posted 5 years ago in General Permalink

Why are the accounts with password are disabled in the User manager?

Everything is good as long as the user has no password. With password, the users are become disabled in the user manager (no modification,clone or delete). See my attached screenshot, test02 and test03 are clones (test03 has no password). I set no password in mysql, and the account become enabled again!

I searched the forum/issues without results, do you have an idea what is wrong?

HeidiSQL 10.1.0.5464 x64 for MySQL 8.0.11

1 attachment(s):
  • heidiUserMan
Xenos's profile image Xenos posted 5 years ago Permalink

Same issue with HeidiSQL 10.1.0.5464 portable on Mysql 8.0.16 I suppose something changed in MySQL 8 and Heidi does not properly support it?

Xenos's profile image Xenos posted 5 years ago Permalink

It seems to come from the new MySQL 8 authentication method. Using this command:

ALTER USER 'theusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'thesecretpassword';

Reverts the authentication method to the legacy one (I required that since apparently PHP 7.3 does not have caching sha2 method yet). Once done, the user becomes active again in HeidiSQL.

That's not fixing the bug, but that allows to use Heidi on MySQL 8 for user management on local device (aka not on prod, as I wouldn't go back to legacy authentication in such case)

ansgar's profile image ansgar posted 5 years ago Permalink

Connecting to such servers should be possible with HeidiSQL 10.1 - that was a difficult fix. But the user manager is still buggy I see. Will check that on MySQL 8 here.

Code modification/commit 493138d from Ansgar Becker <anse@heidisql.com>, 5 years ago, revision 5576
Fix wrong check for valid length of hashed password in MySQL 8 and MariaDB. Predefined length of 0, 16 and 41 characters is only valid with mysql_native_password plugin enabled users.
See https://www.heidisql.com/forum.php?t=26945#p27188
ansgar's profile image ansgar posted 5 years ago Permalink

Problem was that HeidiSQL checked the length of encrypted passwords, which is a valid approch for MySQL up to 5.x, but no longer in MySQL 8 - except for users with mysql_native_password plugin. The next nightly build does no length check for MySQL 8.

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