如何使用 hash('sha256', $salt . $password) 创建 mySQL 用户？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/693288/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how do I create a mySQL user with hash('sha256', $salt . $password)?
提问by m42
I must be missing something.
我肯定错过了什么。
I want to set up a database user account for select-only transactions but mysql is not letting me choose the hash method for a password on creating a user account.
我想为仅选择事务设置数据库用户帐户，但 mysql 不允许我在创建用户帐户时为密码选择哈希方法。
this fails:
这失败了：
GRANT SELECT ON myDB.* TO 'selectuser'@'localhost'
IDENTIFIED BY hash('sha256', 'salted-myfakelongrandompasswordstring');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'hash('sha256', 'salted-myfakelongrandompasswordstring')' at line 1
ERROR 1064 (42000)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的 'hash('sha256', 'salted-myfakelongrandompasswordstring')' 附近使用的正确语法
this passes:
这通过：
GRANT SELECT ON myDB.* TO 'selectuser'@'localhost'
IDENTIFIED BY 'salted-myfakelongrandompasswordstring';
I checked the phpinfo page and the sha256 hash engine is already enabled.
我检查了 phpinfo 页面，sha256 哈希引擎已经启用。
is there a way to change the default hashing algorithm for mysql, or is the syntax just incorrect on my SQL?
有没有办法更改 mysql 的默认散列算法，或者我的 SQL 上的语法不正确？
采纳答案by Bill Karwin
No, you shouldn't use your own password-hashing for MySQL authentication.
不，您不应该使用自己的密码散列进行 MySQL 身份验证。
MySQL uses its own password-hashing function (PASSWORD()), which produces a 41-byte hex string (based on applying SHA1 to the input twice). Unfortunately, a salt is not used.
MySQL 使用自己的密码散列函数 ( PASSWORD())，它生成一个 41 字节的十六进制字符串（基于对输入应用两次 SHA1）。不幸的是，没有使用盐。
If you were able to use GRANTin the manner you show in your question, then MySQL would apply its PASSWORD()function to the string output of the hash()function. Subsequently, when you want to log in, you would have to enter the 256-bit hash of your password, for it to match what is in the MySQL authentication database.
如果您能够GRANT以您在问题中显示的方式使用，那么 MySQL 会将其PASSWORD()函数应用于函数的字符串输出hash()。随后，当您想登录时，您必须输入密码的 256 位散列值，以使其与 MySQL 身份验证数据库中的内容相匹配。
Also, MySQL supports the SHA2()family of hash functions as of MySQL 6.0.5.
此外，MySQLSHA2()从 MySQL 6.0.5 开始支持散列函数系列。
The hash()function is something you're probably remembering from PHP. It is not part of MySQL.
该hash()函数是您可能从 PHP 中记住的东西。它不是 MySQL 的一部分。
update: I attended the MySQL Conference this week and found out that they are totally changing their roadmap for future product version numbers. The SHA2()function is currently part of the MySQL source, but it's undetermined what product version that corresponds to. Also, you need MySQL built with OpenSSL/YaSSL support, for SHA2()to work.
更新：我本周参加了 MySQL 会议，发现他们正在完全改变未来产品版本号的路线图。该SHA2()函数目前是 MySQL 源代码的一部分，但尚未确定对应的产品版本。此外，您需要使用 OpenSSL/YaSSL 支持构建的 MySQLSHA2()才能工作。
Re your comment: Typically MySQL authentication is totally separate from user account authentication in a given web app (this is best practice for several reasons).
回复您的评论：通常 MySQL 身份验证与给定 Web 应用程序中的用户帐户身份验证完全分开（出于多种原因，这是最佳实践）。
Yes, you need to hardcode the username/password for MySQL authentication for your web app. Could be in, but even better would be a config file. Of course, put these outside the web root.
是的，您需要对 Web 应用程序的 MySQL 身份验证的用户名/密码进行硬编码。可能在，但更好的是配置文件。当然，把这些放在 web root 之外。
When a user needs to log in, compute the hash()of their input password, combined with the saltvalue on record for their account. Then compare this to the hash stored in the database for that user. In pseudocode:
当用户需要登录时，计算hash()其输入密码的值，并结合其帐户记录的盐值。然后将其与为该用户存储在数据库中的哈希值进行比较。在伪代码中：
$salt = $db->query("SELECT salt FROM Accounts WHERE account_name = ?",
$input_account_name);
$password_hash = hash('sha256', $salt + $input_password)
$is_password_correct = $db->query("SELECT password_hash = ?
FROM Accounts WHERE account_name = ?",
$password_hash, $input_account_name);
回答by Assaf Lavie
This documentation pageseems to indicate that sha256 is not implemented in MySQL:
这个文档页面似乎表明 sha256 没有在 MySQL 中实现：
Also, regarding the mentioned exploit of sha1, there are stronger versions like sha256, sha384, sha512 etc. but mysql does not implement them; they would have to be implemented in code.
另外，对于前面提到的sha1的exploit，还有sha256、sha384、sha512等更强的版本，但是mysql没有实现；它们必须在代码中实现。
