Quick notes on creating a database user — MariaDB and MySQL use a combination of username and source host to determine access. This means ‘me’@’localhost’ and ‘me’@’remotehost’ can have different passwords and privilege sets. How do you know what the hostname is for your connection? I usually try to connect and read the host from the error message — it’ll say ‘someone’@’something’ cannot access the database.

# Create a user that is allowed to connect from a specific host
create user 'username'@'hostname' identified by 'S0m3P@s5w0rd';
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'hostname';

# Create a user that is allowed to connect from a specific IP
create user 'username1'@'10.5.1.2' identified by 'S0m3P@s5w0rd';
GRANT ALL PRIVILEGES ON dbname.* TO 'username1'@'10.5.1.2';

# Create a user that is allowed to connect from database server
create user 'username2'@'localhost' identified by 'S0m3P@s5w0rd';
GRANT ALL PRIVILEGES ON dbname.* TO 'username2'@'localhost';

# Create a user that is allowed to connect from any host
create user 'username3'@'%' identified by 'S0m3P@s5w0rd';
GRANT ALL PRIVILEGES ON dbname.* TO 'username3'@'%';

# Flush so new privileges are effective
flush privileges;

# View list of database users
SELECT User, Host FROM mysql.user;
+----------------+------------+
| User           | Host       |
+----------------+------------+
| username3      | %          |
| username2      | 10.5.1.2   |
| username       | hostname   |
| root           | 127.0.0.1  |
| root           | ::1        |
| root           | localhost  |
+----------------+------------+
6 rows in set (0.000 sec)