mysql - 选择分配给用户的有限权限的所有用户的进程列表

Question

我正在尝试选择完整进程列表的计数和命令不是来自 mysql 服务器的“ sleep ”的完整进程列表的计数。它用于从外部服务器每 5 分钟执行一次的数据库检查查询。

这对 root 用户来说工作正常，但我不想在外部服务器上使用 root 用户。所以我创建了一个权限有限的新用户。

USE information_schema;
SELECT COUNT(*) FROM PROCESSLIST WHERE COMMAND NOT LIKE 'Sleep'
UNION ALL SELECT COUNT(*) FROM PROCESSLIST; 

但现在新用户只是获得自己的进程，所以结果只是 1 和 1，但它应该是 10 和 60。我如何在不使用 root 用户的情况下让它工作？

Answer 1

您创建的新用户需要有 PROCESS启用特权，以便能够访问其他线程的进程列表(除了他自己的线程)。

来自 MySQL Documentation :

Like the output from the SHOW PROCESSLIST statement, the PROCESSLIST table shows information only about your own threads, unless you have the PROCESS privilege, in which case you will see information about other threads, too. As an anonymous user, you cannot see any rows at all.

并且来自 PROCESS 的定义特权:

Enables display of information about the threads executing within the server (that is, information about the statements being executed by sessions). The privilege enables use of SHOW PROCESSLIST or mysqladmin processlist to see threads belonging to other accounts; you can always see your own threads. The PROCESS privilege also enables use of SHOW ENGINE.

因此，您只需为新用户启用PROCESS 权限即可。您还需要 SELECT 权限。

GRANT PROCESS, SELECT ON *.* TO 'new_user_name'@'%';
FLUSH PRIVILEGES;