Advanced MySQL security tips (a complete guide)

Guard Duty

File-per-Table Tablespace Encryption

InnoDB data-at-rest encryption allows file-per-table tablespace encryption by providing the innodb_file_per_table system variable in the mysql.cnf file. Once enabled, you can provide encryption at rest for tables created in file-per-table tablespaces,

[mysqld]
innodb_file_per_table=ON

and restart the mysql service:

$ sudo service mysql restart

You can enable encryption for a new file-per-table tablespace by specifying an encryption='y' clause along with the create table statement:

mysql> create table testTB (c1 INT) encryption='y';
mysql> alter table testTB encryption='y';
Query OK, 1 row affected (0.33 sec)
Records: 1 Duplicates: 0 Warnings: 0

The alter table statement shows how the system variable also enables encryption for existing InnoDB file-per-table tablespaces.

Encryption-at-Rest Verification

Next, you should query the information_schema.tables column to identify newly created or altered encrypted file_per_table tablespaces; similarly, verify that the plugin status is set to ACTIVE to determine successful plugin configuration (Listing 1).

Listing 1

Encryption-at-Rest Verification

mysql> select table_schema, table_name, create_options from information_schema.tables where create_options like '%encryption%';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| test         | testTB     | ENCRYPTION="Y" |
+--------------+------------+----------------+
 **
mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'keyring%';
+--------------+---------------+
| plugin_name  | plugin_status |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+
1 row in set (0.00 sec)

Finally, confirm data encryption at rest with the strings command to view the encrypted output:

$ strings /var/lib/mysql/testDB/testTB.ibd | head -n 20

In this section, you learned how to configure MySQL to provide encryption at rest to ensure physical data protection. Next up, I show you how to set up the encryption-in-transit configuration for a MySQL client and server to secure data on the network.

Encryption in Transit

Data in transit or in motion is exposed to potential risk if sniffed or intercepted by a man in the middle (MITM) attack. Encryption provides effective measures against MITM to secure unprotected, in-transit data. MySQL and MariaDB databases provide encrypted communication between client and server with the SSL/TLS protocol. In this section, I walk you through the MySQL configuration of SSL to ensure secure communication between the client and server.

MySQL version 5.7.28 and above provides the handy mysql_ssl_rsa_setup tool that automatically creates required files to set up the default encrypted communication. To begin, you should check either the default SSL status connection on the MySQL server instance or the value of the Ssl_cipher variable for the current session (Listing 2). The output indicates an unencrypted connection.

Listing 2

SSL Status

mysql> show global variables like '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | DISABLED        |
| have_ssl      | DISABLED        |
| ssl_ca        |                 |
| ssl_capath    |                 |
| ssl_cert      |                 |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       |                 |
+---------------+-----------------+
9 rows in set (0.53 sec)
 **
mysql> show session status like 'Ssl_cipher';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| Ssl_cipher    |                 |
+---------------+-----------------+
1 row in set (0.50 sec)

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