给用户赋予 information_schema 数据库对象权限失败

鲜于允晨

2023-12-01

1.数据库版本

Mysql 所有版本

2.问题描述

2.1 问题发现

在对用户赋予 information_schema 权限的时候发如下错误：

[root@127.0.0.1][test_chen]> grant select on information_schema.innodb_sys_tables to 'test_user'@'%';
ERROR 1044 (42000): Access denied for user 'root'@'127.0.0.1' to database 'information_schema'
[root@127.0.0.1][test_chen]> grant select on information_schema.* to 'test_user'@'%';
ERROR 1044 (42000): Access denied for user 'root'@'127.0.0.1' to database 'information_schema'
[root@127.0.0.1][test_chen]> 
[root@127.0.0.1][test_chen]> show grants;
+---------------------------------------------------------------------+
| Grants for root@127.0.0.1                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

[root@127.0.0.1][test_chen]> select current_user();
+----------------+
| current_user() |
+----------------+
| root@127.0.0.1 |
+----------------+
1 row in set (0.00 sec)
##这个报错有一定的误导性

2.2 问题分析

https://dev.mysql.com/doc/refman/8.0/en/information-schema-introduction.html

https://bugs.mysql.com/bug.php?id=45430

查看官方文档及bug，发现是这样的。任何用户都有information_schema 下的表或者视图的访问权限(当然有些视图或者表需要一些特殊的权限，比如说 innodb_ 前缀开头的表需要用户具有 process权限才能访问)。虽然任何用户都能访问 information_schema 下面的对象，但是能查看到哪些内容就跟这个用户的权限有关了。下面我们以访问 information_schema 下面的 tables 表来举例:

[test_user@127.0.0.1][information_schema]> show grants;
+-----------------------------------------------------------+
| Grants for test_user@%                                    |
+-----------------------------------------------------------+
| GRANT PROCESS ON *.* TO 'test_user'@'%'                   |
| GRANT EXECUTE ON `test_chen`.* TO 'test_user'@'%'         |
| GRANT SELECT ON `test_shao`.`test_xxx` TO 'test_user'@'%' |
+-----------------------------------------------------------+
3 rows in set (0.00 sec)

[test_user@127.0.0.1][information_schema]> select * from tables\G;
......
......
*************************** 61. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: information_schema
     TABLE_NAME: INNODB_SYS_TABLESTATS
     TABLE_TYPE: SYSTEM VIEW
         ENGINE: MEMORY
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: 1215
    DATA_LENGTH: 0
MAX_DATA_LENGTH: 16763355
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2020-11-27 15:54:28
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: max_rows=13808
  TABLE_COMMENT: 
61 rows in set (0.01 sec)

[test_user@127.0.0.1][information_schema]> select TABLE_SCHEMA,count(*) from information_schema.tables group by TABLE_SCHEMA;
+--------------------+----------+
| TABLE_SCHEMA       | count(*) |
+--------------------+----------+
| information_schema |       61 |
+--------------------+----------+

##用户test_user@% 在 information_schema.tables 中能查询到的记录分为两部分，第一部分就是所有information_schema下面的表或者视图(这里因为test_user@%用户只有 test_shao.test_xxx 表的读权限，且该表实际不存在，所以访问tables表只输出information_schema库下的61个表或者视图对象)
第二部能访问到的记录为，该用户具有对象权限的相关对象(这里可能有点绕，什么叫对象权限，可以在对象级别授予的都叫对象权限)，看下面的列子：

我给test_user@%用户授予 test_chen.test_chen_1 表的 create权限(这里授予任何一种对象权限都行)
grant create on  test_chen.test_chen_1 to 'test_user'@'%';
然后再次访问information_schem.tables

[test_user@127.0.0.1][information_schema]> select * from tables\G;
......
......
*************************** 61. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: information_schema
     TABLE_NAME: INNODB_SYS_TABLESTATS
     TABLE_TYPE: SYSTEM VIEW
         ENGINE: MEMORY
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: 1215
    DATA_LENGTH: 0
MAX_DATA_LENGTH: 16763355
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2020-11-27 16:05:17
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: max_rows=13808
  TABLE_COMMENT: 
*************************** 62. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test_chen
     TABLE_NAME: test_chen_1
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2020-11-27 14:48:30
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
62 rows in set (0.00 sec)

ERROR: 
No query specified

[test_user@127.0.0.1][information_schema]> select TABLE_SCHEMA,count(*) from information_schema.tables group by TABLE_SCHEMA;
+--------------------+----------+
| TABLE_SCHEMA       | count(*) |
+--------------------+----------+
| information_schema |       61 |
| test_chen          |        1 |
+--------------------+----------+

##关于具体对象权限有哪些可以参考我的另一篇博客：关于Mysql权限你真的都了解？

2.3 问题处理

任何用户都有 information_schema 下的表或者视图的访问权限(当然有些视图或者表需要一些特殊的权限，比如说 innodb_ 前缀开头的表需要用户具有 process权限才能访问 grant process on *.* to 'test_user'@'%';)。

给用户赋予 information_schema 数据库对象权限失败

相关阅读

相关文章

相关问答

相关文档