Mysql – After setting root password why does MYSQL still allow me to login without a password

database-administrationMySQLmysql5

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)

Best Answer

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');
Related Topic