Practitioner Level

Configuring and Securing MariaDB

Pricing
1 hour
  • 30 Learning Objectives

About this Hands-on Lab

Before we can start building our world-changing website or application on LEMP, we have to lay the foundation: the stack. In this hands-on lab, we will walk through configuring and securing MariaDB on Ubuntu Linux.

When you have completed this lab, you will have a secure MariaDB installation on Ubuntu Linux.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Confirm that MariaDB is Installed, Enabled, and Running

Become root:

sudo su -

Check the status of the MariaDB installation:

systemctl status mariadb

The service should be enabled and running.

Check the installed version of MariaDB using mysql:

mysql -V

We should see that there’s an instance of MariaDB running. The exact version isn’t important.

Secure the Database Using the mysql_secure_installation Script

Launch the mysql_secure_installation script. Set the MariaDB root password to 123456. This is important, as successful completion of this lab will require this:

mysql_secure_installation

We will be prompted for answers by the mysql_secure_installation script.

Try logging in as the MariaDB root user using the mysql client:

mysql -u root -p=123456

Exit the MariaDB client:

quit
Configure the Network

In order to make MariaDB available outside of the localhost 127.0.0.1, we need to set the bind-address variable to the private IP address of our server. We can find this in the /home/cloud_user/server_info.txt file.

Edit the MariaDB server configuration file:

vi /etc/mysql/mariadb.conf.d/50-server.cnf

Change the bind-address to our private IP.

Configure the Second Port

We’d also like to configure an "extra port."

Add the following lines to the [mariadb] section in /etc/mysql/mariadb.conf.d/50-server.cnf:

# Second Admin Port
extra_port = 8385
extra_max_connections = 10

Save and exit.

Restart the mariadb service and check the status:

systemctl restart mariadb
systemctl status mariadb

The service should be enabled and running.

Validate the Network and the Second Port

The mariadb service should be enabled and active. Check that the MariaDB service is listening on ports 3306 and 8385:

netstat -anp | egrep "3306|8385"
Configure the Firewall to Support MariaDB

Check the firewall configuration:

ufw status

Open port 3306 to allow inbound MariaDB traffic:

ufw allow 3306

Open port 8385 to allow inbound MariaDB traffic on the "extra port":

ufw allow 8385

Recheck the status of the firewall:

ufw status

MariaDB traffic is now allowed through the firewall on both port 3306 and 8385.

Create a Certificate Authority Private Key and Certificate

Create a directory to store our certificates:

mkdir -p /etc/mysql/certificates
cd /etc/mysql/certificates

Generate a private key:

openssl genrsa 2048 > ca-key.pem

Generate the X509 certificate. Hit Enter and use the default answers, except for CN, use ‘ca.bigstatecollege.edu’ for the CN field. THIS IS IMPORTANT!

openssl req -new -x509 -nodes -days 365000 
      -key ca-key.pem -out ca-cert.pem
Create a Private Key for the MariaDB Server

Generate a private key for the server:

openssl req -newkey rsa:2048 -days 365000 
      -nodes -keyout server-key.pem -out server-req.pem

We will have to answer some questions. Hit Enter and use the default answers, except for CN, use ‘server.bigstatecollege.edu’ for the CN field. THIS IS IMPORTANT!

Process the key to remove the passphrase:

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

We should see:

writing RSA key

Create a Self-Signed Certificate for the MariaDB Server

Generate a self-signed X509 certificate for the MariaDB server from the certificate request:

openssl x509 -req -in server-req.pem -days 365000 
      -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 
      -out server-cert.pem

We now have a self-signed X509 certificate for the MariaDB server in the /etc/mysql/certificates/ directory.

Create a Private Key for the MariaDB Client

Generate a private key for the MariaDB client. Hit Enter and use the default answers, except for CN, use ‘client.bigstatecollege.edu’ for the CN field. THIS IS IMPORTANT!

openssl req -newkey rsa:2048 -days 365000 
      -nodes -keyout client-key.pem -out client-req.pem

Next, process the key to remove the passphrase.

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

