Is it possible to grant only metadata privileges in MySQL

Question

TLDR;

Target:

Is it possible to grant privileges to a certain "audit" user to access to MySQL metadata only (schema,table,column at least) without access to exact data in tables?

Version

MySQL 8+

Try:

Before this issues,

I've been already tried or known:

• review MySQL official docs on privileges (notice me if I missed the answer in it)
• search keywords on SOF: mysql, privilege, metadata, etc.
• find solutions with my DBA friends
• grant show databases to users, but it could get the schema lists only
• all grants to infomatica_schema was in vain, as known to all
• SELECT ON *.* is another answer, but my leader dont wanna data leak through it

Background:

My company ordered devops to collect MySQL metadata for some issues of audits or security monitoring or else (I don't know the details of whole story). Unnecessary data leak would not be expected to my leader. BTW, I dont know the specific method where they, audit depts maybe, are going to collect metadata. All I've been required to do is to create a granted user for them.

Answer 1

I think I found a workaround for this, but it's kind of a hack, not a true solution.

https://dev.mysql.com/doc/refman/8.0/en/show-tables.html says:

If you have no privileges for a base table or view, it does not show up in the output from SHOW TABLES or mysqlshow db_name.

That is, you can't use SHOW TABLES, or see the tables in queries against INFORMATION_SCHEMA (because SHOW TABLES is really just a query against those system views).

But the language of "no privileges" got me thinking. Is there a privilege that the user could have, but which does not allow reading or writing data?

https://dev.mysql.com/doc/refman/8.0/en/grant.html says:

The permissible priv_type values at the table level are ALTER, CREATE VIEW, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, and UPDATE.

How about SHOW VIEW? This would only allow viewing metadata, not querying a table or a view.

So I tried it:

mysql> create user 'auditor'@'%';
mysql> grant show view on test.* to 'auditor'@'%';

Then I logged in as that user, and tried it:

mysql> show grants;
+----------------------------------------------+
| Grants for auditor@%                         |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `auditor`@`%`          |
| GRANT SHOW VIEW ON `test`.* TO `auditor`@`%` |
+----------------------------------------------+

mysql> use test

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| Accounts       |
| Bugs           |
| BugsProducts   |
| BugStatus      |
...

I could also view columns, etc.

To be clear, those are concrete tables, not views. But since my auditor user has more than no privileges on the tables (even an irrelevant privilege), it qualifies for purposes of letting them view metadata about the tables.

In MySQL 8.0.20, they added the SHOW ROUTINES privilege. Prior to that, you needed SELECT privilege to view the body of stored procedure or functions. But you didn't mention auditors viewing routines in your question.