How to allow remote connections to mysql from specific IPs only?

Question

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.

score 11 · Answer 1 · edited Oct 22 '14 at 08:49

11

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';

edited Oct 22 '14 at 08:49

tombom

3,158
1
20
28

answered Oct 21 '14 at 12:36

Nawaz Sohail

1,440
3
10
25

4

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. 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 06:56
@pythonman did it work? – committedandroider Oct 07 '19 at 18:26

score 3 · Answer 2 · answered Oct 21 '14 at 00:39

3

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.

answered Oct 21 '14 at 00:39

Manny Calavera

463
3
10

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 01: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 01:29
What is the error message? – Manny Calavera Oct 21 '14 at 01:36
Access denied for user 'test'@'78.141.189.76' (using password: YES) – python man Oct 21 '14 at 01: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 05:44
How do I check for the anonym user? – python man Oct 21 '14 at 12:28
You can view the privilege of particular database for user. SELECT USER, HOST, db, select_priv, insert_priv, grant_priv FROM mysql.db – Md Haidar Ali Khan Apr 16 '15 at 11:14
@pythonman, go to 'sql prompt' and type ' show full columns from yourdatabasename.tablename' it will shows user db permission for particular table & also database. – Md Haidar Ali Khan Apr 16 '15 at 11:19
@pythonman, The user have only 'select' privilege permission for that database. – Md Haidar Ali Khan Apr 16 '15 at 11:21

How to allow remote connections to mysql from specific IPs only?

2 Answers2