Privileges to access information_schema.innodb* views in MySQL 8

MySQL & MariaDB
,
1

Hi everyone,

As the topic mentions, in MySQL 8(percona-server-server), users with the PROCESS privilege can access InnoDB-related views, as described in the official documentation:

The PROCESS privilege also enables use of the SHOW ENGINE statement, access to the INFORMATION_SCHEMA InnoDB tables (tables with names that begin with INNODB_), and (as of MySQL 8.0.21) access to the INFORMATION_SCHEMA.FILES table.

However, I’m seeing some unexpected behavior in version 8.0.41-32.
For example, here’s a user without the PROCESS privilege:

db:(none) > SELECT User, Host, Process_priv FROM mysql.user WHERE User LIKE 'cf75176_site';
+--------------+-----------+--------------+
| User         | Host      | Process_priv |
+--------------+-----------+--------------+
| cf75176_site | localhost | N            |
+--------------+-----------+--------------+

db:(none) > SHOW GRANTS FOR 'cf75176_site'@'localhost';
+------------------------------------------------------------------------+
| Grants for cf75176_site@localhost                                      |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `cf75176_site`@`localhost`                       |
| GRANT ALL PRIVILEGES ON `cf75176_site`.* TO `cf75176_site`@`localhost` |
+------------------------------------------------------------------------+

As expected, trying to run the following command fails:

db:cf75176_site > SHOW ENGINE INNODB STATUS;
ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

But this user can still query certain InnoDB-related views like:

db:cf75176_site > SELECT * FROM information_schema.INNODB_FOREIGN ORDER BY ID DESC LIMIT 10;

This returns rows including metadata from other users databases, which is strange. Here are a few sample results:

+--------------------------------------------------------+-------------------------------------------------+----------------------------------------+--------+------+
| ID                                                     | FOR_NAME                                        | REF_NAME                               | N_COLS | TYPE |
+--------------------------------------------------------+-------------------------------------------------+----------------------------------------+--------+------+
| warning_where/wp_mylisting_relations_ibfk_2            | warning_where/wp_mylisting_relations            | warning_where/wp_posts                 |      1 |   33 |
| warning_where/wp_mylisting_relations_ibfk_1            | warning_where/wp_mylisting_relations            | warning_where/wp_posts                 |      1 |   33 |
| warning_where/wp_mylisting_events_ibfk_1               | warning_where/wp_mylisting_events               | warning_where/wp_posts                 |      1 |   33 |
| warning_where/FK_VIEWS_LISTING_ID                      | warning_where/wp_mylisting_visits               | warning_where/wp_posts                 |      1 |   33 |
| warning_polezno/fk_transaction_order1                  | warning_polezno/wp_dlm_order_transaction        | warning_polezno/wp_dlm_order           |      1 |   48 |
| warning_polezno/fk_order_item_order1                   | warning_polezno/wp_dlm_order_item               | warning_polezno/wp_dlm_order           |      1 |   48 |
| warning_polezno/fk_order_customer_order                | warning_polezno/wp_dlm_order_customer           | warning_polezno/wp_dlm_order           |      1 |   48 |
| vrabiyvg_12/w5gii_userfeedback_survey_responses_ibfk_1 | vrabiyvg_12/w5gii_userfeedback_survey_responses | vrabiyvg_12/w5gii_userfeedback_surveys |      1 |   33 |
| schulga_weblm/vmp_pro_pro                              | schulga_weblm/vmp_products                      | schulga_weblm/vmp_products             |      1 |   33 |
| schulga_weblm/vmp_lic_pro                              | schulga_weblm/vmp_licenses                      | schulga_weblm/vmp_products             |      1 |   33 |
+--------------------------------------------------------+-------------------------------------------------+----------------------------------------+--------+------+
10 rows in set (0.04 sec)

This behavior raises a few questions:

  • Is this expected in MySQL 8.0?
  • Shouldn’t these views require PROCESS privileges? And if so show only user related data?
  • Is there any way to restrict access to these INFORMATION_SCHEMA InnoDB views for non-root or non-admin users?
    Any insights or workarounds would be appreciated. Thanks!
2

Hello @ya.italyantsev,
This behavior is expected. You cannot revoke permissions on information_schema.

Have you searched https://bugs.mysql.com/ to see if there is a feature request to restrict down permissions? You can use something like ProxySQL to firewall queries that attempt to access i_s