{"id":94500,"date":"2022-05-27T16:07:12","date_gmt":"2022-05-27T16:07:12","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=94500"},"modified":"2022-05-27T16:45:52","modified_gmt":"2022-05-27T16:45:52","slug":"security-in-mysql-part-two","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/security-in-mysql-part-two\/","title":{"rendered":"Security in MySQL: Part Two"},"content":{"rendered":"<p>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:<\/p>\n<ul>\n<li>Access control.<\/li>\n<li>User security.<\/li>\n<li>The components and plugins that help keep MySQL safe from cyber crooks.<\/li>\n<li>Security considerations and general security guidelines.<\/li>\n<li>Enterprise-level security controls.<\/li>\n<\/ul>\n<p>The <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/security-in-mysql-part-one\/\">first article of the series<\/a> went through access control and user security; the second part will cover the rest of the security concerns outlined above.<\/p>\n<h2><a id=\"post-94500-_zap2j47y9vya\"><\/a>Components That Keep MySQL Safe<\/h2>\n<p>When thinking about components that keep MySQL safe, it\u2019s 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 \u201csecurity plugins\u201d with the <a href=\"https:\/\/www.mysql.com\/products\/enterprise\/firewall.html\">MySQL Enterprise Firewall<\/a>, 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\u2019s so much more than meets the eye. MySQL is kept safe by the following components:<\/p>\n<ul>\n<li>Plugins that control the authentication mechanism.<\/li>\n<li>Plugins that guard who and how can connect to MySQL.<\/li>\n<li>Plugins that help keep passwords secure.<\/li>\n<li>Enterprise-level security components.<\/li>\n<\/ul>\n<p>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 <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-security-excerpt\/8.0\/en\/authentication-plugins.html\">authentication plugins<\/a>, but here\u2019s what you need to know on a high level:<\/p>\n<table>\n<tbody>\n<tr>\n<td>\n<p><strong>Plugin<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>What\u2019s it About?<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>mysql_native_password<\/p>\n<\/td>\n<td>\n<p>One of the \u201cmain\u201d authentication plugins available in MySQL. This plugin, as suggested by the name, implements \u201cnative\u201d authentication. Native means the authentication that was in place before pluggable authentication was introduced.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>sha256_password [1] and caching_sha2_password [2] (among a couple of others, see explanation to the side *)<\/p>\n<\/td>\n<td>\n<p>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.<\/p>\n<p>* 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 <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-security-excerpt\/5.6\/en\/authentication-plugins.html\">here<\/a>.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>As noted, though, there are also a couple of plugins that guard the connections to MySQL, and the main one is called <code>CONNECTION_CONTROL<\/code>. It essentially logs all connections to MySQL and, as of MySQL 5.7.17, logs them to a separate table called <code>CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS<\/code> 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 <code>FAILED_ATTEMPTS<\/code>.)<\/p>\n<p>To use this plugin, install it first &#8211; either run both of the commands specified below (use <code>connection_control.dll<\/code> instead of <code>connection_control.so<\/code> if you\u2019re running Windows. This applies to all instances of <em>*.so<\/em> files):<\/p>\n<pre class=\"lang:ps theme:powershell-ise\">INSTALL PLUGIN CONNECTION_CONTROL SONAME \u2018connection_control.so\u2019;\r\n\r\nINSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME \u2018connection_control.so\u2019;<\/pre>\n<p>Alternatively, add a line into my.cnf under the <code>[mysqld]<\/code> section, and you will be golden (if you choose to go down this route, don\u2019t forget to restart the server afterwards):<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true\">plugin-load-add=connection_control.so<\/pre>\n<p>After you install the plugin, come back to the documentation and configure it according to your needs &#8211; <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-security-excerpt\/5.6\/en\/connection-control-installation.html#connection-control-plugin-delay-configuration\">the documentation<\/a> should be able to help you with that.<\/p>\n<p>Once connections are under control, it\u2019s time to take care of password security. You know the drill &#8211; use alphanumeric characters and symbols, consider using more than 8 (or, preferably 16) symbols for maximum security, etc.<\/p>\n<p>MySQL, however, is able to take a different approach. To start with, simply add the line below <code>[mysqld]<\/code> inside of your <em>my.cnf<\/em> file, then restart the MySQL daemon (mysqld):<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true\">plugin-load-add=validate_password.so<\/pre>\n<p>This line will activate the password validation plugin. To check if the plugin is enabled, issue a query like <code>SHOW VARIABLES LIKE 'validate_password%'<\/code>; and if the plugin is enabled, you should see something similar to the following (the results may vary depending on the plugin\u2019s configuration):<\/p>\n<pre class=\"theme:powershell-output lang:ps decode:true \">mysql&gt; SHOW VARIABLES LIKE 'validate_password%';\r\n+--------------------------------------+--------+\r\n| Variable_name                        | Value  |\r\n+--------------------------------------+--------+\r\n| validate_password_check_user_name    | ON     |\r\n| validate_password_dictionary_file    |        |\r\n| validate_password_length             | 16     |\r\n| validate_password_mixed_case_count   | 3      |\r\n| validate_password_number_count       | 2      |\r\n| validate_password_policy             | LOW    |\r\n| validate_password_special_char_count | 1      |\r\n+--------------------------------------+--------+<\/pre>\n<p>The plugin can be very easily configured by adding options inside of <code>my.cnf<\/code> or inside of the command line. For all of the available options, head over to <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-security-excerpt\/5.6\/en\/validate-password-options-variables.html\">the documentation<\/a>.<\/p>\n<p>There are a couple of enterprise-based security components of interest as well:<\/p>\n<table>\n<tbody>\n<tr>\n<td>\n<p><strong>Security Component<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>About<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p><a href=\"https:\/\/www.mysql.com\/products\/enterprise\/firewall.html\">MySQL Enterprise Firewall<\/a><\/p>\n<\/td>\n<td>\n<p>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 &#8211; <a href=\"https:\/\/www.mysql.com\/products\/\">such licenses start from $5,000<\/a>.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p><a href=\"https:\/\/www.mysql.com\/products\/enterprise\/audit.html\">MySQL Enterprise Audit<\/a><\/p>\n<\/td>\n<td>\n<p>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.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>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.<\/p>\n<h2><a id=\"post-94500-_in0u571kcfqz\"><\/a>General Security Measures<\/h2>\n<p>No system is secure if the most basic &#8211; general &#8211; security measures are overlooked. The same goes for MySQL. Thankfully, securing infrastructure isn\u2019t that hard and shouldn\u2019t take much effort if you know what you\u2019re doing. Here are some of the most important things to do:<\/p>\n<table>\n<tbody>\n<tr>\n<td>\n<p><strong>Action That Needs Consideration<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>Why?<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>Consider running mysql_secure_installation prior to working with MySQL.<\/p>\n<\/td>\n<td>\n<p>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.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>Never run MySQL as root.<\/p>\n<\/td>\n<td>\n<p>Such a path is dangerous because a root user has the FILE privilege, which enables reading and writing to files &#8211; see more details <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/privileges-provided.html#priv_file\">here<\/a>.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>Don\u2019t store passwords inside of your database in plain text.<\/p>\n<\/td>\n<td>\n<p>If the website you run MySQL on suffers a data breach (and that\u2019s only a matter of time these days), all of your users\u2019 passwords will be visible to an attacker. The attacker will use those passwords for identity theft and <a href=\"https:\/\/www.imperva.com\/learn\/application-security\/credential-stuffing\/\">credential stuffing<\/a> attacks to cause further harm. Instead, opt to use a one-way hashing function like Blowfish or BCrypt.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>Only grant necessary privileges to users.<\/p>\n<\/td>\n<td>\n<p>If any user has privileges that aren\u2019t necessary for him to complete his duties, there\u2019s 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.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>Lock accounts that are not in use.<\/p>\n<\/td>\n<td>\n<p>If you are certain that you or anybody on your team won\u2019t have a need to use an account neither now or in the future, it\u2019s a good idea to lock it to prevent possible damage due to unauthorized access. Lock accounts by adding an <code>ACCOUNT LOCK<\/code> statement to a query, and then, if anybody tries to log in to a locked account, they will see the following message: <br \/>\n<code>ERROR 3118 (HY000): Access denied for user \u2018demo@localhost\u2019<\/code>. Account is locked.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>Back up your data frequently.<\/p>\n<\/td>\n<td>\n<p>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\u2019t forget that it\u2019s equally important to test the backups that you are taking. Otherwise, how can you know that you will be able to quickly restore them?<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>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\u2019t forget that there are also multiple enterprise-level security controls that you can employ if you wish to go down that path.<\/p>\n<h2><a id=\"post-94500-_vtitbeg9epi0\"><\/a>Enterprise-level Security Controls<\/h2>\n<p>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:<\/p>\n<ul>\n<li>MySQL Enterprise Backup capabilities &#8211; 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 &#8211; 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.<\/li>\n<li>MySQL Enterprise Masking capabilities &#8211; 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.<\/li>\n<li>MySQL Enterprise Authentication will let us authenticate all MySQL users by implementing Single Sign On (SSO) capabilities, so it\u2019s worth taking a look into as well.<\/li>\n<li>MySQL Enterprise Encryption &#8211; 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 <a href=\"https:\/\/www.mysql.com\/products\/enterprise\/encryption.html\">the documentation<\/a>.<\/li>\n<li>MySQL Enterprise Monitor &#8211; what\u2019s the use of top-notch security and performance tools when your database infrastructure isn\u2019t even being monitored? The Enterprise Monitor capability will provide you and your business with an easy-to-understand way to manage and monitor what\u2019s happening in your database clusters at any given moment, so you can be sure your data is safe whatever happens. Make sure to <a href=\"https:\/\/www.mysql.com\/products\/enterprise\/demo.html\">head over to MySQL and try the demo of the monitor<\/a> yourself if you\u2019re considering such an option beforehand, though.<\/li>\n<\/ul>\n<h2><a id=\"post-94500-_fzpbjn3bh0rl\"><\/a>To Recap<\/h2>\n<p>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\u2019t forget that security is an always-evolving industry. To keep up with the news, make sure to <a href=\"https:\/\/dev.mysql.com\/doc\/\">read the documentation<\/a>, stay around software engineering forums, and <a href=\"https:\/\/www.red-gate.com\/simple-talk\/\">read blogs<\/a>.<\/p>\n<p>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\u2019t already, come back to the first part of this series, and I will see you in the next blog!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There\u2019s more to security in MySQL than user account privileges. In this article, Lukas Vileikis explains the other components of MySQL security.&hellip;<\/p>\n","protected":false},"author":339547,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,145792],"tags":[95506,5854,4619],"coauthors":[146040],"class_list":["post-94500","post","type-post","status-publish","format-standard","hentry","category-featured","category-mysql","tag-automate","tag-mysql","tag-security"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94500","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/339547"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=94500"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94500\/revisions"}],"predecessor-version":[{"id":94509,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94500\/revisions\/94509"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=94500"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=94500"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=94500"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=94500"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}