ERROR 1044 (42000): Access denied for user ‘root‘@‘localhost‘ to database ‘information_schema‘

mysql给用户授权的时候遇到个报错

mysql> grant select on INFORMATION_SCHEMA.TABLE_PRIVILEGES to usr_r@'192.168.11.11';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

在mos找到一篇文档解释

Why Setting Privileges on INFORMATION_SCHEMA does not Work (文档 ID 1941558.1)

APPLIES TO:

MySQL Server - Version 5.6 and later
Information in this document applies to any platform.

GOAL

To determine how MySQL privileges work for INFORMATION_SCHEMA.
 

SOLUTION

A simple GRANT statement would be something like:

mysql> grant select,execute on information_schema.* to 'dbadm'@'localhost';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

The error indicates that the super user does not have the privileges to change the information_schema access privileges.
Which seems to go against what is normally the case for the root account which has SUPER privileges.

The reason for this error is that the information_schema database is actually a virtual database that is built when the service is started.
It is made up of tables and views designed to keep track of the server meta-data, that is, details of all the tables, procedures etc. in the database server.
So looking specifically at the above command, there is an attempt to add SELECT and EXECUTE privileges to this specialised database.

The SELECT option is not required however, because all users have the ability to read the tables in the information_schema database, so this is redundant.（所以这个权限根本不用授就有了）

The EXECUTE option does not make sense, because you are not allowed to create procedures in this special database.

There is also no capability to modify the tables in terms of INSERT, UPDATE, DELETE etc., so privileges are hard coded instead of managed per user.