What are the apropriate permissions for MySQL(dump) 8 --routines

Question

When using mysqldump with a non-root user and the --routines flag, I get the following error message:

mysqldump: <user> has insufficient privileges to SHOW CREATE FUNCTION `<function name>`!

Additionally, mysqldump hints towards the problem:

-- insufficient privileges to SHOW CREATE FUNCTION `<function name>`
-- does <user> have permissions on mysql.proc?

This seems as clear cut as it gets, until I actually tried it and found out that mysql.proc was deprecated in MySQL 8. I was not able to find the new equivalent, nor could I find out why mysqldump even suggests a deprecated table in the first place. Both my mysqld as well as my mysqldump are version 8.0.21.

Using GRANT ALL PRIVILEGES ON *.* TO <user> 'solves' the issue and allows the user to export the function, but obviously that is not a practical solution for a production system.

So what is the new GRANT or setting that specifically allows a user to SHOW CREATE FUNCTION (on a function that isn't theirs) ?

Answer 1

GRANT SHOW_ROUTINE ON *.* TO 'user'@'localhost;

https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_show-routine