Least-Privilege Account Design

Database Security

Chapter 3 · Least-Privilege Account Design

Chapter 2 introduced GRANT/REVOKE and the concept of database roles. This chapter goes deep on actually designing those grants — the discipline of giving every account exactly the privilege it needs, and not one grant more.

The Least-Privilege Principle

Least privilege means every account — human or service — gets the minimum set of permissions required to do its actual job, nothing broader "just in case" or "to save time." It's the same principle this site's Laravel and Django courses applied to mass-assignment allowlists, and the Auth course applied to role design — here it's applied directly to the database's own grant system.

One Account Per Service, Not One Account Per "The App"

Chapter 2's reporting_job example already hinted at this: a real system is rarely just "the app" as one monolithic thing. A web application, a background job processor, a reporting/analytics job, and an admin panel are genuinely different services with genuinely different needs — each should get its own database account, scoped to exactly what that service does.

Service	What It Actually Needs
Web application	`SELECT`, `INSERT`, `UPDATE`, `DELETE` on its own tables
Background job processor	`SELECT`/`UPDATE` on a job queue table only
Reporting/analytics job	`SELECT` only, across whatever it reports on
Admin panel	broader access, but still scoped — not automatically the database's own root/superuser account

Granular GRANT/REVOKE

Privilege doesn't have to be all-or-nothing at the database level — most database engines support granting down to the table or even column level. A service that only needs a user's display name and avatar has no legitimate reason to be able to SELECT a password hash column, even on the very same table.

-- column-level grant: only the columns this service actually needs
GRANT SELECT (id, display_name, avatar_url) ON users TO 'profile_widget'@'%';

-- narrowing an over-broad grant discovered during a review
REVOKE ALL PRIVILEGES ON shop.* FROM 'legacy_service'@'%';
GRANT SELECT ON shop.orders TO 'legacy_service'@'%';

Read-Only vs. Read-Write Roles

Rather than hand-crafting an ad-hoc grant list for every single account, defining explicit roles — a named, reusable bundle of privileges — keeps grants consistent and reviewable.

CREATE ROLE read_only;
GRANT SELECT ON shop.* TO read_only;

CREATE ROLE read_write;
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO read_write;

-- assigning a role to an account, rather than granting privileges one at a time
GRANT read_only TO 'reporting_job'@'%';

The "Superuser for Everything" Anti-Pattern

"Just grant it ALL PRIVILEGES, it's easier" is the single most common way least privilege gets abandoned in practice — and it turns every account into an equally attractive, equally catastrophic target.

	Superuser-Equivalent App Account	Properly Scoped Account
If this account is compromised	attacker can read/modify/drop any table, in any database on the server	attacker is limited to exactly what this account was ever granted
Can it create new accounts?	often yes — potentially creating a persistent backdoor	no — account management is a separate, narrower privilege
Can it disable auditing (Ch.7)?	often yes	no

This is the exact same principle the MongoDB course's mongodb2-7 chapter drew for MongoDB's own root versus scoped readWrite roles — least privilege isn't a MySQL-specific or Postgres-specific idea, it's a property every database engine's own account system is built to support, and every engine's documentation strongly recommends applying.

Least privilege is defense in depth against every other course's vulnerabilities too

If a SQL injection bug were ever to recur despite the SQLi course's defenses (Chapter 7 there, parameterized queries), the damage it can do is directly bounded by whatever privileges the compromised application's own database account happens to hold. A properly scoped account limits even a successful SQLi exploit to that account's own narrow grants; a superuser-equivalent account turns the exact same bug into a total database compromise. Least privilege doesn't prevent an application-layer bug from existing — it limits how much that bug can cost when one eventually does.

Grants accumulate — audit them periodically

A grant added "temporarily" to unblock a debugging session has a way of never being revoked once the immediate problem is solved. Over months or years, an account's actual privileges tend to drift wider than what it currently needs, even when it started out correctly scoped. Chapter 10's hardening checklist includes a periodic grant review specifically because this drift is the normal, expected outcome of not checking — not an unusual failure.

Hands-On Exercises

Exercise 1

A single "backend" database account is currently used by a web application, a background job processor, and a nightly reporting script. Propose a split into separate accounts, stating what each one should actually be granted.

📄 View solution

Exercise 2

A "profile widget" service only ever displays a user's display name and avatar. Write the column-level GRANT that gives it exactly that access on a users table that also contains an email and a password_hash column.

📄 View solution

Exercise 3

Explain, using this chapter's SQL-injection cross-reference, why least-privilege account design counts as "defense in depth" rather than a replacement for parameterized queries. What specifically does it limit, and what does it not prevent?

📄 View solution

Chapter 3 Quick Reference

Least privilege = every account gets the minimum privilege needed for its actual job, never "just in case" extra
One account per service, not one account for "the app" as a whole — web app, job processor, reporting, and admin panel each get their own scoped account
Grants can be table- or column-level, not just database-wide — restrict sensitive columns (password hashes) even from accounts that need the rest of the table
Roles (CREATE ROLE) bundle reusable privilege sets, keeping grants consistent and reviewable across many accounts
The "superuser for everything" anti-pattern turns every account into an equally catastrophic single point of failure
Least privilege is defense in depth — it bounds the damage of a recurring SQLi or any other application bug, it doesn't prevent the bug itself
Grants drift wider over time — periodic review belongs on the Chapter 10 hardening checklist, not left to chance
Next chapter: Network Security for Databases — binding to private networks, firewall rules, never exposing a DB port to the public internet