Sometimes it’s convenient to retrieve the user creation statement and to copy it to another server.
However, with the new authentication method used as default since MySQL 8.0, caching_sha2_password, this can become a nightmare as the output is binary and some bytes can be hidden or decoded differently depending of the terminal and font used.
Let’s have a look:
If we cut the create user statement and paste it into another server what will happen ?
We can see that we get the following error:
ERROR: 1827 (HY000): The password hash doesn't have the expected format.How could we deal with that ?
The solution to be able to cut & paste the authentication string without having any issue, is to change it as a binary representation (hexadecimal) like this:
And then replace the value in the user create statement:
But there is an easier way. MySQL Server provides the possibility to display the Create User Statement directly with the hexadecimal representation of the authentication string using the variable print_identified_with_as_hex:
The user creation succeeded, and now let’s test to connect to this second server using the same credentials:
Using MySQL Shell Plugins
I’ve updated the MySQL Shell Plugins available on GitHub to use the same technique to be able to cut & paste the user creation and the grants:
And for MySQL HeatWave on OCI ?
Can we use the generated user creation statement and grants with MySQL HeatWave ?
For the user creation, there is no problem and it will work. However for the grants there is a limitation as some of the grants are not compatible or allowed within MySQL HeatWave.
The list of grants allowed in HeatWave is available on this page.
Let’s try:
As you can see, some of the privileges are not allowed and the GRANT statements fail.
You have the possibility to ask to the MySQL Shell Plugin to strip the incompatible privileges, using the option ocimds=True:
Now you can use the generated SQL statements with a MySQL HeatWave instance:
Conclusion
As you can see, the default authentication plugin for MySQL 8.0 and 8.1 is more secure but can be complicated to cut and paste. But MySQL Server provides the required variable to generate statements that can transferred and pasted easily.
Enjoy MySQL !
Why do you store binary data in the text column? Looks like a bug to me. Shouldn’t the column instead be declared as being BINARY in the table definition?
Hello and Thank you for anoraker blog post!
I have relied on setting the variable
set persist|global print_identified_with_as_hex = 1 ;
And then be able to use show create user as normal.
Thank you Markus, I totally forgot about that variable 😀
So yes, you can do:
Hi Markus,
I’ve updated the post with your comment. Thank you !