Create new database and user with MySQL

I am quite often need to create new database with new user and I find out myself searching this process in documentation to be 100% sure that eveything is fine.

This is my litte script that I am using to accomplish that. It will create a new database with user of same name of database that has all privileges granted to that database.

# Define variables
SET @database = '<name of database>';
SET @password = SUBSTR(MD5(RAND()), 1, 16);
SELECT @password as generated_password;

# Create database
SET @create_database = CONCAT('CREATE DATABASE `', @database, '` COLLATE "utf8mb4_general_ci"');
PREPARE stmt FROM @create_database;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

# Create user
SET @create_user = CONCAT('CREATE USER "', @database, '"@"%" IDENTIFIED BY "', @password, '" ');
PREPARE stmt FROM @create_user;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

# Grant all privileges
SET @grant_query = CONCAT('GRANT USAGE ON *.* TO "', @database, '"@"%" IDENTIFIED BY "', @password, '"');
PREPARE stmt FROM @grant_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Tags: #database , Created: 12. 8. 2022
Created by Martin Krizan (2022)