Security in MySQL: Part Two

Welcome back to this series about improving the security in your MySQL infrastructure. The first part of this series touched upon the reasons why should you consider securing your MySQL environment and walked you through some of the security basics. It covered what happens after MySQL-related data breaches and also told you that the security of any MySQL infrastructure can be conveniently split into several parts, the main ones being:

• Access control.
• User security.
• The components and plugins that help keep MySQL safe from cyber crooks.
• Security considerations and general security guidelines.
• Enterprise-level security controls.

The first article of the series went through access control and user security; the second part will cover the rest of the security concerns outlined above.

Components That Keep MySQL Safe

When thinking about components that keep MySQL safe, it’s absolutely crucial to understand what components are in the first place. In this case, the majority of attention should be directed toward security plugins. Some people immediately associate the phrase “security plugins” with the MySQL Enterprise Firewall, which is a firewall monitoring databases for any unauthorized activity (the article will touch upon it in the section about Enterprise-level security controls). Still, there’s so much more than meets the eye. MySQL is kept safe by the following components:

• Plugins that control the authentication mechanism.
• Plugins that guard who and how can connect to MySQL.
• Plugins that help keep passwords secure.
• Enterprise-level security components.

MySQL offers a couple of authentication plugins to help developers choose the most suitable authentication method for their use case. Connection-related plugins are suitable for developers who might want to introduce delays to responses once a specified number of requests have been sent, password security plugins can help ensure that passwords are safe, and enterprise-level security controls are suitable for enterprise-sized businesses that need to make use of auditing and real-time security capabilities offered by MySQL. There are more authentication plugins, but here’s what you need to know on a high level:

Plugin	What’s it About?
mysql_native_password	One of the “main” authentication plugins available in MySQL. This plugin, as suggested by the name, implements “native” authentication. Native means the authentication that was in place before pluggable authentication was introduced.
sha256_password [1] and caching_sha2_password [2] (among a couple of others, see explanation to the side *)	All of these plugins deal with pluggable authentication. Pluggable authentication essentially refers to the fact that connections are made through a local Unix file. SHA256 password-based pluggable authentication ([1]) implements SHA-256 hashing for passwords while caching SHA-2 pluggable authentication ([2]) does the same thing but also adds caching to boost performance. * Pluggable authentication plugins also include plugins that enable the use of native Windows functions to authenticate users, enable users to send passwords to the server in plain text, and do a couple of other things. The full list of pluggable authentication plugins is available here.

As noted, though, there are also a couple of plugins that guard the connections to MySQL, and the main one is called CONNECTION_CONTROL. It essentially logs all connections to MySQL and, as of MySQL 5.7.17, logs them to a separate table called CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS which has two columns denoting who (what account) tried to connect (the column is called USERHOST) and how many failed connection attempts the account in question has incurred (the column is called FAILED_ATTEMPTS.)

To use this plugin, install it first – either run both of the commands specified below (use connection_control.dll instead of connection_control.so if you’re running Windows. This applies to all instances of *.so files):

INSTALL PLUGIN CONNECTION_CONTROL SONAME ‘connection_control.so’;

INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME ‘connection_control.so’;

Alternatively, add a line into my.cnf under the [mysqld] section, and you will be golden (if you choose to go down this route, don’t forget to restart the server afterwards):

plugin-load-add=connection_control.so

After you install the plugin, come back to the documentation and configure it according to your needs – the documentation should be able to help you with that.

Once connections are under control, it’s time to take care of password security. You know the drill – use alphanumeric characters and symbols, consider using more than 8 (or, preferably 16) symbols for maximum security, etc.

MySQL, however, is able to take a different approach. To start with, simply add the line below [mysqld] inside of your my.cnf file, then restart the MySQL daemon (mysqld):

plugin-load-add=validate_password.so

This line will activate the password validation plugin. To check if the plugin is enabled, issue a query like SHOW VARIABLES LIKE 'validate_password%'; and if the plugin is enabled, you should see something similar to the following (the results may vary depending on the plugin’s configuration):

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | ON     |
| validate_password_dictionary_file    |        |
| validate_password_length             | 16     |
| validate_password_mixed_case_count   | 3      |
| validate_password_number_count       | 2      |
| validate_password_policy             | LOW    |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+

The plugin can be very easily configured by adding options inside of my.cnf or inside of the command line. For all of the available options, head over to the documentation.

There are a couple of enterprise-based security components of interest as well:

Security Component	About
MySQL Enterprise Firewall	The firewall guards the perimeter of MySQL and monitors or blocks suspicious activity. The firewall is, unfortunately, not free and comes only with the enterprise edition of MySQL or more advanced counterparts – such licenses start from $5,000.
MySQL Enterprise Audit	Such a security component is also only available within the Enterprise edition of MySQL, and its primary purpose is to allow an organization to strengthen its security via security controls.

