TransWikia.com

Set a password to a pre-hashed value in mysql

Super User Asked on December 25, 2021

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.

2 Answers

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

Answered by Captain Payalytic on December 25, 2021

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.

Answered by RolandoMySQLDBA on December 25, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP