MariaDB Create Database and User (With GRANT and Login Test)

Every new app should get its own MariaDB database and its own login, so a bug in one app can’t read or drop another app’s tables.

Quick answer: run CREATE DATABASE app_db;, then CREATE USER 'app'@'localhost' IDENTIFIED BY 'password'; and GRANT ALL PRIVILEGES ON app_db.* TO 'app'@'localhost';. You don’t need FLUSH PRIVILEGES.

In this tutorial, I’ll walk you through the MariaDB create database and user routine my team follows on MariaDB 13.0.2, from the first statement to a real login test and the two errors you’ll hit.

What You Need Before You Start

• MariaDB running (see how to install MariaDB) and the mariadb client.
• An admin login such as root: CREATE USER needs the global CREATE USER privilege.

Step 1: Create the Database With CREATE DATABASE

We’ll set up a small Denver orders app. IF NOT EXISTS makes the script safe to rerun, and the character set clause gives you full Unicode, emoji included.

-- Database for the Denver orders app
CREATE DATABASE IF NOT EXISTS dfq_mariauser
  CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;

USE dfq_mariauser;
CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(40) NOT NULL,
  state CHAR(2) NOT NULL
);
INSERT INTO customers (full_name, state)
VALUES ('Megan Bowen', 'CO'), ('Alex Wilber', 'TX'), ('Adele Vance', 'WA');

SELECT schema_name, default_character_set_name AS charset,
       default_collation_name AS db_collation
FROM information_schema.SCHEMATA WHERE schema_name = 'dfq_mariauser';

Output:

+---------------+---------+-----------------------+
| schema_name   | charset | db_collation          |
+---------------+---------+-----------------------+
| dfq_mariauser | utf8mb4 | utf8mb4_uca1400_ai_ci |
+---------------+---------+-----------------------+

utf8mb4_uca1400_ai_ci is the server default on my install; naming it keeps every server the same. See MariaDB CREATE DATABASE examples for more options.

In HeidiSQL: right-click the server in the left tree, choose Create new > Database, enter the name and collation, then click OK.

Step 2: Create the User With CREATE USER

A MariaDB account is a user name plus a host. Our app runs on the database server, and reporting connects from the office subnet, so each account gets only the host it uses.

-- App on this server, reports from the office subnet
CREATE USER 'dfq_mu_app'@'localhost' IDENTIFIED BY 'Str0ng!Pass2026';
CREATE USER 'dfq_mu_report'@'10.0.5.%' IDENTIFIED BY 'Rep0rt!Pass2026';

SELECT user, host, plugin
FROM mysql.user
WHERE user LIKE 'dfq\_mu\_%';

Output:

+---------------+-----------+-----------------------+
| User          | Host      | plugin                |
+---------------+-----------+-----------------------+
| dfq_mu_app    | localhost | mysql_native_password |
| dfq_mu_report | 10.0.5.%  | mysql_native_password |
+---------------+-----------+-----------------------+

Both accounts exist with no privileges yet. Later you can show users in MariaDB the same way.

Which host should you use: localhost, % or a subnet?

Host part	Who can log in
'localhost'	Clients on the same machine only
'10.0.5.%' or '10.0.5.0/255.255.255.0'	That IPv4 subnet only
'%' (default if you omit the host)	Any host; avoid unless a firewall limits access

MariaDB matches the most specific host first, so 'localhost' beats '%'. Remote accounts also need MariaDB remote access enabled.

Step 3: GRANT ALL PRIVILEGES on the Database (or Least Privilege)

The app owns its schema, so it gets ALL PRIVILEGES on dfq_mariauser.* only. Reporting just reads, so it gets SELECT.

GRANT ALL PRIVILEGES ON dfq_mariauser.* TO 'dfq_mu_app'@'localhost';
GRANT SELECT ON dfq_mariauser.* TO 'dfq_mu_report'@'10.0.5.%';

SHOW GRANTS FOR 'dfq_mu_app'@'localhost';
SHOW GRANTS FOR 'dfq_mu_report'@'10.0.5.%';

Output:

Grants for dfq_mu_app@localhost
GRANT USAGE ON *.* TO `dfq_mu_app`@`localhost` IDENTIFIED BY PASSWORD '*3D0648AA420BB2FB183A1A465F9B587976C95B3F'
GRANT ALL PRIVILEGES ON `dfq_mariauser`.* TO `dfq_mu_app`@`localhost`
Grants for dfq_mu_report@10.0.5.%
GRANT USAGE ON *.* TO `dfq_mu_report`@`10.0.5.%` IDENTIFIED BY PASSWORD '*15BDAD4E6791F0D055056C105DBA871D4C86AD3B'
GRANT SELECT ON `dfq_mariauser`.* TO `dfq_mu_report`@`10.0.5.%`

GRANT USAGE ON *.* means “can log in, no global rights”. Never grant an app ON *.*; that’s every database, mysql included.

ALL PRIVILEGES doesn’t include GRANT OPTION. If your app only reads and writes rows, GRANT SELECT, INSERT, UPDATE, DELETE is enough; see grant all privileges in MariaDB for table-level grants.

Do You Need FLUSH PRIVILEGES After GRANT in MariaDB?

No. The MariaDB docs say FLUSH PRIVILEGES is only needed when you edit the mysql tables directly, not after CREATE USER, DROP USER or GRANT. More examples: grant user access to a MariaDB database.

