Navicat 1142 SELECT command denied to user 'sx'@'xxx' for table 'user'

使用Navicat使用sx用戶連接資料庫時或者連接為用戶sx開放的資料庫travel_agency時，Navicat視窗彈出上述問題

![](D:\博客園\隨筆\pictures\navicat 1142.jpg)

具體原因

具體原因就是該用戶（sx）無法讀取user表（但注意，應該是mysql版本改動表名發生了變化，實際上無法讀取的是mysql.user表）

處理方式：（不用重啟資料庫服務）

首先檢驗此時的授權情況

mysql> show grants for sx;
+-------------------------------------------------------------------------+
| Grants for sx@%                                                         |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sx'@'%'                                          |
| GRANT ALL PRIVILEGES ON `travel_agency`.* TO 'sx'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------------------+
2 rows in set (0.00 sec)

通過root方式連接資料庫，然后通過Navicat讓sx用戶獲得user資料庫以及mysql.user表的select權限（僅授權select）

![navicat 1142_2](D:\博客園\隨筆\pictures\navicat 1142_2.png)

再次查詢授權情況

mysql> show grants for sx;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for sx@%                                                                                                                                                                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sx'@'%'                                                                                                                                                                                                                 |
| GRANT SELECT ON `user`.* TO 'sx'@'%'                                                                                                                                                                                                           |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `travel_agency`.* TO 'sx'@'%' WITH GRANT OPTION |
| GRANT SELECT ON `mysql`.`user` TO 'sx'@'%'                                                                                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

當然，也可以在命令列中手動賦權，如GRANT SELECT ON user.* TO 'sx'@'%'

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/6046.html

標籤：MySQL

上一篇：MySQL 密碼引數配置與修改 validate_password

下一篇：阿里：MySQL資料庫規范