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)
djangofan
  • 4,172
  • 10
  • 45
  • 59

3 Answers3

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 = "";
EEAA
  • 108,414
  • 18
  • 172
  • 242
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');
Shane Madden
  • 112,982
  • 12
  • 174
  • 248
  • 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 '11 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. – Dmitriy Dokshin Oct 15 '20 at 07:49
2

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

tgunr
  • 121
  • 3