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.