MySQL restrict specific login to single ip address

Question

i am trying to setup a mysql server with some logins that can be accessed from anywhere, and another login ( effectively root access ) from a single machine with a static ip. both machines have a single WAN address and are located in the same hosted server facility. the only firewall present is the one built into the operating system.

Host ( 81.1.1.1 / SERVER-4XP5PS5 / mysql_win64 5.5 / svr2012r2 ):

firewall port translation :

netsh interface portproxy add v4tov4 listenport=5001 connectport=3306 connectaddress=81.1.1.1 protocol=tcp
Advanced Firewall Rules->Add inbound open port 'MySQL5001' / port 5001 / tcp / All connection types

mysql remote login :

create user 'testuser'@'82.2.2.2' identified by 'testpassword';
grant all on *.* to 'testuser'@'82.2.2.2';

Client ( 82.2.2.2 / mysql_win32 5.5 / svr2012r2 ):

mysql.exe -h81.1.1.1 -P5001 -utestuser -ptestpassword
ERROR 1045 (28000): Access denied for user 'testuser'@'SERVER-4XP5PS5' (using password: YES)

it seems the mysql server on the host thinks the connection is coming from the local machine, but it is not, i am running the mysql connect command from the client machine.

any ideas how to get mysql to get testuser to connect through port 5001 while restricting testuser connections to ip 82.2.2.2 ?

note : it works fine if i lose the ip address requirement and replace 82.2.2.2 with %, but that defeats the purpose of what i am trying to achieve.

• Barny: skip-name-resolve is off, so i guess it's not a dns cache messing things up.

Answer 1

Even when your computer has a static IP address - say: 81.1.1.1 - that doesn't mean that on the network your computer is recognized as 81.1.1.1 when a connection is attempted.

The address your computer has - when connecting to computers on the internet - uses what's called a "NAT Gateway address".

So... login to 81.1.1.1 and if it's a windows machine browse: http://whatismyipaddress.com

That's your NAT Gateway IP address.

If you have access only to Powershell on 81.1.1.1 - instead of browsing - you can use curl instead.

curl http://bot.whatismyipaddress.com

Using curl is pretty much your only choice if you're on Linux.

I think your workaround succeeded because mysql command line read the command line argument and passed on validating the real incoming IP address. It used 81.1.1.1 as a mock.

Ah, you'd be a lot better off if you weren't using a static IP at all. If you want help with that post a question like "How to configure your workstation as a fully qualified host on your network".

The answer to your question. How to restrict access to a single IP... You have to set up a VPN and only allow requests from known NAT Gateways. Don't allow NAT Gateways from all of Comcast - otherwise anyone can still get in. Sometimes to get around that you allow authorized users to connect to your network. And then allow connections from the NAT Gateway from there.

Answer 2

Thanks for the contributions - although i have discovered a work around. Instead of using windows firewall to do port translation, i can just change my.ini and set my desired port. and instead of creating a user i just create the grant :

grant all on *.* to 'testuser'@'82.2.2.2' identified by 'testpassword';