0

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?

Ben Reisner
  • 662
  • 6
  • 16

1 Answers1

0

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

Atheba
  • 9
  • 1
  • The user table definitely should not have execute_priv = 'Y'. the point is to narrow access, not to broaden it. I see a procs_priv table, which is presumably where this access should appear, and not in the user or db tables. I haven't set this sort of thing up, but the grant syntax looks plausible. I'm guessing that 'restricteduser'@'%' does not line up with the entry in the db table that was used to authenticate the users' connection? – mc0e Sep 12 '13 at 05:03