At the time of writing this article, the current MariaDB 10.2 offers MariaDB TX Subscription to access the feature MariaDB MaxScale. MariaDB TX subscription offers high-end functionality like implementing business-critical production, test and QA deployments, management, monitoring and recovery tools, mission-critical patching and above all, access to the MariaDB Database Proxy, MaxScale.

Following are the security related features of MariaDB MaxScale:

1. Database firewall filter.
2. Denial of Service protection.
3. Data Masking to protect sensitive data.

This article discusses steps to secure your MariaDB Server installation from the installation phase onwards.

Install MariaDB with mysql_secure_installation

mysql_secure_installation is an executable shell script available with MariaDB to install it with added security measures like setting password for root accounts, remove root accounts that are accessible from outside localhost, remove anonymous user accounts and remove test database (along with its privileges) that can be accessed by anonymous users.

shell> mysql_secure_installation

Below are the prompts being asked by the script:

Enter current password for root (enter for none):

OK, successfully used password, moving on…

Change the root password? [Y/n] n

Remove anonymous users? [Y/n] y

Disallow root login remotely? [Y/n] y

Remove test database and access to it? [Y/n] y

Reload privilege tables now? [Y/n] y

Avoid running mysqld as Linux root user

If the mysqld user has Linux System user “root” access and MariaDB FILE privilege, then he can modify any files in the system. To prevent this, always create or use the included user mysql to run the mysqld process. For this start mysqld with mysql as user.

mysql> mysqld_safe –user=mysql

Below is a list of user management guidelines for a secure MariaDB implementation:

1. Grant root access only to local clients. Example: apache server installed on the same machine or restrict host name to specific host only. ‘root’ @ ‘192.250.200.25’ – specifies access to user root from the host 192.250.200.25 only.
2. Use strong passwords for users.
3. Have separate users for each database.
4. Restrict the hosts or IPs that can access the database server.
5. Grant only required privileges to client users. Administrative privileges like GRANT etc. are not needed for client users.

Password Check and Validation

The Password Validation Plugin API in MariaDB allows for ensuring that user passwords meet at least minimum security requirements. There are 2 plugins – simple_password_check plugin and cracklib_password_check plugin that use this API. Since they are not enabled by default install them manually. Atleast one plugin needs to be enabled to implement password validation checks. If multiple plugins are loaded, all validation checks must be passed for setting or changing passwords.

To install simple_password_check plugin:

INSTALL SONAME 'simple_password_check';

OR

INSTALL PLUGIN 'simple_password_check';

The default values for the options for this plugin enforce the below password validation rules. Password should have:

1. At least 8 character in length.
2. Requires at least one digit.
3. Requires at least one uppercase and one lowercase letter.
4. Requires at least one symbol or special character.

The configuration options that can change the validation requirements are set either through SQL SET GLOBAL statements or through my.cnf/my.ini. Various options are:

--simple-password-check-digits=# minimum number of digits needed in password (range: 0 to 1000, default = 1)
--simple-password-check-letters-same-case=# needs this many upper and lower case letters (range: 0 to 1000, default = 1)
--simple-password-check-minimal-length=# minimum password length (range: 0 to 1000, default = 8)
--simple-password-check-other-characters=# needs this many special characters or symbols (range: 0 to 1000, default = 1)

Examples:

SET GLOBAL simple_password_check_minimal_length = 10;

OR

[mysqld] simple-password-check-minimal-length = 10

If password is directly set by hash, then these validations are bypassed. In order to avoid this, enable the strict_password_validation variable (along with validation plugin) that reject passwords that cannot be validated (hash passwords).

SET GLOBAL strict_password_validation = 1;

OR

[mysqld] strict-password-validation = 1

The SET DEFAULT ROLE statement uses MariaDB’s User Roles feature to assign a default set of packaged privileges as roles to new users.

SET DEFAULT ROLE new_user;

To remove the Default Role from the current user:

SET DEFAULT ROLE NONE;

Use Secure Connections to transfer data

