2

I'm making an app whose functionality includes creating a database and its user. This is accomplished like so:

mysql -e "grant all on database.* to 'user'@'localhost' identified by '$PW';"

$PW is the password to be used. This code can be found in a shell script that is called when a user of the GUI clicks a specific button. But isn't that insecure, since the password is sent over the command line? How can I better secure my application?

6
  • 4
    Why is there any need for a shell script at all? Use a suitable MySQL client library within your application, connect to the server, and issue the query. Jan 7 '16 at 2:48
  • How about some bash tricks like ` mysql -e "grant all on database.* to 'user'@'localhost' identified by '$(cat) ';"` then enter the password on a line followed by newline and ^D? Not at my computer but think that will work. Jan 7 '16 at 3:18
  • You're making an appointment which uses a database and you've only got bash to interact with it, and only have a problem with the password for the initial creation of the database????!!!!
    – symcbean
    Feb 6 '16 at 1:47
  • @Neil: man bash, /readline
    – symcbean
    Feb 6 '16 at 1:49
  • 4
    " This code can be found in a shell script that is called when a user of the GUI clicks a specific button. " , ewwwwwwwwww .... don't be lazy, do what @Michael-sqlbot says, use the libraries and don't reinvent the wheel !! Jun 5 '16 at 16:30
1

But isn't that insecure,

There is no absolute scale of "secure", indeed it is not even a vector or scalar property. (IT) Security is about ensuring that your technology does not do things it is not intended to do - while continuing to do the things it should. This is a very long winded way of saying you should have told us what the threat model is.

Leaving aside the many questions about how your application persists the pasword and whether it needs a password at all and the fact that your method is dependant on the root user not having a password....

Your current method will expose the password (briefly) to other users via 'ps' on Unix/Linux (and probably something similar on MSWindows - you didn't tell us what OS this sits on, but the 'bash' tag kind of implies Unix/Linux). And if it was run directly at the command line, will be present in audit logs and your shell history - although you said this was in a script. There are potential path and alias exploits a third party could use (if they have access) to subvert the behaviour - although is starting to get a little esoteric.

So considering only the issue described in isolation, and constrained by the only tools available being bash, then:

echo "grant all on database.* to 'user'@'localhost' identified by '$PW';" | mysql

is slightly better (by default, echo is a bash builtin and hence does not start a new process with a command line).

0

Presumably, you can create a file on the file system temporarily, in which case, place the grant all on ... by '$PW'; inside a file readable only by your process user and call mysql executing a script in one of the following two ways:

mysql yourDBHere < yourScript.sql

mysql yourDBHere -e "source yourScript.sql"

Be sure to clean up (delete the script) after you're done.

2
  • That's what I was thinking, though I didn't want to have the file touch the disk. If it's necessary though, I guess that's going to have to be okay. Jan 7 '16 at 3:37
  • I was thinking you could also pipe in the SQL via echo "...$PW" | mysql ..., however, I wasn't clear on how to keep the password off of the command line in that case, because the echo would appear in a process list. Which, I think begs the question, how to create a file with a password from a shell script without data appearing on the command line? As I'm not sure about your computing environment, I don't have a recommendation for that without more details. However, a pipe (if on *nix OS) would work. Jan 7 '16 at 3:42
0

Don't claim this is perfect, but something I put together as part of a script I wrote to backup/restore Wordpress (including the DB) link here:

#---------------------------------------------------------------------------------------
# Install MySQL and Dependencies
#---------------------------------------------------------------------------------------
echo -e "\\n\\n######### Installing mysql Server #########\\n\\n"
echo "INFO: Installing mysql-server"
sudo -E apt-get -q -y install mysql-server >>$LOGFILE  #non-interactive mysql installation

#Some security cleaning up on mysql-----------------------------------------------------
sudo mysql -u root -e "DELETE FROM mysql.user WHERE User='';"
echo "INFO: Setting password for root user to $DBPASS"
sudo mysql -u root -e "UPDATE mysql.user SET authentication_string=PASSWORD('$DBPASS') WHERE User='root';"
sudo mysql -u root -e "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');"
sudo mysql -u root -e "DROP DATABASE IF EXISTS test;"
sudo mysql -u root -e "DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';"
sudo mysql -u root -e "FLUSH PRIVILEGES;"

#Create DB for Wordpress with user------------------------------------------------------
echo "INFO: Creating Database with name $WPDBNAME"
sudo mysql -u root -e "CREATE DATABASE IF NOT EXISTS $WPDBNAME;"
echo "INFO: Granting Permission to $WPDBUSER with password: $WPDBPASS"
sudo mysql -u root -e "GRANT ALL ON *.* TO '$WPDBUSER'@'localhost' IDENTIFIED BY '$WPDBPASS';"
sudo mysql -u root -e "FLUSH PRIVILEGES;"

#Setup permission for my.cnf propery----------------------------------------------------
sudo chmod 644 /etc/mysql/my.cnf
1
  • There is an awful lot wrong with this script - not least it does not address the issue raised in the question.
    – symcbean
    Aug 1 '18 at 11:46

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.