| Bug #98732 | Copying credentials with SHOW CREATE USER / caching_sha2_password not working | ||
|---|---|---|---|
| Submitted: | 25 Feb 2020 9:15 | Modified: | 26 Feb 2020 10:44 |
| Reporter: | Simon Mudd (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.19 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[26 Feb 2020 8:00]
Georgi Kodinov
Hello, cashing_sha2 (unlike mysql_native) sometimes might contain binary symbols that may or may not work well when copied over terminals etc. Can you please try with https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_print_identifi... ? This makes SHOW CREATE USER print it as hex.
[26 Feb 2020 8:08]
MySQL Verification Team
Thank you Joro(Shane suggested me the same other day). Hello Simon, Please try as Joro suggested? Thank you! regards, Umesh
[26 Feb 2020 8:12]
MySQL Verification Team
- 8.0.19 rm -rf 98732/ bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/98732 --log-error-verbosity=3 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/98732 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/98732/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv=/tmp/ --print-identified-with-as-hex=ON 2>&1 & bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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> show variables like 'print%'; +------------------------------+-------+ | Variable_name | Value | +------------------------------+-------+ | print_identified_with_as_hex | ON | +------------------------------+-------+ 1 row in set (0.01 sec) mysql> create user x identified by 'hello'; Query OK, 0 rows affected (0.01 sec) mysql> show create user x\G *************************** 1. row *************************** CREATE USER for x@%: CREATE USER 'x'@'%' IDENTIFIED WITH 'caching_sha2_password' AS 0x2441243030352448670E756C7E594E3C4C686B3E57723A23544D3A47496446335234614159366E2E6632594C4C6E76772F5464666F736764664C7142306947424F636A723031 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) mysql> CREATE USER 'x2'@'%' IDENTIFIED WITH 'caching_sha2_password' AS 0x2441243030352448670E756C7E594E3C4C686B3E57723A23544D3A47496446335234614159366E2E6632594C4C6E76772F5464666F736764664C7142306947424F636A723031 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT; Query OK, 0 rows affected (0.00 sec) mysql> show create user x2\G *************************** 1. row *************************** CREATE USER for x2@%: CREATE USER 'x2'@'%' IDENTIFIED WITH 'caching_sha2_password' AS 0x2441243030352448670E756C7E594E3C4C686B3E57723A23544D3A47496446335234614159366E2E6632594C4C6E76772F5464666F736764664C7142306947424F636A723031 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)
[26 Feb 2020 10:44]
Simon Mudd
I can confirm that use of print_identified_with_as_hex = 1 works. The value displayed can then be copied to another server or used with a different username and it works. Documentation shows this was added quite recently in 8.0.17, I guess to resolve this specific issue. What surprises me somewhat is this setting is not enabled by default as it requires me to add version specific configuration to settings on 8.0 servers, not all run 8.0.17+, so that is inconvenient. However, the problem is clearly resolved. I was just unaware of this new setting.
[26 Feb 2020 11:01]
MySQL Verification Team
Thank you Simon for confirming. regards, Umesh
[26 Feb 2020 11:34]
MySQL Verification Team
> > as it requires me to add version specific configuration > could use on all servers if you don't mind a warning on older versions: [mysqld] loose-print_identified_with_as_hex=1
Description: It is quite frequent to copy grants from one system to another using the SHOW CREATE USER command as this does not require the user/system to actually know the password as all that is being copied is the CREATE USER command with the hash. It's known that the caching_sha2 mechanism is more secure so it's good to use it. However, with caching_sha2_password this seems to be harder than I would expect. When I try to use the output of SHOW CREATE USER this is not accepted by the server. I'd expect this to work. How to repeat: Example: mysql [localhost:8019] {msandbox} ((none)) > create user x identified by 'hello'; Query OK, 0 rows affected (0.01 sec) mysql [localhost:8019] {msandbox} ((none)) > show create user x\G *************************** 1. row *************************** CREATE USER for x@%: CREATE USER 'x'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$n\rf6xnN\'~#6LIo;/e3V7QZznL4o8Yhiyni.uIgOq/rKSE/8WQKTQaknWodD' 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) Now copy the SHOW CREATE USER output to create a duplicate user with the same credentials: mysql [localhost:8019] {msandbox} ((none)) > create user x2 IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$n\rf6xnN\'~#6Io;/e3V7QZznL4o8Yhiyni.uIgOq/rKSE/8WQKTQaknWodD' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT; ERROR 1827 (HY000): The password hash doesn't have the expected format. mysql [localhost:8019] {msandbox} ((none)) > select @@default_authentication_plugin; +---------------------------------+ | @@default_authentication_plugin | +---------------------------------+ | caching_sha2_password | +---------------------------------+ 1 row in set (0.00 sec) Switching back to the older mysql_native_password (which requires a server restart and setting default_authentication_plugin=mysql_native_password) I can do: mysql [localhost:8019] {msandbox} ((none)) > create user old identified by 'a'; Query OK, 0 rows affected (0.00 sec) mysql [localhost:8019] {msandbox} ((none)) > show create user old\G *************************** 1. row *************************** CREATE USER for old@%: CREATE USER 'old'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*667F407DE7C6AD07358FA38DAED7828A72014B4E' 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) mysql [localhost:8019] {msandbox} ((none)) > create user old2 IDENTIFIED WITH 'mysql_native_password' AS '*667F407DE7C6AD07358FA38DAED7828A72014B4E' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT; Query OK, 0 rows affected (0.00 sec) mysql [localhost:8019] {msandbox} ((none)) > select @@default_authentication_plugin; +---------------------------------+ | @@default_authentication_plugin | +---------------------------------+ | mysql_native_password | +---------------------------------+ 1 row in set (0.00 sec) mysql [localhost:8019] {msandbox} ((none)) > This works fine. So it looks to me as if the input from SHOW CREATE USER is not being accepted correctly in CREATE USER, or the quoting is being shown incorrectly. Again it could be that I'm doing something wrong but the behaviour feels wrong. Suggested fix: Ensure that SHOW CREATE USER output can be used appropriately as input to a different system or provide a better error message if the user is doing the wrong thing.