4

I am attempting to grant a user execute privileges to a single stored procedure without any other privileges in a MySQL 5.1.45 instance.

The stored procedure is defined with SQL SECURITY as DEFINER.

As a user with permission to create a stored procedure

USE dataDB; DROP PROCEDURE IF EXISTS fetchData; DELIMITER // CREATE DEFINER=`billy`@`%` PROCEDURE `fetchData`(IN `id` INT UNSIGNED) LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER COMMENT 'Accepts an ID and returns the record' BEGIN SELECT T.id, T.name FROM table AS T WHERE T.id = id; END; // DELIMITER ; ######## GRANT EXECUTE ON PROCEDURE `dataDB`.fetchData TO 'otherUser'@'%'; FLUSH PRIVILEGES; ########

As 'otherUser'@'%'

USE dataDB; /* SQL Error (1044): Access denied for user 'kny_opea'@'%' to database 'kny_mint_pre' */ CALL dataDB.fetchData(3); /* SQL Error (1370): execute command denied to user 'otherUser'@'%' for routine 'dataDB.fetchData' */

I then granted the following

GRANT EXECUTE ON `dataDB`.* TO 'otherUser'@'%';

But this becomes an issue as the user then has execute permissions to any other existing procedure and any future procedures within dataDB.

How do I grant execute permission to a single procedure to the invoker without granting execute privilege to all procedures or another privilege that would allow the user to USE the database?

Improve this question
2
  • This has been asked on Stack Overflow Here stackoverflow.com/questions/24493288/… Commented Aug 7, 2015 at 18:17
  • Without debating the merits of this solution, for a moment, does GRANT USAGE ON dataDB.* TO 'otherUser'@'%'; coupled with granting execute on that singular procedure not do what you want? The "usage" permission typically means "grant nothing in particular, but create a placeholder here for more specific permissions to cascade under." It seems unlikely that a procedure could have access effectively granted, if you don't have permission to see the existence of the database the procedure is in. Commented Aug 11, 2015 at 3:20

1 Answer 1

Reset to default
1

SUGGESTION #1

Check the user in the Stored Procedure and leave if it is not the right user

DELIMITER // CREATE DEFINER=`billy`@`%` PROCEDURE `fetchData`(IN `id` INT UNSIGNED) LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER COMMENT 'Accepts an ID and returns the record' ThisStoredProcedure:BEGIN SET @cur_user = CURRENT_USER(); IF LEFT(@cur_user,LOCATE('@',@cur_user) - 1) = 'otherUser' THEN LEAVE ThisStoredProcedure; END IF: SELECT T.id, T.name FROM table AS T WHERE T.id = id; END; // DELIMITER ;

SUGGESTION #2

Create New Proc with SQL Security to Invoker and change grants of otherUser to match

DELIMITER // CREATE DEFINER=`billy`@`%` PROCEDURE `fetchDataForOthers`(IN `id` INT UNSIGNED) LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA SQL SECURITY INVOKER COMMENT 'Accepts an ID and returns the record' BEGIN SELECT T.id, T.name FROM table AS T WHERE T.id = id; END; // DELIMITER ;
Improve this answer
2
  • Suggestion #1 doesn't seem sensible to me, it would require granting execute permission to all procedures within the DB and then modifying each stored procedure with that logic plus any other users you wanted to prevent from running it. Commented Aug 7, 2015 at 19:53
  • Suggestion #2 would require me to grant select to the invoker anyways on the table being selected which would allow them to USE the database anyways which then I could just leave it as the DEFINER. Thank you for the feedback. Commented Aug 7, 2015 at 19:54

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.