Meaning of Execute_priv on mysql.db table
- by Ben Reisner
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?
