4

I have a fresh install of mysql server 5.2.6 on a 64 bit Ubuntu running on a remote server with static IP. Is there anyway to allow Mysql remote connections to be made from pre-approved IPs only? I am looking for simple and elegant way to do this without doing too much OS related admin and just handle it with mysql.

5

You can use hostname in grant statements as well, as you are using shared ip then you can limit it with hostname specific grant such as below

grant all on db.* to 'user'@'hostname' identified by 'password';

To allow from all IP's/hosts

grant all on db.* to 'user'@'%' identified by 'password';

For localhost only

grant all on db.* to 'user'@'localhost' identified by 'password';
  • 2
    Thanks but this is not answer to my question – python man Oct 21 '14 at 14:31
  • to check for anonym user, you can try mysql -u<user>. In order to restrict ip access you need to provide ip or hostname in grant statement. you can check your hostname and grant access like that grant all on db.* to 'user'@'hostname' identified by 'password'. Give it a try – Nawaz Sohail Oct 22 '14 at 6:56
  • @pythonman did it work? – committedandroider Oct 7 at 18:26
2

Try this:

GRANT SELECT,UPDATE,INSERT,DELETE ON yourdb.* To your_user@'192.162.1.1' identified by 'password';

To allow entire intranet, or similar:

GRANT SELECT,UPDATE,INSERT,DELETE ON yourdb.* To your_user@'192.162.%' identified by 'password';

Reference: http://dev.mysql.com/doc/refman/5.5/en/grant.html

Hope this helps.

  • i did but now it is refusing connection from my IP, however i am using a shared ip but not sure if that makes any diffrences – python man Oct 21 '14 at 1:24
  • this is what i put in GRANT ALL PRIVILEGES ON . TO 'test'@'78.141.189.76' WITH GRANT OPTION; – python man Oct 21 '14 at 1:29
  • What is the error message? – Manny Calavera Oct 21 '14 at 1:36
  • Access denied for user 'test'@'78.141.189.76' (using password: YES) – python man Oct 21 '14 at 1:58
  • That means 2 possibilities. (1) wrong password. Try resetting password to known one (2) insufficient privileges. Also, do you have an anonymous user? That could have taken precedence over user you created. – Manny Calavera Oct 21 '14 at 5:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.