blog

How to Secure MySQL: Part Two

Lukas Vileikis

Published

In the previous post about MySQL security, we have covered a range of options that can be used to make your MySQL instance(s) more secure. They included:

  • General MySQL security measures;
  • Controlling access in MySQL;
  • Creating, altering, and deleting users in MySQL;
  • Granting and revoking privileges to and from users in MySQL;
  • Checking what privileges are assigned to users in MySQL.

In this post, we will dive into the rest of the options, including:

  • Account categories in MySQL;
  • Roles in MySQL;
  • Reserved accounts in MySQL;
  • Password management in MySQL;
  • Account locking in MySQL;
  • Security plugins offered by MySQL;
  • Securing MySQL backups.

Keep in mind that once again, we will not cover absolutely everything you need to know, but we will try to provide good starting points to do your own research.

Account Categories in MySQL

Account categories were introduced in MySQL 8 – specifically, in MySQL 8.0.16. Here’s the crux of it:

  • There are two separate account categories: regular users and system users;
  • A regular user is a user without the SYSTEM_USER privilege – a system user is a user with the SYSTEM_USER privilege;
  • A regular user can modify regular accounts – such a user cannot modify system accounts;
  • A system user can modify both system and regular accounts;
  • Regular accounts can be modified by both regular users and system users;
  • System accounts can only be modified by system users.

To make use of account categories in MySQL security-wise, keep in mind that the SYSTEM_USER privilege affects things like account manipulation, and killing sessions and statements within them – this concept in MySQL allows restricting certain modifications to certain accounts thus making MySQL more secure. Account categories can also be used to protect system accounts against manipulation by regular accounts: to do so, do not grant mysql schema modification privileges to regular accounts.

To grant an account SYSTEM_USER privileges, use the following query on a created account:

GRANT SYSTEM_USER ON *.* TO system_user;

Roles in MySQL

In MySQL, roles are collections of privileges. When you grant a user account a role in MySQL, you grant all of the privileges associated with that role. Roles can be created using the CREATE ROLE statement:

CREATE ROLE ‘role_1’, ‘role_2’;

Role names consist of a user part and a host part – the user part cannot be blank and the host part defaults to “%” if it is not specified.

When roles are created, you should assign privileges to them. Privileges can be assigned using the GRANT statement:

  • GRANT ALL ON demo_database.* TO ‘demo_user’; would grant all privileges to a user called demo_user on a database called demo_database;
  • GRANT INSERT, SELECT, UPDATE, DELETE ON database.* TO ‘demo_user’; would grant INSERT, SELECT, UPDATE, and DELETE privileges to a user called demo_user on a database called demo_database
  • GRANT SELECT ON demo_database.* TO ‘demo_user’; would grant SELECT privileges to a user called demo_user on a database called demo_database.

To assign a role to an individual user, use this syntax:
 

GRANT ‘role_name’ TO ‘user_name’@’localhost’;

To assign multiple roles to an individual user, use this syntax:

GRANT ‘role_1’, ‘role_2’ TO ‘user_name’@’localhost’;

To assign roles to multiple users at the same time, use this syntax:

GRANT ‘role_name’ TO ‘user1’@’localhost’, ‘user2’@’localhost’;

Roles can be helpful in preventing security incidents because if an attacker knows the password of a not very privileged user wrongly assuming the user is very “powerful” role-wise, your application (and your database) could be very well saved.

Reserved Accounts in MySQL

When reserved accounts are concerned, keep in mind that MySQL creates accounts during the data directory initialization. There are a few accounts that should be considered reserved in MySQL:

  • ‘root’@’localhost’ – this account is a superuser account and it has god-like privileges across all MySQL databases (it can perform any operation across any MySQL database). It is worth noting that the root user can also be renamed to avoid exposing a highly privileged account. To rename the account, run the following query:
RENAME USER ‘root’@’localhost’ TO ‘username’@’localhost’;
  • Make sure to issue a FLUSH PRIVILEGES
  • ‘mysql.sys’@’localhost’ – this account is a system user that is used as the definer for view, procedures and functions in the sys schema. Added in MySQL 5.7.9 to avoid issues that might arise if the root account is renamed.
  • ‘mysql.session’@’localhost’ – this account is used internally by plugins to access the server.

