Is it possible to grant metadata permissions exclusively in MySQL?

Question

TLDR；

Target:

Is it possible to grant an "audit" user access to only the MySQL metadata (at least the schema, tables, columns) and not the exact data in the table?

Version

MySQL 8

try:

Before this question,

I have tried or know:

• Check out the official MySQL documentation on permissions (please let me know if I missed an answer there)
Search keywords on
• SOF: mysql, privilege, metadata, etc.
• Finding solutions with my DBA friends
• Grant user showdatabases permission, but can only get the schema list
• As we all know, all funding for infomatica_schema is wasted
• SELECT ON *.* is another answer, but my leader doesn't want to leak data through it

background:

My company orders devops to collect MySQL metadata for auditing or security monitoring or other issues (I don't know the details of the whole story). My leadership anticipates that unnecessary data breaches will not occur. BTW, I don't know the specific method by which they (maybe the audit department) will collect the metadata. All I have to do is create an authorized user for them.

Answer 1

I think I found a solution to this problem, but it's just a hack and not a real solution.

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

That is, you cannot use SHOW TABLES, nor view the table in a query against INFORMATION_SCHEMA (because SHOW TABLES is really only a query against these system views).

But the language of “no privilege” got me thinking. Are there permissions that a user can have but are not allowed to read or write data?

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

SHOW VIEW How about it? This only allows viewing metadata, not querying tables or views.

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:

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 can also view columns etc.

To be clear, these are specific tables, not views. But since my auditor user doesn't have any permissions on the table (not even irrelevant permissions), it serves the purpose of letting them see metadata about the table.

In MySQL 8.0.20, they added the SHOW ROUTINES permission. Prior to this, you required SELECT permission to view the body of a stored procedure or function. But you didn't mention the auditor viewing routine in your question.