SWE-3004: Security Audit Trail Schema Weakness

Description

Schemas that lack proper audit trail tables or security event logging structures, making it impossible to detect breaches, investigate incidents, or meet compliance requirements. This includes missing tables for tracking access attempts, privilege changes, data modifications, or security-relevant events like patch history and vulnerability status.

Illustrative Cantor Point

The Cantor Point occurs when designing the initial schema without considering security monitoring needs. The decision to "add logging later" or to rely on application-level logging without database-backed persistence creates a divergent path where security incidents become invisible and undetectable until catastrophic breach occurs.

Categories: CP-Schema, CP-API, CP-Process

Real-World Examples / Observed In

SingHealth (2018): Lack of proper audit logging allowed attackers to operate undetected for months, with no visibility into abnormal access patterns or privilege escalations [See: Cases-By-Year/2018 Data Integrity Failures.md#6]
Banking System: Missing audit trail for admin actions allowed rogue employee to modify records undetected
Healthcare Provider: Unable to prove HIPAA compliance due to missing access logs

Common Consequences & Impacts

Technical Impacts

- Inability to detect ongoing attacks
- No forensic trail for investigations
- Cannot identify access patterns
- Blind to privilege abuse

Human/Ethical Impacts

- Patient data exposed without trace
- Financial fraud undetected
- Privacy violations invisible
- Accountability impossible

Business Impacts

- Regulatory non-compliance
- Failed audits
- Legal liability
- Inability to prosecute attackers

Recovery Difficulty & Escalation

Recovery Difficulty:

4.5

Escalation Risk:

9.5

ADI Principles & Axioms Violated

Principle of Observable Integrity: Systems must be observable to be secure
Principle of Trust Insufficiency: Cannot verify without audit trails

Detection / 60-Second Audit

-- Check for audit-related tables
SELECT 
    CASE 
        WHEN COUNT(*) = 0 THEN 'CRITICAL: No audit tables found'
        ELSE 'Found ' || COUNT(*) || ' potential audit tables'
    END as audit_status,
    string_agg(table_name, ', ') as audit_tables
FROM information_schema.tables
WHERE table_schema = 'public'
AND (
    table_name LIKE '%audit%' 
    OR table_name LIKE '%log%'
    OR table_name LIKE '%history%'
    OR table_name LIKE '%event%'
);

-- Check for security tracking columns
SELECT 
    t.table_name,
    CASE
        WHEN COUNT(CASE WHEN c.column_name IN ('created_by', 'updated_by') THEN 1 END) = 0 
            THEN 'Missing user tracking'
        WHEN COUNT(CASE WHEN c.column_name IN ('created_at', 'updated_at') THEN 1 END) = 0 
            THEN 'Missing timestamps'
        ELSE 'Has tracking'
    END as status
FROM pg_tables t
LEFT JOIN information_schema.columns c 
    ON t.tablename = c.table_name
WHERE t.schemaname = 'public'
GROUP BY t.table_name
HAVING COUNT(CASE WHEN c.column_name IN ('created_by', 'updated_by', 'created_at', 'updated_at') THEN 1 END) < 4;

-- Check for trigger-based auditing
SELECT 
    tablename,
    COUNT(tgname) as audit_triggers
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
JOIN pg_tables pt ON c.relname = pt.tablename
WHERE tgname LIKE '%audit%'
GROUP BY tablename;

-- Check for audit-related tables
SELECT 
    CASE 
        WHEN COUNT(*) = 0 THEN 'CRITICAL: No audit tables found'
        ELSE CONCAT('Found ', COUNT(*), ' potential audit tables')
    END as audit_status,
    GROUP_CONCAT(table_name) as audit_tables
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND (
    table_name LIKE '%audit%' 
    OR table_name LIKE '%log%'
    OR table_name LIKE '%history%'
    OR table_name LIKE '%event%'
);

-- Check for security tracking columns
SELECT 
    t.table_name,
    CASE
        WHEN SUM(CASE WHEN c.column_name IN ('created_by', 'updated_by') THEN 1 ELSE 0 END) = 0
            THEN 'Missing user tracking'
        WHEN SUM(CASE WHEN c.column_name IN ('created_at', 'updated_at') THEN 1 ELSE 0 END) = 0
            THEN 'Missing timestamps'
        ELSE 'Has tracking'
    END as tracking_status
FROM information_schema.tables t
LEFT JOIN information_schema.columns c 
    ON t.table_name = c.table_name AND t.table_schema = c.table_schema
WHERE t.table_schema = DATABASE()
AND t.table_type = 'BASE TABLE'
GROUP BY t.table_name;

-- Check for triggers (audit mechanism)
SELECT 
    event_object_table,
    COUNT(*) as trigger_count,
    GROUP_CONCAT(trigger_name) as triggers
FROM information_schema.triggers
WHERE trigger_schema = DATABASE()
AND trigger_name LIKE '%audit%'
GROUP BY event_object_table;

-- Check for audit-related tables
SELECT 
    CASE 
        WHEN COUNT(*) = 0 THEN 'CRITICAL: No audit tables found'
        ELSE CONCAT('Found ', COUNT(*), ' potential audit tables')
    END as audit_status,
    STRING_AGG(name, ', ') as audit_tables
FROM sys.tables
WHERE (
    name LIKE '%audit%' 
    OR name LIKE '%log%'
    OR name LIKE '%history%'
    OR name LIKE '%event%'
);

-- Check for security tracking columns
SELECT 
    t.name as table_name,
    CASE
        WHEN SUM(CASE WHEN c.name IN ('created_by', 'updated_by') THEN 1 ELSE 0 END) = 0
            THEN 'Missing user tracking'
        WHEN SUM(CASE WHEN c.name IN ('created_at', 'updated_at') THEN 1 ELSE 0 END) = 0
            THEN 'Missing timestamps'
        ELSE 'Has tracking'
    END as tracking_status
FROM sys.tables t
LEFT JOIN sys.columns c ON t.object_id = c.object_id
GROUP BY t.name
HAVING SUM(CASE WHEN c.name IN ('created_by', 'updated_by', 'created_at', 'updated_at') THEN 1 ELSE 0 END) < 4;

-- Check for SQL Server Audit specifications
SELECT 
    a.name as audit_name,
    a.type_desc,
    s.name as specification_name,
    s.is_state_enabled
FROM sys.server_audits a
LEFT JOIN sys.server_audit_specifications s ON a.audit_guid = s.audit_guid;

Prevention & Mitigation Best Practices

Implement Comprehensive Audit Tables:

-- Security event log
CREATE TABLE security_audit_log (
    id BIGSERIAL PRIMARY KEY,
    event_time TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    event_type VARCHAR(50) NOT NULL,
    user_id BIGINT,
    ip_address INET,
    user_agent TEXT,
    resource_type VARCHAR(100),
    resource_id VARCHAR(100),
    action VARCHAR(50),
    result VARCHAR(20),
    details JSONB,
    INDEX idx_event_time (event_time),
    INDEX idx_user_id (user_id),
    INDEX idx_event_type (event_type)
);

-- Data access log
CREATE TABLE data_access_log (
    id BIGSERIAL PRIMARY KEY,
    accessed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    user_id BIGINT NOT NULL,
    table_name VARCHAR(100) NOT NULL,
    record_ids TEXT[],
    operation VARCHAR(20),
    query_hash VARCHAR(64),
    execution_time_ms INTEGER,
    INDEX idx_accessed_at (accessed_at),
    INDEX idx_user_table (user_id, table_name)
);

-- Patch management
CREATE TABLE security_patches (
    id SERIAL PRIMARY KEY,
    cve_id VARCHAR(20),
    severity VARCHAR(20),
    component VARCHAR(100),
    discovered_date DATE,
    patch_available_date DATE,
    applied_date DATE,
    applied_by VARCHAR(100),
    verification_status VARCHAR(50),
    notes TEXT
);

Add Triggers for Automatic Logging:

CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $
BEGIN
    INSERT INTO security_audit_log (
        event_type, user_id, resource_type, 
        resource_id, action, details
    ) VALUES (
        TG_TABLE_NAME || '_' || TG_OP,
        current_setting('app.current_user_id')::BIGINT,
        TG_TABLE_NAME,
        CASE TG_OP
            WHEN 'DELETE' THEN OLD.id::TEXT
            ELSE NEW.id::TEXT
        END,
        TG_OP,
        jsonb_build_object(
            'old', to_jsonb(OLD),
            'new', to_jsonb(NEW)
        )
    );
    RETURN COALESCE(NEW, OLD);
END;
$ LANGUAGE plpgsql;

Implement Anomaly Detection Views:

CREATE VIEW suspicious_access_patterns AS
SELECT 
    user_id,
    COUNT(*) as access_count,
    COUNT(DISTINCT table_name) as tables_accessed,
    array_agg(DISTINCT ip_address) as ip_addresses
FROM data_access_log
WHERE accessed_at > NOW() - INTERVAL '1 hour'
GROUP BY user_id
HAVING COUNT(*) > 1000  -- Unusual volume
    OR COUNT(DISTINCT table_name) > 20;  -- Accessing many tables

Regular Audit Reports: Automated compliance reports
Immutable Audit Logs: Write-once, append-only
Encrypted Storage: Protect audit data
Retention Policies: Keep logs per compliance requirements

Real World Examples

• Medical records database with no access logging
• Attacker accessed 1.5M patient records over 8 months
• No way to identify which records were compromised
• No forensic trail to understand attack methods
• HIPAA violation: $4.3M fine plus class-action lawsuits
• Root cause: "We'll add logging in phase 2" - phase 2 never came

-- Original schema - no accountability
CREATE TABLE transactions (
    id BIGINT PRIMARY KEY,
    account_id BIGINT,
    amount DECIMAL(15,2),
    transaction_date DATE
);

-- Admin could modify transactions without trace:
-- UPDATE transactions SET amount = amount * 0.9 WHERE id = 12345;
-- No record of who, when, or why

-- Core audit infrastructure
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    event_timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    event_type VARCHAR(50) NOT NULL,
    table_name VARCHAR(100),
    record_id BIGINT,
    user_id BIGINT NOT NULL,
    session_id UUID,
    ip_address INET,
    user_agent TEXT,
    old_values JSONB,
    new_values JSONB,
    query_text TEXT,
    execution_time_ms INTEGER
);

