3

How can I set a mysql user's password using a pre-hashed password?

=====

I've got a mysql database version 5.1.73 .

According to the mysql documentation, in the newer versions of mysql, creating a user using a prehashed password

CREATE USER 'ans'@'localhost'
    IDENTIFIED BY PASSWORD 'hash_string'

is deprecated and will be removed in a future MySQL release.

However, I cannot figure out what (if anything) is the new way to accomplish this.

We use cobbler to set up our databases, and I would like to pre-populate my databases with the accounts they will need, along with the passwords they will use, without having the clear-text passwords in my scripts. I would have thought

update mysql.user
    set password = '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB'
    where user = 'ans';

would do the trick, but from my testing, that doesn't actually change the mysql login password.

mysql> create user 'ans'@'localhost' identified by 'foo';
mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| ans  | localhost | *F3A2A51A9B0F2BE2468926B4132313728C250DBF |
+------+-----------+-------------------------------------------+
mysql> update mysql.user set password = password('bar') where user = 'ans';
Query OK, 1 row affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| ans  | localhost | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB |
+------+-----------+-------------------------------------------+
mysql> quit
$ mysql -uans -pbar
ERROR 1045 (28000): Access denied for user 'ans'@'localhost' (using password: YES)
$ mysql -uans -pfoo
Welcome to the MySQL monitor.  Commands end with ; or \g.
0

When you hack the password into mysql.user, you are supposed to run

mysql> FLUSH PRIVILEGES;

According to the MySQL Documentation on FLUSH

PRIVILEGES

Reloads the privileges from the grant tables in the mysql database.

The server caches information in memory as a result of GRANT, CREATE USER, CREATE SERVER, and INSTALL PLUGIN statements. This memory is not released by the corresponding REVOKE, DROP USER, DROP SERVER, and UNINSTALL PLUGIN statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES.

Now, you have to restart MySQL for the password to take affect.

0

A bit old now, but for future reference, using the SHOW CREATE USER 'ans'@'localhost' command you can see that the new syntax is of the form:

CREATE USER 'ans'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.