The Real Problem: Apps Connecting to the Database as Root
I’ve seen this pattern in over a dozen projects — from small startups to teams of 20+ experienced engineers: the application’s .env file has DB_USER=root, and nobody thinks it’s a problem. The app runs fine, deployment is smooth, so everyone moves on.
Until something goes wrong.
I once had to deal with database corruption at 3 AM, spending two hours restoring from a backup — after that, I sat down and reviewed how MySQL user management was handled across all our projects. What I found was a pile of issues I had been dismissing for too long.
Why Using Root for Your Application Is Wrong
The root account in MySQL is the equivalent of sudo in Linux — it has the power to do everything: DROP DATABASE, delete all records, even interfere with server configuration.
When an application uses root:
- A single SQL injection vulnerability can wipe out the entire database
- A bug in your code could accidentally
DROP TABLEinstead ofDELETE - If credentials are leaked, an attacker gains full control over the MySQL server
- There’s no way to audit who did what — everything shows up as “root”
The core principle in security is the Principle of Least Privilege: grant only the permissions that are actually needed, nothing more. MySQL has a granular privilege system that makes this straightforward — and setting it up correctly from the start takes less than 5 minutes.
MySQL User Management and Privilege Commands
Creating a New User
There are three ways to create a user — choose based on where your app connects from:
-- Create a user that can only connect from localhost
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'strong_password_here';
-- Create a user that can connect from any IP address
CREATE USER 'appuser'@'%' IDENTIFIED BY 'strong_password_here';
-- Create a user restricted to a specific IP address
CREATE USER 'appuser'@'192.168.1.100' IDENTIFIED BY 'strong_password_here';A common gotcha: 'user'@'localhost' and 'user'@'%' are two different users in MySQL. If you create a user with @'%' but your app connects via localhost, MySQL may return an access denied error with no obvious explanation.
Granting Privileges with GRANT
MySQL has over 30 privilege types, but a typical web application only needs to worry about a few:
-- Grant read/write access to a database (suitable for most apps)
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'appuser'@'localhost';
-- Grant full access to a database (for dev/admin users)
GRANT ALL PRIVILEGES ON myapp_db.* TO 'devuser'@'localhost';
-- Grant read-only access (suitable for reporting/analytics)
GRANT SELECT ON myapp_db.* TO 'readonly_user'@'localhost';
-- Grant privileges on a specific table
GRANT SELECT, INSERT ON myapp_db.orders TO 'appuser'@'localhost';
-- Apply changes immediately
FLUSH PRIVILEGES;Viewing Current Privileges
-- Show privileges for a specific user
SHOW GRANTS FOR 'appuser'@'localhost';
-- Show privileges for the currently logged-in user
SHOW GRANTS;
-- List all users in the system
SELECT user, host, account_locked FROM mysql.user;Revoking Privileges with REVOKE
-- Revoke DELETE privilege (keep SELECT, INSERT, UPDATE)
REVOKE DELETE ON myapp_db.* FROM 'appuser'@'localhost';
-- Revoke all privileges on a database
REVOKE ALL PRIVILEGES ON myapp_db.* FROM 'appuser'@'localhost';
-- Delete the user entirely
DROP USER 'appuser'@'localhost';Changing a User’s Password
-- MySQL 5.7.6 and above
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'new_strong_password';
FLUSH PRIVILEGES;Best Practices from Real-World Experience
1. One Dedicated User per Application
Running 3 apps on one server (blog, shop, CRM)? Create 3 separate users, each with privileges scoped to their respective database. If one app is compromised, the attacker can’t pivot to another app’s database.
CREATE USER 'blog_user'@'localhost' IDENTIFIED BY 'pass_blog';
GRANT SELECT, INSERT, UPDATE, DELETE ON blog_db.* TO 'blog_user'@'localhost';
CREATE USER 'shop_user'@'localhost' IDENTIFIED BY 'pass_shop';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'shop_user'@'localhost';2. Never Grant GRANT OPTION to Application Users
GRANT OPTION allows a user to grant privileges to other users — which is effectively admin-level access. Only DBAs should have this. If you see GRANT ... WITH GRANT OPTION in your team’s setup scripts, that’s a red flag to revisit your entire security model.
3. Set Resource Limits on Users
A compromised user account can send thousands of queries per second and bring down the entire server. Set resource limits to prevent this before it happens:
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'pass'
WITH MAX_QUERIES_PER_HOUR 10000
MAX_CONNECTIONS_PER_HOUR 200
MAX_USER_CONNECTIONS 20;4. Audit Regularly — Remove Unused Users
Once a month, run this query and scan through the list:
SELECT user, host, password_last_changed, account_locked
FROM mysql.user
ORDER BY password_last_changed;Developers who’ve left, projects that have been shut down, old staging environments no one uses anymore — DROP them all. Don’t leave “ghost accounts” sitting around waiting to be exploited.
Standard Setup for New Projects
This is the workflow I use every time I set up a database — with a clear separation between the app user and the deploy user:
-- Step 1: Create the database
CREATE DATABASE myproject_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Step 2: Create the app user (used for day-to-day operations in production)
CREATE USER 'myproject_app'@'localhost' IDENTIFIED BY 'strong_app_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myproject_db.* TO 'myproject_app'@'localhost';
-- Step 3: Create the migration/deploy user (used only during deployments)
CREATE USER 'myproject_deploy'@'localhost' IDENTIFIED BY 'strong_deploy_password';
GRANT ALL PRIVILEGES ON myproject_db.* TO 'myproject_deploy'@'localhost';
-- Step 4: Apply and verify
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'myproject_app'@'localhost';
SHOW GRANTS FOR 'myproject_deploy'@'localhost';The app user only has SELECT, INSERT, UPDATE, DELETE — no DROP, no ALTER. The deploy user has ALL PRIVILEGES, but those credentials never go into the production .env file. They live only in the CI/CD pipeline, used when running migrations, and nowhere else.
After that 3 AM database recovery incident, I’ve applied this principle to every project — and added one more habit: verifying backups daily. Proper access control minimizes the damage when something goes wrong, but backups are what actually save you when everything falls apart.
