如何使用 hash('sha256', $salt . $password) 创建 mySQL 用户？

发布于 2024-07-16 04:18:33 字数 618 浏览 7 评论 0原文

我肯定错过了什么。

我想为仅选择事务设置一个数据库用户帐户，但 mysql 不允许我在创建用户帐户时选择密码的哈希方法。

失败：

GRANT SELECT ON myDB.* TO 'selectuser'@'localhost' 
IDENTIFIED BY hash('sha256', 'salted-myfakelongrandompasswordstring');

错误 1064 (42000)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的 'hash('sha256', 'salted-myfakelongrandompasswordstring')' 附近使用的正确语法

：

GRANT SELECT ON myDB.* TO 'selectuser'@'localhost' 
IDENTIFIED BY 'salted-myfakelongrandompasswordstring';

我检查了 phpinfo 页面，并且 sha256 哈希引擎已启用。

有没有办法更改 mysql 的默认哈希算法，或者我的 SQL 语法是否不正确？

原文

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.

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

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.

is there a way to change the default hashing algorithm for mysql, or is the syntax just incorrect on my SQL?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凯凯我们等你回来 2024-07-23 04:18:33

不，您不应该使用自己的密码哈希进行 MySQL 身份验证。

MySQL 5.x 使用自己的哈希函数 (PASSWORD()），它生成一个 41 字节的十六进制字符串（基于对输入应用两次 SHA1）。不幸的是，没有使用盐。

如果您能够按照问题中显示的方式使用GRANT，那么MySQL会将其PASSWORD()函数应用于hash( ) 函数。随后，当您想要登录时，您必须输入密码的 256 位哈希值，以使其与 MySQL 身份验证数据库中的内容相匹配。

此外，MySQL 支持 SHA2()< /code>从 MySQL 5.5 开始的哈希函数系列。

您可能还记得 PHP 中的 hash() 函数。它不是 MySQL 的一部分。

回复您的评论：通常，MySQL 身份验证与给定 Web 应用程序中的用户帐户身份验证完全分开（出于多种原因，这是最佳实践）。

是的，您需要对 Web 应用程序的 MySQL 身份验证的用户名/密码进行硬编码。可以，但更好的是配置文件。当然，将它们放在网络根目录之外。

当用户需要登录时，计算 hash() 他们输入的密码，结合记录的 salt 值他们的帐户。然后将其与该用户数据库中存储的哈希值进行比较。在伪代码中：

$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);

No, you shouldn't use your own password-hashing for MySQL authentication.

MySQL 5.x uses its own hashing function (PASSWORD()), which produces a 41-byte hex string (based on applying SHA1 to the input twice). Unfortunately, a salt is not used.

If you were able to use GRANT in 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.

Also, MySQL supports the SHA2() family of hash functions as of MySQL 5.5.

The hash() function is something you're probably remembering from PHP. It is not part of MySQL.

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).

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.

When a user needs to log in, compute the hash() of their input password, combined with the salt value on record for their account. Then compare this to the hash stored in the database for that user. In pseudocode:

$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);

回复收藏 0 原文