Mysql – Idempotent user and database creation script with MySQL

MySQL

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

Best Answer

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

PASSWORD FUNCTIONS

Please run the following:

show variables like 'old_passwords';
select password('rolando'),old_password('rolando');

Depending on where old_passwords is enabled or not, you should get either the following:

MySQL 5.0 with old_passwords off

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando')                       | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409        |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.1 with old_passwords on

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409    | 05e97e95241a4409        |
+---------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.5 with old_passwords off

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando')                       | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409        |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.5 with old_passwords on

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409    | 05e97e95241a4409        |
+---------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

PASSWORDS STORED

Please run this query:

SELECT COUNT(1) password_length_count,password_length FROM
(SELECT LENGTH(password) password_length FROM mysql.user) A
GROUP BY password_length;

If you get any password_length of 16, you have to enable old_passwords for the old style authentication to work.

PASSWORDS FROM CLIENT

In some cases, using mysql libraries that came from old yum installs of MySQL, PHP, Perl or Apache may have pre-MySQL4.1 user authentication code. Please upgrade such libraries.

Mysql – How to give root@localhost permission to grant privileges in MySQL

I have dealt with this issue before.

When you ran

select count(1) UserTableColumnCount from information_schema.columns
where table_schema='mysql' and table_name='user';

you should have gotten 42. That's how many columns MySQL 5.5 has for mysql.user. Since you got 39, that means you must have upgraded from MySQL 5.1. That has 39 columns.

I wrote an earlier post about the number of columns in mysql.user in different versions : MySQL service stops after trying to grant privileges to a user

Here is the post where I dealt with this : mysql: Restore All privileges to admin user

Hopefully you could run

# mysql_upgrade --upgrade-system-tables

to realign mysql.user and have it autofill missing permissions with Y.

Give it a Try !!!

If you want to try to fix the mysql.user manually, here are the steps:

#
# Backup the mysql.user table
#
CREATE TABLE mysql.user_backup LIKE mysql.user;
INSERT INTO mysql.user_backup SELECT * FROM mysql.user;
#
# Add Missing Columns
#
ALTER TABLE mysql.user 
    ADD COLUMN Create_tablespace_priv enum('N','Y') NOT NULL DEFAULT 'N'
    AFTER Trigger_Priv
;
ALTER TABLE mysql.user ADD COLUMN plugin char(64);
ALTER TABLE mysql.user ADD COLUMN authentication_string text DEFAULT NULL;
#
# Give root user all privileges
#
UPDATE mysql.user SET
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',
Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',
Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',
Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',
Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',
Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',
Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',
Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y',
Create_tablespace_priv='Y'
WHERE user='root';
FLUSH PRIVILEGES;

That's it !!!

Best Answer

Related Solutions

Mysql – Issue with connecting to the thesql database

PASSWORD FUNCTIONS

PASSWORDS STORED

PASSWORDS FROM CLIENT

Mysql – How to give root@localhost permission to grant privileges in MySQL

Related Question