Step 4: Test the Login as the New User

A root session proves nothing, so we log in as the app account with its demo password and run a two-line script:

mariadb -u dfq_mu_app --password=Str0ng!Pass2026 -t dfq_mariauser < login-test.sql

-- Runs as the new app account, not root
SELECT CURRENT_USER(), COUNT(*) AS customers
FROM customers;

Output:

+----------------------+-----------+
| CURRENT_USER()       | customers |
+----------------------+-----------+
| dfq_mu_app@localhost |         3 |
+----------------------+-----------+

CURRENT_USER() shows the matched account, and the count of 3 proves it can read the table. With a real password, run mariadb -u dfq_mu_app -p so you’re prompted and nothing lands in your history.

Step 5: Change or Drop a MariaDB User, and Use Roles

ALTER USER changes the password and keeps the grants; DROP USER removes the account and its privileges.

ALTER USER 'dfq_mu_app'@'localhost' IDENTIFIED BY 'N3w!Pass2026';
DROP USER IF EXISTS 'dfq_mu_report'@'10.0.5.%';

SELECT user, host FROM mysql.user WHERE user LIKE 'dfq\_mu\_%';

Output:

+------------+-----------+
| User       | Host      |
+------------+-----------+
| dfq_mu_app | localhost |
+------------+-----------+

For more cases, see MariaDB remove user. Avoid CREATE OR REPLACE USER on live accounts: it drops and recreates the account, and when I tried it, SHOW GRANTS listed only USAGE.

Roles: CREATE ROLE and SET DEFAULT ROLE

CREATE ROLE dfq_mu_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON dfq_mariauser.* TO dfq_mu_writer;

CREATE USER 'dfq_mu_api'@'localhost' IDENTIFIED BY 'Api!Pass2026';
GRANT dfq_mu_writer TO 'dfq_mu_api'@'localhost';
SET DEFAULT ROLE dfq_mu_writer FOR 'dfq_mu_api'@'localhost';

SHOW GRANTS FOR 'dfq_mu_api'@'localhost';

Output:

Grants for dfq_mu_api@localhost
GRANT `dfq_mu_writer` TO `dfq_mu_api`@`localhost`
GRANT USAGE ON *.* TO `dfq_mu_api`@`localhost` IDENTIFIED BY PASSWORD '*14BE6145CB52138BB9F7353C12F1F86D2323E7A2'
SET DEFAULT ROLE `dfq_mu_writer` FOR `dfq_mu_api`@`localhost`

Without SET DEFAULT ROLE, you’d have to run SET ROLE dfq_mu_writer in every session.

Fix Common Errors: MariaDB ERROR 1045 and ERROR 1396

ERROR 1045 (28000): Access denied for user

Here’s a wrong password, then the right password from a host the account doesn’t cover:

@echo off
echo Wrong password:
mariadb -u dfq_mu_app --password=WrongPass2026 dfq_mariauser -e "SELECT 1"
echo Right password, wrong host:
mariadb -u dfq_mu_report --password=Rep0rt!Pass2026 dfq_mariauser -e "SELECT 1"

Output:

Wrong password:
ERROR 1045 (28000): Access denied for user 'dfq_mu_app'@'localhost' (using password: YES)
Right password, wrong host:
ERROR 1045 (28000): Access denied for user 'dfq_mu_report'@'localhost' (using password: YES)

Both fail the same way: 'dfq_mu_report'@'localhost' doesn’t exist, only @'10.0.5.%'. Check the user’s host before you reset its password.

ERROR 1396 (HY000): Operation CREATE USER failed

-- The setup script runs a second time
CREATE USER IF NOT EXISTS 'dfq_mu_app'@'localhost'
  IDENTIFIED BY 'Str0ng!Pass2026';
SHOW WARNINGS;

CREATE USER 'dfq_mu_app'@'localhost' IDENTIFIED BY 'Str0ng!Pass2026';

Output:

+-------+------+---------------------------------------------------------------+
| Level | Code | Message                                                       |
+-------+------+---------------------------------------------------------------+
| Note  | 1973 | Can't create user 'dfq_mu_app'@'localhost'; it already exists |
+-------+------+---------------------------------------------------------------+
--------------
CREATE USER 'dfq_mu_app'@'localhost' IDENTIFIED BY 'Str0ng!Pass2026'
--------------

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

IF NOT EXISTS turns it into note 1973, so use it in rerunnable scripts. DROP USER on a missing account also raises 1396; add IF EXISTS.

MariaDB Create Database and User Cheat Sheet

Task	Statement
Database	CREATE DATABASE IF NOT EXISTS db CHARACTER SET utf8mb4;
User	CREATE USER 'u'@'localhost' IDENTIFIED BY '...';
All rights on one database	GRANT ALL PRIVILEGES ON db.* TO 'u'@'localhost';
Read-only	GRANT SELECT ON db.* TO 'u'@'host';
Check	SHOW GRANTS FOR 'u'@'localhost';

Common MariaDB User Mistakes to Avoid

Don’t grant ON *.* or use '%' out of habit, and don’t test as root. Before you drop an old database, back up the MariaDB database.

Next Steps After You Create the User

Connect your app or a GUI client; our MariaDB GUI tools roundup compares them. We’d also suggest these:

• How MariaDB differs from MySQL
• Check which MariaDB version you’re running
• Reset a lost MariaDB root password
• Fix MariaDB syntax error 1064

Frequently Asked Questions