MySQL permissions -- can't create functions even with the 'CREATE ROUTINE' grant

Question

When connecting to my server (from a different machine) I get

Error Code: 1044 Access denied for user 'username'@'%' to database 'dbname'

when I try to create a function. But when I look at my permissions

SHOW GRANTS FOR CURRENT_USER;

I get

'GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE ROUTINE ON *.* TO ''username''@''%'' IDENTIFIED BY PASSWORD ''--stripped--'' WITH GRANT OPTION'

In particular, this includes CREATE ROUTINE. Why can't I make a function? How can I change it so I can?

Answer 1

I think there is a CREATE FUNCTION that is separate from CREATE ROUTINE. But either way, since it looks like your user has 100% full access anyway you could do:

GRANT ALL PRIVILEGES ON *.* TO user@'%' INDENTIFIED BY 'password' WITH GRANT OPTION

However I would note it would be much better to set the '%' to 'localhost' and only access the database in this manner from a local machine (or at least a trusted IP). The lack of security with this could cause you trouble.

Definitely don't use this user/password to connect to the database from a web script!

Edit I forgot: routines and functions have to be granted globally. Adding . tries to add the grant to the tables themselves which is why it doesn't work. Try:

GRANT ALTER ROUTINE,CREATE ROUTINE, EXECUTE ON * TO user@'%' IDENTIFIED BY 'password'

There's a longer description of it here: http://dev.mysql.com/doc/refman/5.0/en/grant.html