4

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?

OldBuildingAndLoan
  • 2,801
  • 4
  • 32
  • 40

3 Answers3

5

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);
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • so best practice would be... create database user accounts via the second query method "IDENTIFIED BY ...", let mySQL hash that password in the background, then hard code the string into a class which is stored outside of the web root. then for actual human user account passwords, hash those with sha256 in code before passing the hashed literal string to mySQL --- which will then re-hash the hashed string... wait, that last bit sounds wrong. – OldBuildingAndLoan May 25 '09 at 14:01
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.

Assaf Lavie
  • 73,079
  • 34
  • 148
  • 203
-1

...IDENTIFIED BY hash('your_password','256'). This will cause MySQL to hash your hashed password ;-)

Gimei
  • 21
  • 2