MySQL 5.7中root@%如何给用户授予information_schema.Tables全量查询权限

根本原因 #

• information_schema是MySQL内置的虚拟系统库，存储所有数据库对象的元数据，官方默认禁止直接对该库的任何对象执行GRANT赋权操作，所以执行grant select on information_schema.Tables to test_user;会直接报1044错误。
• information_schema.TABLES的返回结果是动态过滤的：普通用户只能看到自己至少拥有一项权限的表的元数据，新建用户默认只有极少权限，所以只能返回50条结果；root用户拥有全局所有权限，所以可以返回全部300条结果。

解决方法 #

根据实际业务需求选择对应方案即可：

方案1：允许用户查询所有业务表的实际数据 #

直接给用户授予全局SELECT权限，执行语句：

-- 注意替换test_user和对应的用户主机地址，比如'%'或者'localhost'
GRANT SELECT ON *.* TO 'test_user'@'%';
FLUSH PRIVILEGES;

授予后用户既可以查看所有表的元数据，也可以查询所有业务表的实际数据。

方案2：仅允许用户查看所有表的元数据，禁止查询业务表实际数据 #

先授予全局SELECT权限，再单独回收业务库的查询权限即可，示例语句：

-- 先授予全局SELECT权限，让用户可以看到所有表的元数据
GRANT SELECT ON *.* TO 'test_user'@'%';
-- 回收指定业务库的SELECT权限，禁止用户查询实际数据，可重复执行回收多个库的权限
REVOKE SELECT ON 业务库名.* FROM 'test_user'@'%';
FLUSH PRIVILEGES;

如果需要隐藏的库较少，也可以授予全局SELECT权限后，通过其他权限控制规则实现，或者直接给用户授予全局SHOW VIEW+SHOW DATABASES权限，也能实现查看大部分元数据的需求。

验证方法 #

使用test_user账号登录MySQL，执行以下语句验证是否返回全量结果：

SELECT COUNT(*) FROM information_schema.Tables;

内容的提问来源于stack exchange，提问作者G Cong