MySQL Bugs: #49070: WB creating strange GRANTs

Bug #49070	WB creating strange GRANTs
Submitted:	24 Nov 2009 20:53	Modified:	3 Feb 2010 13:04
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench: Administration	Severity:	S2 (Serious)
Version:	5.2 r4753	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
While trying to figure out what the mysterious missing "Administrative Roles" were (bug #49069), I decided to click the DBA role, and see that additional privileges it granted beyond the pre-existing ALL PRIVILEGES WITH GRANT OPTION.  It turned out to add a lot of privileges on system tables:

mysql> show grants;
+-------------------------------------------------------------------------------
-------+
| Grants for root@localhost
       |
+-------------------------------------------------------------------------------
-------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
       |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`event` TO 'root'@'localhost'
       |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`db` TO 'root'@'localhost'
       |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`columns_priv` TO 'root'@'loca
lhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`func` TO 'root'@'localhost'
       |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`procs_priv` TO 'root'@'localh
ost'   |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`proc` TO 'root'@'localhost'
       |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`user` TO 'root'@'localhost'
       |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`tables_priv` TO 'root'@'local
host'  |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`host` TO 'root'@'localhost'
       |
+-------------------------------------------------------------------------------
-------+
10 rows in set (0.00 sec)

Obviously, these are excessive, but I have to wonder why such additional GRANTs are required.  Checking the DBA role box added the following psuedo-grants:

* Modify Access Control
* Modify routines  <-- inconsistent capitalization

I have no idea what access control or routine altering is envisoned here, or why this would be done directly against the mysql system tables, or why this would be a good thing.

How to repeat:
Add DBA role.

Suggested fix:
Don't grant privileges explicitly against system tables.

Why is granting specific privileges on system tables less safe than granting GLOBAL privileges for the entire system?

I think the question really needs to be; what use case requires direct manipulation of the mysql system tables?  For example, what would require direct access to the mysql.user table that the GRANT privilege cannot accomplish?  The only thing I can really think of here is backup of system tables, but these aren't part of the backup role.

We need to perform direct manipulation on the mysql user tables to be able to support security admin in
WB. GRANT OPTION alone is not enough as we need to do call DESCRIBE to view the available privs,
and directly select and update the tables in the mysql schema for some operations. It's not viable
to perform all possible changes by turning them into grant commands.