You should see the following: writing RSA key

Create a Self-Signed Certificate for the MariaDB Client

Generate a self-signed X509 certificate for the MariaDB client from the certificate request:

openssl x509 -req -in client-req.pem -days 365000 
      -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 
      -out client-cert.pem

Allow the mysql user access to the certificates, and add read permission for group and other:

chmod 644 *
Verify the Self-Signed Certificates for the MariaDB Client and Server

Verify that the X509 certificate was correctly generated using the openssl verify command:

openssl verify -CAfile ca-cert.pem client-cert.pem server-cert.pem

We should see the following:

client-cert.pem: OK

server-cert.pem: OK

Enable TLS for the MariaDB Server

Add the server configuration in the [mariadb] configuration block in /etc/mysql/mariadb.conf.d/50-server.cnf:

vi /etc/mysql/mariadb.conf.d/50-server.cnf

For the server, in the [mariadb] configuration block, add this:

# SSL Configuration
ssl_cert = /etc/mysql/certificates/server-cert.pem
ssl_key = /etc/mysql/certificates/server-key.pem
ssl_ca = /etc/mysql/certificates/ca-cert.pem

Save and exit the server configuration file.

Enable TLS for the MariaDB Client

Edit the file:

vi /etc/mysql/mariadb.conf.d/50-client.cnf

In the [client-mariadb] configuration block, add the following:

# SSL Configuration
ssl_cert = /etc/mysql/certificates/client-cert.pem
ssl_key = /etc/mysql/certificates/client-key.pem
ssl_ca = /etc/mysql/certificates/ca-cert.pem

Save and exit the client configuration file.

Activate TLS for the MariaDB Client and Server

Restart the MariaDB server:

systemctl restart mariadb

Once the MariaDB server is restarted, verify it’s running:

systemctl status mariadb

The service should be enabled and running.

Check that TLS is Enabled on the MariaDB Server

Check that TLS is now enabled on the MariaDB server:

mysql -u root -p=123456 -e "SHOW VARIABLES LIKE 'have_ssl';"

We should see that have_ssl returns YES.

TLS is enabled on the MariaDB server.

Verify the Connection of the MariaDB Client is Using TLS

In order to verify that the connection from the MariaDB client to the server is using TLS, we can check the value of the connection’s Ssl_cipher status variable. If the value is non-null, then the connection is using TLS:

mysql -u root -p=123456 -e "SHOW SESSION STATUS LIKE 'Ssl_cipher';"

We should see a non-null value as the result.

Examine the SSL Configuration of the MariaDB Server

Let’s check out the SSL configuration.

mysql -u root -p=123456 -e "SHOW VARIABLES LIKE '%ssl%';"

Check for TLS v 1/1.1/1.2 – Substitute our private IP address for YOUR_PRIVATE_IP here!

TLSv1.0:

openssl s_client -connect YOUR_PRIVATE_IP:3306 -tls1

TLSv1.1:

openssl s_client -connect YOUR_PRIVATE_IP:3306 -tls1_1

TLSv1.2:

openssl s_client -connect YOUR_PRIVATE_IP:3306 -tls1_2

Create the Encryption Key File

Let’s create some directories to store our certificates for both the client and server:

mkdir -p /etc/mysql/encryption
cd /etc/mysql/encryption

Create an encryption key file:

for i in `seq 1 10` ; do echo $i";"`openssl rand -hex 32` >> /etc/mysql/encryption/keyfile ; done
cat /etc/mysql/encryption/keyfile
Encrypt the Encryption Key File

Generate a random encryption password:

openssl rand -hex 128 > /etc/mysql/encryption/keyfile.key

Encrypt the key file:

openssl enc -aes-256-cbc -md sha1 
   -pass file:/etc/mysql/encryption/keyfile.key 
   -in /etc/mysql/encryption/keyfile 
   -out /etc/mysql/encryption/keyfile.enc

We will see a warning. We can ignore it.

*** WARNING : deprecated key derivation used.

Using -iter or -pbkdf2 would be better.

Configure MariaDB to Use the Encrypted Key File – Part 1

