[SOLVED] Which MySql system table holds information about SHOW

Which MySql system table holds information about SHOW_ROUTINE privileges?

I would like to retrieve information from a MySql instance, which users have SHOW_ROUTINE privilege.

This privilege can be only granted globally according to the documentation. Such privileges are normally in mysql.user system table.

SELECT * FROM mysql.user;

However, I don't see this privilege in that table.

Other option would be mysql.procs_priv table, but no luck here either:

SELECT * FROM mysql.procs_priv;

I'm using MySql v8.0.36.

I'm wondering where is SHOW_ROUTINE privilege recorded.

Solution

SHOW_ROUTINE is one of the dynamic privileges stored in mysql.global_grants.

Example on my local system (8.0.39):

mysql> select * from mysql.global_grants where user = 'root' and priv = 'SHOW_ROUTINE';
+------+-----------+--------------+-------------------+
| USER | HOST      | PRIV         | WITH_GRANT_OPTION |
+------+-----------+--------------+-------------------+
| root | localhost | SHOW_ROUTINE | Y                 |
+------+-----------+--------------+-------------------+

Read https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#static-dynamic-privileges for details.