MySQL Bugs: #20009: user w/SUPER priv can't see all procs via show processlist as per manual

Bug #20009	user w/SUPER priv can't see all procs via show processlist as per manual
Submitted:	22 May 2006 20:12	Modified:	5 Jun 2006 22:50
Reporter:	Sharif Alexandre	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.16-max-log	OS:	Linux (CentOS release 4.1)
Assigned to:	Mike Hillyer	CPU Architecture:	Any

Description:
According to the manual:

SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the SUPER privilege, you can see all threads.

However, when I create a user with the SUPER privilege and execute 'show processlist' only the user's threads are shown.  If the 'process' privilege is also granted to the user, then the user can see all system threads through 'show processlist'

How to repeat:
[root@(none)]> create user u1 identified by 'u1';
Query OK, 0 rows affected (0.01 sec)

[root@(none)]> grant SUPER on *.* to u1;
Query OK, 0 rows affected (0.00 sec)

[root@(none)]> show grants for u1;
+---------------------------------------------------------------------------------------------------+
| Grants for u1@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT SUPER ON *.* TO 'u1'@'%' IDENTIFIED BY PASSWORD '*556BEF296211C2AF58F53DA3EDDD0A3371B6ECD5' |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[root@(none)]> quit
Bye
[mysql@mysql-1 22]$ mysql -u u1 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1437232 to server version: 5.0.16-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

[u1@(none)]> show processlist;
+---------+------+-----------+------+---------+------+-------+------------------+
| Id      | User | Host      | db   | Command | Time | State | Info             |
+---------+------+-----------+------+---------+------+-------+------------------+
| 1437232 | u1   | localhost | NULL | Query   |    0 | NULL  | show processlist |
+---------+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

[u1@(none)]> show full processlist;
+---------+------+-----------+------+---------+------+-------+-----------------------+
| Id      | User | Host      | db   | Command | Time | State | Info                  |
+---------+------+-----------+------+---------+------+-------+-----------------------+
| 1437232 | u1   | localhost | NULL | Query   |    0 | NULL  | show full processlist |
+---------+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

[u1@(none)]> quit
Bye
[mysql@mysql-1 22]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1438154 to server version: 5.0.16-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

[root@(none)]> grant process on *.* to u1;
Query OK, 0 rows affected (0.00 sec)

[root@(none)]> quit
Bye
[mysql@mysql-1 22]$ mysql -u u1 -pu1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1438225 to server version: 5.0.16-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

[u1@(none)]> show processlist;
+---------+--------------+-----------------------------------+----------------+---------+-------+----------------------+------------------------------------------------------------------------------------------------------+
| Id      | User         | Host                              | db             | Command | Time  | State                | Info                                                                                                 |
+---------+--------------+-----------------------------------+----------------+---------+-------+----------------------+------------------------------------------------------------------------------------------------------+
| 1379867 | user1  | app-1:39665         | db1 | Sleep   |   292 |                      | NULL                                                                                                 |
| 1381870 | user2  | app-1:41287         | db1 ....

Suggested fix:
Either update the documentation to reflect the fact that the 'process' privilege is also needed to see all user threads or all users with only the SUPER privilege to in fact view all user threads.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.21, and add the results of

select user(), current_user();

for the session where you are executing SHOW PROCESSLIST.

As requested, I repeated the test using v5.0.21-max-log:

[mysql@localhost mysql]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 5.0.21-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

[root@(none)]> select version(), user(), current_user();
+----------------+----------------+----------------+
| version()      | user()         | current_user() |
+----------------+----------------+----------------+
| 5.0.21-max-log | root@localhost | root@localhost |
+----------------+----------------+----------------+
1 row in set (0.00 sec)

