在MySQL中如何给普通用户授予查看所有用户线程/连接的权限,当然,默认情况下show processlist是可以查看当前用户的线程/连接的。
mysql> grant process on MyDB.* to test;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
第一次授予这样的权限,错误原因是process权限是一个全局权限,不可以指定在某一个库上(个人测试库为MyDB),所以,把授权语句更改为如下即可:
mysql> grant process on *.* to test;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
如果不给拥有授予PROESS权限 ,show processlist命令只能看到当前用户的线程,而授予了PROCESS权限后,使用show processlist就能看到所有用户的线程。官方文档的介绍如下:
SHOW PROCESSLIST shows you which threads are running. You can also get this information from the INFORMATION_SCHEMA PROCESSLIST table or the mysqladmin processlist command. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.
我们先创建下面账号test2,然后测试如下:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant select,insert,update,delete on MyDB.* to test2@'%' identified by 'test2';
Query OK, 0 rows