The most common MySQL security mistake in small projects is simple: the application connects as root, often with a short password, and often from %. This lesson shows the safer setup.
Why not root
root can:
- read and change every database on the server,
- create and drop users and databases,
- read and write files on the server (with
FILE), - shut the server down.
If your application has any flaw (SQL injection, a leaked .env file, a vulnerable plugin), an attacker who gets the application’s credentials gets everything that account can do. With root, that is the entire server. With a restricted account, it is one database, and not even its structure.
The accounts a project needs
| Account | Used by | Privileges |
|---|---|---|
shop_app | The running application | SELECT, INSERT, UPDATE, DELETE on shop.*; EXECUTE on its routines |
shop_migrate | Deployments and schema changes | ALL on shop.* (or CREATE, ALTER, DROP, INDEX, REFERENCES, TRIGGER plus the DML ones) |
shop_report | BI tools, analysts | SELECT on shop.* (or on views) |
shop_backup | The backup job | SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES, PROCESS, RELOAD (as mysqldump needs) |
| Named admin accounts | People | Roles with the needed administrative rights |
CREATE DATABASE shop;
CREATE USER 'shop_app'@'10.0.0.%' IDENTIFIED BY 'Ap9!pass#Long#Random#1';
CREATE USER 'shop_migrate'@'10.0.0.%' IDENTIFIED BY 'Mig8!pass#Long#Random#2';
CREATE USER 'shop_report'@'10.0.0.%' IDENTIFIED BY 'Rep7!pass#Long#Random#3';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'10.0.0.%';
GRANT ALL PRIVILEGES ON shop.* TO 'shop_migrate'@'10.0.0.%';
GRANT SELECT ON shop.* TO 'shop_report'@'10.0.0.%';
SELECT grantee, privilege_type FROM information_schema.schema_privileges WHERE table_schema = 'shop' ORDER BY grantee, privilege_type;+---------------------------+-------------------------+
| grantee | privilege_type |
+---------------------------+-------------------------+
| 'shop_app'@'10.0.0.%' | DELETE |
| 'shop_app'@'10.0.0.%' | INSERT |
| 'shop_app'@'10.0.0.%' | SELECT |
| 'shop_app'@'10.0.0.%' | UPDATE |
| 'shop_migrate'@'10.0.0.%' | ALTER |
| 'shop_migrate'@'10.0.0.%' | ALTER ROUTINE |
| 'shop_migrate'@'10.0.0.%' | CREATE |
| 'shop_migrate'@'10.0.0.%' | CREATE ROUTINE |
| 'shop_migrate'@'10.0.0.%' | CREATE TEMPORARY TABLES |
| 'shop_migrate'@'10.0.0.%' | CREATE VIEW |
| 'shop_migrate'@'10.0.0.%' | DELETE |
| 'shop_migrate'@'10.0.0.%' | DROP |
| 'shop_migrate'@'10.0.0.%' | EVENT |
| 'shop_migrate'@'10.0.0.%' | EXECUTE |
| 'shop_migrate'@'10.0.0.%' | INDEX |
| 'shop_migrate'@'10.0.0.%' | INSERT |
| 'shop_migrate'@'10.0.0.%' | LOCK TABLES |
| 'shop_migrate'@'10.0.0.%' | REFERENCES |
| 'shop_migrate'@'10.0.0.%' | SELECT |
| 'shop_migrate'@'10.0.0.%' | SHOW VIEW |
| 'shop_migrate'@'10.0.0.%' | TRIGGER |
| 'shop_migrate'@'10.0.0.%' | UPDATE |
| 'shop_report'@'10.0.0.%' | SELECT |
+---------------------------+-------------------------+
23 rows in set(ALL PRIVILEGES ON shop.* expands to the list shown.) Note the narrow host 10.0.0.%: these accounts work only from the application network.
The migration account is separate on purpose
The application needs to read and write rows. It does not need to alter or drop tables. Splitting the two means:
- An injection hole in the app cannot drop tables.
- A mistaken
DROPin application code fails. - The powerful credentials exist only in the deployment pipeline, not in the web server’s environment.
WordPress and similar software
WordPress needs more than plain DML: its installer and updates run CREATE TABLE, ALTER TABLE and DROP. A common compromise:
- Run the application with
SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROPon its own database only, because core, plugin and theme updates change tables. - Never share that account with other sites.
- Keep the account host-restricted and the password long and random.
- Keep software updated, since that is where most WordPress compromises start.
If your update process is controlled (staging first, then deployment), you can use the stricter split with a migration account. For a typical single-site WordPress, one account on one database is acceptable. A shared root is not.
Connecting from PHP (and others)
<?php
$dsn = 'mysql:host=10.0.0.5;dbname=shop;charset=utf8mb4';
$pdo = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASSWORD'), [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::MYSQL_ATTR_SSL_CA => '/etc/ssl/mysql-ca.pem',
]);The user and the password come from environment variables (or a secret manager), not from a file in the web root and not from the source code.
Where to keep the credentials
| Do | Do not |
|---|---|
| Environment variables, a secrets manager, a config file outside the web root with restricted permissions | Hard-coded in source code |
| Different credentials for development, testing, staging and production | The production password on a developer laptop |
| Rotate passwords regularly, and after an employee leaves (Dual Passwords) | The same password for years |
Treat .env as a secret: never commit it, never serve it | A .env or wp-config.php backup (.env.bak, wp-config.php~) reachable by URL |
Add .env to .gitignore, and block access to dotfiles and backup files in the web server configuration. If a credential ever reached a Git repository, rotate it, even if the commit was deleted.
Check what an account can do
Always test a new account the way the application will use it. A real session as shop_app gives:
SELECT COUNT(*) FROM shop.customers; -> works
DROP TABLE shop.orders; -> ERROR 1142 (42000): DROP command denied to user 'shop_app'@'localhost' for table 'orders'
SELECT user FROM mysql.user; -> ERROR 1142 (42000): SELECT command denied to user 'shop_app'@'localhost' for table 'user'(From a real MySQL 8.4 server.) If the first statement works and the other two are denied, the account is right.
Audit the existing accounts
SELECT user, host, account_locked, password_expired FROM mysql.user ORDER BY user, host;(your accounts: look for host % entries, accounts with empty passwords, root accounts reachable from other hosts, and users that nobody recognises)Look for:
- accounts with host
%, - accounts without a password,
rootallowed from anywhere ('root'@'%'),- accounts that belong to people who left, or applications that no longer exist,
- accounts with global privileges that are not administrators.
SELECT user, host, authentication_string = '' AS empty_password FROM mysql.user WHERE user NOT LIKE 'mysql.%' ORDER BY user;+--------------+-----------+----------------+
| user | host | empty_password |
+--------------+-----------+----------------+
| root | localhost | 1 |
| shop_app | 10.0.0.% | 0 |
| shop_migrate | 10.0.0.% | 0 |
| shop_report | 10.0.0.% | 0 |
+--------------+-----------+----------------+
4 rows in setRemove the old defaults
On a freshly installed server, run the hardening tool (mysql_secure_installation): it sets a root password, removes anonymous users, restricts root to localhost and drops the test database. Managed services and Docker images usually do this for you. Confirm it.