MySQL权限丢失导致只能看见information_schema一个库的解决办法
Tags： information_schemaMySQL故障处理权限
👉 本文共约282个字，系统预计阅读时间或需2分钟。
麦老师自己之前也碰到过这个问题，可以参考：http://blog.itpub.net/26736162/viewspace-2648966/
这里再简单的整理一下处理过程吧。
报错：ERROR 1044 (42000): Access denied for user 'root'@'%'
解决：
1 2 3 4 5 6 7 8 | -- 给mysql的root %用户添加grant权限。并给创建的用户赋予权限 select user,host,grant_priv,super_priv from mysql.user; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'lhr' WITH GRANT OPTION; update mysql.user set grant_priv='Y',super_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("lhr")); |
问题：为何root@'localhost'只能看到information_schema一个库？
答案：这是因为用户root@'localhost'权限缺失导致的，可以给于该用户grant option权限即可，赋权命令为：“GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';”。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 | C:\Users\Administrator>mysql -uroot -plhr -P3311 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) mysql> select user,host,grant_priv,super_priv from mysql.user; ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table 'user' mysql> show grants for 'root'@'%'; +-------------------------------------------------------------+ | Grants for root@% | +-------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION | +-------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show grants for 'root'@'localhost'; +--------------------------------------------------------------+ | Grants for root@localhost | +--------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'root'@'localhost' | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +--------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> exit Bye C:\Users\Administrator>mysql -uroot -plhr -P3311 -h192.168.59.21 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user,host,grant_priv,super_priv from mysql.user; +-----------+-----------+------------+------------+ | user | host | grant_priv | super_priv | +-----------+-----------+------------+------------+ | root | % | Y | Y | | mysql.sys | % | N | N | | root | localhost | N | N | +-----------+-----------+------------+------------+ 3 rows in set (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'lhr' WITH GRANT OPTION; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select user,host,grant_priv,super_priv from mysql.user; +-----------+-----------+------------+------------+ | user | host | grant_priv | super_priv | +-----------+-----------+------------+------------+ | root | % | Y | Y | | mysql.sys | % | N | N | | root | localhost | Y | Y | +-----------+-----------+------------+------------+ 3 rows in set (0.00 sec) mysql> show grants for 'root'@'localhost'; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show grants for 'root'@'%'; +-------------------------------------------------------------+ | Grants for root@% | +-------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION | +-------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> exit Bye C:\Users\Administrator>mysql -uroot -plhr -P3311 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.02 sec) -- USAGE表示仅仅只有连接MySQL的权限，而没有其它权限。而且，这里的USAGE没有GRANT OPTION的权限，否则依然可以查看所有的数据库。 mysql> show grants for root@localhost; +--------------------------------------------------------------+ | Grants for root@localhost | +--------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +--------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> revoke Grant option on *.* from root@localhost; Query OK, 0 rows affected (0.01 sec) mysql> show grants for root@localhost; +--------------------------------------------------------------+ | Grants for root@localhost | +--------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'root'@'localhost' | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +--------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> select current_user(); +----------------+ | current_user() | +----------------+ | root@% | +----------------+ 1 row in set (0.00 sec) -- 重新以root@localhost登陆查看： mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) mysql> show grants; +--------------------------------------------------------------+ | Grants for root@localhost | +--------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'root'@'localhost' | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +--------------------------------------------------------------+ 2 rows in set (0.00 sec) |
