Database audit logging is now a core security expectation, with standards like SOC 2, ISO 27001, GDPR, HIPAA, and PCI DSS requiring a complete record of who accessed what, when, and from where.
Yet building a consistent audit trail across different database engines is still challenging. This article explains why, what "good" looks like, and how to design a reliable auditing strategy.
Why Audit Logging Matters
Audit logging provides the answers to the most critical operational and security questions:
- Who accessed the data?
- What did they query or modify?
- When did it occur?
- From where did the access originate?
This information is essential for:
- Detecting unauthorized access
- Investigating security incidents
- Meeting compliance requirements
- Understanding schema and data evolution
- Establishing accountability across engineering teams
Without reliable audit logs, organizations lack visibility at the exact moment it matters most.
The Real-World Pain Today (Across All Major Databases)
All major relational databases — MySQL, PostgreSQL, SQL Server, Oracle and cloud-managed variants like AWS RDS, Google Cloud SQL, and Azure Database — provide audit capabilities. However, how they provide these capabilities varies dramatically, and implementing them correctly requires deep expertise.
Here are common issues teams encounter:
MySQL (Community Edition) — Example
MySQL CE’s general and slow logs are all-or-nothing and extremely noisy. Selective auditing (especially for non-root users) requires additional plugins that introduce configuration complexity and variability across environments.
PostgreSQL — Example
PostgreSQL relies on extensions such as pgaudit for structured auditing.
While powerful, these extensions require careful tuning to avoid overwhelming log volume while still capturing all critical operations — including SELECTs.
Cloud Databases (AWS RDS, Google Cloud SQL, Azure Database) — Example
Cloud platforms wrap underlying engine audit logs into provider-specific formats. Teams often struggle with:
- inconsistent event types
- missing or partial SQL text
- difficulty correlating logs across mixed engines or environments
In short:
Audit information exists everywhere — but it’s fragmented, inconsistent, and often incomplete.
What a Good Audit Log Should Capture
A reliable audit log must capture every database action, not just modifications. In modern security models, access is just as important — and often more important — than change.
A robust audit log includes:
-
Real human identity No shared admin or application accounts. Every query must map to an actual person.
-
Full query text, including:
- DDL (all schema changes)
- DML (INSERT, UPDATE, DELETE)
- SELECT (all read operations — because viewing sensitive data is a high-risk event)
-
Authentication events Both successful logins and failed login attempts.
-
Permission changes The audit log must record any permissions granted or revoked for specific users.
-
Execution outcome Whether the operation succeeded, failed, or was rejected.
-
Optional contextual metadata Such as ticket/issue ID, environment, deployment reference, or any policy configurations or changes.
A complete record of SELECT queries ensures you always know exactly who viewed what data, which is a mandatory capability under many security and privacy frameworks.
Approaches to Audit Logging
Teams typically rely on one or more of the following auditing methods:
1. Engine-native auditing
Each database engine includes its own audit features.
Pros:
- High fidelity
- Deeply integrated with database internals
Cons:
- Different for every engine
- Easily becomes noisy without tuning
- Harder to unify across environments
2. Cloud provider audit logs
Cloud platforms provide audit streams for their managed databases.
Pros:
- Easy to enable
- Centralized in cloud logging services
- Integrated with monitoring tools
Cons:
- Inconsistent formats and event coverage
- SQL text may be missing
- Hard to correlate across multi-cloud or multi-engine stacks
3. Proxy / workflow-based auditing
SQL is routed through a centralized gateway or workflow before executing.
Pros:
- Unified audit trail across all engines
- Automatically tied to real human identity
- Can embed metadata (ticket ID, environment)
- Ensures DDL, DML, and SELECT are always captured
Cons:
- Requires routing SQL through a central component
For example: A workflow platform like Bytebase produces complete, contextual audit logs because all SQL flows through a single, identity-aware pipeline.
Recommended Best Practices
Regardless of database engine or auditing method, strong audit practices share the same foundations:
- Use individual identities — never share DB accounts.
- Record all DDL, DML, and SELECT — access visibility is non-negotiable.
- Store logs off-host — prevents tampering or accidental deletion.
- Apply retention policies (90, 180, or 365+ days).
- Integrate logs into a SIEM for alerting and correlation (Datadog, Splunk, CloudWatch, Grafana).
- Treat default engine settings cautiously — they often require substantial tuning.
A minimal-noise, high-fidelity audit log is better than a noisy one that nobody can use.
