Permissions for mysql 'SHOW CREATE PROCEDURE'?

Question

I'm working on creating a 'read-only' mysql database. I want the user to be able to see everything, but not be able to modify anything. My user currently has the following grants:

GRANT USAGE ON . TO 'sqlprostudio-ro'@'%' IDENTIFIED BY PASSWORD '*x'
GRANT SELECT, EXECUTE, SHOW VIEW ON northwind.* TO 'sqlprostudio-ro'@'%'

I can see stored procedures, but if I run:

SHOW CREATE PROCEDURE x

The 'Create procedure' column returns null. Is there a way I can allow a read-only user to see the creation procedure without being able to modify it?

Answer 1

the second line in man says:

To use either statement, you must be the user named in the routine DEFINER clause or have SELECT access to the mysql.proc table. If you do not have privileges for the routine itself, the value displayed for the Create Procedure or Create Function field will be NULL.

The other option (just a suggestion) you may design a procedure or function (preferable) to show the info in question.

Regards,

Answer 2

From MySQL 8.0.20, instead of adding the global SELECT permissions (too broad), add the new SHOW_ROUTINE permissions:

GRANT SHOW_ROUTINE ON *.* TO <USER>@<LOCATION>;

Answer 3

Under MySQL 8.0 things have changed, and you have to grant global SELECT privs to the user, so you'll need to do:

GRANT SELECT ON *.* to ....

Answer 4

For modern versions use :

GRANT SELECT ON mysql.proc TO <USER>@<LOCATION>;

Answer 5

For MySQL 8, connect your database via Workbench, go to Administration -> User and Privileges, and select the user account you want to modify, then switch to "Administrative Roles", tick "SELECT" on right panel (Global Privileges), Click 'Apply' and done.