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).

Figure 1: PCAP file in Wireshark.

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

Express-Checkout as PDF
Price $2.95
(incl. VAT)

Buy ADMIN Magazine

SINGLE ISSUES
 
SUBSCRIPTIONS
 
TABLET & SMARTPHONE APPS
Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

  • MariaDB vs. MySQL

    MariaDB is a cousin of MySQL – but not an identical twin.

  • FreeRADIUS for WiFi Hotspots

    Tired of contending with shared passwords for wireless networks? Use WPA Enterprise and a FreeRADIUS server to set up a user password solution for wireless users.

  • Credential management with HashiCorp Vault
    Admin teams can use secret sharing to centrally manage shared access to user accounts and services. HashiCorp Vault is one of the few tools that has proven effective when it comes to implementing this solution. Here's how to use this open source tool and keep important credentials safe.
  • Kopano Groupware – an open source productivity suite
    Kopano Groupware seeks to be more than a slot-in replacement for Microsoft Exchange. We reveal how you can commission the platform and the highlights it offers.
  • OpenLDAP Workshop
    Centralized user management with LDAP or Active Directory is the standard today, although many prefer to manage user data manually rather than build this kind of infrastructure. In this article, we look at a better approach with OpenLDAP.
comments powered by Disqus