Home / server / Questions / 257984
Accepted
djangofan
Asked: 2011-04-11 09:39:52 +0800 CST

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

  • 772

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)
mysql database-administration mysql5

3 Answers

  1. 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 = "";
    • 4
  2. 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');
    • 4

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

    • 2