In this case, you cannot do very much security-wise, but do keep in mind that the root account has god-like privileges which means that it can perform any operation across any MySQL database and exercise caution when deciding who to grant the privileges to access the account. Also, keep in mind what the other MySQL accounts are used for.

Password Management in MySQL

MySQL also supports password management features. Some of them include:

  • The ability to periodically expire passwords;
  • The ability to avoid password reuse;
  • The ability to generate passwords;
  • The ability to check whether the password in use is strong;
  • The ability to temporarily lock users out after too many failed login attempts.

Now, we will look into these options further.

To expire a password manually, use the ALTER USER statement like so:

ALTER USER ‘user’@’localhost’ PASSWORD EXPIRE;

To set a global policy, modify the my.cnf file such that it includes the default_password_lifetime parameter. The parameter can be defined beneath the [mysqld] section (the following example sets the password lifetime to 3 months (90 days)):

default_password_lifetime=90

If you want the passwords to never expire, set the parameter default_password_litetime to 0.
You can also set password expiration for specific users. If you would want to set the interval of password expiration for a user called demo_user, you could use the following example:

ALTER USER ‘demo_user’@’localhost’ PASSWORD EXPIRE INTERVAL 90 DAY;

To disable password expiration:

ALTER USER ‘demo_user’@’localhost’ PASSWORD EXPIRE NEVER;

To reset the global password expiration policy:

ALTER USER ‘demo_user’@’localhost’ PASSWORD EXPIRE DEFAULT;

​​Password reuse restrictions do not allow passwords to be reused – to make use of this feature, use the password_history and password_reuse_interval variables. You can either put these variables in my.cnf by looking at the example below or set them at runtime by adding SET PERSIST in front of the statements below.

To prohibit reuse of any of the 5 previously used passwords newer than 365 days, use:

password_history=5
password_reuse_interval=365

To require a minimum of 5 password changes before allowing reuse:

ALTER USER ‘demo_user’@’localhost’ PASSWORD HISTORY 5;

The same can be done when creating a user – replace ALTER USER with CREATE USER.

To generate a random password when creating a user, run:

CREATE USER demo_user@localhost IDENTIFIED BY RANDOM PASSWORD;

To change the password of a user to a randomly generated one:

SET PASSWORD FOR demo_user@localhost TO RANDOM;

Your random password will be displayed underneath.

Keep in mind that the default random passwords have a length of 20 characters. The length can be controlled by the generated_random_password_length variable which has a range from 5 to 255.

To check whether a used password is strong, you can use the VALIDATE_PASSWORD_STRENGTH variable – the function displays a number from 0 to 100 with 0 being the weakest and 100 being the strongest:
SELECT VALIDATE_PASSWORD_STRENGTH(‘password’);

Account Locking in MySQL

MySQL 8.0.19 also introduced the ability to temporarily lock user accounts. This can be accomplished using the variables FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME.

To enable account locking when creating a user, run:

CREATE USER ‘demo_user’@’localhost’ IDENTIFIED BY ‘password’ FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 5;

The value after FAILED_LOGIN_ATTEMPTS specifies after how many failed attempts the account is locked, the value after PASSWORD_LOCK_TIME specifies the account lock time in days. It is also possible to specify a value which does not end until the account is unlocked by specifying PASSWORD_LOCK_TIME as UNBOUNDED.

Security Plugins Offered by MySQL

MySQL also offers a couple of plugins that can further enhance security capabilities. MySQL offers:

  • Authentication plugins;
  • Connection-control plugins;
  • Password validation plugins;
  • Audit plugins;
  • Firewall plugins;

These plugins can be used for a number of things security-wise:

Authentication Plugins

Authentication plugins can allow users to choose between multiple pluggable authentication methods available in MySQL. They can be used together with CREATE USER or ALTER USER statements. Here’s an example: 

CREATE USER ‘user_1’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

This query would implement authentication using the native password hashing method.

Connection-Control Plugins

