More restrictive grants

[grafra]

In the README.md file the following is mentioned as Required Grants

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'XXXXXXXX' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

However this allows the user exporter to access sensitive information, too.
I think it would be more secure to reduce the SELECT to the minimum.

We succeeded to get the mysqld_exporter running with the following settings

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'XXXXXXXX' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT TO 'exporter'@'localhost';
GRANT SELECT ON performance_schema.* TO 'exporter'@'localhost';
GRANT SELECT ON information_schema.* TO 'exporter'@'localhost';

As mentioned in #242 the minimal required grants might depend on the version of the database and the mysqld_exporter used, but maybe one could provide such minimal secure configurations for the most common versions.

E.g. by adding a section like the following to README.md or placing it on a sub page

With MySQL 8.0.x and mysqld_exporter version 0.13.0 on CentOS 7 the following minimal grants work too:

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'XXXXXXXX' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT TO 'exporter'@'localhost';
GRANT SELECT ON performance_schema.* TO 'exporter'@'localhost';
GRANT SELECT ON information_schema.* TO 'exporter'@'localhost';

It was actually quite ease to find the minimal set of grants. All one has to do is check the logs of mysqld_exporter. When it could not read some information the log output indicated which table was the problem and from there one could deduct which schema one had to allow a SELECT for.

I think using such a restrictive set of grants would be beneficial for security, since it guarantees that the mysqld_exporter has no access to data in the tables.

[SuperQ]

I think it was MySQL 5.1 that had the most issue with GRANT on information_schema. I've got an idea on how we could test this easily.

[frittentheke]

On MariaDB 10.6.7 the

GRANT PROCESS, REPLICATION CLIENT TO 'exporter'@'localhost'; needs the ON *.* part actually ... otherwise it complains:

MariaDB [(none)]> GRANT PROCESS, REPLICATION CLIENT TO 'exporter'@'localhost';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TO 'exporter'@'localhost'' at line 1

and by using:
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'exporter'@'localhost'; it just works:

MariaDB [(none)]> GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'exporter'@'localhost';
Query OK, 0 rows affected (0.026 sec)

[konfusator]

Reading the documentation I doubt that GRANT PROCESS, REPLICATION CLIENT TO 'exporter'@'localhost' will work. PROCESS and REPLICATION CLIENT are global rights, and global rights are assigned via ON *.* – see https://dev.mysql.com/doc/refman/8.0/en/grant.html#grant-global-privileges or https://mariadb.com/kb/en/grant/#global-privileges. So the README should read

GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'exporter'@'localhost';
GRANT SELECT ON performance_schema.* TO 'exporter'@'localhost';
GRANT SELECT ON information_schema.* TO 'exporter'@'localhost';

This is just a documentation issue and should be fixed easily (and – in my opinion as first-time-user – quickly)

[jplitza]

On MariaDB 10.11, REPLICATION CLIENT isn't even needed, it's enough to grant SLAVE MONITOR. Also, the privileges on information_schema are automatic. So this is what we are using:

GRANT PROCESS, SLAVE MONITOR ON *.* TO 'exporter'@'localhost';
GRANT SELECT ON performance_schema.* TO 'exporter'@'localhost';

I checked that the output lines don't differ from admin users and there are no errors in the exporter log.

[ElSamhaa]

Getting the following error on MySQL 8.0.39 Community Server:

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

The docs mentions the following:

INFORMATION_SCHEMA and Privileges

For most INFORMATION_SCHEMA tables, each MySQL user has the right to access them, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA ROUTINES table), users who have insufficient privileges see NULL. Some tables have different privilege requirements; for these, the requirements are mentioned in the applicable table descriptions. For example, InnoDB tables (tables with names that begin with INNODB_) require the PROCESS privilege.
The same privileges apply to selecting information from INFORMATION_SCHEMA and viewing the same information through SHOW statements. In either case, you must have some privilege on an object to see information about it.