Thursday, June 4, 2009

MySQL grant privileges on database for any host - Windows vs. Linux


Usually it's not a good idea to grant privileges on a MySQL database for any host, but this option is very useful during the development process. I often get called by a QA person or another developer and need to look into his\her's db. In order to investigate the problem I some times need to debug my local server while it's using his\her's DB as a data source, in these cases allowing access to the db from any host is extremely useful. I recently found out that the grant privileges for host syntax may be misleading when using Linux.

On Windows the following line would allow the user to access the db from any host:

grant all on dbname.* to 'dbuser'@'%' identified by 'dbpassword';

While on Linux (or at least on my Red Hat) This would allow any host but localhost to access the DB(!). that's why it's important to specifically assign privileges for localhost in addition to privileges for any host:

grant all on dbname.* to 'dbuser'@'%' identified by 'dbpassword';
grant all on dbname.* to 'dbuser'@'localhost' identified by 'dbpassword';

And there you go...the Linux DB will be accessible from any machine including localhost.

Update: Evans points out that there might be configuration issues to consider on Linux

3 comments:

Evans said...

This is a very tricky issue - I said tricky because you'd expect access from remote location to any installation of MySQL. I don't know about Windows (I don't use it!) but on Linux, you'd have to first enable it in the config file before anyone can remotely access it.

See here for how to configure it: http://www.jroller.com/evans/entry/remote_connection_problem_to_mysql

Lior Boord said...

Thanks Evans, just added that to the post.

Anonymous said...

Yo
if you delete the blank usernames from mysql.user you will not have this issue.

delete from mysql.user where user='';
flush privileges;

peace