今天尝试使用MySQL,新建了一个用户runner,
[10:30:27]mysql> insert into mysql.user(host,user,password) values('%','runner',PASSWORD('runner'));
[10:30:27]Query OK, 1 row affected, 3 warnings (0.00 sec)
[10:30:27]mysql> flush privileges;
然后尝试登录:
[10:59:48][root@localhost RuleRunner]# mysql -urunner -p
[10:59:49]Enter password:
[10:59:49]ERROR 1045 (28000): Access denied for user 'runner'@'localhost' (using password: YES)
很诡异的问题。经过多次尝试,发现无密码反倒是可以登录:
[11:06:21][root@localhost RuleRunner]# mysql -urunner
[11:06:21]Welcome to the MySQL monitor. Commands end with ; or \g.
[11:06:21]Your MySQL connection id is 14
[11:06:21]Server version: 5.1.66 Source distribution
无语。我之前知道mysql.user表里面会保留无密码的用户,于是决定去检查一下:
[11:02:52]mysql> select user,password from mysql.user;
[11:02:52]+--------+-------------------------------------------+
[11:02:52]| user | password |
[11:02:52]+--------+-------------------------------------------+
[11:02:52]| root | *80926D3F75126C85D893B06075303123B69AAD58 |
[11:02:52]| root | |
[11:02:52]| root | |
[11:02:52]| | |
[11:02:52]| | |
[11:02:52]| root | *80926D3F75126C85D893B06075303123B69AAD58 |
[11:02:52]| runner | *12750620CF570966293289036B62068F8AA9A42C |
[11:02:52]| runner | |
[11:02:52]| reader | *FC69E042CE30D92E2952335F690CF2345C812E36 |
[11:02:52]| reader | |
[11:02:52]+--------+-------------------------------------------+
网上有帖子简易删除所有用户字段为空的用户,我尝试了一下,发现没用。实际有效的方法是删除所有password为空的用户:
[11:08:35]mysql> delete from mysql.user where password='';
[11:08:35]Query OK, 4 rows affected (0.00 sec)
删除后的结果是这样的:
[11:08:38]mysql> select user,password from mysql.user;
[11:08:38]+--------+-------------------------------------------+
[11:08:38]| user | password |
[11:08:38]+--------+-------------------------------------------+
[11:08:38]| root | *80926D3F75126C85D893B06075303123B69AAD58 |
[11:08:38]| root | *80926D3F75126C85D893B06075303123B69AAD58 |
[11:08:38]| runner | *12750620CF570966293289036B62068F8AA9A42C |
[11:08:38]| reader | *FC69E042CE30D92E2952335F690CF2345C812E36 |
[11:08:38]+--------+-------------------------------------------+
然后,再次尝试以runner用户登录:
[11:09:37][root@localhost RuleRunner]# mysql -urunner -p
[11:09:39]Enter password:
[11:09:39]Welcome to the MySQL monitor. Commands end with ; or \g.
[11:09:39]Your MySQL connection id is 24
[11:09:39]Server version: 5.1.66 Source distribution
至此,问题解决。
但是,为什么MySQL会保留空字段的用户呢?为何会同时插入无密码的新用户?这些问题还不理解。求高人指点。