MySQL restrict specific login to single ip address

MySQLwindows-server

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.

Best Answer

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

Related Solutions

Mysql – Trying to use MySQL Workbench with TCP/IP over SSH – failed to connect

I stumbled upon this question when I myself had encountered this error. I could finally figure out the configuration.

I didn't touch anything in /etc/mysql/my.cnf which already has bind_address = 127.0.0.1. So only localhost can connect.
I use OpenSSH server. So in its config file /etc/ssh/sshd_config I changed from no to yes the param responsible for TCP forwarding, thus AllowTcpForwarding yes.
Finally I have the following entered in MySQL WorkBench.
- SSH Hostname: 192.168.0.8:22 (my SSH server listens to port 22)
- SSH Username: sshuser
- SSH Key File: *C:\Users\windowsuser\.ssh\id_rsa* (should be private key, even though it says public)
- MySQL Hostname: 127.0.0.1 (this should not be changed, since MySQL server by default is bound to localhost only which I didn't change)
- MySQL Server Port: 3306 (also default)
- Username: root

The only remaining thing for you is to configure correctly your SSH server to work with keys rather than passwords. Hope this will help someone.

Windows – Cannot Connect to Oracle Database running on Windows 8 Hyper V Virtual Machine

Ultimately, this came down to a networking issue -- configuring the guest VM's network adapter (internal switch) with a static IP (this was the hard part), assigning a hosts file entry for that IP in both the Host and the Guest machines and then altering both tnsnames.ora files (host and guest) and listner.ora (guest) to use the mapped host name.

Then everything just worked.

I've pasted my internal switch config for both the host and guest and the listener and tnsnames.ora files below.

STEPS TO CONFIGURE THE GUEST VM / HOST:

Assuming that you've already created a guest VM, with a Windows Server OS, here are the networking steps...

Using Hyper-V manager, use the virtual switch manager add an "Internal" type switch
Using Hyper-V manager, open the settings dialog for the guest VM (right click on the VM). Add a network adapter using the internal switch you just created.
Here are my internal switch adapter settings for the host. Note that there is a baked in assumption here that you're outside the range of IP's that would otherwise appear on your network. I know that my home network uses this range (192.168.x.x), and I know I'm ok. Also, I know that my workplace uses 10.x.x.x for wireless, so I'm good there, too.

host internal switch settings

4 . And here's how I configured the guest VM:

guest network adapter settings

5 . I added hosts file aliases for the guest VM on both the host and the guest:

# internal vm's
192.168.0.120    brettvm

6 . Here's the listner.ora on the guest vm (database server):

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = c:\oracle

7 . Here's the tnsnames.ora on the guest VM:

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

8 . Finally, here's the tnsnames on the client (host machine):

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Bon appetit!

--Brett

Best Answer

Related Solutions

Mysql – Trying to use MySQL Workbench with TCP/IP over SSH – failed to connect

Windows – Cannot Connect to Oracle Database running on Windows 8 Hyper V Virtual Machine

Related Question