user management problems

Hi, i have 2 problems with your user management.

1) Heidi always tells me this user has an empty password ... user has a password and it works; with an older mysql server it doesn't show this message

2) with ansi mode enabled (sql_mode = ANSI in /etc/mysql/my.cnf) it sets the correct rights but after save it doesn't show any rights.
there is a difference in the SHOW GRANTS FOR outputs with and without ansi mode:


normal mode:
mysql> SHOW GRANTS FOR 'test'@'localhost';
+-------------------------------------------------------------------+
| Grants for test@localhost |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
| GRANT ALL PRIVILEGES ON `example`.* TO 'test'@'localhost' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

ANSI mode:
mysql> SHOW GRANTS FOR 'test'@'localhost';
+-------------------------------------------------------------------+
| Grants for test@localhost |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
| GRANT ALL PRIVILEGES ON "example".* TO 'test'@'localhost' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)


Heidisql: 9.3.0.5003
MariaDB: 10.0.22

Hope you can fix it

Ok, issue 2) is most probably due to a regular expression in Heidi which does not expect double quotes. I can fix that.

Issue 1) is probably caused by the same thing in another expression.

Issue 1) with me, too.

I run two Databases:
1.) MySQL 5.1.63 on Windows 7 (locally).
When starting User Management, "SELECT `user`, `host`, `password` FROM `mysql`.`user`;" is performed, results are OK.
2.) MariaDB 10.1.8 on Windows Server 2008.
When starting User Management, "SELECT `user`, `host`, `authentication_string` FROM `mysql`.`user`;" is performed, results show empty authentication_string indeed.

When performing "SELECT `user`, `host`, `password` FROM `mysql`.`user`;" results are shown all right as well.

Note: https://mariadb.com/kb/en/mariadb/mysqluser-table/ states about the column "authentication_string": "Authentication string for the authentication plugin."

Here it says the same about mySQL 5.5: "The user table has a Password column for storing credential information. As of MySQL 5.5.7, the user table also has plugin and authentication_string columns for storing authentication plugin and credential information."

Looks like you might have to check both columns to determine if a password is set?

Regards, Grüße und danke für deine Arbeit, Wolfgang

r5016 should work with ANSI mode enabled.

Now I can see the password hash, so the "show grants" seems to be OK.

But no difference opening the User Management Dialogue. Tried r5012 and r5016 with and w/o ANSI mode (sql_mode = REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION) - still shows "empty password" in red down left. See attachment.

I think the problem is "authentication_string" as stated above.

Thank you, W.

just stumbled upon http://www.heidisql.com/forum.php?t=19911 - seems to be closely related.

You might as well want to read this as well:
"The original logic (still preserved in MariaDB) was simple: if the plugin field is not set (meaning old table), the server looks at the password field and implicitly uses mysql_native_password plugin. If the plugin field is set (new way, pluggable-auth compatible), the server uses plugin and authentication_string pair, and ignores the password field (the password is stored in the authentication_string)."

HTH, W.

So, just for the records: if the field plugin is empty, HeidiSQL should take the old password field. And only if plugin has some non-empty string, it should take the password from authentication_string, right?

I think so. But I am not an expert on authentication issues, just read about the above details yesterday. I wonder if you could set passwords in HeidiSQL when you are running some weird authentication plugin ... But if so, you will probably have your own means to manage users. It might be sufficient to assume a set password if either password OR authentication_string is non-empty.

Is this still in progress or forgotten? Because it is still happening in HeidiSQL 9.4 using latest MariaDB 10.1.18.

Every User in the user manager got complaints of having an empty password. But this is not true