{"id":94296,"date":"2022-05-20T02:58:26","date_gmt":"2022-05-20T02:58:26","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=94296"},"modified":"2022-05-27T16:45:04","modified_gmt":"2022-05-27T16:45:04","slug":"security-in-mysql-part-one","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/security-in-mysql-part-one\/","title":{"rendered":"Security in MySQL: Part One"},"content":{"rendered":"<p>Security is a critical part of any infrastructure \u2013 it\u2019s even more so in the database world, where one step in the wrong direction can be the cause of system disruptions and downtime, customer dissatisfaction, and in the worst-case scenario \u2013 deaths.<\/p>\n<p>As MySQL is one of the primary relational database management systems being used in the world today, it\u2019s critical to understand how to go about properly the database infrastructure to keep data breaches away from the organization\u2019s websites or, if they\u2019ve happened already, to become a thing of the past.<\/p>\n<h2>Why secure MySQL?<\/h2>\n<p>First, the basics \u2013 why should you secure your MySQL infrastructure in the first place?<\/p>\n<p>You see, chances are that both you and people that use software developed by you, whether knowingly or not, provide the website with data that is then processed by a database. Regardless of what the website is about, data is still a critical part of any database-backed infrastructure. Users inside of the database mean more exposure to a specific project, blog posts probably mean marketing, and products and customers inside of it usually mean revenue to your business.<\/p>\n<p>However, the sad fact is that people usually think about securing their most critical infrastructure only when it\u2019s too late to do so; once a data breach happens, everyone\u2019s scrambling to <a href=\"https:\/\/breachdirectory.com\/\">save themselves from identity theft by using data breach search engines and archives<\/a> and putting efforts into research related to how they should go about protecting their database in the future. As sad as it would be to state it, such efforts are often futile; if you don\u2019t put efforts into database security before a thief comes knocking on the door, the data taken from a MySQL database will be exploited for identity theft purposes, sold (then reused for identity theft attacks), twisted in other ways, and finally, archived somewhere in a dark alley of black-hat hackers evaluating the strength of the next database \u2013 their next upcoming victim. Doesn\u2019t sound like a very promising scenario, does it?<\/p>\n<h2>How to secure MySQL?<\/h2>\n<p>The security of MySQL infrastructure can be conveniently split into multiple categories:<\/p>\n<ul>\n<li>Access control.<\/li>\n<li>User security.<\/li>\n<li>The components and plugins that help keep MySQL safe.<\/li>\n<li>Security considerations and general security guidelines.<\/li>\n<li>Enterprise-level security controls.<\/li>\n<\/ul>\n<p>In the list above, I have outlined seemingly few categories \u2013 however, if you think about it, the majority of security issues fall somewhere within their range from one side or another: improper access control is often the cause of SQL injection escalation, insecure user accounts might mean guessable passwords, missing security plugins mean missing out on the security features offered by MySQL which makes the job easier for an attacker.<\/p>\n<p>I\u2019m now going to go through these points one by one in detail. One article is probably not sufficient to cover all of these issues, though, so if something\u2019s missing in this one, please head over to the following parts of this guide.<\/p>\n<h2>Access control in MySQL<\/h2>\n<p>Controlling access in MySQL is arguably the cornerstone of its security. After all, if access to every resource were so heavily restricted that nobody except the people who absolutely need it could access it, there would be many fewer issues related to data theft, right?<\/p>\n<p>While there is a degree of truth in that statement because, with MySQL, everything gets a little more complex. In this RDBMS, access control usually entails several aspects, including, but not limited to the following:<\/p>\n<ul>\n<li>Properly setting up privileges for every user.<\/li>\n<li>Limiting a specific account.<\/li>\n<li>Account locking and unlocking.<\/li>\n<li>User activity control.<\/li>\n<li>Password security.<\/li>\n<\/ul>\n<p>Properly setting up privileges is one of the most important aspects of access control. Simple <code>GRANT<\/code> and <code>REVOKE<\/code> statements can either give or, as the name suggests, revoke privileges to and from users. The proper assignment of privileges is said to be crucial for the security of any infrastructure backed by MySQL; the stronger strategy of assigning the privileges is, the less chance the attacker has to penetrate the defenses.<\/p>\n<p>Privileges span multiple categories: they can be either administrative (meaning that they can enable users to control what operations MySQL performs and how it does it), they can apply to a specific database, or span database objects (i.e., things that are stored inside a database.) Some of the privileges are as follows:<\/p>\n<table>\n<tbody>\n<tr>\n<td>\n<p><strong>Privilege<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>Explanation<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>ALL<\/p>\n<\/td>\n<td>\n<p>Granting this privilege would, as the privilege itself suggests, grant ALL privileges to a specific user. Dangerous from a security point of view, but can be used in a local environment when testing features and whatnot.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>CREATE<\/p>\n<\/td>\n<td>\n<p>Enables a specific user to create databases and tables within them.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>ALTER<\/p>\n<\/td>\n<td>\n<p>Enables a user to alter a specific table \u2013 in other words, enables a user to change table structure.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>DELETE or DROP<\/p>\n<\/td>\n<td>\n<p>Enables a user to delete rows or drop entire tables and databases.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p>Privileges in MySQL also have a couple of brothers named roles. Roles are collections of privileges, and granting a specific role to a user means granting all of the privileges assigned to that role; it\u2019s all pretty self-explanatory. Find the full list of privileges <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/privileges-provided.html\">here<\/a>, and for a more thorough explanation of what privileges are and what they do, head over to <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/roles.html\">this part of the documentation<\/a>.<\/p>\n<p>It can be very useful to limit specific accounts, but such a feature is often overlooked. It\u2019s possible to set a limit on the number of queries that can be issued every hour by a specific account, how many times an account can connect to the server, how many connections can a specific account make to the server at one moment, etc. The <code>MAX_USER_CONNECTIONS<\/code> variable defines how many connections a given account can make at the same time; the <code>MAX_CONNECTIONS_PER_HOUR<\/code> variable will define how many connections per hour can be made; <code>MAX_UPDATES_PER_HOUR<\/code> will set boundaries on how many <code>UPDATE<\/code> queries can be run in an hour, and so on. The usage of such variables will look similar to the following query (replace <code>YOUR_VARIABLE<\/code> with the variable you want to set.) Also, keep in mind that all variables set by the client revert back to normal if they\u2019re not specified inside of my.cnf.<\/p>\n<pre class=\"theme:undefined lang:tsql decode:true \">ALTER USER \u2018your_user\u2019@\u2019localhost\u2019 WITH [YOUR_VARIABLE] 0;\r\n<\/pre>\n<p>Accounts can also easily be locked (that prohibits any further activity from originating from them) by issuing a simple <code>ACCOUNT<\/code> <code>LOCK<\/code> command. It works when creating or modifying the account:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">[CREATE USER \/ ALTER USER] demo_account IDENTIFIED BY \u2018verystrongpasswordhere\u2019 ACCOUNT LOCK;<\/pre>\n<p>You can check if a specific account is locked by issuing a query such as the one below which also tells on which which host it resides):<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-94297\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/05\/word-image-7.png\" alt=\"Image with query SELECT host, account_locked FROM mysql.user where user = 'root'; and results localhost, N\" width=\"778\" height=\"241\" \/><\/p>\n<p>You can unlock an account by specifying <code>ACCOUNT<\/code> <code>UNLOCK<\/code> instead of <code>ACCOUNT<\/code> <code>LOCK<\/code>. That\u2019s it!<\/p>\n<p>Password security comes down to a couple of simple things as well: MySQL allows password expiration. You can set a \u201clifetime\u201d for passwords by using a <code>default_password_lifetime<\/code> variable in <em>my.cnf<\/em>. You can also impose password reuse restrictions through the use of <code>password_history<\/code>, which defines how many previous passwords need to be \u201cblacklisted\u201d before being allowed to be used again, and <code>password_reuse_interval<\/code>, which prohibits using any passwords that were used within the last X days.<\/p>\n<h2>User security<\/h2>\n<p>Another thing closely related to access control and password security is the security of MySQL users themselves. The majority of the recommendations within this sphere can seem pretty basic at first glance, but when combined with the things mentioned above, they all can create a powerful force of security within MySQL. To further user account security within MySQL, consider not sending passwords over plain text and send them using SSL instead (use the <code>REQUIRE<\/code> <code>SSL<\/code> option when creating a user once SSL is configured on your server), avoid running MySQL as root (the root user has the FILE privilege which can cause the server to create files), and, of course, require all users to have strong passwords. I will elaborate on this in the next part of this series, but completing the outlined steps should be a good preventative to ensure that your database stays secure whatever happens.<\/p>\n<h2>Summary<\/h2>\n<p>I hope that this article has widened your horizons in the MySQL space. Follow the advice outlined in this blog post by tightening up your users and using proper access controls, and your database will surely ride fast on the security highway. However, that\u2019s not it. To properly secure your database, you will also need to take care of plugins that help keep MySQL safe from attacks, keep an eye on enterprise-level security control, and follow general security guidelines whenever you\u2019re working with it.<\/p>\n<p>I hope that this article has been informational, and you will share it (or at least the knowledge contained within this article) with your friends to help them secure their MySQL instances. My <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/security-in-mysql-part-two\/\">next article<\/a> will cover more ways to secure MySQL. Until next time, be sure to put what you\u2019ve learned in practice.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are several tools available to keep a MySQL database secure. In this article Lukas Vileikis discuses access control and user privileges.&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],"coauthors":[146040],"class_list":["post-94296","post","type-post","status-publish","format-standard","hentry","category-featured","category-mysql","tag-automate"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94296","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=94296"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94296\/revisions"}],"predecessor-version":[{"id":94508,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94296\/revisions\/94508"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=94296"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=94296"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=94296"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=94296"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}