MariaDB: Create user, password and database with session variables

When setting up MariaDB databases for development or production, it's useful to use session variables for sensitive information like usernames and passwords. Here's a quick way to create a MariaDB user, set a password, and create a database using session variables that you set once and reuse.

Connect to MariaDB and set session variables

First, connect to MariaDB and define your database credentials as session variables:

# Connect to MariaDB as root
sudo mariadb -u root

Once connected to MariaDB, set your session variables:

-- Set session variables for database credentials
SET @DB_USER = 'myapp_user';
SET @DB_PASS = 'secure_password_123';
SET @DB_NAME = 'myapp_database';

Create the database and user

Now you can use these variables in your MariaDB commands:

-- Create the database
SET @sql = CONCAT('CREATE DATABASE IF NOT EXISTS ', @DB_NAME);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Create the user with password
SET @sql = CONCAT('CREATE USER IF NOT EXISTS ''', @DB_USER, '''@''localhost'' IDENTIFIED BY ''', @DB_PASS, '''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Grant all privileges on the database to the user
SET @sql = CONCAT('GRANT ALL PRIVILEGES ON ', @DB_NAME, '.* TO ''', @DB_USER, '''@''localhost''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Apply the privilege changes
FLUSH PRIVILEGES;

-- Show the created database and user
SET @sql = CONCAT('SHOW DATABASES LIKE ''', @DB_NAME, '''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT('SELECT User, Host FROM mysql.user WHERE User = ''', @DB_USER, '''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

For remote access

If you need the user to connect from a specific network (e.g., WireGuard VPN subnet 172.16.29.0/24):

-- Set the network range for remote access (WireGuard subnet 172.16.29.0/24)
SET @NETWORK_RANGE = '172.16.29.%';

-- Create user for remote access from specific network
SET @sql = CONCAT('CREATE USER IF NOT EXISTS ''', @DB_USER, '''@''', @NETWORK_RANGE, ''' IDENTIFIED BY ''', @DB_PASS, '''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT('GRANT ALL PRIVILEGES ON ', @DB_NAME, '.* TO ''', @DB_USER, '''@''', @NETWORK_RANGE, '''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

FLUSH PRIVILEGES;

For access from any host, you can set @NETWORK_RANGE = '%' instead.

This way, you set up credentials once in your current MariaDB session, copy-paste the commands and you're good to go.