✏️ Explanatory Question
Level: Hard — A core security responsibility; over-privileged accounts are one of the biggest real-world breach vectors.
Scenario: A security audit finds that your web application connects to MySQL using the root account. If the app is compromised via SQL injection, the attacker gets full control of every database. How do you design proper, minimal database access, and what does "least privilege" mean in practice?
Least privilege means every account gets only the permissions it actually needs to do its job — nothing more. A web app that only reads and writes application data should never have DROP, GRANT, or access to other databases.
Why using root for apps is dangerous: If the app account has full privileges, a single SQL injection or leaked credential lets an attacker read all data, drop tables, or create new admin users. Scoping the account limits the "blast radius" of any compromise.
| Level | Scope | Example |
|---|---|---|
| Global | All databases | ON *.* |
| Database | One database | ON shop.* |
| Table | One table | ON shop.orders |
| Column | Specific columns | SELECT (name, email) |
-- Create a dedicated app user (not root!)
CREATE USER 'app_user'@'10.0.%' IDENTIFIED BY 'Str0ng!Passw0rd';
-- '10.0.%' restricts logins to your app subnet, not from anywhere
-- Grant ONLY the data operations the app needs, on ONLY its database
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_user'@'10.0.%';
-- NOT granted: DROP, ALTER, CREATE, GRANT, or access to other schemas
-- Apply the changes
FLUSH PRIVILEGES;-- Read-only user for analytics/reporting (cannot modify anything)
CREATE USER 'report_user'@'%' IDENTIFIED BY 'pass2';
GRANT SELECT ON shop.* TO 'report_user'@'%';
-- Migration/deploy user that CAN change schema (used only in deploys)
CREATE USER 'deploy_user'@'10.0.0.5' IDENTIFIED BY 'pass3';
GRANT ALL PRIVILEGES ON shop.* TO 'deploy_user'@'10.0.0.5';
-- Column-level: support staff can see names but NOT card numbers
CREATE USER 'support'@'%' IDENTIFIED BY 'pass4';
GRANT SELECT (id, name, email) ON shop.customers TO 'support'@'%';-- See what a user can do
SHOW GRANTS FOR 'app_user'@'10.0.%';
-- Revoke a privilege that was too broad
REVOKE DELETE ON shop.* FROM 'app_user'@'10.0.%';
-- Remove a user entirely
DROP USER 'old_user'@'%'; The user@host matters as much as the password: MySQL identifies accounts by both username and host. 'app'@'localhost' and 'app'@'%' are different accounts. Restricting the host (e.g., to your app server's IP/subnet) means a stolen password is useless from anywhere else.
root for applications; create scoped users.'%', where possible.REQUIRE SSL).SHOW GRANTS regularly. Interviewer follow-up: "You have 50 microservices each needing similar-but-slightly-different permissions — managing individual grants is a nightmare. What now?" → Use roles (MySQL 8.0). Define a role once (e.g., app_readwrite), grant privileges to the role, then assign the role to many users. Managing one role beats editing 50 users individually — which is exactly the next question.