Configuring and Securing MariaDB

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.

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

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.

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.

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"

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

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

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.

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

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

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.

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.

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

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.

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

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

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.

Edit the MariaDB server configuration file:

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

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

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

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

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!

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.

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.

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%';

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.

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