How to create password hash for MySQL externally?

Question

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.

Answer 1

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