Tuesday, July 13, 2021

Do You Really Need to Upgrade from MySQL Native Password to Caching SHA256 Password Authentication?

    If you have not read MDS, PHP and authentication by my brilliant colleague LeFred please do so. The TL;DR is that most recent versions of the PHP support the MySQL 8.0 standard for authentication, Caching SHA 256, but with PHP 7.3 and earlier you will have to use the older MySQL Native Password authentication.

    Someone reached out to me with a direct message to ask me if they really needed to change from MySQL native to Caching SHA 256.  

    Well, what versions of MySQL are you using?  The good news is that they are running 8.0.23 in production, '25 in test and development. But all the accounts are using MySQL Native Authentication.

    What versions of PHP?  There was some hesitation before this question was answered.  Dev is 8.0.8, test is a mixture 8.0.8, 7.4.21, and 7.4.21, and production 7.4.21. With one little exception of 7.4.20 that they are trying to move away from, they promised, by the end of the month.

    Do we have to re-do all the accounts to access MySQL to this new method?  Well, it depends.   In this case they had several projects using for the most part unique schemas. And each had separate usernames & authentication strings -- they were not using the MySQL root account for everything thing.  Passwords were rotated a few times each year.  

    No direct access from the internet to the instances?  Well, there is one application that directly connects through a dedicated port on the firewall. Everything else that touches the MySQL instances are within the firewall.  And nobody really seems to check on just who is using that connection or audits activity on that direct connection.  

    The Caching SHA 256 method is much more secure hashing method.  It is required to use either a TLS connection or an unencrypted connection that supports password exchange using an RSA key pair.  Please see SHA-256 Pluggable Authentication. By default MySQL 8.0 sets up secure connections between the clients and servers.

    I asked if there was anything in their data that was sensitive, proprietary, covered by privacy laws, or anything they just did not want made public. Well, yes there was.   

Do They Need to Upgrade?

    I told this person, no, you do not need to immediately upgrade from MySQL native to SHA 256 but I would suggest that they plan to upgrade over the next several months.  Other than being paranoid about the one direct connection through the firewall, it sounded like they were keeping on top of things.  

    Being paranoid about the security of your data should be the default mode of and DBA or developer.  Going with a lower standard than the MySQL 8.0 defaults means you are actively downgrading your protection.  

    I recommend reading Protecting MySQL Passwords With the sha256_password Plugin from the MySQL Server Team Blog for more technical details on using SHA 256 authentication.