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

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


具體原因

具體原因就是該用戶（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）

  

• 再次查詢授權情況

  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'@'%'