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

Navicat 1142 SELECT command denied to user 'sx'@'xxx' for table 'user' 使用Navicat使用sx用户连接数据库时或者连接为用户sx开放的数据库travel_agency时,Navicat窗口弹出上述问题 ![]...

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

使用Navicat使用sx用户连接数据库时或者连接为用户sx开放的数据库travel_agency时,Navicat窗口弹出上述问题

![](D:博客园随笔picturesnavicat 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:博客园随笔picturesnavicat 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'@'%'

  • 发表于 2020-07-17 08:00
  • 阅读 ( 104 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除