Mysql – GRANT EXECUTE ON PROCEDURE unable to USE database

MySQLmysql-5.1permissionsstored-procedures

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?

Best Answer

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 ;
Related Question