Auditing & Logging

Database Security

Chapter 7 · Auditing & Logging

Chapter 2 argued that individual accounts are worthless for accountability without something actually recording what each one does. This chapter is that missing piece — and the database-specific instance of a category the OWASP course already named: A09 Security Logging & Monitoring Failures (owasp1-9).

Why Auditing Depends on Chapter 2's Foundation

Auditing only works if it's built on individual, non-shared accounts (Chapter 2) — a log that says "admin ran this query" for five different developers tells you nothing useful at all. This chapter assumes that foundation is already in place and builds the actual logging and detection on top of it.

Query Logging

Most database engines can log every query, along with which account ran it and when — MySQL's general query log, PostgreSQL's log_statement, MongoDB's auditLog. Logging everything is expensive in both storage and performance overhead, which is why most real deployments scope logging deliberately rather than capturing every single statement.

-- PostgreSQL: log every data-modifying statement (not every SELECT)
ALTER SYSTEM SET log_statement = 'mod';

-- MySQL: enabling the general query log (expensive — scope carefully)
SET GLOBAL general_log = 'ON';

Audit Trails for Sensitive Table Access

Rather than logging every query against every table, a more sustainable approach specifically tracks access to sensitive tables — a customers or payments table, for instance — using dedicated audit tooling (MySQL Enterprise Audit, PostgreSQL's pgAudit extension) or triggers that record who read or modified which rows.

	Logging Everything	Targeted Audit Logging
Storage & performance cost	high — every query, every table	manageable — scoped to what actually matters
Signal-to-noise ratio	low — buried in routine traffic	high — sensitive-table access stands out
Sustainable long-term?	rarely — usually disabled again once it slows things down	yes

Detecting Anomalous Access Patterns

Logs that just accumulate and are never reviewed provide little real protection — the value is in noticing when something looks wrong, ideally close to when it happens. A few concrete anomaly signals worth watching for:

A read-only account (Chapter 3's reporting_job) suddenly issuing an UPDATE or DELETE — either a bug, or a sign that account's credentials are being misused.
A sudden spike in query volume from one account, especially outside its normal usage hours.
An account reading far more rows than its typical pattern — a common signature of data exfiltration in progress.

This is A09, applied to the database specifically

owasp1-9 already covered insufficient logging, tampered logs, and the absence of real-time monitoring/alerting at the application layer. Everything in that chapter applies here too, just aimed at the database's own activity instead of the app's — the database is simply another system that needs logs which are actually complete, actually protected, and actually watched.

An attacker with database access may try to erase the evidence first

If an attacker gains genuine database access — through a compromised account, a recurring SQLi bug, or any other path this course has covered — one of their first moves is often covering their tracks: deleting or altering the very audit log meant to catch them, exactly the "tampered logs" failure owasp1-9 warned about. Audit records need to be shipped to a separate, append-only or write-once system that a compromised database account cannot itself modify — logging to a table inside the same database being audited means the audit trail is only as trustworthy as the database it's supposed to be watching.

Hands-On Exercises

Exercise 1

Explain why Chapter 2's individual-accounts requirement is a prerequisite for auditing to be meaningful. What specifically does a shared account's audit log fail to tell you?

📄 View solution

Exercise 2

A read-only reporting account (per Chapter 3's least-privilege design) suddenly issues a DELETE statement. Even though the account's own database-level privileges should prevent it from succeeding, explain why this event is still worth alerting on immediately.

📄 View solution

Exercise 3

Explain why storing audit logs inside the same database they're auditing is a weaker design than shipping them to a separate, append-only system. What specific attack does this protect against?

📄 View solution

Chapter 7 Quick Reference

Auditing is only meaningful on top of Chapter 2's individual accounts — a shared account's log can't identify who actually did something
Query logging (general/audit logs) records every statement, by account, but has real storage/performance overhead — scope it deliberately
Targeted audit trails on specifically sensitive tables (customers, payments) are more sustainable than logging everything
Anomalous access patterns to watch for: a read-only account attempting writes, a query-volume spike, an account reading unusually many rows
This chapter is A09 (OWASP) applied specifically to the database — owasp1-9's insufficient-logging/no-monitoring failures apply here directly
Audit logs must be tamper-resistant — shipped to a separate, append-only system a compromised DB account can't itself alter or delete
Next chapter: Backup Security — encrypting backups, secure storage/access, "your backups are also an attack target"