mysql grant all on *.* to xxx@'%' 报Access denied for user 'root'@'localhost'

今日，开发反馈某台mysql服务器无法登陆，解决之后，远程登录后发现用户只能看到information_schema，其他均看不到。

故登录服务器执行：

mysql> grant all on *.* to root@'%';

报了ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

查看user_privileges看见权限都是有的，如下：

mysql> select * from user_privileges;
+--------------------+---------------+-------------------------+--------------+
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------+---------------+-------------------------+--------------+
| 'root'@'localhost' | def | SELECT | YES |
| 'root'@'localhost' | def | INSERT | YES |
| 'root'@'localhost' | def | UPDATE | YES |
| 'root'@'localhost' | def | DELETE | YES |
| 'root'@'localhost' | def | CREATE | YES |
| 'root'@'localhost' | def | DROP | YES |
| 'root'@'localhost' | def | RELOAD | YES |
| 'root'@'localhost' | def | SHUTDOWN | YES |
| 'root'@'localhost' | def | PROCESS | YES |
| 'root'@'localhost' | def | FILE | YES |
| 'root'@'localhost' | def | REFERENCES | YES |
| 'root'@'localhost' | def | INDEX | YES |
| 'root'@'localhost' | def | ALTER | YES |
| 'root'@'localhost' | def | SHOW DATABASES | YES |
| 'root'@'localhost' | def | SUPER | YES |
| 'root'@'localhost' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'localhost' | def | LOCK TABLES | YES |
| 'root'@'localhost' | def | EXECUTE | YES |
| 'root'@'localhost' | def | REPLICATION SLAVE | YES |
| 'root'@'localhost' | def | REPLICATION CLIENT | YES |
| 'root'@'localhost' | def | CREATE VIEW | YES |
| 'root'@'localhost' | def | SHOW VIEW | YES |
| 'root'@'localhost' | def | CREATE ROUTINE | YES |
| 'root'@'localhost' | def | ALTER ROUTINE | YES |
| 'root'@'localhost' | def | CREATE USER | YES |

so。。。。。

mysql> grant super on *.* to root@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> grant show databases on *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

grant 单独的权限都是有的，grant all无论是*.*还是db.*都报上述错误。。。

难不成密码未生效？？？

mysql> update user set password=password('XXX') where host='%';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

重新登录客户端，果然这问题导致。。。可问题是客户端一直都可以登录，只是无法看到其他db。。。哎，又被坑了。。。

巴特西

mysql grant all on . to xxx@'%' 报Access denied for user 'root'@'localhost'

最新文章

热门文章