The Transport Layer Security (TLS) can be used to encrypt data during transfer. In MariaDB TLS support is pre-compiled, but disabled by default. To enable it, start the server with –ssl option. Configure the server for encrypted connections by adding the Certificate Authority (CA) certificate, Server Public Key Certificate and the Server Private Key in my.cnf.

[mysqld]
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

Use the file names as applicable.

For client side configuration, use CA certificate, Client Public Key certificate and Client Private Key. Invoke the client like:

mysql --ssl-ca=ca.pem

Encrypt Tables and Data

Table encryption ensures that even if someone intrudes into database or access data, the data is protected due to encryption and is immune to tampering. In MariaDB the storage engines XtraDB, InnoDB and Aria supports encryption with Aria needing tables to be created with the default option ROW_FORMAT=PAGE. All tablespaces, individual tables and log files can be encrypted. Only data that are at rest in the database are encrypted and transferring data sent to the client are not encrypted. For that, secure connections using TLS protocol needs to be used.

The limitations are:

1. Disk based Galera gcache is not encrypted.
2. The Audit Plugin cannot create encrypted output. Instead the output needs to be sent to syslog for configuring protection.
3. File based general query and slow query logs cannot be encrypted.
4. Aria log is not encrypted.
5. Error log is not encrypted since it contains query text, data, crashes, assertion failures etc. It can also be protected through syslog.

For table encryption, use an encryption plugin like file_key_management plugin and configure storage engines to use it. This plugin has the following components to be configured:

1. file_key_management_filename : file that stores the encryption keys and plugin read the keys from this file. 2.file_key_management_filekey : an optional key to decrypt the key file. The key can also be a filename that contains the key preceded by the string FILE (eg: FILE:/path/to/filekey). 3. file_key_management_encryption_algorithm : the encryption algorithm

An example configuration in my.cnf or my.ini:

[mysqld]
file_key_management_encryption_algorithm=aes_cbc
file_key_management_filename = /home/mdb/keys.enc
file_key_management_filekey = secret

To encrypt XtraDB and InnoDB tables, use the below configuration (values are examples) in my.cnf or my.ini:

[mysqld] plugin-load-add=file_key_management
file-key-management
file-key-management-filename = /mount/usb1/keys.txt
innodb-encrypt-tables
innodb-encrypt-log
innodb-encryption-threads=4

Then use CREATE TABLE or ALTER TABLE queries with the below options:

ENCRYPTED : YES/NO (YES) ENCRYPTION_KEY_ID : Positive Integer

Example:

CREATE TABLE T (id int, value varchar(255)) ENCRYPTED=YES ENCRYPTION_KEY_ID=17; ALTER TABLE T ENCRYPTED=YES ENCRYPTION_KEY_ID=18;

To disable encryption and decrypt the table:

ALTER TABLE T encrypted=NO;

Aria tables can be encrypted by the below configuration in my.cnf/my.ini:

[mysqld]
aria-encrypt-tables=1

To encrypt temporary tables in disk created during query execution, set the below parameter in my.cnf/my.ini:

[mysqld] encrypt-tmp-disk-tables=1

To encrypt temporary files created on disk during binary log transaction caching, filesorts etc:

[mysqld]
encrypt-tmp-files=1

To encrypt binary logs and relay logs:

[mysqld]
encrypt-binlog=1

Setup Auditing and Logging

The MariaDB Audit Plugin logs lot of security and performance related relevant information like incoming connections, executed queries, accesses to individual tables and information about users who read/write at what time to the database. The logs are entered on a file or syslog. This information helps us to detect suspicious activities and to investigate security breaches.

Enable logging by setting server_audit_logging to ON.

SET GLOBAL server_audit_logging=on

The events that can be logged are grouped as CONNECT, QUERY and TABLE events. To log these events, set the server_audit_events variable:

SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE';

OR

[mysqld]

…

server_audit_events=connect,query

User activities are also logged since the activities belong to any one of the types connect, query or table. You can include or exclude users whose events are to be logged using the server_audit_incl_users variable.

To add a user without removing previous usernames:

SET GLOBAL server_audit_incl_users = CONCAT(@@global.server_audit_incl_users, ',maria');

To specifically include or exclude users:

server_audit_incl_users=robert
server_audit_excl_users=sam,rocky