MySQL SHOW TABLES and information_schema privileges
This subject came up when writing monitoring scripts: How do you give privileges to a MySQL or MariaDB user, so it will be able to access the table metadata without accessing the data?
As a refresher, MySQL schema and table metadata is accessible in the ISO-standard set of views contained in the information_schema database.
It can also be found at a more limited level with the SHOW TABLES and SHOW TABLE STATUS commands.
Unfortunately, turns out there are no metadata privileges for such needs. The rule is the following:
Each MySQL user has the right to access these tables, 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. These restrictions do not apply for InnoDB tables; you can see them with only 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. © 2019, Oracle Corporation and/or its affiliates
That means the user must be granted some kind of privilege on the destination object, or it won’t be able to read any metadata. The issue here, is that we don’t want to give our user any kind of potentially sensitive or destructive privilege (i.e. nothing in the SELECT/INSERT/UPDATE/DELETE range).
A quick call to SHOW PRIVILEGES gives us the following overview:
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
+-------------------------+---------------------------------------+-------------------------------------------------------+
We just need to pick up a privilege in the Tables context for our monitoring user to be able to read metadata. All things considered, the References seems to be the less destructive - it only allows a user to create foreign keys on a table. Create could also be used if you don’t fear being spammed with useless tables.
To implement that we just need to grant the chosen privilege to our monitoring user. It will now be able to access object metadata in the information_schema views.
grant references on backenddb.* TO 'monitoring_user'@'%';