How to set hashed password for MySQL database user

Question

My boss would like me to write a script for our developers to run that would create a new DB user on their dev builds. He does not want me to have the actual password in the code; only the hash. I have the hashed password, but when I try creating a user with that hashed password; it just hashes the hashed password again.

CREATE USER 'test_user1'@'localhost' IDENTIFIED BY 'password'; shows me the hashed password is "*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19"

But if I attempt... CREATE USER 'test_user2'@'localhost' IDENTIFIED BY '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19';

"password" is not actually my password for test_user2.

I have also tried the following UPDATE but "password" still doesn't work for test-user2:

UPDATE `mysql`.`user` SET `authentication_string` = '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' WHERE (`Host` = 'localhost') and (`User` = 'test_user2');

How can I prevent it from hashing the value I'm entering, since I already know the hashed value?

I have also run GRANT SELECT for both users in my testing.

Answer 1

The key is to use IDENTIFIED WITH mysql_native_password AS 'hash'. Consider the following, for example:

CREATE USER 'jeffrey'@'localhost'
  IDENTIFIED WITH mysql_native_password AS 'hash-goes-here';

Relevant excerpt from the documentation:

IDENTIFIED WITH auth_plugin AS 'auth_string'

Sets the account authentication plugin to auth_plugin and stores the 'auth_string' value as is in the mysql.user account row. If the plugin requires a hashed string, the string is assumed to be already hashed in the format the plugin requires.