-- Partitioned by month for performance
CREATE TABLE audit_log_2024_01 PARTITION OF audit_log
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Indexes for common queries
CREATE INDEX idx_audit_timestamp ON audit_log(event_timestamp);
CREATE INDEX idx_audit_user ON audit_log(user_id, event_timestamp);
CREATE INDEX idx_audit_table_record ON audit_log(table_name, record_id);

-- Trigger function for automatic auditing
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $
BEGIN
    INSERT INTO audit_log (
        event_type,
        table_name,
        record_id,
        user_id,
        session_id,
        ip_address,
        old_values,
        new_values
    ) VALUES (
        TG_OP,
        TG_TABLE_NAME,
        COALESCE(NEW.id, OLD.id),
        current_setting('app.user_id')::BIGINT,
        current_setting('app.session_id')::UUID,
        inet_client_addr(),
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END
    );
    RETURN COALESCE(NEW, OLD);
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

-- Apply to sensitive tables
CREATE TRIGGER transactions_audit
    AFTER INSERT OR UPDATE OR DELETE ON transactions
    FOR EACH ROW EXECUTE FUNCTION audit_trigger();

-- Access pattern monitoring
CREATE MATERIALIZED VIEW suspicious_access AS
SELECT 
    user_id,
    date_trunc('hour', event_timestamp) as hour,
    COUNT(*) as access_count,
    COUNT(DISTINCT table_name) as tables_accessed,
    array_agg(DISTINCT ip_address) as ips_used
