0

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

bill
  • 35
  • 4
  • 1
    [SHOW CREATE PROCEDURE Statement](https://dev.mysql.com/doc/refman/5.6/en/show-create-procedure.html) 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 column is NULL. – Raymond Nijland Jul 27 '22 at 07:44
  • 1
    *"Does anyone have any suggestions or can see something i am missing, or forgot?"* Does the user also have `SELECT` on the `mysql.proc` table ??? – Raymond Nijland Jul 27 '22 at 07:45
  • hi @RaymondNijland Thanks for the comments. I actually just re-read the mysql manual for that CREATE PROCEDURE command and it pointed to the same issue. So, i granted SELECT to that user on the mysql.proc table and it worked. I will mark your response as THE ANSWER! :) Thanks! – bill Jul 27 '22 at 07:50

1 Answers1

1

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?

Barmar
  • 741,623
  • 53
  • 500
  • 612