I am running into an issue granted a user access to alter stored procedures and functions within MySQL 5.7.22-0 running on Ubuntu.

  • User has ALL PRIV on the database except GRANT
  • User has SELECT PRIV on mysql.proc
  • User can DROP and CREATE --> they can not ALTER

Here is the output from SHOW GRANTS


GRANT USAGE ON *.* TO 'userA'@'%'   
GRANT ALL PRIVILEGES ON `mydatabase`.* TO 'userA'@'...  
GRANT SELECT ON `mysql`.`proc` TO 'userA'@'%'   

Output for SHOW CREATE PROCEDURE on a specifc pro

SHOW CREATE PROCEDURE mydatabase.spAddEmailListContact

spAddEmailListContact   NO_AUTO_VALUE_ON_ZERO   

CREATE DEFINER=`userB`@`%` PROCEDURE `spAddEmailListContact`(
        IN `EmailListID` INTEGER(11),
        IN `CustID` INTEGER(11),
        IN `ContactID` INTEGER(11))
END utf8mb4 utf8mb4_general_ci  latin1_swedish_ci   

Any thoughts on what I am missing?

Note: A key requirement is to allow SEVERAL developers to edit procedures without requiring them to be super users. (this is so simple to do in mssql :-/ it surprises me that this is difficult in mysql)

  • Did the super user build the procedures and functions? If yes, there might be an issue with sql security.
    – iLikeMySql
    Commented Jun 14, 2018 at 16:11
  • Using the admin user to change the definer of the procedures in question to the user you want should solve the problem
    – iLikeMySql
    Commented Jun 14, 2018 at 16:19
  • No it is set to a different user (the owner of the database). So in the example above the DEFINER is set to "mydatabase@%"
    – Shawn
    Commented Jun 14, 2018 at 16:20
  • Wouldn't changing the definer mean that the proc would execute under that user context? If so, how do I support multiple users for editing the database? I cannot set the DEFINE to several users, can I?
    – Shawn
    Commented Jun 14, 2018 at 16:20
  • To alter the procedure you usually have to be either admin / super user or be the user specified in the DEFINER of the stored routine
    – iLikeMySql
    Commented Jun 14, 2018 at 16:21

1 Answer 1


Perhaps you want this in the CREATE:


or, more likely,

  • that changes the security to the user that is executing it - this would mean that when the proc is used in the application it would run under a different security context for each result based on the user connecting. This would be a major change in the design. What I am struggling with is -- if a user has ALTER PROCEDURE permissions than they should be able to edit a stored proc -- otherwise what is the point of that GRANT?
    – Shawn
    Commented Jun 22, 2018 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.