Bug #30395 SHOW PROCESSLIST strange results after REVOKE PROCESS ON *.* FROM ...
Submitted: 13 Aug 2007 19:10 Modified: 13 Aug 2007 19:29
Reporter: Matthias Leich Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[13 Aug 2007 19:10] Matthias Leich
Description:
Snip from my test protocol:
----------------------------------------------------------
CREATE USER otto@'localhost';
GRANT PROCESS ON *.* TO otto@'localhost';
GRANT PROCESS ON *.* TO ''@'localhost';
---- Establish connection otto (user=otto) ----
---- Establish connection anonymous (user=''=<anonymous>) ----
SHOW GRANTS;
Grants for @localhost
GRANT PROCESS ON *.* TO ''@'localhost'
SHOW processlist;
Id User Host      ...
1  root localhost ...
2  otto localhost ...
3       localhost ...
SELECT * FROM information_schema.processlist;
<result fits to SHOW processlist output above>
---- Switch to connection otto ----
SHOW GRANTS;
Grants for @localhost
GRANT PROCESS ON *.* TO ''@'localhost'
SHOW processlist;
Id User	Host      ...
1  root	localhost ...
2  otto	localhost ...
3       localhost ...
SELECT * FROM information_schema.processlist;
<result fits to SHOW processlist output above>
---- Switch to connection default ----
REVOKE PROCESS ON *.* FROM otto@'localhost';
REVOKE PROCESS ON *.* FROM ''@'localhost';
---- Close connections otto + anonymous and establish them again ----
---- Establish connection otto (user=otto) ----
SHOW GRANTS;
Grants for otto@localhost
GRANT USAGE ON *.* TO 'otto'@'localhost'
SHOW processlist;
Id User Host      ...
4  otto localhost ...
SELECT * FROM information_schema.processlist;
ID USER HOST	DB	COMMAND	TIME	STATE	INFO
4  otto localhost	test	Query	0	preparing	SELECT * FROM information_schema.processlist
---- Establish connection anonymous (user=''=<anonymous>) ----
SHOW GRANTS;
Grants for @localhost
GRANT USAGE ON *.* TO ''@'localhost'
SHOW processlist;
ERROR 42000: Access denied; you need the PROCESS
             privilege for this operation
  <------ What's this ?
  1. The manual says:
     PROCESS Enables the user to see !all! processes
             with SHOW PROCESSLIST
     It does not say that somebody needs this privilege
     for seeing his own process.
  2. The user otto has also no processlist privilege
     but can run SHOW PROCESSLIST with success.
  3. Somebody might say that the anonymous user gets
     some however different privileges than my user
     otto. But than the next statement must also fail
     like "SHOW PROCESSLIST".
SELECT * FROM information_schema.processlist;
ID USER HOST      ...
5       localhost ...

My environment:
- mysql-5.1-build 
  last Changeset ChangeSet@1.2563, 2007-08-08
  compile-pentium-debug-max
- Linux OpenSuSE 10.2 (x86-64)
- Intel Core2Duo (x86 64 Bit)

How to repeat:
Please use the attached file ml011.test.
[13 Aug 2007 19:29] Matthias Leich
testscript for replaying the bug

Attachment: ml011.test (application/octet-stream, text), 1.42 KiB.