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

Question

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)?

Answer 1

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.)

Answer 2

A wrapper script in some language (I'd use php and run via cli since it would be a quick copy/paste from stuff I already have) that is executed, then deleted, or at least edited again to change all passwords to "changeme" or similar.

Answer 3

As Federico Sierra points out in the comments, you can use mysqladmin password command to change the password for any user you can log in as.

Formerly the password had to be supplied on the command line, but as of 5.7, if the password is omitted it prompts for one.

From the mysqladmin documentation:

password new_password

Set a new password. This changes the password to new_password for the account that you use with mysqladmin for connecting to the server. Thus, the next time you invoke mysqladmin (or any other client program) using the same account, you will need to specify the new password.

[...]

In MySQL 5.7, the new password can be omitted following the password command. In this case, mysqladmin prompts for the password value, which enables you to avoid specifying the password on the command line. Omitting the password value should be done only if password is the final command on the mysqladmin command line. Otherwise, the next argument is taken as the password.

So, if you can log in as the user (i.e. you know a user's password - and you are on a host you are permitted to log in from) - then you can change their password with:

mysqladmin password -u <user_name> [-p]

e.g.

$ mysqladmin password -u dave -p
Enter password: 
New password: 
Confirm new password: 

If course, if you have root privileges, you can reset the user's password (and change their host if necessary) to enable you to log in to do this. But that comes with its own risks, so it's not ideal.