3

I used to be able to create a MySQL-compatible password by running my password through sha1 twice, but it appears this doesn't work in MySQL 8.

MySQL seems to use these password plugins now. The syntax is thus (in JS):

const createUserSql = `CREATE USER ${db.escapeValue(agency.login)} IDENTIFIED WITH mysql_native_password BY ${db.escapeValue(passwordHash)};`;

I want to create passwords in Node.js that will work with MySQL. I know I can just use plain-text passwords and let MySQL hash them, but I'm printing this SQL to a terminal and I don't want the passwords to be visible, so I want to pre-hash them.

What algorithm will work with MySQL 8? I'm willing to use any of the built-in password plugins.

sha256_password sounds nice, but I don't think it's a straight sha256 hash, sounds like it has a salt built in, so I'm not sure how to create one in Node.js.

The MySQL PASSWORD() function is gone too. I didn't really want to do a SELECT PASSWORD(:plainTextPass) to hash my passwords, but now that isn't even an option.

mpen
  • 272,448
  • 266
  • 850
  • 1,236

3 Answers3

5

To anyone else who happens to find this while searching for information:

We're in the process of upgrading a MySQL 5.0 installation to 5.7 first, and then to 8.0. We're relying on the mysql_native_password authentication plugin, since that's the only one that Ansible currently supports, and all of our old user accounts have hashes with that scheme anyway. We want our Ansible tasks to have premade hashes for the mysql_user module, including in the post-8.0 era once we get there.

After some digging, I found this blog post:

https://blog.pythian.com/hashing-algorithm-in-mysql-password-2/

The PASSWORD() function is gone in MySQL 8, but it looks like this returns the exact equivalent value:

SELECT CONCAT('*', UPPER(SHA1(UNHEX(SHA1('the_password')))));

JK Laiho
  • 3,538
  • 6
  • 35
  • 42
4

The password hash is not calculated based on the clear text alone anymore. So a password() function is impossible to implement. It has been this way since 5.5 btw: just look at the old_passwords system variable. Starting with 8.0 we decided to have the authentication plugin do the hashing and we've added new methods to the authentication plugin API. I could theoretically make a new password function that will take extra arguments (authentication method, user name, clear text password etc) and call the relevant plugin.

But the result of all that is stored into mysql.user.authentication_string anyway. And can always be passed back to MySQL via the CREATE USER ... IDENTIFIED WITH ... AS ... syntax.

So a workaround for what you need would be executing CREATE USER followed by SHOW CREATE USER (and DROP USER eventually).

If you still feel like having the a variant of the PASSWORD function back please file a feature request in bugs.mysql.com. And FYI we do accept code contributions too :)

The hashing used itself is not rocket science too. I believe that for 8.0's new default authentication method (caching_sha256) it's defined here: https://dev.mysql.com/doc/dev/mysql-server/latest/page_caching_sha2_authentication_exchanges.html#sect_caching_sha2_definition

So if you don't want to do the round-trip to the server you can use the above definition.

Joro
  • 51
  • 3
  • Thank you for responding Joro! So I could do `CREATE USER ... IDENTIFIED WITH caching_sha256 AS 'precomputed_hash'` then? Where `precomputed_hash` is `SHA256(SHA256(user_password))`? Am I reading that right? Or do I need a nonce from the server? – mpen Jun 28 '18 at 16:51
  • It should work. For the record this is how we binlog CREATE USER. I don't think you need anything from the server. – Joro Jul 02 '18 at 06:51
1

Not sure if I understand the problem. MySQL will hash whatever content you provide using one of those authentication plugins (which implements some specific hashing strategy itself behind the curtain).

If you want to perform additional hashing at the application level, that is up to you, but shouldn't be an issue as long has the application always accounts for that additional hashing round.

The thing you will probably have to worry about is to make sure the client you are using has support for that authentication plugin.

As an example, none of the two most popular community-driven mysql drivers for Node.js (which I believe is your environment), has support for the default authentication plugin - caching_sha2_password - used by MySQL 8.0 (more details in this answer).

But connecting with a mysql_native_password account (which seems to be your case) should not be an issue.

ruiquelhas
  • 1,905
  • 1
  • 17
  • 17
  • I'm writing a script that exports SQL for creating MySQL users. I don't want this SQL to contain plain-text passwords. Thus I want pre-hash the user passwords before they ever reach MySQL. My question is how can I hash these passwords, preferably without ever connecting to MySQL? It used to be a simple matter if sha1'ing the password, but that no longer works in MySQL 8. – mpen Jun 19 '18 at 19:05
  • I'm still confused. You can hash them however you like as long as you also create that same user account on mysql with the same password hash. Using the default authentication plugin, MySQL would also hash it as well, but that should not be a problem. In any case, if you are planning do just do client-side hashing, you can always use the [mysql_clear_password](https://dev.mysql.com/doc/refman/8.0/en/cleartext-pluggable-authentication.html) plugin when creating MySQL users. – ruiquelhas Jun 20 '18 at 11:16
  • "Using the default authentication plugin, MySQL would also hash it as well, but that should not be a problem" -- how would that *not* be a problem? Because double hashing is still safe? That's fine, but I wouldn't be able to log in from anywhere. I'm only using these hashes to create the user accounts, our app and 3rd party apps (phpmyadmin/adminer) will *not* hash the passwords before sending them to MySQL. The hash I create has to match exactly what MySQL will do internally for this to be of any use. – mpen Jun 20 '18 at 21:08
  • Ok, mixing 3rd party apps is new information. Regardless, I fail to see how the old setup with `PASSWORD()` would work under those conditions. You would still have to be sure all 3rd party apps would be using the same hashing strategy as `PASSWORD()` in order for everything to work. But maybe I'm missing something. In any case, I don't see how you can make that work with MySQL 8.0. – ruiquelhas Jun 22 '18 at 11:32
  • You are missing something. You only have to create the MySQL user account once, and then everything works as normal. None of the apps have to choose a "hashing strategy" -- MySQL does that for you when you connect. – mpen Jun 22 '18 at 17:08