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).