Connection-control plugins can introduce an increasing delay in server responses to connection attempts if the connection attempts exceed a certain number – they are able to stop potential brute-force attacks. This plugin library was introduced to MySQL in the version 5.7.17 and it can be added to MySQL either via my.cnf or by loading the plugins into the server at runtime.
In order to add the plugins to my.cnf, add the following line beneath [mysqld]:

plugin-load-add=connection_control.so

After modifying the file, save your changes and restart MySQL.
In order to load the plugins into the server at runtime, run:

INSTALL PLUGIN CONNECTION_CONTROL SONAME ‘connection_control.so’;
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME ‘connection_control.so’;

Adjust the .so suffix as necessary. If you’ve accomplished everything correctly, the CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS table should contain all of the failed attempts to connect.

Password Validation Plugins

Password validation plugins can allow users to use stronger passwords if used properly. The password validation plugin can be installed via my.cnf or by loading the plugin into the server at runtime. To install the plugin via my.cnf, add the following line underneath [mysqld], then restart the server:

plugin-load-add=validate_password.so

To load the plugin at runtime, run the following statement:

INSTALL PLUGIN validate_password SONAME ‘validate_password.so’;

To load the plugin at runtime and prevent it from being removed, add validate-password=FORCE_PLUS_PERMANENT to my.cnf.

To prevent the server from running if the plugin is not initialized, use the –validate-password option with a value of FORCE or FORCE_PLUS_PERMANENT.

The password strength policy can also be changed: to do so, change the validate_password_policy value to LOW, MEDIUM, or STRONG. The value of LOW checks only password length, MEDIUM policy adds some conditions and STRONG policy adds the condition that password substrings that consist of 4 or more characters must not match words in a dictionary file that can be specified by modifying the validate_password_dictionary_file variable.

Keyring Plugins

Keyring plugins can enable server components and plugins to securely store sensitive information for retrieval. To load the plugin into MySQL, add the following underneath [mysqld]:

early-plugin-load=keyring_file.so

To specify the keyring vault file, add the following (the keyring_vault_config variable should point to the configuration file):

loose-keyring_vault_config=”/var/lib/mysql_keyring/keyring_vault.conf”

The keyring file should contain the vault_url variable which defines the vault server address, the secret_mount_point variable which defines the mount point name where the keyring vault stores the keys, and a token which should be defined by the vault server. Optionally, the vault_ca variable can also be defined (it should point to the CA certificate used to sign the vault’s certificates).

Restart the server for the changes to take effect;

Audit Plugins

Audit plugins can enable monitoring, logging, and blocking of activity performed on MySQL servers. To install MySQL Enterprise Audit, run a script located in the share directory of your MySQL instance (avoid putting your MySQL instance password in the terminal – use my.cnf):

mysql < /path/to/audit_log_filter_linux_install.sql

You can also prevent the plugin from being removed at runtime - add the following in the [mysqld] section:

audit_log=FORCE_PLUS_PERMANENT

Restart the server to apply the changes. Do note that the rule-based logging logs no auditable events by default, so to make it log everything, create a filter:

SELECT audit_log_filter_set_filter(‘log_filter’, ‘{ “filter”: { “log”: true } }’);

Then assign it to an account:

SELECT audit_log_filter_set_user(‘%’, ‘log_filter’);

Note that audit plugins are only available in MySQL Enterprise Edition;

Firewall Plugins

Firewall plugins can enable users to permit or deny the execution of specific SQL statements based on specific patterns. MySQL Enterprise Firewall was introduced in MySQL 5.6.24 - it is able to protect data by monitoring, alerting, and blocking unauthorized activity: it is able to block SQL injection attacks, monitor threats, and block suspicious traffic as well as to detect intrusions into the database. The firewall is also able to log blocked statements - they can be inspected and a real-time count of approved and rejected statements can also be observed.

To install MySQL Enterprise Firewall, simply enable it when installing MySQL Server on Windows, it can also be installed, disabled, or uninstalled with the help of MySQL Workbench 6.3.4. The firewall can also be installed manually by running a script in the share directory of your MySQL installation. To enable the firewall, add the following line underneath [mysqld] and restart the server:

mysql_firewall_mode=ON

The firewall can also be enabled at runtime:

SET GLOBAL mysql_firewall_mode = ON;