FROM audit_log
WHERE event_timestamp > CURRENT_TIMESTAMP - INTERVAL '24 hours'
GROUP BY user_id, date_trunc('hour', event_timestamp)
HAVING COUNT(*) > 1000 OR COUNT(DISTINCT ip_address) > 3;

-- Compliance reporting view
CREATE VIEW compliance_audit_report AS
SELECT 
    date_trunc('day', event_timestamp) as audit_date,
    table_name,
    event_type,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_id) as unique_users
FROM audit_log
WHERE table_name IN ('patient_records', 'transactions', 'user_permissions')
GROUP BY date_trunc('day', event_timestamp), table_name, event_type
ORDER BY audit_date DESC, table_name;

AI Coding Guidance/Prompt

Prompt: "When implementing security audit schemas:"
Rules:
  - Require audit tables for any system with sensitive data
  - Flag schemas without user tracking columns
  - Suggest triggers for automatic audit logging
  - Require timestamp columns with timezone
  - Warn about missing access pattern monitoring
  
Example:
  # Bad: No audit trail
  CREATE TABLE sensitive_medical_records (
    id BIGINT PRIMARY KEY,
    patient_id BIGINT,
    diagnosis TEXT,
    treatment TEXT
  );
  
  # Good: Comprehensive audit trail
  CREATE TABLE sensitive_medical_records (
    id BIGINT PRIMARY KEY,
    patient_id BIGINT NOT NULL,
    diagnosis TEXT,
    treatment TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by BIGINT NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE,
    updated_by BIGINT,
    version INTEGER DEFAULT 1
  );
  
  -- Audit trigger for all changes
  CREATE TRIGGER medical_records_audit
    AFTER INSERT OR UPDATE OR DELETE ON sensitive_medical_records
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();
  
  -- Access logging view
  CREATE VIEW medical_record_access_audit AS
  SELECT 
      l.accessed_at,
      u.username,
      l.operation,
      l.record_ids,
      l.ip_address
  FROM data_access_log l
  JOIN users u ON l.user_id = u.id
  WHERE l.table_name = 'sensitive_medical_records'
  ORDER BY l.accessed_at DESC;

Relevant Keywords

security audit trail schema weakness Symptoms: slow queries, data inconsistency, constraint violations Preventive: schema validation, constraint enforcement, proper typing Tech stack: PostgreSQL, MySQL, SQL Server, Oracle Industry: all industries, enterprise, SaaS