{"id":14111,"date":"2020-05-08T17:37:04","date_gmt":"2020-05-08T15:37:04","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/"},"modified":"2020-05-08T17:37:04","modified_gmt":"2020-05-08T15:37:04","slug":"the-evolution-of-mysql-authentication-mechanism","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/","title":{"rendered":"The evolution of MySQL authentication mechanism"},"content":{"rendered":"<p>The authentication, the first level of security for each IT system, is the stage to verify the user identity through the basic username and password scheme. It is crucial to have a mechanism to protect and secure password storing and transmitting over network.<\/p>\n<p>In MySQL, there is plenty of different authentication methods available, and last versions improved the security of this concept.<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Auth-evolution-3.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-39510 size-full\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Auth-evolution-3.png\" alt=\"MySQL authentication\" width=\"1518\" height=\"173\" \/><\/a><br \/>\n<!--more--><br \/>\nAt the beginning, the mechanism, called <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/old-native-pluggable-authentication.html\"><strong>mysql_old_password<\/strong><\/a>, was pretty insecure: it\u2019s based on a broken hashing function and the password is 16 bytes long. It was not so complex for attackers to find a plaintext password from the hash stored in the <em>password<\/em> column of <em>mysql.user<\/em> table. It has been removed in MySQL 5.7.5.<\/p>\n<p>A new method was introduced in MySQL 4.1 and it became the <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/native-pluggable-authentication.html\"><strong>mysql_native_password<\/strong><\/a> plugin as of MySQL 5.5, enabled by default. It\u2019s based on a SHA-1 hashing algorithm and the password is 41 bytes long. On one side, it\u2019s more secure than mysql_old_password because hashes cannot be used to authenticate. But on the other side, it still has weaknesses, especially for \u201ctoo simple\u201d passwords, because for the same passwords we get the same hash. Again, it\u2019s not so complicated to search for the stolen hashes in rainbow tables to obtain the correspondent plaintext password.<\/p>\n<p>There have been other improvements in MySQL 5.6: <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/sha256-pluggable-authentication.html\"><strong>sha256_password<\/strong><\/a> plugin adds a random salt in hashes generation, so this last one is unique and rainbow tables are useless. In MySQL 8.0, default authentication remains strong (same SHA-256 password hashing mechanism) but in addition it becomes faster: a cache was added on the server side to enable a faster re-authentication for accounts that have connected previously. It&#8217;s called <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/caching-sha2-pluggable-authentication.html\"><strong>caching_sha2_password<\/strong><\/a> and it&#8217;s now the default plugin.<\/p>\n<p>This MySQL authentication evolution must be seriously considered by DBAs during upgrade processes and following aspects must not to be underestimated:<\/p>\n<ul>\n<li>Read <a href=\"https:\/\/dev.mysql.com\/doc\/\">MySQL official documentation<\/a> to prepare your upgrade procedure<\/li>\n<li>Don\u2019t forget to run mysql_upgrade or to restart the MySQL server with upgrade=FORCE depending on your MySQL version (after importing your dump): it not only examines user tables to find out possible incompatibilities, but upgrades also system tables in <em>mysql<\/em>, <em>performance_schema<\/em> and <em>sys<\/em> databases<\/li>\n<li>Try to keep your MySQL server up to date in order to avoid surprises or difficult procedures to put in place due to a too large delta (as an example, you can find <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-security-excerpt\/5.7\/en\/account-upgrades.html\">here<\/a> the procedure to migrate away from pre-4.1 password hashing method &#8211; good luck \ud83d\ude09 )<\/li>\n<li>As of MySQL 5.7.6, the <em>password<\/em> column was removed and values are now stored in the <em>authentication_string<\/em> column of <em>mysql.user<\/em> table<\/li>\n<li>As of MySQL 5.5, the <em>plugin<\/em> column has been added to <em>mysql.user<\/em> table and as of MySQL 5.7 the server doesn\u2019t enable any accounts with an empty <em>plugin<\/em> value<\/li>\n<li>As of MySQL 8.0, you have the choice to define one of the 3 following values for the <em>default_authentication_plugin<\/em> system variable: mysql_native_password, sha256_password and caching_sha2_password. But then you have also the possibility to set a different authentication plugin for a specific account using the following syntax: CREATE | ALTER USER \u2026 IDENTIFIED WITH <em>&lt;auth_plugin&gt;<\/em> [&#8230;];<\/li>\n<\/ul>\n<p>I would like to share with you three last considerations before closing this short blog post:<\/p>\n<ul>\n<li>Once again, keep your MySQL server up to date<\/li>\n<li>MySQL is getting better and better in terms of security<\/li>\n<li>\u201c<em>Security is always excessive until it\u2019s not enough.<\/em>\u201d (Robbie Sinclair, Head of Security, Country Energy, NSW Australia) \ud83d\ude09<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>The authentication, the first level of security for each IT system, is the stage to verify the user identity through the basic username and password scheme. It is crucial to have a mechanism to protect and secure password storing and transmitting over network. In MySQL, there is plenty of different authentication methods available, and last [&hellip;]<\/p>\n","protected":false},"author":42,"featured_media":14113,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,1316,149],"tags":[144,1934],"type_dbi":[],"class_list":["post-14111","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","category-database-management","category-mysql","category-security","tag-mysql","tag-mysql-authentication"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>The evolution of MySQL authentication mechanism - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The evolution of MySQL authentication mechanism\" \/>\n<meta property=\"og:description\" content=\"The authentication, the first level of security for each IT system, is the stage to verify the user identity through the basic username and password scheme. It is crucial to have a mechanism to protect and secure password storing and transmitting over network. In MySQL, there is plenty of different authentication methods available, and last [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-05-08T15:37:04+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Auth-evolution.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1785\" \/>\n\t<meta property=\"og:image:height\" content=\"203\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Elisa Usai\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Elisa Usai\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/\"},\"author\":{\"name\":\"Elisa Usai\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/ac5847ee8d7bad4196e72660c1377b1f\"},\"headline\":\"The evolution of MySQL authentication mechanism\",\"datePublished\":\"2020-05-08T15:37:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/\"},\"wordCount\":588,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Auth-evolution.png\",\"keywords\":[\"MySQL\",\"MySQL authentication\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\",\"MySQL\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/\",\"name\":\"The evolution of MySQL authentication mechanism - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Auth-evolution.png\",\"datePublished\":\"2020-05-08T15:37:04+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/ac5847ee8d7bad4196e72660c1377b1f\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Auth-evolution.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Auth-evolution.png\",\"width\":1785,\"height\":203},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The evolution of MySQL authentication mechanism\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/ac5847ee8d7bad4196e72660c1377b1f\",\"name\":\"Elisa Usai\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/d41c7e94b7e4cd42bdcc5b82003c00562de8da0cf0a1081fbe832d47e3a828ff?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d41c7e94b7e4cd42bdcc5b82003c00562de8da0cf0a1081fbe832d47e3a828ff?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d41c7e94b7e4cd42bdcc5b82003c00562de8da0cf0a1081fbe832d47e3a828ff?s=96&d=mm&r=g\",\"caption\":\"Elisa Usai\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/elisa-usai\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"The evolution of MySQL authentication mechanism - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/","og_locale":"en_US","og_type":"article","og_title":"The evolution of MySQL authentication mechanism","og_description":"The authentication, the first level of security for each IT system, is the stage to verify the user identity through the basic username and password scheme. It is crucial to have a mechanism to protect and secure password storing and transmitting over network. In MySQL, there is plenty of different authentication methods available, and last [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/","og_site_name":"dbi Blog","article_published_time":"2020-05-08T15:37:04+00:00","og_image":[{"width":1785,"height":203,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Auth-evolution.png","type":"image\/png"}],"author":"Elisa Usai","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Elisa Usai","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/"},"author":{"name":"Elisa Usai","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/ac5847ee8d7bad4196e72660c1377b1f"},"headline":"The evolution of MySQL authentication mechanism","datePublished":"2020-05-08T15:37:04+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/"},"wordCount":588,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Auth-evolution.png","keywords":["MySQL","MySQL authentication"],"articleSection":["Database Administration &amp; Monitoring","Database management","MySQL","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/","url":"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/","name":"The evolution of MySQL authentication mechanism - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Auth-evolution.png","datePublished":"2020-05-08T15:37:04+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/ac5847ee8d7bad4196e72660c1377b1f"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Auth-evolution.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Auth-evolution.png","width":1785,"height":203},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/the-evolution-of-mysql-authentication-mechanism\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"The evolution of MySQL authentication mechanism"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/ac5847ee8d7bad4196e72660c1377b1f","name":"Elisa Usai","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/d41c7e94b7e4cd42bdcc5b82003c00562de8da0cf0a1081fbe832d47e3a828ff?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/d41c7e94b7e4cd42bdcc5b82003c00562de8da0cf0a1081fbe832d47e3a828ff?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d41c7e94b7e4cd42bdcc5b82003c00562de8da0cf0a1081fbe832d47e3a828ff?s=96&d=mm&r=g","caption":"Elisa Usai"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/elisa-usai\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14111","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/42"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=14111"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14111\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/14113"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=14111"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=14111"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=14111"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=14111"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}