Mysql all user processlist without root permission

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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 <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="7d09180e09043d11121e1c1115120e09">[email protected]</a>                  |
+---------------------------------------------+
| GRANT PROCESS ON *.* TO 'testy'@'localhost' |
+---------------------------------------------+

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


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x