Users, Grants, and Remote Access Without Opening 3306

MariaDB identity is a pair: a username and a host. The same login name with two hosts is two users with two password hashes and two privilege sets. That is the feature people forget, and it is why GRANT ALL TO app@% shows up in incident reviews. Remote access is a separate decision from user creation. You can have an application on a second VPS talking to MariaDB without ever publishing TCP 3306 on a public address.

The default after a careful install is bind-address 127.0.0.1. Keep it. Application traffic on the same host uses the Unix socket or 127.0.0.1. Traffic from another host should travel an SSH tunnel, a WireGuard interface, or a private address you control. L3/L4 DDoS filtering in Lucknow DC01 will absorb junk at the edge; it will not make a database handshake a public API.

Create users with the host you actually need

Do not start from root. Do not start from %. Name the database, name the user, and name the host. localhost means the Unix socket and local TCP. A dotted IP means that source address only. A subnet in CIDR form is a last resort, not a convenience.

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'app'@'localhost' IDENTIFIED BY 'rotate-this-secret';
CREATE USER 'app'@'10.8.0.12' IDENTIFIED BY 'rotate-this-secret';
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'rotate-backup-secret';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER ON appdb.* TO 'app'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER ON appdb.* TO 'app'@'10.8.0.12';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON appdb.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
SELECT user, host, plugin FROM mysql.user ORDER BY user, host;

The backup user does not need INSERT. The application user does not need SUPER, PROCESS, or FILE. FILE in particular lets a compromised app write to paths the mysqld user can reach. PROCESS lets it inspect other sessions. Skip them until a tool proves it cannot run without them.

After FLUSH PRIVILEGES, test both identities from the client that will actually connect. A grant that works from your laptop via a tunnel is not proof that the app host can connect. A grant that works as root is not proof that app@localhost can.

Prefer a tunnel over a public bind

If the application is on another VPS, the clean pattern is still bind 127.0.0.1 on the database host and punch a tunnel from the app host. The app then talks to its own localhost, and MariaDB sees a local connection or a connection from the tunnel's source, depending on how you forward.

# on the application VPS: local 3307 forwards to MariaDB on db01
ssh -N -L 3307:127.0.0.1:3306 deploy@db01.internal
# application DSN points at 127.0.0.1:3307
mariadb --host=127.0.0.1 --port=3307 --user=app --password appdb -e "SELECT 1"

Keep the tunnel under systemd so it comes back after a reboot. Fail2ban on SSH and key-only logins matter more once the database is reachable through that path. If you later decide a private overlay is cleaner than SSH, bind MariaDB on the overlay address only, grant to that source IP, and still leave the public interface unbound.

If you must bind a non-loopback address, do all three of these or do not ship:

• bind-address is the private or overlay IP, never 0.0.0.0
• the user host is that application IP, never %
• the host firewall allows 3306 from that IP only, and the public security group still drops 3306

A public 3306 with a strong password is still a public brute-force surface. Password plugins and max_connect_errors slow attackers down. They do not remove the surface.

Inspect grants before you debug the app

When a login fails, read the server's idea of the user before you rotate secrets in the app. SHOW GRANTS FOR the exact user@host pair. SHOW VARIABLES LIKE bind_address. ss -lntp to see what is actually listening. Most "access denied" tickets are a host mismatch: the client connected as app@10.8.0.12 while you granted app@localhost, or skip-name-resolve is on and a hostname grant can never match.

SHOW GRANTS FOR 'app'@'localhost';
SHOW GRANTS FOR 'app'@'10.8.0.12';
SHOW VARIABLES WHERE Variable_name IN ('bind_address','skip_name_resolve');

Drop leftover accounts. Anonymous users and test@% are still the classic install leftovers. Keep one admin path: either unix_socket for the local root OS user, or a named dba@localhost with a password stored in a root-only client config. Two admin paths is how you lose track of who can dump the whole instance.

Least privilege is a host, a database, and a verb list, not a strong password on a world-open port. Keep 3306 off the public interface and move the application through a path you can audit. You can stand up two Ubuntu instances on Netbay in Lucknow in under a minute each and rehearse the tunnel before the application ever stores a password at netbayhosts.in.