[root@(none)]> show processlist;
+----+-------------+-----------------+-------------+---------+------+-------+------------------+
| Id | User        | Host            | db          | Command | Time | State | Info             |
+----+-------------+-----------------+-------------+---------+------+-------+------------------+
|  3 | user1 | localhost:52085 | db1 | Sleep   |  431 | NULL  | NULL             |
|  4 | user1 | localhost:55042 | db1 | Sleep   |  372 | NULL  | NULL             |
|  5 | user1 | localhost:45383 | db1 | Sleep   |  312 | NULL  | NULL             |
|  6 | user1 | localhost:53456 | db1 | Sleep   |  252 | NULL  | NULL             |
|  7 | user1 | localhost:48860 | db1 | Sleep   |  192 | NULL  | NULL             |
|  8 | user1 | localhost:55784 | db1 | Sleep   |  132 | NULL  | NULL             |
|  9 | user1 | localhost:54908 | db1 | Sleep   |   72 | NULL  | NULL             |
| 10 | user1 | localhost:54609 | db1 | Sleep   |   12 | NULL  | NULL             |
| 13 | root        | localhost       | NULL        | Sleep   |  475 | NULL  | NULL             |
| 14 | root        | localhost       | NULL        | Query   |    0 | NULL  | show processlist |
+----+-------------+-----------------+-------------+---------+------+-------+------------------+
10 rows in set (0.00 sec)

[root@(none)]> show grants for u1;
+---------------------------------------------------------------------------------------------------+
| Grants for u1@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT SUPER ON *.* TO 'u1'@'%' IDENTIFIED BY PASSWORD '*556BEF296211C2AF58F53DA3EDDD0A3371B6ECD5' |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[root@(none)]> quit
Bye
[mysql@localhost mysql]$ mysql -u u1 -pu1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15 to server version: 5.0.21-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

[u1@(none)]> select version(), user(), current_user();
+----------------+--------------+----------------+
| version()      | user()       | current_user() |
+----------------+--------------+----------------+
| 5.0.21-max-log | u1@localhost | u1@%           |
+----------------+--------------+----------------+
1 row in set (0.00 sec)

[u1@(none)]> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 15 | u1   | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

[u1@(none)]> quit
Bye
[mysql@localhost mysql]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16 to server version: 5.0.21-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

[root@(none)]> grant process on *.* to u1;
Query OK, 0 rows affected (0.01 sec)

[root@(none)]> show grants for u1;
+------------------------------------------------------------------------------------------------------------+
| Grants for u1@%                                                                                            |
+------------------------------------------------------------------------------------------------------------+
| GRANT PROCESS, SUPER ON *.* TO 'u1'@'%' IDENTIFIED BY PASSWORD '*556BEF296211C2AF58F53DA3EDDD0A3371B6ECD5' |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[root@(none)]> quit
Bye
[mysql@localhost mysql]$ mysql -u u1 -pu1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17 to server version: 5.0.21-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

[u1@(none)]> show processlist;
+----+-------------+-----------------+-------------+---------+------+-------+------------------+
| Id | User        | Host            | db          | Command | Time | State | Info             |
+----+-------------+-----------------+-------------+---------+------+-------+------------------+
|  3 | user1 | localhost:52085 | db1 | Sleep   |   48 | NULL  | NULL             |
|  4 | user1 | localhost:55042 | db1 | Sleep   |  468 | NULL  | NULL             |
|  5 | user1 | localhost:45383 | db1 | Sleep   |  408 | NULL  | NULL             |
|  6 | user1 | localhost:53456 | db1 | Sleep   |  348 | NULL  | NULL             |
|  7 | user1 | localhost:48860 | db1 | Sleep   |  288 | NULL  | NULL             |
|  8 | user1 | localhost:55784 | db1 | Sleep   |  228 | NULL  | NULL             |
|  9 | user1 | localhost:54908 | db1 | Sleep   |  168 | NULL  | NULL             |
| 10 | user1 | localhost:54609 | db1 | Sleep   |  108 | NULL  | NULL             |
| 13 | root        | localhost       | NULL        | Sleep   |  571 | NULL  | NULL             |
| 17 | u1          | localhost       | NULL        | Query   |    0 | NULL  | show processlist |
+----+-------------+-----------------+-------------+---------+------+-------+------------------+
10 rows in set (0.00 sec)

[u1@(none)]>

OK. Your new user has SUPER privilege, really. But according to the manual (http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html)

"The processlist command displays information about the threads executing within the server (that is, information about the statements being executed by clients). The kill command terminates server threads. You can always display or kill your own threads, but you need the PROCESS privilege to display threads initiated by other users and the SUPER  privilege to kill them."

So, the behaviour you demonstrated is expected and described.

But documentation should be corrected, surely. This page, http://dev.mysql.com/doc/refman/5.0/en/show-processlist.html, is wrong:

"If you have the SUPER privilege, you can see all threads."

Looks like PROCESS should be used instead of SUPER here.

Corrected SUPER to read PROCESS in the section specified in the bug report.