How do you grant permissions to a MySQL user using a shell script with variables?

Question

I'm trying to create a provisioning script for Scotch Box (Vagrant) that will populate a database with seed content when I fire up the server.

When I name the actual user, database, password, and host in the lines of code being piped to mysql, it worked perfectly.

But when I added a "config" section with those items defined as variables, everything worked except the GRANT: The user was created, the database was created, and the data was imported, but the user didn't get access to the database.

Here is the script:

#!/usr/bin/env bash
# ...

echo "CREATE USER '$USER'@'$HOST' IDENTIFIED BY '$PASS'" | mysql -uroot -proot
echo "CREATE DATABASE $DB" | mysql -uroot -proot
echo "GRANT ALL ON $DB.* TO '$USER'@'$HOST'" | mysql -uroot -proot
mysql -uroot -proot $DB < /var/www/data/db.sql

Is there a way to pass these items as variables to the GRANT command?

Answer 1

I did the following. I wrote a script (testfile) as below and it worked. The big difference AFAICS is that with my script I export my variables before trying to use them. BTW, I wouldn't call it $USER - I'd go with $MY_USER - there's already a Linux system variable called $USER. I also didn't have these lines

#!/usr/bin/env bash
# ...

I just chmod'ed the file - $> chmod 755 testfile and ran it thus ./testfile.

[pol@localhost inst]$ more testfile 
export MY_USER=puser
export MY_PASS=ppass
export MY_HOST=localhost
export MY_DB=ptest
echo "CREATE USER '$MY_USER'@'$MY_HOST' IDENTIFIED BY '$MY_PASS'" | ./bin/mysql -S ./mysql.sock -u root -pdba
echo "CREATE DATABASE $MY_DB" | ./bin/mysql -S ./mysql.sock -uroot -pdba
echo "GRANT ALL ON $MY_DB.* TO '$MY_USER'@'$MY_HOST'" | ./bin/mysql -S ./mysql.sock -u root -pdba
[pol@localhost inst]$ 

I have since added the line (to test the running of an sql script file following the user and database creation - with grants).

./bin/mysql -S ./mysql.sock -u $MY_USER -p$MY_PASS $MY_DB < test.sql

to the end of testfile. This has the advantage of not using root, but rather the user you have just created. It is always good practice to run commands as root as rarely as possible. Keeping the root password in a script file is rather insecure. You can keep it in your .bashrc - check out the MySQL environment variables here, in this case $MYSQL_PWD.

[EDIT] - here is an alternative method. The -e stands for execute (mysql --help) - and is rather more conventional than the echo construct that you used.

./bin/mysql -S ./mysql.sock -u root -pdba -e "CREATE USER '$MY_USER'@'$MY_HOST' IDENTIFIED BY '$MY_PASS'"
./bin/mysql -S ./mysql.sock -u root -pdba -e "CREATE DATABASE $MY_DB"
./bin/mysql -S ./mysql.sock -u root -pdba -e "GRANT ALL ON $MY_DB.* TO '$MY_USER'@'$MY_HOST'"
./bin/mysql -S ./mysql.sock -D $MY_DB -u $MY_USER -p$MY_PASS -e "SOURCE test.sql"

(This worked on 5.6 - the previous example worked on a milestone release).

You can see the result below. Don't worry about the ./bin/mysql, nor the -S ./mysql.sock - I have a non-standard source install. For a normal install, just use mysql.

You can get rid of the annoying "password ... insecure" warnings by issuing the command this way ./testfile &>/dev/null

[pol@localhost inst]$ ./testfile
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[pol@localhost inst]$ 
[pol@localhost inst]$ ./bin/mysql --defaults-file=./my.cnf -S ./mysql.sock -u puser -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 46
Server version: 5.7.7-rc-log Source distribution

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ptest              |
+--------------------+
2 rows in set (0.00 sec)

mysql> exit;
Bye
[pol@localhost inst]$