首页 / 技术问答 / Mysql问答列表 / 问答详情

MySQL 密码哈希没有预期的格式

我正在使用Percona tollkit pt-show-grants命令在另一个环境中复制用户和权限，该命令返回类似的内容

[root@mysqlen1 ~]# pt-show-grants --only my_user
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 8.0.33-commercial at 2023-07-06 13:32:06
-- Grants for 'my_user'@'%'
CREATE USER IF NOT EXISTS `my_user`@`%`;
ALTER USER `my_user`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$E)\,\ZrJ%8K[y!MrHfAAAN44c/fazzdk49SINtIQYq6zPo67VJ0lBbLX2' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROLE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON *.* TO `my_user`@`%` WITH GRANT OPTION;
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `my_user`@`%` WITH GRANT OPTION;

但是执行ALTER USER会返回以下错误:

mysql> ALTER USER `my_user`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$E)\,\ZrJ%8K[y!MrHfAAAN44c/fazzdk49SINtIQYq6zPo67VJ0lBbLX2' 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.

为什么？我认为Percona tollkit pt-show-grants命令没有问题，因为它基本上执行SHOW CREATE USER命令，结果是一样的:

mysql> SHOW CREATE USER 'my_user'@'%';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for my_user@%                                                                                                                                                                                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER `my_user`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$E)\,\ZrJ%8K[y!MrHfAAAN44c/fazzdk49SINtIQYq6zPo67VJ0lBbLX2' 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)

推荐答案

这可能是由于散列中的二进制符号，在您的终端应用程序中可能无法正确显示/处理.这也可能受当前连接的字符集的影响.

您是否try 过:

SET @@SESSION.print_identified_with_as_hex = 1;

从MySQL文档中:

SHOW CREATE USER的OUTPUT的IDENTIFED WITH子句中显示的密码哈希值可能包含无法打印的字符，这些字符会对终端显示和其他环境产生不利影响.启用print_identified_with_as_hex会导致show create用户将哈希值显示为十六进制字符串，而不是常规字符串文字.即使启用了此变量，不包含不可打印字符的哈希值仍显示为常规字符串文字.

例如(将Windows命令提示符与我的默认CHARACTER_SET_CLIENT cp850一起使用)，如果我们创建用户:

mysql> CREATE USER `my_user`@`localhost` IDENTIFIED WITH 'caching_sha2_password' BY 'my strong password';
Query OK, 0 rows affected (0.01 sec)

然后(裁剪输出，因为我们只对密码散列感兴趣):

mysql> SHOW CREATE USER 'my_user'@'localhost';
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for my_user@localhost                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER `my_user`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$lL`\'?5xT.%↓cJ.¶.PQbn/IHRX12PeN9qrO23RAla71qmV28pEeeztcgJ.Cvgul.' |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

现在，try 从上面的输出创建另一个用户(my_user2):

mysql> CREATE USER `my_user2`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$lL`\'?5xT.%↓cJ.¶.PQbn/IHRX12PeN9qrO23RAla71qmV28pEeeztcgJ.Cvgul.';
ERROR 1396 (HY000): Operation CREATE USER failed for 'my_user2'@'localhost'

设置print_identified_with_as_hex = 1:

mysql> SET @@SESSION.print_identified_with_as_hex = 1;
Query OK, 0 rows affected (0.00 sec)

现在再次try 创建一个输出为SHOW CREATE USER ...的新用户:

mysql> SHOW CREATE USER 'my_user'@'localhost';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for my_user@localhost                                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER `my_user`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035246C4C60273F3578542E2519634A2E142E5051626E2F49485258313250654E3971724F323352416C613731716D563238704565657A7463674A2E437667756C2E |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE USER `my_user2`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035246C4C60273F3578542E2519634A2E142E5051626E2F49485258313250654E3971724F323352416C613731716D563238704565657A7463674A2E437667756C2E;
Query OK, 0 rows affected (0.00 sec)

Note:更改我的连接的字符集也有效，不需要更改print_identified_with_as_hex.

mysql> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.00 sec)

Mysql相关问答推荐

如何在逗号分隔字符串值中使用LEFT函数

更新MySQL表中子记录的序号

无条件理解MySQL中内连接和交叉连接的相似性

根据上一行S列结果计算列的查询

为什么MySQL不同版本的值会变化？

错误：此查询集包含对外部查询的引用，并且只能在子查询中使用

在带有 D.I 的 .NET AWS Lambda 中使用 MySql.Data

Travis 构建失败并出现错误 LOAD DATA LOCAL INFILE 文件请求因访问限制而被拒绝

如何让连续 3 周或以上的用户有订单？

从三个不同的表中生成两列，并使用分组变量计算这些列的比率

如何使用同一个表在 2 个字段上左连接

从 2 个不同的表中获取数据并将它们加入 sql

在 MySQL 中 Select COUNT of MAX

FreeBASIC 中的 MySQL 访问读取

使用case查询并更新最后一条记录

MySQL使用多列 Select 重复记录

MySQL：将日期时间插入其他日期时间字段

MySQL Partitioning / Sharding / Splitting - 走哪条路？

我可以在单个 Amazon RDS 实例上创建多少个数据库

MySQL 1062 - 键 'PRIMARY' 的重复条目 '0'

实用课程推荐
相关教程推荐
休闲君评测 |  TOOLFK工具网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630