Passwords are passed insecurely as command line arguments

[heiderich]

Passwords are passed as command line arguments to external programs in several places, which is insecure:

webwork2/bin/update-OPL-statistics

Line 210 in db7eb3b

`$mysql_command --host=$host --port=$port --user=$dbuser --password=$dbpass $db < $global_sql_file`;

webwork2/bin/upload-OPL-statistics

Line 54 in db7eb3b

`$mysqldump_command --host=$host --port=$port --user=$dbuser --password=$dbpass $db OPL_local_statistics > $output_file`;

More instances of this form are suggested in #985.

[mgage]

for mysqldump a low tech solution is to create a read-only user. You still get the error message but the insecurity is less.

https://scriptingmysql.wordpress.com/2017/01/06/easy-to-use-perl-scripts-to-backup-your-mysql-database-with-mysqldump-and-then-ftp-the-files-to-a-remote-server/

---

If you run the script on the command line, or if you run it in cron, you should see something like this:

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

It isn’t a good idea to have your password in plain text anywhere, so you should create a user which only has the limited read-only permissions needed to run mysqldump. You will need to change the value of “database_name” in the GRANT statement to match each database you want to backup. You will need to run the GRANT statement for every database you want to backup, or you can use an asterisk “*” in place of the database name.

CREATE USER 'mysqlbackup'@'192.168.1.2' IDENTIFIED WITH sha256_password BY '';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON database_name.* TO 'mysqlbackup'@'192.168.1.2';

[mgage]

For updating the statistics we should try to use DBI instead of the commandline mysql client.
I don't know whether that would be faster or slower -- but long term if we design it right -- it will allow us to use postgres or some other database fairly easily.

I don't believe a long term stable fix is needed for the 2.15 release. We've been sending things over the command line for some time.

[dpvc]

There is also this approach, which uses the MYSQL_PWD environment variable to handle the password, which is more secure than the command-line parameters.

Note also that the insecurity of the approach is only in play if there are other users running on the same server as the database, as it is the ps command (or similar ones) that show the command-line arguments that is the source of the insecurity. If there are no users to run ps on the server, the issue is pretty moot.

[heiderich]

There is also this approach, which uses the MYSQL_PWD environment variable to handle the password, which is more secure than the command-line parameters.

I am not sure whether this would be much better (depending on the environment). Note that https://dev.mysql.com/doc/refman/5.7/en/password-security-user.html says the following about this:

This method of specifying your MySQL password must be considered extremely insecure and should not be used. Some versions of ps include an option to display the environment of running processes. On some systems, if you set MYSQL_PWD, your password is exposed to any other user who runs ps. Even on systems without such a version of ps, it is unwise to assume that there are no other methods by which users can examine process environments.

On the same page they propose a third method, which seems safer if used correctly:

Store your password in an option file. For example, on Unix, you can list your password in the [client] section of the .my.cnf file in your home directory:

[client]
password=password

To keep the password safe, the file should not be accessible to anyone but yourself. To ensure this, set the file access mode to 400 or 600. For example:

shell> chmod 600 .my.cnf

To name from the command line a specific option file containing the password, use the --defaults-file=file_name option, where file_name is the full path name to the file. For example:

shell> mysql --defaults-file=/home/francis/mysql-opts

[taniwallach]

@dpvc In your option would using %ENV to set the environment variable MYSQL_PWD via the %ENV hash

$ENV{'MYSQL_PWD'}=$dbpass;

and running the mysql/mysql_dump command via backticks without the --password option be a reasonably secure approach?

[taniwallach]

I made an additional commit to use the $ENV{'MYSQL_PWD'}=$dbpass; appoach in #985

[dpvc]

In your option would using %ENV to set the environment variable MYSQL_PWD ... and running the mysql/mysql_dump command via backticks without the --password option be a reasonably secure approach?

I am not a security expert, so I'm not sure I'm qualified to make the call. But it does look like ps eat does show environment variables for me on Mac OSX, even for processes I don't own, so that does look like it is not a help for systems where the database is being run on a machine that also has normal user logins (that is not a good idea, and probably most people do run WeBWorK and their MySQL on servers that don't have other users, but I'm sure someone somewhere does have its all on a public machine).

On the other hand, the file approach is also problematic, if you are on a system that makes regular backups. Backup tapes (or other media) are essentially unprotected (access to the backup media gives you complete access to the contents of the media, regardless of usernames and passwords on the original system). So if you have a file that is backed up, you need to consider that to be public (at least to the people who have access to the backup media).

Perfect security is impossible.

[heiderich]

On the other hand, the file approach is also problematic, if you are on a system that makes regular backups. Backup tapes (or other media) are essentially unprotected (access to the backup media gives you complete access to the contents of the media, regardless of usernames and passwords on the original system). So if you have a file that is backed up, you need to consider that to be public (at least to the people who have access to the backup media).

I guess the same applies to the configuration file holding the password in the first place.

[dpvc]

I guess the same applies to the configuration file holding the password in the first place.

Indeed.

[mgage]

Thanks David. This looks like it might be the best quick fix:

https://unix.stackexchange.com/questions/205180/how-to-pass-password-to-mysql-command-line#comment771368_205184

---

Set MYSQL_PWD in the environment (export MYSQL_PWD=muhpassword) and execute your command without the -p. See MySQL Program Environment Variables. In spite of the manual's dire warnings, this is rather safe. Unless you start weird warez in the same shell later. So we run: MYSQL_PWD=$(cat foo.php etc) mysql -u foouser -h barhost – David Tonhofer Mar 1 '18 at 18:02

---

Note that there is the MYSQL_PWD environment variable which is read by mysql if you don't specify -p. And under Linux this is a secure method because the environment of a user cannot be read by other unprivilleged users - in contrast to the argument vector. – maxschlepzig Jul 14 at 14:55

[mgage]

implemented by @taniwallach in commit 00d4f30 in PR #985

[Alex-Jordan]

@drgrice1 Would you agree that this can be closed?