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?
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.