# Database Audit Logging - The Practical Guide

> A guide to audit logging in databases.

Adela | 2025-11-27 | Source: https://www.bytebase.com/blog/database-audit-logging/

---

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.