MySQL Bugs: #49949: A user that has "GRANT ALL PRIVILEGES" gets a "command denied to user"

Bug #49949	A user that has "GRANT ALL PRIVILEGES" gets a "command denied to user"
Submitted:	28 Dec 2009 6:38	Modified:	28 Dec 2009 7:36
Reporter:	Daniel Ore	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	5.1.41	OS:	Linux (Fedora 12)
Assigned to:		CPU Architecture:	Any

Description:
A user that is supposed to have all privileges but then gets a "command denied to user" for ALTER and INDEX

This happened on Fedora Core 12 but not on Ubuntu 9.10.
I have NO idea why.

How to repeat:
as root, create a user like this:

CREATE USER 'valire'@'%' IDENTIFIED BY 'valire';
GRANT ALL PRIVILEGES ON *.* TO 'valire'@'%' WITH GRANT OPTION;

mysql> show grants for valire@'%';
+---------------------------------------------------------------------------------------+
| Grants for valire@%                                                                   |
+---------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'valire'@'%' IDENTIFIED BY PASSWORD '4d4bf4e974831b43' |
+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Login to mysql:
mysql - u valire -p

run the following:

CREATE DATABASE VM DEFAULT CHARSET utf8;

CREATE TABLE VM.RULE_DEF (
		ID INTEGER NOT NULL,
		DEFINITION_XML TEXT,
		NAME VARCHAR(255)
	)TYPE=INNODB;

now, try to run:
mysql> ALTER TABLE VM.RULE_DEF ADD CONSTRAINT RULE_DEF_PK PRIMARY KEY (ID);
ERROR 1142 (42000): ALTER command denied to user 'valire'@'localhost' for table 'RULE_DEF'

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You add privileges to user  'valire'@'%', but connect as  'valire'@'localhost'

See http://dev.mysql.com/doc/refman/5.1/en/invoking-programs.html for details