Advanced MySQL security tips (a complete guide)
Guard Duty
Intercepting the Plaintext Table
To create a client that can use its credentials to log in remotely to the MySQL server and access tables, enter:
$ mysql -u user -p -h <SSLsrvr_IPaddress>
In the meantime, initiate a tshark
session at your server or client to sniff the plaintext data:
$ tshark -i any > mysql_plaintext.pcap
Hitting Ctrl+C stops the capture process and opens the mysql_plaintext.pcap
file in Wireshark to retrieve the plaintext (Figure 1).
Configuring the MySQL Server
To enable SSL support, you need to create the required SSL files and keys manually. MySQL requires private keys and X509 certificates signed by a certificate authority (CA) to ensure secure encrypted communication. Similarly, the MySQL server needs private keys and X509 certificates from every client that wants to connect to the server over SSL. Table 1 lists the files you need to prepare manually.
Table 1
Keys and Certificates
File | Function |
---|---|
ca-key.pem
|
The private key used to generate an X509 certificate for the certificate authority. |
ca.pem
|
The X509 certificate containing certificate details and public key. |
server-req.pem
|
The server certificate signing request (CSR). |
server-key.pem
|
The private server key. |
server-cert.pem
|
A self-signed X509 certificate that contains server certificate metadata and the public key. |
client-req.pem
|
The client CSR. |
client-key.pem
|
The client private key |
client-cert.pem
|
A self-signed X509 client certificate. |
Creating SSL Files with OpenSSL
The OpenSSL command-line tool will help you prepare and generate the required SSL files. This handy tool uses the OpenSSL library to perform various tasks, like generating X509 request certificates, providing private keys, verifying and signing X509 certificates, and so on.
Before generating SSL files, create a directory in which to place keys and certificates for encryption in transit:
$ mkdir /var/lib/mysql/new_certs && cd /var/lib/mysql/new_certs
Next, generate the RSA 2048-bit private key to create the CA X509 certificate that signs the server and client X509 certificates:
$ openssl genrsa 2048 > ca-key.pem $ openssl req -new -x509 -nodes -days 3500 -key ca-key.pem -out ca.pem
The openssl
command generates the server's private key and certificate signing request. Once obtained, you need to remove the passphrase and sign server-req.pem
with the CA key and certificate to obtain the final X509 certificate for the server:
$ openssl genrsa 2048 > server-key.pem $ openssl req -new -key server-key.pem -out server-req.pem $ openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
MySQL configuration for SSL only requires server-key.pem
, server-cert.pem
, and the CA certificate.
Similarly, you need to generate the private key (cert-key.pem
) and a self-signed X509 certificate for the MySQL client:
$ openssl genrsa 2048 > client-key.pem $ openssl req -new -key client-key.pem -out client-req.pem $ openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
The openssl verify
command lets you verify that OpenSSL has generated the correct certificates:
$ openssl verify -CAfile ca.pem server-cert.pem client-cert.pem server-cert.pem: OK client-cert.pem: OK
The OK value indicates that the X509 certificate was signed correctly.
Buy this article as PDF
(incl. VAT)