You know what I do right after I spin up a fresh MySQL box?
I create dedicated users. Then I stop using root.
Not because I'm paranoid. Because I like sleeping. And because I've watched "temporary" root access turn into "wait, why does the app user have DROP TABLE in production" more times than I want to count. Sleep is good. Panic at 3am is not.
This is boring work. It's also the kind of boring work that saves your weekend when something breaks at 2am and you need to figure out which service did what.
Root is for setup, not for apps
root is a sledgehammer. It's fine for initial setup and emergencies. It's a bad default for:
- application connections
- data pipelines
- admin scripts you run half-awake
- anything that lives longer than an afternoon
The goal isn't to never mess up. The goal is to build systems that catch you when you do. Least-privilege DB users are part of that system.
The one command you need (and the part people mess up)
Here's the basic syntax:
CREATE USER 'name'@'host.name' IDENTIFIED BY 'VerySecurePassword';
Three pieces matter:
- Username:
'name' - Host:
'host.name'(or an IP, or%, more on that in a second) - Password: set by
IDENTIFIED BY
That last part is not optional.
If you skip IDENTIFIED BY, you might create a user that can connect without a password. It depends on your auth plugin and server config, but why risk it?
So don't do this:
CREATE USER 'app'@'%';
I don't care if it works on your laptop. Plenty of terrible ideas work on laptops. Production is different.
Picking the right host: don't use % unless you mean it
The host part controls where the user can connect from.
Common patterns:
'app'@'localhost'for local services on the DB machine'app'@'10.0.1.50'for a single app server'app'@'10.0.1.%'for a subnet (still risky, but sometimes real life)'app'@'%'for anywhere (usually a mistake)
If you're moving fast at a startup and you don't know the final network yet, you can start broader and tighten later. Just do it on purpose. Put it on a checklist. Future-you is busy and cranky.
Give permissions like you're paying for each one
Creating the user is step one. Step two is permissions.
A typical app user often needs something like:
GRANT SELECT, INSERT, UPDATE, DELETE
ON yourdb.*
TO 'app'@'10.0.1.50';
And then:
FLUSH PRIVILEGES;
(Yes, MySQL often applies grants without it now. I still run it because I like being boring and correct.)
Avoid handing out:
GRANT ALLDROPALTER- anything global like
ON *.*
Unless you're doing migrations and you know that's what you're doing. Even then, consider a separate "migration user" you only use during deploys.
Passwords: stop making them "Summer2026!"
Strong passwords matter. I've seen databases get exposed through weird paths - a misconfigured security group here, a debug port someone left open there, a tunnel that was "just for testing" six months ago. Attackers don't need creativity. They need time.
I generate passwords with my own tool here:
Secure Random Password Generator
Use whatever generator you trust. The point is: long, random, not reused, and stored in a real secret manager (or at least an environment variable that isn't committed to Git because someone got "helpful").
My basic checklist after provisioning a DB
- create an app user with least privileges
- create an admin user for humans (not
root) - create a migration user if needed
- confirm backups exist and have been tested
- write down the grants so nobody has to guess later
None of this is exciting. But it works.
If you take one thing from this: don't use root for your app, and always include IDENTIFIED BY when creating users.
-Sethers