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

mysql mysql-error-1044
Charles picture Charles · Oct 22, 2010 · Viewed 20k times · Source

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

Cfreak picture Cfreak · Oct 22, 2010

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

Related questions

Mysql password hashing method old vs new

I'm trying to connect to a mysql server at dreamhost from a php scrip located in a server at slicehost (two different hosting companies). I need to do this so I can transfer new data at slicehost to dreamhost. Using …

Read More
How can I run with root@localhost privileges in MySQL?

I'm trying to run a GRANT ALL statement: GRANT ALL ON daisyrepository.* TO daisy@localhost IDENTIFIED BY 'password_here'; I've ran this statement on the server itself using Remote Desktop using: PHPMyAdmin MySQL Administrator Command line client Everytime I run …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More