原先数据库里root用户授权登录主机为%mysql> show grants for root@"%";
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO [email protected]%‘ IDENTIFIED BY PASSWORD ‘*ABE28A948664E6CEA78541ABEE3A910833361F23‘ WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
[email protected]@localhost 然后删除root@"%", flush privileges。整好之后以为完事了。然后再添加授权的时候糗事发生了。mysql> grant all on *.* to root@"111.74.99.66" ;
ERROR 1045 (28000): Access denied for user [email protected] (using password: YES)
我给root授权的时候明明给的all呀,咋就没授权权限呢?
一番百度之后并和其他数据库的root用户对比,最终查询user表的root用户的Grant_priv字段发现为N。15:05:18
15:05:28 mysql> select user,host,Grant_priv from user;
15:05:28 +---------+---------------+------------+
15:05:28 | user | host | Grant_priv |
15:05:28 | root | 127.0.0.1 | N |
15:05:28 | root | localhost | N |
15:05:28 +---------+---------------+------------+
15:05:28 10 rows in set (0.00 sec)
15:05:28
15:07:33 mysql>
心惊胆颤的将root用户的Grant_priv 字段修改为Y,15:17:10 mysql> update mysql.user set Grant_priv="Y" where user="root" and host="localhost";
15:17:10 Query OK, 1 row affected (0.00 sec)
15:17:19 mysql> flush privileges;
15:17:19 Query OK, 0 rows affected (0.00 sec)
15:17:20 mysql> Bye
然后退出登录,再进来授权,问题解决。15:18:05 mysql> grant all on mysql.* to [email protected];
15:18:05 Query OK, 0 rows affected (0.00 sec)
授权all权限后,啥权限都有就是没有grant权限。
原文:http://damaicha.blog.51cto.com/6046098/1870607