Alternatively, to persist the firewall (meaning that the firewall will not have to be re-enabled on each subsequent server restart):

SET PERSIST mysql_firewall_mode = ON;

Then, grant a FIREWALL_ADMIN privilege to any account that administers the firewall and the FIREWALL_USER privilege to any account that should only have access to its own firewall rules. Also,  grant the EXECUTE privilege for the stored procedures of the firewall in the mysql database. In order for the firewall to function, register profiles with it, then train the firewall to know the allowed statements that the database can execute and afterwards tell the firewall to match incoming statements against the set whitelist. Each profile has an operational mode - OFF, RECORDING, PROTECTING or DETECTING. OFF disables the profile, RECORDING trains the firewall, PROTECTING allows or denies statement execution and DETECTING detects (but does not block) intrusion attempts. Rules for a specified profile can be reset by setting its value to RESET. OFF will disable the profile. To set the mode, use the following query where name is the profile name and OFF is the operational mode: 

CALL mysql.sp_set_firewall_mode(name, ‘OFF’);

The firewall plugin is also only available in MySQL Enterprise Edition.

Securing MySQL Backups

As far as MySQL backups are concerned, you have a couple of options.

  • If you’re using mysqldump, you can store your username and password in my.cnf and invoke mysqldump like so (the following command will dump all databases into a file /home/backup.sql):
$ mysqldump --defaults-extra-file=/var/lib/my.cnf --single-transaction --all-databases > /home/backup.sql
  • ​By storing your username and password inside of my.cnf, you do not write your password inside the terminal - such a method for taking backups is more secure because while the dump is running the command can be seen via the ps ax command.

  • You can also consider using mysqldump-secure which is a POSIX-compliant wrapper script which is capable of compressing and encrypting backups with strong security in mind.

  • Backups can be encrypted by using OpenSSL - simply take your backup, then encrypt it with the following command:

    $ openssl enc -aes-256-cbc -salt -in backup.tar.gz -out backup.tar.gz.enc -k password

    The command above will create a new encrypted file backup.tar.gz.enc in the current directory. The file will be encrypted with the password you chose (replace password with your desired password). The file can be decrypted later by running the following command:

    $ openssl aes-256-cbc -d -in backup.tar.gz.enc -out backup.tar.gz -k password

    Replace password with your password.

  • mysqldump has another option to encrypt your backups (the following example also compresses them with gzip):

    $ mysqldump --all-databases --single-transaction --triggers --routines | gzip | openssl  enc -aes-256-cbc -k password > backup.xb.enc

    Replace password with your desired password.

  • You can also encrypt your backups using mariabackup or xtrabackup. Here’s an example from the MariaDB documentation:

    $ mariabackup --user=root --backup --stream=xbstream  | openssl  enc -aes-256-cbc -k password > backup.xb.enc

    Replace password with your desired password.

  • Backups can also be encrypted using ClusterControl - if the encryption option is enabled for a particular backup, ClusterControl will encrypt the backup using AES-256 CBC (encryption happens on the backup node). If the backup is stored on a controller node, the backup files are streamed in an encrypted format using socat or netcat. If compression is enabled, ClusterControl will first compress the backup, after that - encrypt it. The encryption key will be generated automatically if it does not exist, then stored inside CMON configuration in the backup_encryption_key option. Keep in mind that this key is encoded and should be decoded first. To do so, run the following command:

    $ cat /etc/cmon.d/cmon_ClusterID.cnf | grep ^backup_encryption_key | cut -d"'" -f2 | base64 -d > keyfile.key

    The command will read the backup_encryption_key and decode its value to a binary output. The keyfile can be used to decrypt the backup like so:

    $ cat backup.aes256 | openssl enc -d -aes-256-cbc -pass file:/path/to/keyfile.key > backup_file.xbstream.gz

    For more examples, check the ClusterControl documentation.

Conclusion

In these posts about MySQL security we covered some security measures that can be of good use if you feel the need to tighten the security of your MySQL instance(s). While we did not cover absolutely everything, we feel that these points can be a good starting point when tightening the security of your MySQL installation. Take from these posts what you will, do your own research, and apply the security measures most applicable in your situation.

Subscribe below to be notified of fresh posts