Edit the MariaDB server configuration file:

vi /etc/mysql/mariadb.conf.d/50-server.cnf
Configure MariaDB to Use the Encrypted Key File – Part 2

Add the following to the [mariadb] configuration block:

# Encryption at Rest
plugin-load-add=file_key_management
loose_file_key_management_filename = /etc/mysql/encryption/keyfile.enc
loose_file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
loose_file_key_management_encryption_algorithm = AES_CTR
innodb-encrypt-tables
innodb-encrypt-log
innodb-encryption-threads = 4
innodb-tablespaces-encryption
innodb_default_encryption_key_id=7
Activate Data-at-Rest Encryption

Save the configuration file, exit the editor, and restart the MariaDB server:

systemctl restart mariadb

Once the MariaDB server is restarted, verify it’s running:

systemctl status mariadb
Create an Encrypted Database Table

Test encryption by creating a database table with encryption:

mysql -u root -p=123456

We will need to create and use a new database. We will call this database encryption_test:

CREATE DATABASE encryption_test;

Now we need to USE the encryption_test database:

USE encryption_test;

Let’s create a table, tab1, in the encryption_test database:

CREATE TABLE tab1 (
   id int PRIMARY KEY,
   str varchar(50)
);
Examining the Encrypted Database Table

Let’s take a look at the new table, tab1:

SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE NAME='encryption_test/tab1';

We now have a database table, tab1, in the encryption_test database that is encrypted!

Creating a Non-Encrypted Database Table

Let’s try creating a database table that’s not encrypted:

CREATE TABLE tab2 (
   id int PRIMARY KEY,
   str varchar(50)
) ENCRYPTED=NO;

Let’s take a look at the new table, tab2:

SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE NAME='encryption_test/tab2';

We’ve created a table with no encryption called tab2. Note that the ENCRYPTION_SCHEME is set to 0.

Forcing Encryption

What if we want to force our DBAs to use encryption? We can do that by setting the innodb_encrypt_tables variable to FORCE. We can do this in the 50-server.cnf configuration file to make it permanent, but let’s set it in the MariaDB client so we can try creating an unencrypted table with encryption forced without having to cycle the server:

SET GLOBAL innodb_encrypt_tables='FORCE';

This will force encryption.

Displaying the InnoDB Configuration

If we want to see all the innodb variables, we can use the following in the MariaDB client:

SHOW VARIABLES LIKE '%innodb%';

This should show all of the variables and their values. If we’d like to see the variables that relate to encryption, use:

SHOW VARIABLES LIKE '%innodb_enc%';
Attempt to Create a Non-Encrypted Table

Let’s attempt to create an unencrypted table:

CREATE TABLE tab3 (
   id int PRIMARY KEY,
   str varchar(50)
) ENCRYPTED=NO;

We aren’t allowed to create an unencrypted table.

Attempt to Create an Encrypted Table

Let’s try the same command again, with encryption:

CREATE TABLE tab3 (
   id int PRIMARY KEY,
   str varchar(50)
) ENCRYPTED=YES;

Now we can check out the details on the tab3 table:

SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE NAME='encryption_test/tab3';

Exit the MariaDB client:

quit
Contact SalesSee Pricing

Additional Resources

Big State College (BSC) is a Large Ten Conference school in a Midwestern state. BSC is looking to deploy a centralized web hosting service using the LEMP stack.

As the engineers tasked with executing this project, we will be securing our new MariaDB installation as part of deploying the new environment. Let's begin!

Connecting to the Lab Server

Use the credentials provided on the hands-on lab overview page, and log in using an SSH connection to the Public IP or DNS of the server as cloud_user.

Making a SSH connection using the ssh command. Replace <<public_IP_or_DNS>> with the Public IP or DNS provided on the overview page.

ssh cloud_user@<<public_IP_or_DNS>>

If you can't use ssh or prefer a GUI client, use the credentials provided on the hands-on lab overview page to configure your SSH connection to the lab server.

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Don't have an account?

Get Started
Who’s going to be learning?
MyselfMy Organization