Bug #654 SHOW GRANTS hides real grants when grants on both column and table exist
Submitted: 13 Jun 2003 19:06 Modified: 23 Jun 2003 5:54
Reporter: Harrison Fisk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.13 OS:Any (ALL)
Assigned to: CPU Architecture:Any

[13 Jun 2003 19:06] Harrison Fisk 
Description:
The SHOW GRANTS statement hides the real grants given if the user has the same grants on the table level and a column in the table.   It shows only the column level grant, while it should show both or the table level grant. 

If the user has access to both the entire table and only a single column you get something like:
+----------------------------------------------------------------+
| Grants for newuser@localhost                                   |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'newuser'@'localhost'                    |
| GRANT SELECT (user) ON `mysql`.`user` TO 'newuser'@'localhost' |
+----------------------------------------------------------------+

While you should see the entire table grant instead, as the user can access the entire table.

How to repeat:
-- Add new user with select on an entire table
GRANT select ON mysql.user TO foo@localhost;

-- Add privileges to only a single column in the previous table
GRANT select (user) ON mysql.user TO foo@localhost;

-- Perform a SHOW GRANTS FOR statement which doesn't show correct privileges
SHOW GRANTS FOR foo@localhost;

Suggested fix:
Change SHOW GRANTS to show the higher level grant, or possibly both.
[14 Jun 2003 4:37] MySQL Verification Team 
This is not a SHOW GRANTS bug, but a GRANT bug.

Second GRANT actually revoked first GRANT. This should not be done. It should be done with REVOKE only.
[17 Jun 2003 6:20] MySQL Verification Team 
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html
[23 Jun 2003 5:54] Michael Widenius 
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

This was after all an problem in the output of SHOW GRANTS.
This fix will be in 4.0.14