Lead Image © alphaspirit, 123RF.com

Lead Image © alphaspirit, 123RF.com

Advanced MySQL security tips (a complete guide)

Guard Duty

Article from ADMIN 63/2021
By
Security safeguards protect data on MySQL servers.

MySQL security configurations include a range of topics, along with their possible effects on MySQL servers and corresponding applications. In this article, I look at MySQL security encryption services, account-associated authorization systems, and other required security precautions to ensure protection against misuse and attacks. This security guide will help you protect sensitive data, even if the MySQL service is compromised at some point.

Most of the advanced MySQL security configurations require changes to the server's main configuration file my.cnf. This file is generally located inside the /etc/mysql directory or in the /opt/lampp/etc/ folder for LAMPP installations. However, you can locate the file with the find or locate command in Linux.

Encryption at Rest

By default, MySQL stores plaintext data inside InnoDB tables. Any non-admin user with access to these files can read tables and pose a security threat. Data at rest or in transit both require protection to avoid potential risk. MySQL transparent data encryption (TDE) enables encryption at rest to prevent information threats and privacy breaches, even if your system is compromised at some level.

MySQL provides TDE by enabling encryption at rest for physical files in the database. It encrypts data automatically on the go before writing to the storage and decrypts before reading. In this section, I will show you how to configure encryption at rest to ensure protection from physical data theft.

Getting Started

MySQL InnoDB tablespaces are stored in .ibd file format and are generally located in the /var/lib/mysql directory. You can also find them with the locate command:

$ locate ibd | less
/var/lib/mysql/testDB/testTB.ibd
/var/lib/mysql/sys/sys_config.ibd
...

Once located, you can retrieve sensitive plaintext data with the cat or strings command:

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

The command output will generate plaintext information containing raw data stored in the corresponding database table.

Key Management

The centralized key management solution offers electronic code book (ECB) and cipher block chaining (CBC) block encryption for tablespace keys and data encryption in the MySQL server. Encryption at rest for the InnoDB search engine and tablespaces involve a two-tier key architecture that implements easy key management and master key rotation:

  • Tablespace key: An encrypted key stored in the tablespace header.
  • Master key: A key that decrypts the tablespace key.

MySQL implements InnoDB tablespace encryption with the use of tablespace keys. After tablespace encryption, the master key encrypts the tablespace key to place it inside the tablespace header.

When an authenticated user accesses the encrypted table, InnoDB uses the master key to decrypt the tablespace key. The decrypted tablespace key allows you to perform read/write operations on data.

Master Encryption Key Rotation

A decrypted tablespace key never changes; instead, you can only change the master key by key rotation, which is an instance-level operation that re-encrypts all the tablespace keys and saves them back to the tablespace header:

mysql> alter instance rotate InnoDB master key;

However, the process does not re-encrypt or decrypt the tablespace data.

MySQL Keyring Plugin

Data-at-rest encryption in MySQL supports keyring plugins that enable internal server components to retrieve sensitive content. Here, I use a keyring_file plugin to store the keyring data inside local files in the server host. Configuration requires system variables in the mysql.cnf file, which is located inside the /etc/mysql/conf.d directory:

[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql/keyring-data/keyring

The system variable keyring_file_data defines the keyring_file data location for data storage.

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

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

  • MariaDB vs. MySQL

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

  • 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