User 'user_name@localhost' has no password set. #268

Open
karek314 opened this Issue Nov 9, 2016 · 13 comments

Projects

None yet

5 participants

@karek314
karek314 commented Nov 9, 2016

Latest version from mysqltuner.com

User 'user_name@localhost' has no password set.

Gives this warning even when there IS password set, it happens after i have updated password for this user.

@jmrenouard jmrenouard added the bug label Nov 12, 2016
@jmrenouard
Collaborator

Hi @karek314

can you send us result from
DESC mysql.user;

@karek314

Hi,
I need also to add that this issue happens for 2 other users as well.
Here is what you have asked for, trimmed
screen shot 2016-11-12 at 18 48 52

@jmrenouard
Collaborator

Hi @karek314

Can you perform following query ?

Select user, host from mysql.user where Password is NULL or Password ='';

@jmrenouard

@karek314
karek314 commented Nov 12, 2016 edited

It shows the same users which has been displayed in mysqltuner output.

But
Select user, host from mysql.user where Password is NULL; - Gives empty result

and

Select user, host from mysql.user where Password = ''; - Display those users mentioned before

Which is more interesting
SELECT * FROM user
Shows that those users passwords are empty, but in fact i can not access databases with those users without providing correct password. Obviously password is set but not sure why SELECT returns wrong result. I have tried manually changing passwords via command line as well with phpmyadmin.

Server version: 10.0.28-MariaDB-1~trusty - mariadb.org binary distribution
Phpmyadmin Version information: 4.5.4.1deb2ubuntu2
@acs-ferreira
acs-ferreira commented Nov 30, 2016 edited

If you

SET PASSWORD FOR 'user_name'@'localhost' = PASSWORD('the_pass_you_want');
FLUSH PRIVILEGES;

Does it helps?

@karek314

I've tried to change password this way as well. Which is weird it always return that password is empty, not sure if this is some new kind of security measure in new MariaDB or what, password is there, can't log in without password or with wrong password but it always returns that is empty when SELECT.

@acs-ferreira

I'm using MariaDB 10.1.19 and no having that issue.

dba@db-a:~$ mysql -V
mysql  Ver 15.1 Distrib 10.1.19-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
@karek314
karek314 commented Nov 30, 2016 edited

mysql Ver 15.1 Distrib 10.0.28-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

@untamed0
untamed0 commented Dec 10, 2016 edited

I've exactly the same issue. Both mysqltuner and Select user, host from mysql.user where Password is NULL or Password =''; returns the users with 'no password'.

However, a password is set as I can't login with an empty or incorrect password.

Also same version (latest on Debian 8 repo):
mysql Ver 15.1 Distrib 10.0.28-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

@tssajo
tssajo commented Dec 13, 2016

@jmrenouard I know the solution to this problem:

( from http://stackoverflow.com/questions/30692812/mysql-user-db-does-not-have-password-columns-installing-mysql-on-osx )

The important part is this: "In MySQL 5.7, the password field in mysql.user table was removed, now the field name is 'authentication_string'."

This is the case with MariaDB 10.1 too, NOT just MySQL 5.7 !

Please also note that if you upgrade from an older MySQL version to a newer MariaDB version then in the users table some users will have password hashes stored the old way (still in the password column) and newly added/modified users will have their passwords stored in the new authentication_string column (and these newly added/modified users will have their password field empty).

So I would first try to do a query like this:

SELECT `user`,`host` from `mysql`.`user` WHERE (`password` IS NULL OR `password`='') AND (`authentication_string` IS NULL OR `authentication_string`='');

If the above query ran fine then I would use its results.
If the above query failed with an error, that probably means that the user table does not have an authentication_string column (it's older MySQL version) so in that case (and only in that case!!) I would fall back to this (original) query:

SELECT `user`,`host` from `mysql`.`user` WHERE `password` IS NULL OR `password`='';

I hope this helps.

@untamed0

I can confirm @tssajo 's proposed fix works on my setup.

@tssajo
tssajo commented Dec 13, 2016

@untamed0 Thanks for testing it. But there is more to this. See this issue is essentially the same: #234

From the linked issue, you can see that in some MySQL versions there is no password column in the user table. :) So we really should check for the following three cases:

1.) There is both password and authentication_string columns in the user table -- this is the "mixed" situation (this is the case with my MariaDB 10.1 which I "upgraded" from MySQL 5.6)

2.) There is authentication_string column but there is no password column anymore (this is reportedly the case with new MySQL 5.7.x installs)

3.) There is password column but there is no authentication_string column (this is the legacy case, older MySQL versions use this)

So using just two queries as I suggested above won't suffice. All three cases must be handled.

@jmrenouard
Collaborator

Is there anyone of us can provide us a PR for that ?

Thanks by advance

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment