3

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?

  • This has been asked on Stack Overflow Here stackoverflow.com/questions/24493288/… – davidgarrison Aug 7 '15 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. – Michael - sqlbot Aug 11 '15 at 3:20
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 ;
  • 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. – codemonkee Aug 7 '15 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. – codemonkee Aug 7 '15 at 19:54

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.