2

Let's say I have a server with an SQL Database somewhere on the other side of the planet. Is it safe to establish a connection over the Internet? e.g. with MySQL Workbench.

I'm asking because I heard that connections to servers, particularly using said software, are by default unencrypted. If I force SSL for every remote connection, what risks remain? Is an open MySQL port a security risk in itself?

technical_difficulty
  • 143
  • 1
  • 6
  • In theory every connection can be listen by others. But this is so difficult. If you want more secure way then you must use ssl. Ssl is more secure way. If you want make more more secure your connection then you can use VPN. – kodmanyagha Jun 27 '17 at 12:17

2 Answers2

5

The MySQL Reference Manual has a whole Chapter 6 for Security.

First, you shouldn't allow connections from anywhere over the internet, but only from the known trusted hosts. While the user also have list of allowed hosts, 6.1.1 Security Guidelines suggests doing this on firewall level, before the hosts even gets connected to your MySQL Server (default port 3306). This increases security as it also prevents using any potential exploits.

If you really need to have direct connection between your local Workbench and remote MySQL Server, Use Secure Connections. But there are other ways of securing the connection to your SQL server, too. Considering the effort needed for the access control and securing the SQL connection with OpenSSL, they are much more practical:

  1. Use a SSH tunnel and you can connect your MySQL server just like the Workbench was there. This example binds local port 13306 and tunnel connections to localhost:3306 on remote side:

    ssh -L 13306:localhost:3306 user@sql.example.com

  2. Use VPN and allow connections from the internal VPN network only.

With both of these approaches you can bind MySQL to localhost only; it's the most secure option.

Esa Jokinen
  • 46,944
  • 3
  • 83
  • 129
2

To give an answer on your question: No it's not safe.

Just to name some security issues:

1: Login Your login data gets transferred plaintext over the internet you DO NOT want that obviously :)

2: SQL queries Your queries that you send (and their responses) are send in plain text again you DO NOT want that as one can easily tamper that data.

Furthermore having query results back in plain text which could contain sensitive information is a whole other issue. In case of personal files you could even get sued in some countries for neglecting/ignoring safety guidelines for personal data.

3: SQL port open on the internet is alway an extra attack vector for a potential malicious user. Think of remote mysql server exploits etc.

If you really need a remote connection use SSL.

I've sumarized the steps you need to follow assuming you run a mysql server on linux:

On the mysql-server, generate a mysql ssl certificate:

Gernerate the CA ca private key:

openssl genrsa 4096 > ca-key.pem

Generate the certificate using the key previously created (answer the default questions):

openssl req -sha256 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem

Next create a new private key for the mysql server (again, default questions):

openssl req -sha256 -newkey rsa:4096 -days 730 -nodes -keyout server-key.pem > server-req.pem

Export private key:

openssl rsa -in server-key.pem -out server-key.pem

Create server certificate:

openssl x509 -sha256 -req -in server-req.pem -days 730  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Now in your my.cnf (located in /etc/mysql/my.cnf) edit the mysqld section and add the follwoing:

ssl-ca=/etc/mysql/ca-cert.pem ssl-cert=/etc/mysql/server-cert.pem ssl-key=/etc/mysql/server-key.pem

restart your mysql service:

service mysql restart

Now you have your mysql server running with server certificates, next we will add a users from a specific address (your remote static ip if possible) so in your mysql console execute the following:

GRANT ALL PRIVILEGES ON 'database'.* TO 'yourremotelogin'@'your.remote.ip.xxx' IDENTIFIED BY 'password' REQUIRE SSL;
FLUSH PRIVILEGES;

Okay so now we have a SSL mysql server which accepts remote connections over SSL from your particular ip, but we still need to generate client certificates:

So, still on the mysql server execute the following:

    openssl req -sha256 -newkey rsa:4096 -days 730 -nodes -keyout client-key.pem > client-req.pem
    openssl rsa -in client-key.pem -out client-key.pem
    openssl x509 -sha256 -req -in client-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

Now copy the ca-cert.pem, client-key.pem and client-cert.pem to your remote pc where your mysql workbench is situated.

In mysql workbench open up the Server connection management and edit your existing connection towards your server. In the conenction tab, select the SSL sub-tab and select "use SSL require".

In the fields below select ca-cert.pem for your SSL CA File, cielnt-cert.pem for SSL CERT File and client-key.pem for SSL Key File.

Now if you try to connect you will still be asked for a password (which you defined earlier) but it's over SSL :)

Hope this answer is the answer you were looking for.

timmeyh
  • 968
  • 1
  • 6
  • 25