MySQL Bugs: #45430: The 'root' user can't grant rights on information schema

Bug #45430	The 'root' user can't grant rights on information schema
Submitted:	10 Jun 2009 13:46	Modified:	28 Sep 2010 21:26
Reporter:	Christophe Bismuth	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	5.1.34, 5.1.36-bzr	OS:	Windows (XP SP3)
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
Dear team,

I can grant rights on the information schema only from the MySQL Administrator GUI tool.

Here is the command line I'd like to execute.

Thank you,
Chris

C:\>mysql --version
mysql  Ver 14.14 Distrib 5.1.34, for Win32 (ia32)

C:\>mysql --user=root --password=password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 93
Server version: 5.1.34-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT ALL ON information_schema.* TO testuser;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

How to repeat:
Just install a MySQL server, create a new user and try to grant rights to him on the information schema ... it doesn't work for me.

I can agree that error message is misleading (verified on latest 5.1.36 from bzr), but actually you never need to grant any privilege on INFORMATION_SCHEMA. Please, read the manual (http://dev.mysql.com/doc/refman/5.1/en/information-schema.html):

"Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges will see NULL."

Thank you very much for your help, I'll update posts found in the forum.

I still think that error message is misleading, and this is the problem. It should clearly say that GRANT is just not needed.

Password can't typiped.

The server behavior is actually correct and as intended, this is not a bug.

The bottom line with GRANT is that user A can not grant privileges to user B that user A does not have.
In case of the information schema, nobody has INSERT, UPDATE, DELETE, etc privileges on any table, so root@localhost does not have the ALL privilege.

The error message given by the GRANT statement is consistent with security checks, and is correct: ALL access on information_schema.* is just denied.

Changing the error message for the GRANT statement to make special cases for the information schema is not a desirable change, as the GRANT implementation itself works as expected.

The real issue is that a user such as root@localhost who is given ALL on *.* may not in fact have the ALL privilege on information_schema.*, but this a documentation concern already covered and not a bug in the code.

Closing as not a bug