How to test remote access to port 3306

[KADEN]

Securing root access remotely

The root account in MySQL is the most dangerous, since it corresponds to the DBA, ie the administrator of the database. For this reason it is necessary to pay special attention to securing that access.

So far we have specified a password for the root account and changed the user name. Assuming that the password is not easy to guess or crackable (see "Reflections on passwords), this is a first good protection. But we're not going to stop there! Indeed, we will prohibit any direct access to the MySQL server via the outside.

Completely prohibit remote access

If your MySQL server is queried and managed in local (eg Apache / PHP and MySQL on the same machine), the most secure and quickest is to simply prohibit all access from another machine.

For this, we can configure a firewall (netfilter / iptables, packet filter, ...) to block all incoming access to port MySQL, usually 3306.

However, MySQL offers an even simpler: Do not allow TCP / IP connections only via a local system process, such as a Unix socket (the famous mysql.sock), named pipes or shared memory (Windows only).

To disable the TCP / IP, you must start the server with the - skip-networking or modify the [mysqld] in the server configuration file (eg / etc / my.cnf):

[mysqld] 
skip-networking

To facilitate communication via socket, it is advisable to specify the path to the socket or in the configuration files and server MySQL:

[mysqld] 
socket = / tmp / mysql.sock 

[customer] 
socket = / tmp / mysql.sock

Now that we have blocked all remote access, we can also clean the table privileges, deleting users who could log in remotely:

Mysql.user DELETE FROM WHERE Host <> 'localhost'; 

FLUSH PRIVILEGES;

[KADEN]

Allow remote access via SSH tunnel

If you necessarily need a remote access to MySQL, there is a relatively tight security, especially valid for many client / server. This solution is to use an SSH (Secure SHell) which not only enhance the identification, but in addition will encrypt all communications between the client and MySQL server!

As its name suggests, the primary purpose of the Secure SHell is to allow secure access to a console (shell) to execute commands remotely. However, the assurance of confidentiality and integrity of data sent over the network, it also provided another feature: the tunneling.

Tunneling, also known as port forwarding (port forwarding) is to use SSH as a sub-layer whose purpose is to secure data exchange. In practice, this is to connect to an SSH server and open a local port. The local port is used to connect to (in our case, MySQL) SSH client and forwards the data to the SSH server, which forwards the same to the service.

See an example to better understand:

• The machine of the MySQL server is mysql.mondomaine.com. MySQL listens on port 3306.
• The machine is the MySQL administrator hote1.mondomaine.com. The administrator wants to connect to MySQL server remaining on this machine.
• On the MySQL server machine is installed an SSH server (eg OpenSSH) that listens on port 25.
• The administrator has installed an SSH client (eg Putty or OpenSSH on Windows) on its machine.
• The administrator connects to mysql.domaine.com on port 22 with the SSH client. In addition, it configures the client to manage a tunnel: the local port 63306 will be used to forward traffic to the MySQL server, ie to the port of mysql.domaine.com 3306.
• The administrator connects to a Client (client console, MySQL Query Browser, MySQL Administrator) at localhost: 63306. SSH client forward all traffic to the SSH server (port 22) of mysql.mondomaine.com.
• SSH server mysql.mondomaine.com to remark that it is a flow tunnel. It redirects it to the local port 3306.

[KADEN]

Given this pattern of use, we can already point out one thing: no direct connection on port 3306 of the MySQL server is established with the client. All data from the client are sent over the SSH server port. To secure our MySQL server, we can already prohibit connections from outside.

We have seen how to prohibit any TCP / IP connection with the skip-networking option. However, it is not appropriate in the case of an SSH tunnel. Indeed, the SSH server must be able to redirect the data to the MySQL server, and for that there is no alternative to TCP / IP. It is therefore necessary to allow the TCP / IP local.

To do this we use the bind-address option which allows to limit the source of the connections. That is what can be added to the configuration file for MySQL:

[mysqld] 
bind-address = 127.0.0.1

After rebooting the server, only connections from 127.0.0.1 (localhost) will be accepted. And since we're a bit paranoid and especially perfectionists, we will also modify the privileges of the root user (or other name if you have changed, as has been previously advised) in MySQL:

Mysql.user DELETE FROM WHERE User = 'root' AND Host! = 'Localhost'; 

FLUSH PRIVILEGES;

After execution of these requests, the root user can log into MySQL as it is on the same machine.

We do not see how to set up (and secure) a SSH server, or how to configure the client to enable a tunnel. I advise you, however, look at OpenSSH, which includes a server and SSH client. By default, the OpenSSH server (sshd) is configured to allow tunneling. To be sure, check that option PermitTunnel is "yes" in the configuration file for sshd (usually / etc / ssh / sshd_config)

Here is an example of connection tunnelée using the MySQL console client and the OpenSSH SSH client:

> Ssh-f-L 66306:127.0.0.1:3306 sleep mysql.mondomaine.com 10 
admin@host1.mondomaine.com 's password: xxxxxxx 

=> From here, we are connected to the server and our SSH tunnel is enabled on the local port 66306. 

> Mysql-h 127.0.0.1-P 66306-u root-p 

=> To connect to the MySQL server, we use localhost: 66306 not mysql.mondomaine.com: 3306

Through this tunnel, not only communication between the client and MySQL server will be encrypted, but in addition, identification will be strengthened since it will also log into SSH. For information, you can disable the identification if the SSH to enter two passwords you discomfort. However, remember to secure the system used for the SSH connection!

If the MySQL server machine also has the administration programs (mysql, mysqldump, ...), it is not necessary to set up port-forwarding. You can simply run the command directly on the shell. A small example:

> Mysql.mondomaine.com ssh / usr / local / mysql / bin / mysqldump-A> backup