Bug #95850 Incorrect privileges in documentation for "EXPLAIN FOR CONNECTION"
Submitted: 17 Jun 2019 21:38 Modified: 2 Aug 2019 14:48
Reporter: Carlos Tutte Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.7.26, 8.0.16 OS:Any
Assigned to: CPU Architecture:Any

[17 Jun 2019 21:38] Carlos Tutte
Description:
According to the official documentation (https://dev.mysql.com/doc/refman/8.0/en/explain-for-connection.html) only PROCESS privilege is needed to check other connection ID and show explain for that query.

According to the following text, PROCESS privilege is not enough:

How to repeat:
On session 1 with root user:
CREATE USER 'limited'@'%' IDENTIFIED BY 'pass';
GRANT PROCESS ON *.* TO 'limited'@'%';
FLUSH PRIVILEGES;

CREATE DATABASE test;
use test;

CREATE TABLE `joinit` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `s` varchar(64) DEFAULT NULL,
  `t` time NOT NULL,
  `g` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

INSERT INTO joinit VALUES (NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )));
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit; -- +256 rows
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit; -- +512 rows
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit; -- +1024 rows
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit; 
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit; 
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit; 
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit; -- +16k rows
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit; 
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit; 
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit; 
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit; 
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit; 
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit; -- +1M rows

SELECT * FROM joinit a, joinit b, joinit c, joinit d WHERE a.i=b.i AND b.i=c.i AND c.i=d.i;

While SELECT query is executing, execute on another session with use 'limited':
mysql -u limited -ppass
SHOW PROCESSLIST;
EXPLAIN FOR CONNECTION 8; -- use correct ID

You should get:
ERROR 1045 (28000): Access denied for user 'limited'@'%' (using password: YES)

And no EXPLAIN provided

Suggested fix:
According to my test, session using "EXPLAIN FOR CONNECTION" should have as much privileges as session executing the query being checked.

Check if all privileges are needed (or just a subset) and update documentation with needed privileges.
[18 Jun 2019 5:16] MySQL Verification Team
Hello Carlos Tutte,

Thank you for the report and test case.
Verified as described with 8.0.16 build.

Thanks,
Umesh
[2 Aug 2019 14:48] Paul DuBois
Posted by developer:
 
You are correct, the PROCESS privilege is not enough. You must also have sufficient privileges to explain the query itself. I will update the description.
[23 Sep 2022 13:05] Mikael P HOUNDEGNON GBAI
Any update on when the documentation will be updated ?