MySql - Script to create user and database with hashed password

Question

Following the instructions at this link, I'm trying to use the following syntax to create an user with a password

DROP DATABASE IF EXISTS forum;
CREATE DATABASE forum;
DELETE FROM mysql.user WHERE Host='localhost' AND User='forum_admin';
#In this way, the password will not be logged in mysql history
SELECT @password:=PASSWORD('forum_admin');
GRANT ALL PRIVILEGES ON forum.* TO 'forum_admin'@'localhost' IDENTIFIED BY PASSWORD '@password';
FLUSH PRIVILEGES;

However, the penultimate instruction gives this error

ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.

So, how can I solve? I don't want to manually insert the result of the SELECT @password (as explained here or here for example), but I'd like to use those result to create automatically a new user.

Danyal Sandeelo · Accepted Answer · 2021-07-02 12:18:39Z

18

You dont need to create the hash, the passwords are already stored as hash.. This is how a new user is created

 create user forum_admin@'localhost'  identified by 'password' ;
 GRANT ALL PRIVILEGES ON forum.* TO 'forum_admin'@'localhost';
 flush privileges;

In your code: Change

GRANT ALL PRIVILEGES ON forum.* TO 'forum_admin'@'localhost' IDENTIFIED BY PASSWORD 'pass_string';

to

GRANT ALL PRIVILEGES ON forum.* TO 'forum_admin'@'localhost' IDENTIFIED BY 'pass_string';

edited Jul 2, 2021 at 12:18

answered Dec 2, 2014 at 18:56

Danyal Sandeelo

12.4k10 gold badges57 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tigerjack Over a year ago

oh wait, now it seems just a stupid question :S Haven't noticed the PASSWORD item at all. Btw, on a comment in the first link of my answer "it is less secure since the password can appears in a mysql history file in clear form." Is it untrue?

Danyal Sandeelo Over a year ago

@tigerjack89 ya that is a good point...Create a file, name it as createUser.sql ... write all the mentioned queries in it... save it.... load the sql file via command line ... mysql -uroot -p databasename < createUser.sql ...here the database name will be mysql...

Elvin Over a year ago

I initially thought it's an optional keyword like GRANT ALL [PRIVILEGES] but it wasn't. It seems to just call the password function on the string provided. dev.mysql.com/doc/refman/5.7/en/…

frank · Accepted Answer · 2016-03-17 04:22:03Z

7

I meet this problem too. May be you should get your password hash value

mysql> select password('1');
+-------------------------------------------+
| password('1')                             |
+-------------------------------------------+
| *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
+-------------------------------------------+

then excute the grant command

mysql> grant all 
-> on *.*
-> to xiushanfan identified by password '*xxxxxxxxxxx(copy from above)' 
-> with grant option;

Everything will be ok.

Here is the link:http://shareolite.blogspot.com/2014/03/how-to-solve-mysql-error-1827-hy000.html

Good luck!

answered Mar 17, 2016 at 4:22

frank

2,4041 gold badge21 silver badges23 bronze badges

2 Comments

user6743474 Over a year ago

This worked for me. I wasn't aware of the select password('.......') option. This fixed the misleading problem of 'database not selected' error you sometimes get. It is really a privileges issue and the above fixes it. Thanks .

Michael Aicher Over a year ago

Also important to note is that due to the keyword 'password' this command is not logged within the .mysql_history file by default. So you dont need to take care of cleanup to keep your password secret.

Collectives™ on Stack Overflow

MySql - Script to create user and database with hashed password

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related