We created a user/login for some devs, and we have an issue where this user/login cannot view stored procedure code.

This is for MySQL 5.6.

I checked the GRANTS and also the information_schema (schema_privileges) and things look "good" to me.

Here are the commands I used to GRANT the database access and privileges:

GRANT SELECT, INSERT, UPDATE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `mydatabase`.*  TO 'dev-user'@'%' ;

After that, I run the FLUSH PRIVILEGES command.
And, when I run SHOW GRANTS for `dev-user` , I get the following response:

GRANT SELECT, INSERT, UPDATE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON mydatabase.* TO 'dev-user'@'%'

After that the user runs the command SHOW CREATE procedure mydatabase.sp_test and the output has NULL in the CREATE PROCEDURE Column.

This same user/login can create a new procedure and running SHOW CREATE procedure has the procedure code visible in that CREATE PROCEDURE column.

And, as a quick test, i created another user with GRANT ALL PRIVILEGES on the database and i get the same results. That user also not see the stored procedure code.

When I run a query on the information_schema.SCHEMA_PRIVILEGES system table, i can see that the user has included:

• ALTER
• ALTER ROUTINE
• CREATE
• CREATE ROUTINE

Does anyone have any suggestions or can see something i am missing, or forgot?

Thanks for any help

CodePudding user response：

As stated in the documentation:

To use either statement, you must be the user named in the routine DEFINER clause or have SELECT access to the mysql.proc table.

So grant them that access:

GRANT SELECT ON mysql.proc TO 'dev-user'@'%';

BTW, it's not necessary to use FLUSH PRIVILEGES after the GRANT statement. See MySQL: When is Flush Privileges in MySQL really needed?