What is the Principle of Least Privilege for Database Access?
Understand least-privilege database access: scoped roles, narrow grants, and why it limits damage from leaked credentials.
Expected Interview Answer
Least privilege for database access means every user, service, or application account is granted only the specific permissions it needs to do its job — nothing more — so a compromised or buggy credential can only touch a narrowly scoped slice of the database instead of everything in it.
In practice this means creating dedicated roles per function (a reporting role gets read-only SELECT on specific views, an application role gets INSERT/UPDATE only on the tables it writes, and no role is casually granted superuser or DBA rights) rather than one shared admin account reused everywhere. Permissions should be granted at the narrowest scope that works — specific tables or columns rather than the whole schema — and reviewed periodically to revoke access that is no longer needed as roles change. This limits the blast radius of a SQL injection bug, a leaked credential, or a compromised service: the attacker inherits only what that specific role was allowed to do.
- Limits the blast radius of a leaked or compromised credential
- Reduces the damage a SQL injection bug can cause
- Makes audits simpler because each role has a clear, narrow purpose
- Prevents accidental destructive operations by overprivileged accounts
AI Mentor Explanation
Think of a cricket ground where the ticket-scanning staff can only open the turnstiles, not the trophy room, while the groundskeeper can access the pitch equipment shed but not the ticketing system. Each role gets exactly the keys needed for their job, nothing more, so a lost keycard only exposes one area. Least privilege applies this same scoped-key logic to database roles: a reporting account only reads certain views, an application account only writes to its own tables, and no account casually holds the master key to everything.
Step-by-Step Explanation
Step 1
Define roles by function
Create separate database roles for reporting, application writes, and administration rather than one shared account.
Step 2
Grant the narrowest scope
Assign SELECT/INSERT/UPDATE on specific tables or views instead of schema-wide or superuser access.
Step 3
Deny by default
Start every new role with zero privileges and add only what its job explicitly requires.
Step 4
Review and revoke periodically
Audit granted privileges regularly and revoke access that is no longer needed as roles or services change.
What Interviewer Expects
- States that access should be scoped to only what a role explicitly needs
- Gives a concrete example of role separation (e.g. read-only reporting vs application write role)
- Explains how least privilege reduces the impact of a leaked credential or SQL injection
- Mentions periodic review and revocation of stale privileges
Common Mistakes
- Using one shared superuser account for every application and service
- Granting schema-wide or database-wide privileges when a single table would do
- Never revisiting or revoking access after a role or project changes
- Assuming least privilege only matters for human users, not service accounts
Best Answer (HR Friendly)
“I make sure every database account, whether it belongs to a person or a service, only has the exact permissions it needs to do its job — nothing broader. That way, if a credential ever leaks or a service gets compromised, the damage is limited to a small, well-understood slice of the database instead of the whole system.”
Code Example
-- Reporting role: read-only, no write access anywhere
CREATE ROLE reporting_ro WITH LOGIN PASSWORD '__from_vault__';
GRANT CONNECT ON DATABASE sales_db TO reporting_ro;
GRANT SELECT ON SalesSummaryView TO reporting_ro;
-- Application role: writes only to its own tables, no DDL
CREATE ROLE order_service WITH LOGIN PASSWORD '__from_vault__';
GRANT SELECT, INSERT, UPDATE ON Orders, OrderItems TO order_service;
REVOKE ALL ON SCHEMA public FROM order_service;Follow-up Questions
- How would you audit which accounts have overly broad privileges?
- How does least privilege interact with row-level security?
- What is the difference between least privilege and role-based access control?
- How would you design privileges differently for staging versus production?
MCQ Practice
1. Least privilege in database access means?
Least privilege scopes each account narrowly to exactly what its function requires, minimizing the impact of any single compromised credential.
2. Why is a shared superuser account for every application a security risk?
A shared superuser account means any leak or bug in one application can expose or damage the entire database, not just its own data.
3. Which is a correct application of least privilege?
A reporting tool only needs to read data, so it should be scoped to SELECT-only on the relevant views, not broader write or admin rights.
Flash Cards
What is least privilege in database access? — Granting each account only the exact permissions it needs to perform its function, nothing more.
Why avoid one shared admin account? — Because a single leaked credential would then expose unrestricted access to the entire database.
How should new roles start? — With zero privileges by default, then granted only what their specific job requires.
What should happen to stale privileges? — They should be reviewed periodically and revoked once no longer needed.