Some fun is coming 
While I was writing the internal guidelines on how to migrate from MariaDB to Percona Server, I had to export the users accounts in a portable way. This given MariaDB uses some non standard syntax brings me to first test some external tools such as Fred https://github.com/lefred/mysqlshell-plugins/wiki/user#getusersgrants and our PT-SHOW-GRANTS tool.
Useless to say this had open a can worms, given first I had to fix/convert the specifics for MariaDB (not in the scope of this blog), then while testing I discover another nasty issue, that currently prevent us to easily export the new Passwords in MySQL 8 (and PS 8) when caching_sha2 is used.
So what is the problem I am referring to?
Well the point is that when you generate passwords with caching_sha2 (default in mysql 8) the password generated can (will) contain characters that are not portable, not even between mysql 8.
Let's see a practical example to understand.
If I use old mysql_native_password and I create a user such as:
create user dba@'192.168.1.%' identified with mysql_native_password by 'dba';
My authentication_string will be:
root@localhost) [(none)]>select user,host,authentication_string,plugin from mysql.user where user ='dba' order by 1,2; +------+-------------+-------------------------------------------+-----------------------+ | user | host | authentication_string | plugin | +------+-------------+-------------------------------------------+-----------------------+ | dba | 192.168.1.% | *381AD08BBFA647B14C82AC1094A29AD4D7E4F51D | mysql_native_password | +------+-------------+-------------------------------------------+-----------------------+
At this point if you want to export the user:
(root@localhost) [(none)]>show create user dba@'192.168.1.%'G
*************************** 1. row ***************************
CREATE USER for This email address is being protected from spambots. You need JavaScript enabled to view it..1.%: CREATE USER `dba`@`192.168.1.%` IDENTIFIED WITH 'mysql_native_password' AS '*381AD08BBFA647B14C82AC1094A29AD4D7E4F51D' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.01 sec)
You just need to use the text after the semicolon and all will work fine. Remember that when you want to preserve the already converted password you need to use the IDENTIFIED … AS <PW> not the BY or you will re-convert the password ;).
Anyhow .. this is simple and what we are all used to.
Now if you instead try to use caching_sha2 things will go differently:
root@localhost) [(none)]>create user dba@'192.168.4.%' identified with caching_sha2_password by 'dba';
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [(none)]>select user,host,authentication_string,plugin from mysql.user where user ='dba' order by 1,2;
+------+-------------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------+-------------+------------------------------------------------------------------------+-----------------------+
| dba | 192.168.1.% | *381AD08BBFA647B14C82AC1094A29AD4D7E4F51D | mysql_native_password |
| dba | 192.168.4.% | $A$005$@&%1H5iNQx|.l{N7T/GosA.Lp4EiO0bxLVQp8Zi0WY2nXLr8TkleQPYjaqVxI7 | caching_sha2_password |
+------+-------------+------------------------------------------------------------------------+-----------------------+
2 rows in set (0.00 sec)
Probably you will not see it here given that while converted on your screen the special characters will be replaced, but the password contains invalid characters.
If I try to extract the Create USER text I will get:
(root@localhost) [(none)]>show create user dba@'192.168.4.%'G
*************************** 1. row ***************************
CREATE USER for This email address is being protected from spambots. You need JavaScript enabled to view it..4.%: CREATE USER `dba`@`192.168.4.%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$@&%1H5iNQx|.l{N7T/GosA.Lp4EiO0bxLVQp8Zi0WY2nXLr8TkleQPYjaqVxI7' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.00 sec)
However if I try to use this text to generate the user after I drop it:
(root@localhost) [(none)]>drop user dba@'192.168.4.%';
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [(none)]>create user dba@'192.168.4.%' IDENTIFIED AS 'NQx|.l{N7T/GosA.Lp4EiO0bxLVQp8Zi0WY2nXLr8TkleQPYjaqVxI7';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS 'NQx|.l{N7T/GosA.Lp4EiO0bxLVQp8Zi0WY2nXLr8TkleQPYjaqVxI7'' at line 1
Don’t waste time, there is nothing wrong in the query, except the simple fact that you CANNOT use the text coming from the authentication_string when you have caching_sha2.
So? What should we do?
The answer is easy, we need to convert the password into binary and use/store that.
Let us try.
First create the user again:
(root@localhost) [(none)]>select user,host,authentication_string,plugin from mysql.user where user ='dba' order by 1,2;
+------+-------------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------+-------------+------------------------------------------------------------------------+-----------------------+
| dba | 192.168.1.% | *381AD08BBFA647B14C82AC1094A29AD4D7E4F51D | mysql_native_password |
| dba | 192.168.4.% | $A$005$X>ztS}WfR"k~aH3Hs0hBbF3WmM2FXubKumr/CId182pl2Lj/gEtxLvV0 | caching_sha2_password |
+------+-------------+------------------------------------------------------------------------+-----------------------+
2 rows in set (0.00 sec)
(root@localhost) [(none)]>exit
Bye
[root@master3 ps80]# ./mysql-3307 -udba -pdba -h192.168.4.57 -P3307
...
(This email address is being protected from spambots. You need JavaScript enabled to view it.) [(none)]>
OK as you can see I create the user and can connect, but as we know the PW is not portable.
Let us convert it and create the user:
(root@localhost) [(none)]>select user,host,convert(authentication_string using binary),plugin from mysql.user where user ='dba' and host='192.168.4.%' order by 1,2; +------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+ | user | host | convert(authentication_string using binary) | plugin | +------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+ | dba | 192.168.4.% | 0x2441243030352458193E107A74537D0157055C66527F226B7E5C6148334873306842624633576D4D32465875624B756D722F434964313832706C324C6A2F674574784C765630 | caching_sha2_password | +------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+
So the password is:
0x2441243030352458193E107A74537D0157055C66527F226B7E5C6148334873306842624633576D4D32465875624B756D722F434964313832706C324C6A2F674574784C7656
Let us use it:
(root@localhost) [(none)]>drop user dba@'192.168.4.%'; Query OK, 0 rows affected (0.02 sec) (root@localhost) [(none)]>create user dba@'192.168.4.%' IDENTIFIED with 'caching_sha2_password' AS 0x2441243030352458193E107A74537D0157055C66527F226B7E5C6148334873306842624633576D4D32465875624B756D722F434964313832706C324C6A2F674574784C765630; Query OK, 0 rows affected (0.03 sec)
Let us check the user now:
(root@localhost) [(none)]>select user,host, authentication_string,plugin from mysql.user where user ='dba' and host= '192.168.4.%' order by 1,2; +------+-------------+------------------------------------------------------------------------+-----------------------+ | user | host | authentication_string | plugin | +------+-------------+------------------------------------------------------------------------+-----------------------+ | dba | 192.168.4.% | $A$005$X>ztS}WfR"k~aH3Hs0hBbF3WmM2FXubKumr/CId182pl2Lj/gEtxLvV0 | caching_sha2_password | +------+-------------+------------------------------------------------------------------------+-----------------------+ 1 row in set (0.00 sec) [root@master3 ps80]# ./mysql-3307 -udba -pdba -h192.168.4.57 -P3307 (This email address is being protected from spambots. You need JavaScript enabled to view it.) [(none)]>select current_user(); +-----------------+ | current_user() | +-----------------+ | This email address is being protected from spambots. You need JavaScript enabled to view it..4.% | +-----------------+ 1 row in set (0.00 sec)
As you can see the user has been created correctly and password is again in encrypted format.
In short what you need to do when in need to export users from MySQL/PS 8 is:
- Read the user information
- Convert Password to hex format when plugin is caching_sha2
- Push the AS <password> converted to a file or any other way you were used to
Another possible solution is to use at session level the parameter print_identified_with_as_hex. If set causes SHOW CREATE USER to display such hash values as hexadecimal strings rather than as regular string literals. Hash values that do not contain unprintable characters still display as regular string literals, even with this variable enabled.
This at the end is exactly what Fred and I have done for our tools:
See:
- Fred: https://github.com/lefred/mysqlshell-plugins/commit/aa5c6bbe9b9aa689bf7266f5a19a35d0091f6568
- Pt-show-grants: https://github.com/percona/percona-toolkit/blob/4a812d4a79c0973bf176105b0d138ad0a2a46b2f/bin/pt-show-grants#L2058
Conclusions
MySQL 8 and Percona server comes with a more secure hashing mechanism caching_sha2_password which is also the default. However if you have the need to migrate users and you use your own tools to export and import the passwords, you must update them as indicated. Or use the Percona Toolkit tools that we keep up to date for you.
Have fun with MySQL!!