0

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)

migrated from stackoverflow.com Jun 14 '18 at 16:46

This question came from our site for professional and enthusiast programmers.

  • Did the super user build the procedures and functions? If yes, there might be an issue with sql security. – iLikeMySql Jun 14 '18 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 Jun 14 '18 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 Jun 14 '18 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 Jun 14 '18 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 Jun 14 '18 at 16:21
0

Perhaps you want this in the CREATE:

SQL SECURITY INVOKER

or, more likely,

SQL SECURITY DEFINER
  • 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 Jun 22 '18 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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