2

I have written a shell script which creates a database and user on a MySQL server. The user and database are to be used by a web application and generally this task only be performed once per website installation, but sometimes, (e.g. testing the script) the script needs to be run many times.
Currently the script assumes the user always exists and just pre-emptively deletes, i.e.

DELETE FROM mysql.user WHERE User = '${web_app_sql_user}';

Ive also tried drop user ${web_app_sql_user}@localhost; but then that fails if the user doesn't exist if you can believe that! lol

Script

#!/bin/bash
echo -e "Creating a SQL user and database for the webapp"

read -s -p "enter SQL root user password: " password

cat <<- EOF | mysql -v -uroot -p$password
    DROP DATABASE IF EXISTS \`${web_app_database}\`;
    CREATE DATABASE \`${web_app_database}\` CHARACTER SET utf8 COLLATE utf8_general_ci;

    -- Deal with possibility user already exists!
    DELETE FROM mysql.user WHERE User = '${web_app_sql_user}';
    -- drop user ${web_app_sql_user}@localhost;
    FLUSH PRIVILEGES;

    CREATE USER '${web_app_sql_user}'@localhost IDENTIFIED BY '${web_app_sql_user_password}';
    -- CREATE USER '${web_app_sql_user}'@localhost IDENTIFIED BY '${web_app_sql_user_password}';
    GRANT ALL ON ${web_app_database}.* TO '${web_app_sql_user}'@'localhost';

    GRANT USAGE ON * . * TO  '${web_app_sql_user}'@'localhost' IDENTIFIED BY  '${web_app_sql_user_password}' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
    GRANT ALL PRIVILEGES ON  \`${web_app_database}\` . * TO  '${web_app_sql_user}'@'localhost';

    FLUSH PRIVILEGES;
EOF

Result when running the script

--------------
DROP DATABASE IF EXISTS `web_app_database`
--------------

--------------
CREATE DATABASE `web_app_database` CHARACTER SET utf8 COLLATE utf8_general_ci
--------------

--------------
DELETE FROM mysql.user WHERE User = 'web_app_user'
--------------

--------------
FLUSH PRIVILEGES
--------------

--------------
CREATE USER 'web_app_user'@localhost IDENTIFIED BY ''
--------------

ERROR 1396 (HY000) at line 17: Operation CREATE USER failed for 'web_app_user'@'localhost'

Is it possible for MySQL to be able to handle these situations?

  • MySQL Server version: 5.5.46-0ubuntu0.14.04.2 (Ubuntu)

Updated script based on michael-sqlbot's answer

read -s -p "enter SQL root user password: " password
cat <<- EOF | mysql -v -uroot -p$password
    DROP DATABASE IF EXISTS \`${web_app_database}\`;
    CREATE DATABASE \`${web_app_database}\` CHARACTER SET utf8 COLLATE utf8_general_ci;
    GRANT ALL PRIVILEGES ON  \`${web_app_database}\` . * TO  '${web_app_sql_user}'@'localhost';
    FLUSH PRIVILEGES;
EOF
0

2 Answers 2

2

The task is too complex to safely (and simply) do in a shell script. Get into some programming language (Perl, etc) and write enough code to check for various end cases, syntax errors, punctuation in user names, etc, etc. You can also check for the existence of the user and/or database and exit or say "Dup name" or proceed with the delete, or whatever.

And totally disallow 'root' and 'mysql' and 'information_schema' and 'performance_schema'!

The CREATE USER + 3 GRANTs can be simplified to just

GRANT ALL PRIVILEGES ON db.* TO user@localhost IDENTIFIED BY '...' WITH ...
1

One possible alternative is this:

GRANT USAGE ON *.* TO '${web_app_sql_user}'@'localhost';

Do this first, then you can drop the user, then create the user.

The USAGE privilege is sort of a dummy privilege, that grants you the ability to connect to the server and that is all. Trying to REVOKE the privilege is a no-op, and even the on *.* in the grant statement is essentially meaningless. Every user has it, granting it is idempotent, and if a user doesn't exist, they're implicitly created with no privileges on actual database objects... so you can then drop them without an error.

This works as long as your @@SQL_MODE doesn't include NO_AUTO_CREATE_USER, which isn't there by default in 5.5.

In MySQL Server 5.7, Oracle (motto: "if it ain't broke, it will be broken soon") has deprecated the implicit account creation that happens when you GRANT to a non-existent user, and will likely remove it in a future release, but to their credit, they've added DROP USER IF EXISTS. They also added CREATE USER IF NOT EXISTS, but you don't want that, since you don't know what privileges the user might already have.

2
  • hey thanks a bunch for this, using this idea I was able to reduce the script to 3 lines of SQL and the new script works whether the user was pre-existing or not! If your interested I added the updated script to My question. As you outlined its requires understanding and navigating the changing landscape around these MySQL commands. I just went straight for the full GRANT ALL PRIVILEGES rather than GRANT USAGE I'm guessing your thinking was GRANT USAGE will reset existing privileges to a "blank slate" - so you have a known starting point, But I'm quite confident about the privileges issue Jan 28, 2016 at 3:50
  • No, my intention was not that GRANT USAGE would provide a blank slate... GRANT USAGE is a no-op on existing users, so my intention was that you could use that first, then DROP USER would not fail, because you'd then always have something to drop. Your shortcut runs the risk of the user having old/unexpected permissions. Jan 28, 2016 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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