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

Time:2021-1-25

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

 

Using NavicatWhen using SX user to connect to databaseperhapsConnecting to the open database travel for user SX_ Agency, Navicat window pops up the above problem

! [] (D: blog Garden, essays, pictures, Navicat 1142. JPG)

 

Specific reasons

The specific reason is that the user (SX) can’t read the user table (but note that the MySQL version has changed, and the table name has changed. In fact, the user (SX) can’t read the user tablemysql.user(table)

 

Processing method: (no need to restart the database service)

  • First check the authorization at this time

    mysql> show grants for sx;
    +-------------------------------------------------------------------------+
    | Grants for [email protected]%                                                         |
    +-------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'sx'@'%'                                          |
    | GRANT ALL PRIVILEGES ON `travel_agency`.* TO 'sx'@'%' WITH GRANT OPTION |
    +-------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
  • Connect to the database through the root mode, and then let the SX user get the user database and the mysql.user Select permission of table (select only)

    ![navicat 1142_ 2] (d): blog Garden, essays, pictures, navigator 1142)_ 2.png)

  • Check the authorization again

    mysql> show grants for sx;
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for [email protected]%                                                                                                                                                                                                                                |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | 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)

    Of course, you can also manually empower in the command line, such asGRANT SELECT ON user.* TO 'sx'@'%'

 

Recommended Today

Zooteam front end weekly No.75

Zhengcai cloud front end tabloid No.75 For more previous tabloids, please visit: https://weekly.zoo.team Node.js Memory management and V8 garbage collection mechanism It mainly describes the Node.js Memory management of server, memory leak and garbage collection mechanism of V8 If only I had studied nginx like this! (detailed explanation of many pictures) “This article is mainly […]