Bug #100003 EXPLAIN FOR CONNECTION seems to still require the SUPER privilege

• Submitted: 26 Jun 2020 11:18
• Modified: 29 Jun 2020 8:26
• Reporter: Saverio Miroddi
• Status: Verified
• Category: MySQL Server: Security: Privileges
• Severity: S3 (Non-critical)
• Version: 8.0.20, 5.7.30
• OS: Any
• CPU Architecture: Any

[26 Jun 2020 11:18] Saverio Miroddi

Description:
I'm experiencing a different behavior (as if it was <= 5.7) from what the manpage says about EXPLAIN FOR CONNECTION:

> EXPLAIN requires the same privileges required to execute the explained statement. Additionally, EXPLAIN also requires the SHOW VIEW privilege for any explained view. EXPLAIN ... FOR CONNECTION also requires the PROCESS privilege if the specified connection belongs to a different user.

Specifically, when I try to EXPLAIN a trivial 'SELECT SLEEP(...)' query run by another, the current user still needs the SUPER privilege in order to run the EXPLAIN successfully.

How to repeat:
Run the following in a Bash shell. It requires that a `mysql` shell command without user, runs with `root` user, who has all privileges, including SUPER.

The script can be copy/pasted and executed, although, it's better to execute it in pieces, in order to avoid race conditions.

```sh
####################################################################
# Setup and double checks
####################################################################

mysql -e '
  DROP USER IF EXISTS myuser@localhost;
  DROP USER IF EXISTS myadmin@localhost;

  CREATE USER myuser@localhost;
  CREATE USER myadmin@localhost;

  GRANT
    ALL
    ON *.*
    TO myadmin@localhost
    WITH GRANT OPTION
  ;

  GRANT
    PROXY
    ON ""@""
    TO myadmin@localhost
    WITH GRANT OPTION
  ;
'

# Double check that there are no differences between `root` and `myadmin`.
#
diff <(mysql -e 'SHOW GRANTS FOR myadmin@localhost\G') <(mysql -e 'SHOW GRANTS FOR root@localhost\G' | sed 's/root/myadmin/g')

# Double check that `myadmin` has the PROCESS privilege.
#
mysql -e 'SHOW GRANTS FOR myadmin@localhost' | perl -lne 'print "$1 ... $2" if /(GRANT ).*(, PROCESS,)/'

# Double check that `myadmin` can run the same query that it's going to EXPLAIN.
#
mysql -umyadmin -e 'SELECT SLEEP(0)'

####################################################################
# Prepare query [information]
####################################################################

mysql -umyuser -e 'SELECT SLEEP(3600)' &

sleep_thread_id=$(mysql -e 'SHOW PROCESSLIST' | perl -lne 'print $1 if /(\d+).*SELECT SLEEP/' | tail -n 1)

####################################################################
# Test
####################################################################

mysql -umyadmin -e "EXPLAIN FOR connection $sleep_thread_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 |
# +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

mysql -e "REVOKE SUPER ON *.* FROM myadmin@localhost"

mysql -umyadmin -e "EXPLAIN FOR connection $sleep_thread_id"

# ERROR 1045 (28000) at line 1: Access denied for user 'myadmin'@'localhost' (using password: NO)
```

[26 Jun 2020 11:29] Saverio Miroddi

There's a minor omission.

The command:

```sh
mysql -umyadmin -e 'SELECT SLEEP(0)'
```

will display:

```
GRANT  ... , PROCESS,
```

[29 Jun 2020 8:26] MySQL Verification Team

Hello Saverio Miroddi,

Thank you for the report and test case.
Observed identical behavior on 5.7.30 and 8.0.20.

regards,
Umesh