Security hardening – users, grants, least privilege

Security hardening for MySQL – users, grants, and least privilege. The default install mindset (‘root can do everything’) does not survive a internet-facing app. Separate roles for app, migrations, backups, and humans; grant only what each role needs.

Principles

One MySQL user per application and environment – not shared credentials across staging and production
App user: DML on its database only – no DROP, no SUPER, no FILE
Migration user: DDL in controlled deploys – not wired into the web app
Humans: personal accounts, not shared ‘dev’ login
Prefer TLS for connections over untrusted networks

Creating application users

CREATE USER 'shop_app'@'10.0.1.%' IDENTIFIED BY 'use-a-long-random-secret';

GRANT SELECT, INSERT, UPDATE, DELETE
ON shop_production.*
TO 'shop_app'@'10.0.1.%';

FLUSH PRIVILEGES;

Restrict host to your app subnet (10.0.1.%) or a single IP. 'shop_app'@'%' from anywhere is convenient and wrong.

Migration and read-only roles

CREATE USER 'shop_migrate'@'10.0.1.10' IDENTIFIED BY 'another-long-secret';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP, REFERENCES
ON shop_production.*
TO 'shop_migrate'@'10.0.1.10';

CREATE USER 'shop_report'@'10.0.2.%' IDENTIFIED BY 'reporting-secret';

GRANT SELECT ON shop_production.* TO 'shop_report'@'10.0.2.%';

Reporting tools and BI get SELECT only. They cannot truncate a table because someone misconfigured a sync job.

Column-level grants (sparingly)

GRANT SELECT (id, name, email), UPDATE (name)
ON shop_production.customers
TO 'support_tool'@'10.0.3.5';

Column grants get fiddly fast. Often better: a view exposing safe columns and grant SELECT on the view.

CREATE VIEW customer_directory AS
SELECT id, name, email, created_at FROM customers;

GRANT SELECT ON shop_production.customer_directory TO 'support_tool'@'10.0.3.5';

Auditing who can do what

SHOW GRANTS FOR 'shop_app'@'10.0.1.%';

SELECT user, host FROM mysql.user;

SELECT * FROM information_schema.schema_privileges
WHERE grantee LIKE '%shop_app%';

Dangerous privileges to avoid on app users

SUPER, SYSTEM_VARIABLES_ADMIN – bypass security settings
FILE – read/write server files
SHUTDOWN, RELOAD – obvious
CREATE USER, GRANT OPTION – privilege escalation
mysql system database access for app accounts

Password and auth hygiene

ALTER USER 'shop_app'@'10.0.1.%' IDENTIFIED BY 'new-rotated-secret';

-- MySQL 8 default: caching_sha2_password
-- Ensure client libraries support it or use mysql_native_password only if you must

Store credentials in environment variables or a secrets manager – not in git. Rotate after staff leave or suspected leak. Disable unused accounts:

ALTER USER 'old_intern'@'%' ACCOUNT LOCK;
DROP USER IF EXISTS 'legacy_app'@'%';

Network and surface area

Bind MySQL to private interfaces. No public port 3306 on the internet. Firewall between app tier and database. SQL injection in the app still runs with the app user’s privileges – prepared statements everywhere (see the intermediate PHP tutorial) limit damage but least privilege limits blast radius.

The next tutorial covers observability – slow query log and metrics that actually help.