Mysql user creation script

Question

I'm trying to automate MySQL user creation procedure. I thought of creating a temp file that would contain mysql user creation statements, then I would have call it like this :

mysql -u root -proot < temp

But I'm stuck with mysql syntax : here's the content of my temp file :

DROP DATABASE IF EXISTS mytestdatabase;
CREATE DATABASE mytestdatabase;
SELECT @password:="my password";
DELETE FROM mysql.user WHERE Host='localhost' AND User='mytestdatabase';
GRANT ALL PRIVILEGES ON mytestdatabase.* TO 'mytestdatabase'@'localhost' IDENTIFIED BY PASSWORD '@password';
FLUSH PRIVILEGES;

But the line

GRANT ALL PRIVILEGES ON mytestdatabase.* TO 'mytestdatabase'@'localhost' IDENTIFIED BY PASSWORD '@password';

(Password hash should be a 41-digit hexadecimal number )

is not interpreted as I would expect it to be. Even if I remove single quotes around the @password tag I still have errors (syntax error)

How can I make this work ?

ERROR 1372 (HY000) at line 9: Password hash should be a 41-digit hexadecimal number — ling
– ling, Commented Mar 2, 2012 at 7:48

Community · Accepted Answer · 2017-05-23 11:33:15Z

55

Just to answer why the error occurs and to show the differnce:

A) Expects @password to be a hash string ¹ value:

GRANT ALL PRIVILEGES 
    ON `mydb` . * TO 'username'@'localhost' IDENTIFIED 
BY
PASSWORD '@password';

^{Note the use of the PASSWORD keyword!}

B) Expects @password to be a clear-text string value:

GRANT ALL PRIVILEGES 
    ON `mydb` . * TO 'username'@'localhost' IDENTIFIED 
BY
'@password';

^{Note the missing PASSWORD keyword!}

¹ Where "hash string" is the result of SELECT PASSWORD('clearTextPasswd'); - see Snowman's answer for an example.

edited May 23, 2017 at 11:33

CommunityBot

11 silver badge

answered Jun 26, 2012 at 15:20

eyecatchUp

10.7k5 gold badges59 silver badges65 bronze badges

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

Comments

Devart · Accepted Answer · 2012-03-02 08:03:44Z

13

If you do not want to store password in clear text, then save it in hashed format -

GRANT ALL PRIVILEGES ON mytestdatabase.* TO 'mytestdatabase'@'localhost'
  IDENTIFIED BY PASSWORD '*7560636C2922C05954FE6A2446AA00C84708B57B';

Where hashed password is a result of this query -

SELECT PASSWORD('my password');

answered Mar 2, 2012 at 8:03

Devart

120k23 gold badges172 silver badges189 bronze badges

Comments

Avinash · Accepted Answer · 2016-12-22 07:36:29Z

8

I have also got same problem, it is resolved just by setting the password by mentioned query below.

SET PASSWORD FOR 'user'@'localhost' = PASSWORD('MyNewPass');

edited Dec 22, 2016 at 7:36

Avinash

4,3092 gold badges26 silver badges41 bronze badges

answered Dec 22, 2016 at 6:23

abhishek

831 silver badge3 bronze badges

Comments

mhu · Accepted Answer · 2013-04-12 11:37:23Z

5

Take a look at the following url and this will help you to fix the issue:

http://linuxadministrator.pro/blog/?p=147

mysql> select password('12345');
+-------------------------+
| password('123456') |
+-------------------------+
| 2ff898e158cd0311        |
+-------------------------+
1 row in set (0.00 sec)

mysql> create user test identified by password '2ff898e158cd0311';
Query OK, 0 rows affected (0.00 sec)

edited Apr 12, 2013 at 11:37

mhu

18.1k10 gold badges67 silver badges97 bronze badges

answered Apr 12, 2013 at 11:17

Snowman

511 silver badge1 bronze badge

Comments

Nir Alfasi · Accepted Answer · 2012-03-02 07:44:43Z

1

try:

GRANT ALL PRIVILEGES ON mytestdatabase.* 
      TO mytestdatabase@localhost IDENTIFIED BY 'PASSWORD';

where PASSWORD is your password (in quotes).

answered Mar 2, 2012 at 7:44

Nir Alfasi

53.6k11 gold badges94 silver badges138 bronze badges

4 Comments

ling Over a year ago

Yes it would work, but I heard it is less secure since the password can appears in a mysql history file in clear form. I would prefer to avoid this solution whenever possible.

Nir Alfasi Over a year ago

Then you can set it as I showed and then change it like this: SET PASSWORD FOR mytestdatabase= PASSWORD('PASSWORD');

ling Over a year ago

the clear form of the password still appears, but maybe because it's called from a file it won't be logged in the "mysql history". I may try to get more infos how that "mysql history" really works. Thank you.

Nir Alfasi Over a year ago

thegeekstuff.com/2010/01/…

jamesTheProgrammer · Accepted Answer · 2013-08-23 13:39:52Z

0

I opened phpMYAdmin and went to the the users tab, found the user i was trying to connect with clicked Edit Privileges, scrolled down to the Change Login Information / Copy User section and choose Any host for the Host option, it was set to local. When I clicked the Go button, it generated a script similar to the ones listed here.

GRANT ALL PRIVILEGES ON * . * TO 'james'@'%' IDENTIFIED BY PASSWORD '*780E1D13F1C7713F341A117499C6D8644464C4CF' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

answered Aug 23, 2013 at 13:39

jamesTheProgrammer

1,7734 gold badges22 silver badges34 bronze badges

Comments

Kishan Bheemajiyani · Accepted Answer · 2013-09-26 10:50:39Z

0

Well hat is error bcoz you have wrote the wrong way just you have wrote,

GRANT ALL PRIVILEGES ON *.* TO 'USER'@'localhost' IDENTIFIED BY PASSWORD '@password';

and the true syntax is follows. look here.

GRANT ALL PRIVILEGES ON *.* TO 'USER'@'localhost' IDENTIFIED BY '@password';

because your password is causing that error. it must be in as shown.

answered Sep 26, 2013 at 10:50

Kishan Bheemajiyani

3,4396 gold badges39 silver badges74 bronze badges

Collectives™ on Stack Overflow

Mysql user creation script

7 Answers 7

Comments

Comments

Comments

Comments

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related