Enterprise security components will be covered in little more detail in a section dedicated to them, but before doing so, you should understand some general security guidelines and things you should consider before embarking on a longer security journey together with MySQL.

General Security Measures

No system is secure if the most basic – general – security measures are overlooked. The same goes for MySQL. Thankfully, securing infrastructure isn’t that hard and shouldn’t take much effort if you know what you’re doing. Here are some of the most important things to do:

Action That Needs Consideration	Why?
Consider running mysql_secure_installation prior to working with MySQL.	The script is especially important when configuring MySQL or its flavors (think MariaDB or Percona Server) for the first time. It allows setting a password for the root account, removes the test database (it can be accessed by anonymous users by default), and does a couple of other things.
Never run MySQL as root.	Such a path is dangerous because a root user has the FILE privilege, which enables reading and writing to files – see more details here.
Don’t store passwords inside of your database in plain text.	If the website you run MySQL on suffers a data breach (and that’s only a matter of time these days), all of your users’ passwords will be visible to an attacker. The attacker will use those passwords for identity theft and credential stuffing attacks to cause further harm. Instead, opt to use a one-way hashing function like Blowfish or BCrypt.
Only grant necessary privileges to users.	If any user has privileges that aren’t necessary for him to complete his duties, there’s a security risk. Should an attacker gain access to such an account via a data breach or a credential stuffing attack, the consequences can be very severe.
Lock accounts that are not in use.	If you are certain that you or anybody on your team won’t have a need to use an account neither now or in the future, it’s a good idea to lock it to prevent possible damage due to unauthorized access. Lock accounts by adding an ACCOUNT LOCK statement to a query, and then, if anybody tries to log in to a locked account, they will see the following message: ERROR 3118 (HY000): Access denied for user ‘demo@localhost’. Account is locked.
Back up your data frequently.	This one is obvious, but you would be surprised how many people still neglect the power of backups; backups are sometimes thought of as a last resort, but if your server fails or electricity goes out mid-query, backups can be real saviors! Also, when backing your data up, don’t forget that it’s equally important to test the backups that you are taking. Otherwise, how can you know that you will be able to quickly restore them?

Make sure to follow these general security measures at any time you find yourself working with MySQL. Also, consider installing one or more components into MySQL to keep yourself on the safe side, and finally, don’t forget that there are also multiple enterprise-level security controls that you can employ if you wish to go down that path.

Enterprise-level Security Controls

This article discussed some of the enterprise security controls MySQL lets you use earlier on, however, there are some more things to cover. For example, you already know that the enterprise edition of MySQL offers you the ability to use a custom firewall, but did you know that the enterprise edition of MySQL also comes with:

• MySQL Enterprise Backup capabilities – this tool is a beast in and of itself as it can offer hot backups without interrupting any of the transactions that are currently running in your MySQL instance, and it can also easily and quickly deal with all kinds of backups – no matter full, incremental, or partial. Not only that, it also offers backup encryption, compression, and validation, helping you make sure that your backups are in a top-notch shape both during the day and during the night.
• MySQL Enterprise Masking capabilities – this solution is self-explanatory, and it can help developers adhere to regulations such as GDPR and HIPAA by de-identifying and masking sensitive user data as required.
• MySQL Enterprise Authentication will let us authenticate all MySQL users by implementing Single Sign On (SSO) capabilities, so it’s worth taking a look into as well.
• MySQL Enterprise Encryption – this capability of MySQL enterprise edition lets DBAs and developers encrypt sensitive data as it passes through MySQL. It lets businesses encrypt data stored in MySQL and secures the most important assets using a combination of keys. For more details, head over to the documentation.
• MySQL Enterprise Monitor – what’s the use of top-notch security and performance tools when your database infrastructure isn’t even being monitored? The Enterprise Monitor capability will provide you and your business with an easy-to-understand way to manage and monitor what’s happening in your database clusters at any given moment, so you can be sure your data is safe whatever happens. Make sure to head over to MySQL and try the demo of the monitor yourself if you’re considering such an option beforehand, though.

To Recap

This series of MySQL security has gone through some of the aspects to help improve security in the aforementioned RDBMS. Take from these articles what you will, apply general security precautions to all of your MySQL-based architectures, and don’t forget that security is an always-evolving industry. To keep up with the news, make sure to read the documentation, stay around software engineering forums, and read blogs.

I sincerely hope that these two parts on MySQL security have provided you with an understanding of what security issues MySQL engineers face on a daily basis and how to best use them to your advantage. If you haven’t already, come back to the first part of this series, and I will see you in the next blog!

The post Security in MySQL: Part Two appeared first on Simple Talk.

from Simple Talk https://ift.tt/VtpnrGT
via