MySQL: How to Block specific hosts

[Varius]

Hi all,

I was wondering if there was any way to block specific IPs from connecting to MySQL?

For example, I want all machines on an internal network to be allowed to connect except for one.

I currently use 192.168.1.% in mysql.user table so that all hosts are allowed.

Is there a way to leave that and disallow just one host? Or must I remove that line and enter every single IP on my network one by one (except ofcourse the one I don't want to connect) ??

Thanks

[Big E]

Ugh - I don't trust mysqld security.

You could use TCP wrappers to restrict access, but even better - use iptables/ipchains to block at the packet level.

[teksonline]

mysql is not a firewall, you do not issue block statements...

If you want you can fill me in on what your database does, i think you just have no understanding on administration of mysql, you only have to add the ip's to each database, with its own set of special commands it can run, and not mysql in general anyhow, if you are not doing that, then you are doing it all wrong.

[kernelpanic]

Originally Posted by Big E

use iptables/ipchains to block at the packet level.

This is the best way here - block specific hosts which you think may cause trouble at the packet level, and then grant access to specific hosts which you want to be able to run queries. If I were you, I would avoid pattern matches/wildcards in your access allowances - just list each allowed host seperately.

Also, make sure you're on a switched network, or all this filtering is moot.

[Varius]

Originally Posted by teksonline

mysql is not a firewall, you do not issue block statements...

If you want you can fill me in on what your database does, i think you just have no understanding on administration of mysql, you only have to add the ip's to each database, with its own set of special commands it can run, and not mysql in general anyhow, if you are not doing that, then you are doing it all wrong.

Actually, I got this to work no problem, the way I wanted.

Instead of granting each host I want to have access, it was a LOT easier to just keep my wildcard host (192.168.1.%) and then add a row containing the IP I wanted to block, with the same username as the record above, a different password and no privileges.

Thus, anyone trying to connect from that IP with the same username all other servers use, will:

1) Have to find out the password somehow
2) If they do get the password, they will have no privileges on any database tables.

[Varius]

Originally Posted by kernelpanic

This is the best way here - block specific hosts which you think may cause trouble at the packet level, and then grant access to specific hosts which you want to be able to run queries. If I were you, I would avoid pattern matches/wildcards in your access allowances - just list each allowed host seperately.

Also, make sure you're on a switched network, or all this filtering is moot.

Actually the network is only internal...the cause of searching for this was mostly curiosity.

I just wanted to block one of demo servers that our team uses from accessing the real DB, incase they are testing something and have the wrong host specified

[phpslave]

unfortunately i don't think you can, if you granted access to a block of ips i think that is it with mysql. Only thing could think of is to run a sql script that would grant access to each ip, excluding the demo server or server ips that you don't want

[kernelpanic]

Originally Posted by Varius

Actually the network is only internal...the cause of searching for this was mostly curiosity.

I just wanted to block one of demo servers that our team uses from accessing the real DB, incase they are testing something and have the wrong host specified

Ah, that clears up a lot. If the network is trusted, just add a block with iptables for that one IP to prevent any confusion.

[teksonline]

please note that using .% is not a secure method, since i know now you use 192.blah.% let me show you why this is worthless..

I just added 192.168.1.mysqlhacker.com to my dns
I can now connect to your mysql with my domain and host

This is mysql security 101, you really need to read the manuals

[pxxx]

Originally Posted by teksonline

please note that using .% is not a secure method, since i know now you use 192.blah.% let me show you why this is worthless..

I just added 192.168.1.mysqlhacker.com to my dns
I can now connect to your mysql with my domain and host

This is mysql security 101, you really need to read the manuals

Ok... i am going to stay away from you....

[Varius]

Originally Posted by teksonline

please note that using .% is not a secure method, since i know now you use 192.blah.% let me show you why this is worthless..

I just added 192.168.1.mysqlhacker.com to my dns
I can now connect to your mysql with my domain and host

This is mysql security 101, you really need to read the manuals

true, but then again, you have to break into our network first, since our DBs have no external access

[teksonline]

heh, probably not too hard for me, but i have no use for another mans porn nor a desire to reak havok... dunno what rush hackers get outta that stuff.. never interested me a bit

[teksonline]

nice attack from 207.173.177.210

OrgName: Electric Lightwave Inc
OrgID: ELIX
Address: 4400 NE 77th Ave
City: Vancouver
StateProv: WA
PostalCode: 98662
Country: US

are you done giggling now, because i don't fuck around and trust me my skills are far superior than yours

[Varius]

Originally Posted by teksonline

nice attack from 207.173.177.210

OrgName: Electric Lightwave Inc
OrgID: ELIX
Address: 4400 NE 77th Ave
City: Vancouver
StateProv: WA
PostalCode: 98662
Country: US

are you done giggling now, because i don't fuck around and trust me my skills are far superior than yours

Huh? I hope you weren't aiming this post towards me, cuz if you were, I'd say I don't have a clue what you are talking about

[Big E]

Originally Posted by kernelpanic

This is the best way here - block specific hosts which you think may cause trouble at the packet level, and then grant access to specific hosts which you want to be able to run queries. If I were you, I would avoid pattern matches/wildcards in your access allowances - just list each allowed host seperately.

Also, make sure you're on a switched network, or all this filtering is moot.

FYI - being on a switched network doesn't guarentee the inability to sniff.

[phpslave]

LOL, he didn't ask about security or anything, just how to do or if you can do something in mysql, but every felt the need to flex. LOL I love it.

[kernelpanic]

Originally Posted by Big E

FYI - being on a switched network doesn't guarentee the inability to sniff.

Well, fiddling with bound MACs is beyond most people's realm of expertise

[kernelpanic]

Originally Posted by phpslave

LOL, he didn't ask about security or anything, just how to do or if you can do something in mysql, but every felt the need to flex. LOL I love it.

He didn't make his goals clear until midway through the thread. From the outset, it looked to be another security question.

[Varius]

Originally Posted by phpslave

LOL, he didn't ask about security or anything, just how to do or if you can do something in mysql, but every felt the need to flex. LOL I love it.

Thanks for understanding my question hehe

Not everything I ask is because I have to do it...sometimes its just to satisfy my curiosity or enhance my knowledge

[FrankWhite]

use ipfw to blockout ips that you dont want connecting.