大网数据广告活动
服务器运维问答

How can I change a MySQL user password without typing it visibly on the console?

· 52CE 我爱测

I've seen various methods to set passwords in MySQL, e.g, things like: GRANT USAGE ON db.* to 'dave'@'localhost' IDENTIFIED BY 'supersecretpassword'); SET PASSWORD [FOR 'dave'@'localhost'] = PASSWORD('reallysecretpassword'); UPDA…

问题原文

原帖链接 · mwfearnley · CC BY-SA 3.0

I've seen various methods to set passwords in MySQL, e.g, things like:

GRANT USAGE ON db.* to 'dave'@'localhost' IDENTIFIED BY 'supersecretpassword');
SET PASSWORD [FOR 'dave'@'localhost'] = PASSWORD('reallysecretpassword');
UPDATE mysql.user SET PASSWORD=PASSWORD('confidentialpassword') WHERE user='dave' AND host='localhost';

But they seem to involve typing the password in the clear as part of an SQL command, which raises paranoia about things like whether it ends up in my SQL command history, or about whether anyone is looking over my shoulder or can peek at my terminal scrollback.

Is there anyway I can get MySQL to prompt me to give the password without echoing it to the screen (the same way Unix does with passwd)?

社区高分解答

原帖链接 · mwfearnley · CC BY-SA 3.0

The password-hashing algorithm is fairly simple, and can be replicated in other programmling languages.

According to https://www.pythian.com/blog/hashing-algorithm-in-mysql-password/, in MySQL 4.1 and up, the PASSWORD function takes the (binary) SHA1 of the password string, twice, then returns that as a hex string preceded by an asterisk.

For example, in SQL:

> SELECT PASSWORD('test'), SHA1(UNHEX(SHA1('test')));
+-------------------------------------------+------------------------------------------+
| PASSWORD('test')                          | SHA1(UNHEX(SHA1('test')))                |
+-------------------------------------------+------------------------------------------+
| *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | 94bdcebe19083ce2a1f959fd02f964c7af4cfc29 |
+-------------------------------------------+------------------------------------------+

Using MySQL code on the console defeats the purpose, but here's a fairly simple Python script that prompts for a password and generates a MySQL-compatible password string from it.

import hashlib, getpass

def mysql_password(p):
    return '*' + hashlib.sha1( hashlib.sha1(p).digest() ).hexdigest().upper()

password = getpass.getpass()

print(mysql_password(password))

This can then be assigned directly to a user in MySQL:

SET PASSWORD FOR 'dave'@'localhost' = '*94BDCE...';

So it is only the password hash that is ever seen on the console/MySQL history. (It's no more than can be seen by anyone with access to the mysql.user table.)

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2017-04-17)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 3.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。