Meaning of Execute_priv on mysql.db table

Question

I created user 'restriceduser' on my mysql server that is 'locked down'. The mysql.user table has a N for all priveledges for that account. The mysql.db table has Y for only Select, Insert, Update, Delete, Create, Drop; all other privileges are N for that account. I tried to create a stored procedure and then grant him access to run only that procedure, no others, but it does not work.

The user receives: Error: execute command denied to user 'restricteduser'@'%' for routine 'mydb.functionname'

The stored procedure:

CREATE DEFINER = 'restriceduser'@'%' FUNCTION `functionname`(sIn MEDIUMTEXT, sformat MEDIUMTEXT)
RETURNS int(11)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
....
END;

The grant statement I tried:

GRANT EXECUTE ON PROCEDURE mydb.functionname TO 'restricteduser'@'%';

I was able to work around by modifying his mysql.db entry with

update mysql.db set execute_priv='Y' where user='restricteduser'

This seems to be more then I want, because it opens up permissions for him to run any stored procedure in that database, while I only wanted him to have permissions to run the designated function.

Does anyone see where my issue may lie?

Answer 1

The user table for the restricted user that you have created will need execute_priv = 'Y'. This grant supersedes the db grant.