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