6

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

6
  • if you are in 5.7 try with mysqladmin -u user_name password in other case you can execute query from a file to avoid shell history. Apr 17 '17 at 16:59
  • So in other words: mysqladmin password (with optional user/password params as with mysql) lets you log in and change your own password? That is useful, although I guess it requires first knowing/resetting the existing password?)
    – mwfearnley
    Apr 18 '17 at 21:22
  • Mysqladmin is a tool to perform administrative tasks, see dev.mysql.com/doc/refman/5.7/en/mysqladmin.html. You can change the password without known the previous password. Apr 18 '17 at 22:21
  • Can mysqladmin be used to set, the password for, say, dave@192.168.0.1, while logging in as root@localhost?
    – mwfearnley
    Apr 19 '17 at 7:44
  • (Your comment is still the best answer by the way. If you can flesh it out into a full answer it'll at least get an upvote.)
    – mwfearnley
    Apr 19 '17 at 9:52
3

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

3
  • This answer has solutions for other scripting languages beginning with 'P': unix.stackexchange.com/a/234592/38647
    – mwfearnley
    Jun 26 '17 at 13:44
  • Puby is my favourite programming language.
    – mpen
    Jun 18 '18 at 17:41
  • 1
    Haha :) - in my defence, someone added Ruby after I posted that..
    – mwfearnley
    Jun 19 '18 at 8:40
0

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.

1
  • PHP can be made to hide input too. You could write a script to ask for the pass (hidden) and execute the SQL. Might still be in MySQL binlogs, not sure.
    – mpen
    Jun 19 '18 at 19:14
0

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.

2
  • Strangely, this does not work in mariadb unless the user in questino has SUPER privileges.
    – Otheus
    Dec 7 '19 at 5:26
  • It may be due to the user authenticating with the auth_socket plugin. You can change it with askubuntu.com/a/801950/37574, although once you understand what’s happening, it’s possibly worth keeping.
    – mwfearnley
    Dec 8 '19 at 9:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.