在 phpMyAdmin 中使用 SHA256 设置用户密码后访问被拒绝

2022-01-05 00:00:00 sha256 mysql phpmyadmin

在看到 phpMyAdmin 生成的默认密码使用 mysql_native_password 并且因此在使用相同密码时哈希没有改变后，我更新了一个用户的密码并将哈希设置为 SHA256.

After seeing that default passwords generated by phpMyAdmin used mysql_native_password and therefore hashes didn't changed when equal passwords where used, I updated one user's password and set the hashing to SHA256.

当我尝试使用设置的密码再次登录时，它说:

When I tried to login again using the setted password it says:

mysqli_real_connect(): (HY000/1045): 用户 'xxxx'@'xxxx' 访问被拒绝(使用密码:YES).

mysqli_real_connect(): (HY000/1045): Access denied for user 'xxxx'@'xxxx' (using password: YES).

服务器使用 https 和 phpMyAdmin 强制使用 SSL.

The server uses https and phpMyAdmin forces to uses SSL as well.

一旦使用 SHA256 更改密码，再次获得访问权限的唯一方法是手动设置密码并将密码插件恢复为 mysql_native_password.

Once the password is changed with SHA256, the only way to gain access again is manually setting the password and reverting password plugin to mysql_native_password.

如何让用户使用 SHA256 密码登录 phpMyAdmin?

How can I login to phpMyAdmin with users using SHA256 passwords?

注意:如果我尝试使用相同的用户和 SHA256 密码登录到 mysql 控制台，它工作正常.

Note: if I try to use the same user and the SHA256 password to login to mysql console it works fine.

推荐答案

MySQL 最近更改了默认的身份验证类型，并且在 MySQL 和 PHP 之间，这种更改需要很长时间才能被 PHP 支持.旧方法是mysql_native_password，新方法是caching_sha2_password.从 7.4 开始的 PHP 版本支持新方法.由于这与 PHP 本身有关，支持此方法的 phpMyAdmin 需要您运行更新的 PHP 安装(phpMyAdmin 本身已准备好进行此更改，但您使用它的能力取决于您的 PHP 版本).

MySQL has recently changed the default authentication type and, between MySQL and PHP, this change took quite a while to be supported by PHP. The old method was mysql_native_password and the new one is caching_sha2_password. PHP versions starting with 7.4 support the new method. Since this is related to PHP itself, phpMyAdmin supporting this method requires you to run an updated PHP installation (phpMyAdmin itself is ready for this change, but your ability to use it depends on your PHP version).

你基本上有两个选择:

  1. 将您的 PHP 升级到 7.4 或更高版本.
  2. 将用户的身份验证类型更改为旧的 mysql_native_password.您可以通过编辑用户来实现(从 phpMyAdmin 的用户帐户选项卡中，编辑用户，然后从下拉列表中更改身份验证方法).
  1. Upgrade your PHP to 7.4 or newer.
  2. Change the authentication type on your user to the older mysql_native_password. You can do that by editing the user (from the User accounts tab in phpMyAdmin, edit the user, then change the authentication method from the dropdown).

相关文章