ERROR 1044 (42000): Access denied for user 'root'@'%'
--给mysql的root %用户添加grant权限。并给创建的用户赋予权限
select user,host,grant_priv from user;
update user set grant_priv='Y' where user='root';
flush privileges
show grants for 'root'@'%';
--mysql_upgrade -u root -p
--insert into mysql.user(Host,User,Password) values("localhost","root",password("1234"));
从供应商那边接手一个 MySQL 数据库(数据库版本为 5.7.21 MySQL Community Server (GPL) ),在创建账号时遇到了 “ ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database xxx ” 错误,如下所示
mysql> grant all on xxx.* to xxx@'192.168.%' identified by 'xxx';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'xxxx'
mysql>
照理说, root 用户应该有任何权限,那么为什么出现这个错误呢? 查看当前用户为 root@localhost ,顺便查看了一下各个 root 账号的权限。如下所示:
mysql> select current_user() from dual;
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec
mysql> select host,user from user where user='root';
+-----------+----------+
| host | user |
+-----------+----------+
| % | root |
| 127.0.0.1 | root |
| ::1 | root |
| localhost | root |
+-----------+----------+
7 rows in set (0.00 sec)
mysql> show grants for root@'localhost';
+--------------------------------------------------------------+
| Grants for root@localhost |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for root@'127.0.0.1';
+---------------------------------------------------------------------+
| Grants for root@127.0.0.1 |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for root@'%';
+-------------------------------------------+
| Grants for root@% |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)
如上所示, root@localhost 账号没有 WITH GRANT OPTION 选项,关于 WITH GRANT OPTION 选项,如果想让授权的用户,也可以将这些权限授予给其他用户,需要选项 “ WITH GRANT OPTION “ 。也就是说有这个选项就可以将权限传递给第三方。这也是上面 root@localhost 用户 给其它用后授权报错的原因,如果以 root@127.0.0.1 登录(此账号拥有 WITHGRANT OPTION 选项),创建用户并授权就不会有这个错误,如下所示:
# mysql -host 127.0.0.1 -u root -p
Enter password:
mysql> grant all on xxx.* to xxx@'192.168.%' identified by 'test1249';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
当然还有其它方面的原因也可能会引起这个错误,不过在这个案例当中,确实仅仅是因为上面原因引起。特此记录一下这个案例 @ !
About Me
........................................................................................................................ ● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除 ● 本文在itpub( http://blog.itpub.net/26736162 )、博客园( http://www.cnblogs.com/lhrbest )和个人weixin公众号( xiaomaimiaolhr )上有同步更新 ● 本文itpub地址: http://blog.itpub.net/26736162 ● 本文博客园地址: http://www.cnblogs.com/lhrbest ● 本文pdf版、个人简介及小麦苗云盘地址: http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答: http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址: http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群号: 230161599 (满) 、618766405 ● weixin群:可加我weixin,我拉大家进群,非诚勿扰 ● 联系我请加QQ好友 ( 646634621 ) ,注明添加缘由 ● 于 2019-06-01 06:00 ~ 2019-06-30 24:00 在西安完成 ● 最新修改时间:2019-06-01 06:00 ~ 2019-06-30 24:00 ● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解 ● 版权所有,欢迎分享本文,转载请保留出处 ........................................................................................................................ ● 小麦苗的微店 : https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书 : http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麦苗OCP、OCM、高可用网络班 : http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麦苗腾讯课堂主页 : https://lhr.ke.qq.com/ ........................................................................................................................ 使用 weixin客户端 扫描下面的二维码来关注小麦苗的weixin公众号( xiaomaimiaolhr )及QQ群(DBA宝典)、添加小麦苗weixin, 学习最实用的数据库技术。
........................................................................................................................ |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2648966/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26736162/viewspace-2648966/