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 |
[25 Feb 2020 9:15]
Simon Mudd
[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