MySQL bypasses granting information_schema objects and reports ERROR 1044 (4200) error

This question is a discussion among netizens in a WeChat group about MySQL permissions. There is such a business requirement (below is his original words):

Because many functions of MySQL rely on primary keys, I want to use the zabbix user to monitor all tables in the business database to see if primary keys have been established.

The monitoring statement is:

FROM information_schema.tables t1 
    LEFT OUTER JOIN information_schema.table_constraints t2 
          ON t1.table_schema = t2.table_schema 
            AND t1.table_name = t2.table_name 
            AND t2.constraint_name IN ( 'PRIMARY' ) 
WHERE t2.table_name IS NULL 
    AND t1.table_schema NOT IN ( 'information_schema', 'myawr', 'mysql', 
                  'performance_schema', 
                  'slowlog', 'sys', 'test' ) 
    AND t1.table_type = 'BASE TABLE'

But I don't want the zabbix user to be able to read the data in the business database. Once the zabbix user is not given permission to read the business database data, information_schema.TABLES and information_schema.TABLE_CONSTRAINTS will not contain the table information of the business database, and it will not be possible to count whether the business database table has a primary key. Is there any way to prevent Zabbix from reading the business database data while monitoring whether the business database table has no primary key established ?

First of all, we need to know a fact: the view under information_schema cannot be authorized to a certain user. As shown below

mysql> GRANT SELECT ON information_schema.TABLES TO test@'%';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

For more information about this issue, please refer to this article on mos: Why Setting Privileges on INFORMATION_SCHEMA does not Work (Document 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.

So how to solve this authorization problem? Direct authorization doesn’t work, so we can only bypass this problem and implement authorization indirectly. The idea is as follows: first create a stored procedure (user database) that finds the number of tables without primary keys, and then grant it to the test user.

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `moitor_without_primarykey`()
BEGIN
   SELECT COUNT(*) 
FROM information_schema.tables t1 
    LEFT OUTER JOIN information_schema.table_constraints t2 
          ON t1.table_schema = t2.table_schema 
            AND t1.table_name = t2.table_name 
            AND t2.constraint_name IN ( 'PRIMARY' ) 
WHERE t2.table_name IS NULL 
    AND t1.table_schema NOT IN ( 'information_schema', 'myawr', 'mysql', 
                  'performance_schema', 
                  'slowlog', 'sys', 'test' ) 
    AND t1.table_type = 'BASE TABLE';
END //
DELIMITER ;
 
 
mysql> GRANT EXECUTE ON PROCEDURE moitor_without_primarykey TO 'test'@'%';
Query OK, 0 rows affected (0.02 sec)

At this point, test can indirectly query objects under information_schema.

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| test@% |
+----------------+
1 row in set (0.00 sec)
 
mysql> call moitor_without_primarykey;
+----------+
| COUNT(*) |
+----------+
| 6 |
+----------+
1 row in set (0.02 sec)
 
Query OK, 0 rows affected (0.02 sec)

Check the permissions of the test user.

mysql> show grants for test@'%';
+-------------------------------------------------------------------------------+
| Grants for test@% |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`%` |
| GRANT EXECUTE ON PROCEDURE `zabbix`.`moitor_without_primarykey` TO `test`@`%` |
+-------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

This is the end of this article about MySQL bypassing the grant of objects in information_schema and reporting ERROR 1044 (4200). For more relevant MySQL ERROR 1044 (4200) content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!