how do I create a mySQL user with hash('sha256', $salt . $password)?

Question

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?

Answer 1

This documentation page seems to indicate that sha256 is not implemented in 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.

Answer 2

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

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.

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

The hash() function is something you're probably remembering from PHP. It is not part of 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.

---

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