GRANT vs. privileges on mysql table

[Shannon Carey]

I have a user with GRANT OPTION on a table.

+-----------------------------------------------------------------------------+
| Grants for rcscarey@localhost |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rcscarey'@'localhost' |
| GRANT ALL PRIVILEGES ON `gtd`.* TO 'rcscarey'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------+

I try to use that user to GRANT SELECT to another user on that table, and get this:

mysql> GRANT SELECT ON `gtd`.* TO 'meow'@'localhost' IDENTIFIED BY 'meow';
ERROR 1044 (42000): Access denied for user 'rcscarey'@'localhost' to database 'm
ysql'

So then I use the root account to give access to the mysql database:
mysql> GRANT INSERT, UPDATE ON `mysql`.* TO 'rcscarey'@'%';
Query OK, 0 rows affected (0.05 sec)

And then my other user can grant as it should:
mysql> GRANT SELECT ON `gtd`.* TO 'meow'@'localhost' IDENTIFIED BY 'meow';
Query OK, 0 rows affected (0.00 sec)

And it can't grant on tables it doesn't have GRANT OPTION for:
mysql> GRANT SELECT ON `eval`.* TO 'meow'@'localhost' IDENTIFIED BY 'meow';
ERROR 1044 (42000): Access denied for user 'rcscarey'@'localhost' to database 'e
val'

However, it can still INSERT INTO mysql.db:
mysql> use mysql;
Database changed
mysql> INSERT INTO db (Host, Db, User, Select_priv) VALUES ('localhost', 'eval',
'meow', 'Y');
Query OK, 1 row affected (0.00 sec)

Is the GRANT system completely broken? Does this mean I cannot restrict users to creating only users with same or lesser security? As soon as I give INSERT ON mysql.db privileges to a user, they can go wild! What's the deal? I should be able to GRANT WITH GRANT OPTION to a user, and they should be able to GRANT without having access to the mysql table!



Edited 1 time(s). Last edit at 01/30/2007 11:06AM by Shannon Carey.