11

After setting root password why does MYSQL still allow me to login without a password from the command line? I can type "mysql" at a root unix prompt and it asks for no password and still allows me root access. I am not understanding why "mysql -u root" is not NOW asking me for the password that I set on the account.

Also, I cannot login to the mysql from a remote machine as 'root'. Didn't I configure it correctly below? I get the error: "Host...is not allowed to connect to this MySQL server. Didn't I configure it for '%' ?

Here is my user table:

mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| %         | root |                                           |
| 127.0.0.1 | root |                                           |
| ::1       | root |                                           |
| localhost |      |                                           |
| %         |      |                                           |
+-----------+------+-------------------------------------------+
6 rows in set (0.00 sec)

3 Answers 3

4

Looks like the password is set on the 'root'@'localhost' user entry, but not on the 'root'@'%' entry; password-free authentication would be allowed based on that.

For security purposes, reconsider allowing root access from anywhere. If you do need it, then just get rid of the localhost specifications:

drop user 'root'@'localhost';
drop user 'root'@'127.0.0.1';
drop user 'root'@'::1';

And set the password for the 'root'@'%' user:

set password for 'root'@'%' = password('passwordhere');
2
  • That was EVERYTHING I needed and then some. Thanks. I was unsure what was safe to remove or not safe and you answered my question. Great answer. :-)
    – djangofan
    Apr 10, 2011 at 21:05
  • I have only 'root'@'localhost' line. And also can connect without password after ALTER USER 'root'@'localhost' IDENTIFIED BY '...'. FLUSH PREVILIGIES does't help. Oct 15, 2020 at 7:49
4

Well, the answer is right in that table - you need to remove the "root" lines that have no password listed.

Also, looks like you allow connections from anywhere (?!?!) when using no user or pass. Probably a bad idea. I'd do something like:

mysql> use mysql;
mysql> delete from user where password = "";
2

Don't forget to FLUSH PRIVILEGES or you can still login without password.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.