Mysql all user processlist without root permission

Question

I need a user in mysql to run only one query

SHOW PROCESSLIST;

and show all user process list in mysql (like root user) And this user has no other access

I need this user just for debugging queries.

I do not want the root user to use this simple query!

Any solution?

Answer 1

https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html says:

If you have the PROCESS privilege, you can see all threads, even those belonging to other users. Otherwise (without the PROCESS privilege), nonanonymous users have access to information about their own threads but not threads for other users, and anonymous users have no access to thread information.

Let's test creating a user and granting them only the PROCESS privilege:

mysql> create user 'testy'@'localhost' identified by 'testy';

mysql> grant process on *.* to 'testy'@'localhost';

mysql> exit

$ mysql -utesty -ptesty 

mysql> show processlist;
+----+-------+-----------+------+---------+------+----------+------------------+-----------+---------------+
| Id | User  | Host      | db   | Command | Time | State    | Info             | Rows_sent | Rows_examined |
+----+-------+-----------+------+---------+------+----------+------------------+-----------+---------------+
| 34 | testy | localhost | NULL | Query   |    0 | starting | show processlist |         0 |             0 |
+----+-------+-----------+------+---------+------+----------+------------------+-----------+---------------+

mysql> show grants;
+---------------------------------------------+
| Grants for testy@localhost                  |
+---------------------------------------------+
| GRANT PROCESS ON *.* TO 'testy'@'localhost' |
+---------------------------------------------+

Yes, you can create a special user without SUPER privilege who can view the processlist.