Bug #107314 Privileges for EXPLAIN CONNECTION FOR are incorrectly documented/implemented
Submitted: 17 May 2022 9:06 Modified: 17 May 2022 13:48
Reporter: Saverio Miroddi Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:8.0.28 OS:Any
Assigned to: CPU Architecture:Any

[17 May 2022 9:06] Saverio Miroddi
Description:
Technically, this is a duplicate of the closed report https://bugs.mysql.com/bug.php?id=95850, however, I think that something's still wrong.

Specifically, the guide (https://dev.mysql.com/doc/refman/8.0/en/explain-for-connection.html) states:

> If you have the PROCESS privilege, you can specify the identifier for any connection. Otherwise, you can specify the identifier only for your own connections. In all cases, you must have sufficient privileges to explain the query on the specified connection.

but based on my tests, the PROCESS privilege is not enough - SUPER is necessary.

While it's not possible to know from a user perspective if this is a documentation or implementation problem, I think it's the latter, the SUPER privilege should not be necessary to explain other threads' queries.

This issue is particularly problematic on cloud providers like AWS, where it's not possible to have the SUPER privilege, therefore, preventing some forms of monitoring/analysis.

How to repeat:
This is the simplest possible test case; additionally, due to using SLEEP(), it sidesteps the requirement for the observing user to have schema-related permissions.

-- ----------------------------------------------------------------

mysql (super)> CREATE USER observing_user@'%' IDENTIFIED BY 'foo';
mysql (super)> GRANT ALL ON *.* TO observing_user@'%';
mysql (super)> REVOKE SUPER ON *.* FROM observing_user@'%';

/*
The guide (https://dev.mysql.com/doc/refman/8.0/en/explain-for-connection.html) states:

> If you have the PROCESS privilege, you can specify the identifier for any connection. Otherwise, you can specify the identifier only for your own connections. In all cases, you must have sufficient privileges to explain the query on the specified connection.

Below one can see that this user has PROCESS privilege; additionally, since the query to explain is a simple SLEEP(), not special privileges should be required.
*/

mysql (super)> SHOW GRANTS FOR observing_user@'%'\G
/*
Grants for observing_user@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `observing_user`@`%`
*************************** 2. row ***************************
Grants for observing_user@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `observing_user`@`%`
*/

mysql (super)> CREATE USER observed_user@'%' IDENTIFIED BY 'foo';

# Launch query in background, and gather the thread id
#
$ mysql -uobserved_user -pfoo -e 'SELECT SLEEP(600)' &
$ sleep_query_id=$(mysql -uobserved_user -pfoo -e 'SHOW PROCESSLIST' | grep SLEEP | awk '{print $1}')

# Can't EXPLAIN...
#
$ mysql -uobserving_user -pfoo -e "EXPLAIN FOR CONNECTION $sleep_query_id"
# ERROR 1045 (28000) at line 1: Access denied for user 'observing_user'@'%' (using password: YES)

# ... but can KILL (!).
#
$ mysql -uobserving_user -pfoo -e "KILL $sleep_query_id"
$ mysql -uobserving_user -pfoo -e "SHOW PROCESSLIST"
/*
+----+-----------------------+-----------+------+---------+------+------------------------+------------------+
| Id | User                  | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------------+-----------+------+---------+------+------------------------+------------------+
|  5 | event_scheduler       | localhost | NULL | Daemon  |  706 | Waiting on empty queue | NULL             |
| 47 | observing_user        | localhost | NULL | Query   |    0 | init                   | SHOW PROCESSLIST |
+----+-----------------------+-----------+------+---------+------+------------------------+------------------+
*/

# Now try with SUPER permissions
#
mysql (super)> GRANT SUPER ON *.* TO observing_user@'%';

# Works now.
#
$ mysql -uobserved_user -pfoo -e 'SELECT SLEEP(600)' &
$ sleep_query_id=$(mysql -uobserved_user -pfoo -e 'SHOW PROCESSLIST' | grep SLEEP | awk '{print $1}')
$ mysql -uobserving_user -pfoo -e "EXPLAIN FOR CONNECTION $sleep_query_id"
/*
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
*/

Suggested fix:
Based on my understanding, users with the PROCESS privilege, should be able to EXPLAIN FOR CONNECTION of any user (with the assumption that they have the required schema-related permissions as well).
[17 May 2022 13:21] MySQL Verification Team
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Also, we should point out that our documentation was properly updated to point out what privileges are required for EXPLAIN CONNECTION. That is how we designed the privilege system for this operation and that is how it will remain. This point was discussed in Development and that is a final conclusion.

Thank you for your interest in MySQL.
[17 May 2022 13:48] Saverio Miroddi
Hello,

If there has has been a discussion, I don't argue with that, but the documentation that I've reported is either insufficient or wrong.

Specifically, this is what it says (https://dev.mysql.com/doc/refman/8.0/en/explain-for-connection.html):

> (1) If you have the PROCESS privilege, you can specify the identifier for any connection [...] (2) you must have sufficient privileges to explain the query on the specified connection. 

In the test case provided, the observer user does have PROCESS privilege, which satisfies point (1).

The point (2) doesn't provide any specific information. The phrasing hints that some extra privilege is needed, but the query is a `SELECT SLEEP()`, which doesn't access any table, and additionally, in my test case, the only privilege remaining is SUPER.

Updating the documentation to say something like:

> in order to explain the query of another user's connection, the SUPER privilege is required

would be simple and unambigous.
[17 May 2022 13:51] MySQL Verification Team
Hi,

Your observations have been forwarded to our Documentation department.
[17 May 2022 13:52] MySQL Verification Team
Thank you for your contribution.