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 ?

Answer 1

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.

Answer 2

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');