Mysql用户创建脚本[英] Mysql user creation script

本文是小编为大家收集整理的关于Mysql用户创建脚本的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正在尝试自动化MySQL用户创建过程. 我想创建一个包含mySQL用户创建语句的临时文件, 那我会这样称呼:

mysql -u root -proot

,但我坚持MySQL语法: 这是我的临时文件的内容:


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;

但是线

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

(密码哈希应该是41位数字的十六进制号)

没有像我期望的那样解释. 即使我在@password标签周围删除单个引号,我仍然有错误(语法错误)

我该如何完成此工作?

推荐答案

只是为了回答为什么发生错误并显示不同的原因:


a)期望@password成为哈希字符串 1 值:

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

注意PASSWORD关键字的使用!


b)期望@password成为 clear-text String 值:

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

注意缺少 PASSWORD关键字!


1 其中"哈希字符串"是SELECT PASSWORD('clearTextPasswd');的结果 - 参见雪人的答案例如.

其他推荐答案

如果您不想将密码存储在清晰的文本中,请以哈希格式保存 -

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

hashed密码是此查询的结果 -

SELECT PASSWORD('my password');

其他推荐答案

我也遇到了同样的问题,仅通过以下提到的查询设置密码就可以解决.

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

本文地址:https://www.itbaoku.cn/post/597580.html

问题描述

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 ?

推荐答案

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


A) Expects @password to be a hash string 1 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!


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

其他推荐答案

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

其他推荐答案

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