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 ROUTINE
- CREATE ROUTINE
Does anyone have any suggestions or can see something i am missing, or forgot?
Thanks for any help
As stated in the documentation:
To use either statement, you must be the user named in the routine
DEFINERclause or have
SELECTaccess to the
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?