Non Super User cannot alter stored procedures and functions

Question

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

SHOW GRANTS FOR 'userA'@'%'
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))
  MODIFIES SQL DATA
BEGIN
INSERT INTO 
 emaillistcontacts
(
 EMAILLIST_ID,
 CUST_ID,
 CUSTCNT_ID
) 
VALUES (
 EmailListID,
 CustID,
 ContactID
);
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)

• iLikeMySql: Did the super user build the procedures and functions? If yes, there might be an issue with sql security.
• iLikeMySql: Using the admin user to change the definer of the procedures in question to the user you want should solve the problem
• Shawn: 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: 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?
• iLikeMySql: 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

Answer 1

Perhaps you want this in the CREATE:

SQL SECURITY INVOKER

or, more likely,

SQL SECURITY DEFINER

• Shawn: 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?