RIP mysql_native_password

This presentation discusses the deprecation and removal of the mysql_native_password authentication method in MySQL.

The history of MySQL password authentication methods, with mysql_native_password being introduced in MySQL 4.1 (2004), replaced as default in 8.0 (2018), deprecated in 8.4 (2024), and removed in 9.0 (2024)
The key points covered include:

- Security vulnerabilities of mysql_native_password
- The superior security features of caching_sha2_password (the replacement):
- Migration challenges and practical considerations:
- Upgrading to MySQL 8.0+ while maintaining compatibility
- Best practices for password security in MySQL environments

Transcript

2. RIP mysql_native_password Ronald Bradford April 2025 https://ronaldbradford.com/presentations/ me@ronaldbradford.com
3. Agenda • What is mysql_native_password? • Why is it now RIP? • Some password history • What comes next! • The ecosystem impact
4. About Me Author | Speaker | Contributor • 1999 - Started using MySQL (pre 3.23) (26/30) • 2006 - First MySQL Conference Presentation (19/30) • 2010 - Oracle ACE Director (Alumni) • 2025 - 180+ presentations (19 years) https://ronaldbradford.com 26/30
5. What is mysql_native_password? • 2004 - Introduced as new password format (4.1) • 2018 - Replaced as default (8.0) • 2024 - Deprecated (8.4) • 2024 - Officially removed (9.0) • Still in wide general use 20/30 *7F398A5DFFD3AB4D103B9CC9FD19B35927BF9178 41
6. MySQL 8.4 Release Notes MySQL native password authentication changes. Beginning with MySQL 8.4.0, the deprecated mysql_native_password authentication plugin is no longer enabled by default. To enable it, start the server with --mysql-native-password=ON (added in MySQL 8.4.0), or by including mysql_native_password=ON in the [mysqld] section of your MySQL configuration file (added in MySQL 8.4.0). The default_authentication_plugin system variable, deprecated in MySQL 8.0.27, is removed as of MySQL 8.4.0. Use authentication_policy instead. As part of the removal of default_authentication_plugin , the syntax for authentication_policy has been changed. See the description of authentication_policy for more information. https://dev.mysql.com/doc/refman/8.4/en/mysql-nutshell.html
7. Before mysql_native_password • 3.23 supported format ◦ old_passwords (4.1 name) ◦ OLD_PASSWORD('password') • Still available in 5.7 (GA 2015, EOL 2023) • Brief lived intermediate format in 4.1 20/30 65483ee463b5f312 16
8. Documentation Links https://dev.mysql.com/doc/refman/5.7/en/password-hashing.html https://dev.mysql.com/doc/refman/5.7/en/old-native-pluggable-authentication.html https://dev.mysql.com/doc/refman/5.7/en/account-upgrades.html
9. What is mysql_native_password? A DISABLED (but still available) MySQL plugin for user authentication. (*) As at MySQL 8.4 LTS (30 April 2024) Removed in MySQL 9.0+ mysql> SHOW PLUGINS; +----------------------------------+----------+--------------------+----------------+---------+ | Name | Status | Type | Library | License | +----------------------------------+----------+--------------------+----------------+---------+ | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL | | mysql_native_password | DISABLED | AUTHENTICATION | NULL | GPL | | auth_socket | ACTIVE | AUTHENTICATION | auth_socket.so | GPL | ...
10. Why is mysql_native_password insecure?
11. Why is mysql_native_password insecure? • Same password hash (i.e. no salt) • Weak algorithm (i.e. SHA1 + no iterations) ◦ Rainbow Tables - https://freerainbowtables.com/ • Easy to find password string • Brute force attack • Is there a password?
12. Reproducing a mysql_native_password value? mysql> CREATE USER demo1 IDENTIFIED BY 'MySQLSummit2025'; mysql> SELECT authentication_string FROM mysql.user WHERE user = 'demo1'; mysql> SELECT PASSWORD('MySQLSummit2025'); mysql> SELECT CONCAT('*',UPPER( SHA1(UNHEX(SHA1('MySQLSummit2025'))))) AS hash; +-------------------------------------------+ | hash | +-------------------------------------------+ | *7F398A5DFFD3AB4D103B9CC9FD19B35927BF9178 | +-------------------------------------------+ Other Methods
13. Finding a mysql_native_password hash value? • Find an insecure backup $ strings <data_dir>/mysql/user.MYD ... *6C387FC3893DBA1E3BA155E74754DA6682D04747 127.% msandbox mysql_native_password) *6C387FC3893DBA1E3BA155E74754DA6682D04747 localhost msandbox mysql_native_password) *6C387FC3893DBA1E3BA155E74754DA6682D04747 localhost
14. User Password Management 8.0+ (Native) CREATE USER demoN1 IDENTIFIED WITH mysql_native_password BY 'MySQL80#password'; CREATE USER demoN2 IDENTIFIED WITH mysql_native_password BY 'MySQL80#password'; mysql> SELECT host, user, authentication_string FROM mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | demoN1 | *FCB21307E6CAF5A98FA17960BC84464C80AF193E | | % | demoN2 | *FCB21307E6CAF5A98FA17960BC84464C80AF193E | ...
15. User Password Management 8.0+ (Native) CREATE USER demoN1 IDENTIFIED WITH mysql_native_password BY 'MySQL80#password'; CREATE USER demoN2 IDENTIFIED WITH mysql_native_password BY 'MySQL80#password'; ALTER USER demoN1 IDENTIFIED BY 'MySQL80#newpassword' RETAIN CURRENT PASSWORD; ALTER USER demoN1 DISCARD OLD PASSWORD; mysql> SELECT host, user, authentication_string FROM mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | demoN1 | *FCB21307E6CAF5A98FA17960BC84464C80AF193E | | % | demoN2 | *FCB21307E6CAF5A98FA17960BC84464C80AF193E | ... mysql> SELECT host, user, authentication_string FROM mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | demoN1 | *719AA93C773A2B1C852550945CF28C98B4D274C9 | | % | demoN2 | *FCB21307E6CAF5A98FA17960BC84464C80AF193E | https://dev.mysql.com/doc/refman/8.0/en/password-management.html#dual-passwords
16. The New Authentication
17. The 8.0 default - caching_sha2_password • SHA-256 algorithm • x 1000 iterations (5000) • Salt per password • Requires secure initial connection (TLS or RSA keypair) $A$005$Z[E~'9hBXQbV5LnAhbX/U4Do/I0BWYI.BzDrMkwdHYDT1JHdddRpxF8/5 https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html 59
18. The 8.0 default - caching_sha2_password • $A$005${salt (20 bytes)}{digest (32 bytes)} https://github.com/mysql/mysql-server/blob/trunk/mysys/crypt_genhash_impl.cc#L175-L204 Expected format DELIMITER[digest_type]DELIMITER[iterations]DELIMITER[salt][digest] digest_type: A => SHA256 iterations: 005 => 5*ITERATION_MULTIPLIER salt: Random string. Length SALT_LENGTH digest: SHA2 digest. Length STORED_SHA256_DIGEST_LENGTH https://github.com/mysql/mysql-server/blob/trunk/sql/auth/sha2_password.cc#L402-L435 59
19. Reproducing a caching_sha2_password value? mysql> SELECT authentication_string FROM mysql.user WHERE user = 'demo2'; +------------------------------------------------------------------------+ | authentication_string | +------------------------------------------------------------------------+ jSRK.fIvsYgG8Px8pid/reZiHDmLXgZ6NqoNgqRtiJ3 | +------------------------------------------------------------------------+ mysql> SELECT PASSWORD('MySQLSummit2025'); -- fails mysql> SELECT <algo>??? -- No options mysql> CREATE USER demo2 IDENTIFIED BY 'MySQLSummit2025'; Does not work! Not ASCII readable
20. What is caching_sha2_password v sha256_password • MySQL 5.7 introduced sha256_password (not default) • MySQL 8.0 introduced caching_sha2_password (default) The caching_sha2_password plugin has these advantages, compared to sha256_password: • On the server side, an in-memory cache enables faster reauthentication of users who have connected previously when they connect again. (This server-side behavior is implemented only in MySQL 8.0 and higher.) • Support is provided for client connections that use the Unix socket-file and shared-memory protocols. https://dev.mysql.com/doc/refman/5.7/en/caching-sha2-pluggable-authentication.html
21. Migration Issues
22. “Authentication plugin ‘caching_sha2_password’ is not supported” SQLSTATE[HY000] [1524] Plugin 'mysql_native_password' is not loaded
23. A Cause of Server Password Issues • Dump 5.7 & Load 8.0 (i.e. CREATE USER) ◦ Authentication string changes • Upgrade 5.7 to 8.0 ◦ Authentication string stays the same • Add new users in 8.0 ◦ Potential mismatch
24. The problem with default caching_sha2_password? • No MySQL version with optional usage 5.7 > SHOW PLUGINS; +----------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+---------+---------+ | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | 5.7 > SELECT @@default_authentication_plugin; +---------------------------------+ | @@default_authentication_plugin | +---------------------------------+ | mysql native_password | +---------------------------------+ 1
25. The problem with default caching_sha2_password? • No MySQL version with optional usage 5.7 > SHOW PLUGINS; +----------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+---------+---------+ | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | 5.7 > SELECT @@default_authentication_plugin; +---------------------------------+ | @@default_authentication_plugin | +---------------------------------+ | mysql native_password | +---------------------------------+ 8.0 > SHOW PLUGINS; +----------------------------------+----------+------------------ | Name | Status | Type +----------------------------------+----------+------------------ | mysql_native_password | ACTIVE | AUTHENTICATION | sha256_password | ACTIVE | AUTHENTICATION | caching_sha2_password | ACTIVE | AUTHENTICATION 8.0 > SELECT @@default_authentication_plugin; +---------------------------------+ | @@default_authentication_plugin | +---------------------------------+ | caching_sha2_password | +---------------------------------+
26. The problem with default caching_sha2_password? • No MySQL version with optional usage 5.7 > SHOW PLUGINS; +----------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+---------+---------+ | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | 5.7 > SELECT @@default_authentication_plugin; +---------------------------------+ | @@default_authentication_plugin | +---------------------------------+ | mysql native_password | +---------------------------------+ 8.0 > SHOW PLUGINS; +----------------------------------+----------+------------------ | Name | Status | Type +----------------------------------+----------+------------------ | mysql_native_password | ACTIVE | AUTHENTICATION | sha256_password | ACTIVE | AUTHENTICATION | caching_sha2_password | ACTIVE | AUTHENTICATION 8.0 > SELECT @@default_authentication_plugin; +---------------------------------+ | @@default_authentication_plugin | +---------------------------------+ | caching_sha2_password | +---------------------------------+ 5.6 > SHOW PLUGINS; +----------------------------+----------+--------------------+---- | Name | Status | Type | Lib +----------------------------+----------+--------------------+---- | mysql_native_password | ACTIVE | AUTHENTICATION | NUL | mysql_old_password | ACTIVE | AUTHENTICATION | NUL | sha256_password | ACTIVE | AUTHENTICATION | NUL 5.6 > SELECT @@default_authentication_plugin; ERROR 1193 (HY000): Unknown system variable 'default_authenticatio
27. The problem with default caching_sha2_password? • No MySQL version with optional usage 5.7 > SHOW PLUGINS; +----------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+---------+---------+ | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | 5.7 > SELECT @@default_authentication_plugin; +---------------------------------+ | @@default_authentication_plugin | +---------------------------------+ | mysql native_password | +---------------------------------+ 8.0 > SHOW PLUGINS; +----------------------------------+----------+------------------ | Name | Status | Type +----------------------------------+----------+------------------ | mysql_native_password | ACTIVE | AUTHENTICATION | sha256_password | ACTIVE | AUTHENTICATION | caching_sha2_password | ACTIVE | AUTHENTICATION 8.0 > SELECT @@default_authentication_plugin; +---------------------------------+ | @@default_authentication_plugin | +---------------------------------+ | caching_sha2_password | +---------------------------------+ 5.6 > SHOW PLUGINS; +----------------------------+----------+--------------------+---- | Name | Status | Type | Lib +----------------------------+----------+--------------------+---- | mysql_native_password | ACTIVE | AUTHENTICATION | NUL | mysql_old_password | ACTIVE | AUTHENTICATION | NUL | sha256_password | ACTIVE | AUTHENTICATION | NUL 5.6 > SELECT @@default_authentication_plugin; ERROR 1193 (HY000): Unknown system variable 'default_authenticatio 5.5 > SHOW PLUGINS; +----------------------------+----------+--------------------+--- | Name | Status | Type | Li +----------------------------+----------+--------------------+--- | mysql_native_password | ACTIVE | AUTHENTICATION | NU | mysql_old_password | ACTIVE | AUTHENTICATION | NU
28. User Portability Strategy 8.0> SHOW CREATE USER demo1; CREATE USER `demo1`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$?`Dl*ju9cW^ [M24t;\rjSRK.fIvsYgG8Px8pid/reZiHDmLXgZ6NqoNgqRtiJ3' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT Another 8.0> CREATE USER `demo2`... 2
29. User Portability Strategy 8.0> SHOW CREATE USER demo1; CREATE USER `demo1`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$?`Dl*ju9cW^ [M24t;\rjSRK.fIvsYgG8Px8pid/reZiHDmLXgZ6NqoNgqRtiJ3' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT Another 8.0> CREATE USER `demo2`... ERROR 1827 (HY000): The password hash doesn't have the expected format. Fails (8.0/8.4/9.0/9.3) 2
30. Required User Portability 8.0> SHOW CREATE USER demo1; CREATE USER `demo1`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$?`Dl*ju9cW^ [M24t;\rjSRK.fIvsYgG8Px8pid/reZiHDmLXgZ6NqoNgqRtiJ3' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT 8.0> SELECT host, user, CONVERT(authentication_string USING binary) FROM mysql.user WHERE user = 'demo1'; 0x244124303035243F60446C2A6A751F3963575E7F5B4D3234743B0D6A53524B2E66497673596747385078387069642F72655A6948446 D4C58675A364E716F4E67715274694A33 Another 8.0> CREATE USER `demo1`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035243F60446C2A6A751F3963575E7F5B4D3234743B0D6A53524B2E66497673596747385078387069642F72655A6948446 D4C58675A364E716F4E67715274694A33 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
31. Required User Portability 8.0> SHOW CREATE USER demo1; CREATE USER `demo1`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$?`Dl*ju9cW^ [M24t;\rjSRK.fIvsYgG8Px8pid/reZiHDmLXgZ6NqoNgqRtiJ3' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT 8.0> SELECT host, user, CONVERT(authentication_string USING binary) FROM mysql.user WHERE user = 'demo1'; 0x244124303035243F60446C2A6A751F3963575E7F5B4D3234743B0D6A53524B2E66497673596747385078387069642F72655A6948446 D4C58675A364E716F4E67715274694A33 Another 8.0> CREATE USER `demo1`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035243F60446C2A6A751F3963575E7F5B4D3234743B0D6A53524B2E66497673596747385078387069642F72655A6948446 D4C58675A364E716F4E67715274694A33 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT; Use pt-show-grants
32. User Password Management (Sha2) CREATE USER demoC1 IDENTIFIED WITH caching_sha2_password BY 'MySQL80#password'; CREATE USER demoC2 IDENTIFIED WITH c aching_sha2_password BY 'MySQL80#password'; mysql> SELECT host, user, authentication_string FROM mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | demoC1 | $A$005$v6[;OW@mpG/,TD0nZgXzlMw5s0kl48xt8/40DSLuoTu9zGNlNrRdg.bl/ | | % | demoC2 | $A$005$3[jJ%\h{I<1Mg7jhmxXrq/rR8e3E.m7faU0q6DnyGzKK7GRp3s6F/O2 |
33. User Password Management (Sha2) CREATE USER demoC1 IDENTIFIED WITH caching_sha2_password BY 'MySQL80#password'; CREATE USER demoC2 IDENTIFIED WITH caching_sha2_password BY 'MySQL80#password'; ALTER USER demoC1 IDENTIFIED BY 'MySQL80#newpassword' RETAIN CURRENT PASSWORD; ALTER USER demoC1 DISCARD OLD PASSWORD; mysql> SELECT host, user, authentication_string FROM mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | demoC1 | $A$005$v6[;OW@mpG/,TD0nZgXzlMw5s0kl48xt8/40DSLuoTu9zGNlNrRdg.bl/ | | % | demoC2 | $A$005$3[jJ%\h{I<1Mg7jhmxXrq/rR8e3E.m7faU0q6DnyGzKK7GRp3s6F/O2 | ... mysql> SELECT host, user, authentication_string FROM mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | demoC1 | $A$005$!9[V-l(!L.&Oh2"SOMlpneQq6ydThYLfAqrNBFv7khOK6phlVHVGQao7Yaag6|
34. User Password Management (Native to Sha2) 3 CREATE USER demoN1 IDENTIFIED WITH mysql_native_password BY 'MySQL80#password'; ALTER USER demoN1 IDENTIFIED WITH caching_sha2_password BY 'MySQL80#newerpassword' RETAIN CURRENT PASSWORD; ERROR 3894 (HY000): Current password can not be retained for user 'demoN1'@'%' because authentication plugin is being changed.
35. User Password Management (Native to Sha2) 3 CREATE USER demoN1 IDENTIFIED WITH mysql_native_password BY 'MySQL80#password'; ALTER USER demoN1 IDENTIFIED WITH caching_sha2_password BY 'MySQL80#newerpassword' RETAIN CURRENT PASSWORD; ERROR 3894 (HY000): Current password can not be retained for user 'demoN1'@'%' because authentication plugin is being changed. ALTER USER demoN1 IDENTIFIED WITH caching_sha2_password BY 'MySQL80#newerpassword';
36. User Password Management (Native to Sha2) ALTER USER demoN1 IDENTIFIED WITH caching_sha2_password BY 'MySQL80#newerpassword' ERROR 3894 (HY000): Current password can not be retained for user 'demoN1'@'%' because authentication plugin is being changed. ALTER USER demoN1 IDENTIFIED WITH caching_sha2_password; mysql> SELECT host, user, authentication_string FROM mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | demoN1 | | ALTER USER demoN1 IDENTIFIED WITH caching_sha2_password BY 'MySQL80#newerpassword' RETAIN CURRENT PASSWORD; ERROR 3894 (HY000): Current password can not be retained for user 'demoN1'@'%' because authentication plugin is being changed.
37. User Password Management (Native to Sha2) ALTER USER demoN1 IDENTIFIED WITH caching_sha2_password; mysql> SELECT host, user, authentication_string FROM mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | demoN1 | | $ docker exec -it baseline_mysql_8 mysql -udemoN1 -P3380 -hlocalhost Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 8.0.37 Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ALTER USER demoN1 IDENTIFIED WITH caching_sha2_password BY 'MySQL80#newerpassword' RETAIN CURRENT PASSWORD; ERROR 3894 (HY000): Current password can not be retained for user 'demoN1'@'%' because authentication plugin is being changed.
38. User Password Management (Native to Sha2) ALTER USER demoN1 IDENTIFIED WITH caching_sha2_password; mysql> SELECT host, user, authentication_string FROM mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | demoN1 | | $ docker exec -it baseline_mysql_8 mysql -udemoN1 -P3380 -hlocalhost Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 8.0.37 Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> SELECT USER(); ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. ALTER USER demoN1 IDENTIFIED WITH caching_sha2_password BY 'MySQL80#newerpassword' RETAIN CURRENT PASSWORD; ERROR 3894 (HY000): Current password can not be retained for user 'demoN1'@'%' because authentication plugin is being changed.
39. User Password Management (Native to Sha2) ALTER USER demoN1 IDENTIFIED WITH caching_sha2_password BY 'MySQL80#newerpassword' RETAIN CURRENT PASSWORD; ERROR 3894 (HY000): Current password can not be retained for user 'demoN1'@'%' because authentication plugin is being changed. ALTER USER demoN1 IDENTIFIED WITH caching_sha2_password; mysql> SELECT host, user, authentication_string FROM mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | demoN1 | | $ docker exec -it baseline_mysql_8 mysql -udemoN1 -P3380 -hlocalhost Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 8.0.37 Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> SELECT USER(); ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. ALTER USER demoN1 IDENTIFIED WITH caching_sha2_password; mysql> SELECT host, user, authentication_string, password_expired FROM mysql.user; +-----------+------------------+----------------------------------------------+------------------+ | host | user | authentication_string | password_expired | +-----------+------------------+----------------------------------------------+------------------+ | % | demoN1 | | Y |
40. Best Practice - The User Upgrade Path Pre MySQL 8.0 (GA 2018) application • Upgrade to MySQL 8.0 Database ◦ Keep using mysql_native_password • Upgrade client connector to 8.0+ compatible ◦ i.e. Both plugins are available • Switch users from mysql_native_password to caching_sha2_password ◦ Ideally create new users (Auditability) ◦ Use PASSWORD EXPIRE for old user
41. Best Practice - The User Upgrade Path Pre MySQL 8.0 (GA 2018) application • Upgrade to MySQL 8.0 Database ◦ Keep using mysql_native_password • Upgrade client connector to 8.0+ compatible ◦ i.e. Both plugins are available • Switch users from mysql_native_password to caching_sha2_password ◦ Ideally create new users (Auditability) ◦ Use PASSWORD EXPIRE for old user Mixed models supported via ProxySQL
42. Handling the Disabled Plugin (8.4) • Must be enabled by mysqld parameter + reboot • Must set default-auth for client [mysqld] mysql_native_password=ON $ mysql --default-auth=mysql_native_password https://dev.mysql.com/doc/refman/8.4/en/native-pluggable-authentication.html 8.4> CREATE USER demoN1 IDENTIFIED WITH mysql_native_password BY 'MySQL84#password'; ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded
43. An ecosystem problem? • Pre MySQL 8.0 client do not support caching_sha2_password • Upgrades didn't expose users not using caching_sha2_password • Upgrade before MySQL 8.4 - Native plugin is disabled • MySQL 9.x does not support mysql_native_password MySQL 8.0 GA - Apr 2018
44. Ecosystem example? $ pgloader --version pgloader version "3.6.3~devel" $ pgloader mysql://demo1:***@localhost:3306/airport postgresql://demo1:***@localhost:5432/airport 2025-04-20T21:40:34.010000Z LOG pgloader version "3.6.3~devel" 2025-04-20T21:40:34.011000Z LOG Data errors in '/tmp/pgloader/' 2025-04-20T21:40:34.043000Z LOG Migrating from #<MYSQL-CONNECTION mysql://demo1@localhost:3306/airport > 2025-04-20T21:40:34.043000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://demo1@localhost:5432/airport > 2025-04-20T21:40:34.057000Z ERROR mysql: Failed to connect to mysql at "localhost" (port 3306) as user "demo1": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled. https://github.com/dimitri/pgloader/issues/782 Issue Opened: 2018 Status: Open
45. Useful Password Options • Variables ◦ validate_password.policy ◦ validate_password.* • ALTER USER PASSWORD * • ALTER USER ACCOUNT *
46. MySQL 9.x+ • Can you write your own mysql_native_password plugin!
47. MySQL 9.x+ - Raised from the ashes (2025) • Can you write your own mysql_native_password plugin! https://blogs.oracle.com/mysql/post/mysql-90-its-time-to-abandon-the-weak-authentication-method
48. Conclusion • EASY - Creating users with new authentication • LESS EASY - Upgrading the database users ◦ New admin process • HARD - Replacing all client connectors • HARD - Parallel implementation
49. In Closing - Password Security 101 • Use stronger authentication rules • Use least-privileged authorization • Individual user per service, product, tool, monitoring • Be able to rotate passwords • Weakest link - Plain text password (in files) https://dev.mysql.com/doc/refman/9.2/en/create-user.html
50. Thank You https://ronaldbradford.com