Track Everything

Audit Logging

Record every query, detect intrusions, maintain compliance!

📖 The Story: Sarah's Silent Data Theft

Sarah's company got breached. 10 million customer records stolen over 2 weeks. When investigators asked "Who accessed what?", Sarah had NO ANSWER. No audit logs = no evidence. Attacker walked free. $5M fine for "inadequate security". Could have been prevented with audit logging.

😱 The Breach Without Evidence

Timeline of the Attack:

-- Week 1 (No one knew):
Day 1: Attacker gains access via stolen credentials
Day 2: Tests access - SELECT * FROM customers LIMIT 10
Day 3: Exports 1 million records
Day 4: Exports 2 million more records
Day 5-7: Continues exporting data

-- Week 2 (Still going):
Day 8-13: Another 7 million records stolen
Day 14: Attacker sells data on dark web
Day 15: Victims start reporting fraud
Day 16: Sarah discovers the breach

Total damage: 10M records stolen! 💥
                

The Investigation:

-- Investigator: "Show me the access logs"
Sarah: "We... don't have audit logging enabled"

-- Investigator: "Who accessed the customer table?"
Sarah: "I don't know"

-- Investigator: "When did the breach start?"
Sarah: "I don't know"

-- Investigator: "What queries were run?"
Sarah: "I don't know"

-- Investigator: "From which IP address?"
Sarah: "I don't know"

No evidence = No prosecution! ❌
                

The Consequences:

💰 Regulatory Fine: $5M for inadequate security
⚖️ No Prosecution: Insufficient evidence
📰 Public Shame: "Company couldn't even tell who stole data"
👥 Customer Lawsuits: Class action for $20M
📉 Stock Crash: 60% drop
💼 Sarah Fired: "Gross negligence"

✅ What Audit Logging Would Have Shown

With Audit Logs Enabled:

-- Audit log entries (what investigators would see):

2024-01-15 03:47:23 | user:john_doe | ip:103.45.67.89 | 
SELECT * FROM customers LIMIT 10
↑ Suspicious: 3am access from unfamiliar IP

2024-01-16 04:12:15 | user:john_doe | ip:103.45.67.89 |
SELECT * FROM customers LIMIT 1000000
↑ ALARM: Massive data export!

2024-01-17 02:33:41 | user:john_doe | ip:103.45.67.89 |
SELECT ssn, credit_card FROM customers
↑ CRITICAL: Accessing sensitive PII!

-- Pattern detected:
✅ WHO: john_doe account (compromised)
✅ WHEN: Jan 15-28, 2024 (2 weeks)
✅ WHAT: Customer data exports
✅ WHERE: IP 103.45.67.89 (Russia)
✅ HOW: SELECT with huge LIMITs
                

The Better Outcome:

🔔 Alert on Day 2: SIEM caught suspicious 3am access
🛑 Stopped on Day 3: Blocked IP + disabled account
📊 Full Evidence: Every query logged
⚖️ Prosecution: Attacker identified and convicted
💰 Minimal Fine: Demonstrated "reasonable security"
😊 Sarah Promoted: "Excellent security response"

Audit logging: $0 cost, $25M+ saved! 🎉

🎯 Why Audit Logging?

The business case for tracking everything!

🔍

Detect Intrusions

Catch unauthorized access
Identify compromised accounts
Spot data exfiltration
Alert on suspicious queries
Track attack patterns

Stop breaches early!

⚖️

Legal Compliance

GDPR requires audit trails
HIPAA mandates logging
PCI-DSS requirement
SOX compliance
Prove due diligence

Avoid massive fines!

🕵️

Forensic Investigation

Reconstruct attacks
Timeline of events
Identify patient zero
Scope of breach
Evidence for prosecution

Answer "what happened?"

👥

Insider Threats

Track employee access
Detect privilege abuse
Prevent data theft
Monitor admins
Accountability

Trust but verify!

🐛

Troubleshooting

Debug application issues
Find slow queries
Track schema changes
Performance analysis
User behavior patterns

Operational visibility!

📊

Analytics

Usage patterns
Peak access times
Most queried tables
User activity trends
Capacity planning

Business intelligence!

Cost of NOT Having Audit Logs

Scenario	Without Logs	With Logs
Data Breach	No evidence, $5M fine	Full forensics, $0 fine
Insider Theft	Can't prove who, lawsuit lost	Prosecution successful
Compliance Audit	Failed, operations halted	Passed with evidence
Performance Issue	Days to find root cause	Minutes with query logs

⚙️ Setting Up Audit Logging

Enable comprehensive logging in 5 minutes!

Enable Full Query Logging

Log every CQL statement

-- Edit cassandra.yaml:
audit_logging_options:
  enabled: true
  logger:
    - class_name: BinAuditLogger
  
  # Where to store logs:
  audit_logs_dir: /var/log/cassandra/audit
  
  # Roll logs every hour:
  roll_cycle: HOURLY
  
  # Max queue size (256 MB):
  max_queue_weight: 268435456
  
  # Max total log size (16 GB):
  max_log_size: 17179869184
  
  # What to log (ALL THE THINGS!):
  included_categories: QUERY,DML,DDL,DCL,AUTH,ERROR,PREPARE
  
  # Exclude system queries (reduce noise):
  excluded_keyspaces: system,system_schema,system_traces
  
  # Exclude monitoring user (optional):
  # excluded_users: monitoring_service

-- Restart Cassandra:
sudo systemctl restart cassandra

-- Verify logs are being created:
ls -lh /var/log/cassandra/audit/
/*
-rw-r--r-- 1 cassandra cassandra  45M Jan 15 14:00 BinLog-20240115-1400.cq4
-rw-r--r-- 1 cassandra cassandra  52M Jan 15 15:00 BinLog-20240115-1500.cq4
*/
            

Configure Log Categories

Choose what to track

-- Available categories:

# QUERY: All SELECT statements
included_categories: QUERY
→ Logs: SELECT * FROM users WHERE id=123

# DML: INSERT/UPDATE/DELETE
included_categories: DML
→ Logs: INSERT INTO users VALUES (...)
→ Logs: UPDATE users SET name='John'
→ Logs: DELETE FROM users WHERE id=123

# DDL: Schema changes
included_categories: DDL
→ Logs: CREATE TABLE, ALTER TABLE, DROP TABLE

# DCL: Permission changes
included_categories: DCL
→ Logs: GRANT SELECT ON keyspace.table TO user
→ Logs: REVOKE, CREATE ROLE, ALTER ROLE

# AUTH: Login attempts
included_categories: AUTH
→ Logs: Login success/failure

# ERROR: Failed queries
included_categories: ERROR
→ Logs: Syntax errors, permission denied

# PREPARE: Prepared statements
included_categories: PREPARE
→ Logs: PREPARE statements

-- RECOMMENDED: Log everything
included_categories: QUERY,DML,DDL,DCL,AUTH,ERROR,PREPARE
            

Set Up Log Rotation

Prevent disk from filling up

-- In cassandra.yaml:

# Rotate logs based on time:
roll_cycle: HOURLY     ← New log file every hour
# Options: MINUTELY, HOURLY, DAILY

# Maximum total log size (16 GB):
max_log_size: 17179869184
# When exceeded, oldest logs deleted

-- Alternative: External log rotation (logrotate)
# /etc/logrotate.d/cassandra-audit:
/var/log/cassandra/audit/*.cq4 {
    daily
    rotate 30        # Keep 30 days
    compress
    delaycompress
    notifempty
    create 0644 cassandra cassandra
}

-- Verify rotation:
ls -lh /var/log/cassandra/audit/ | head -5
            

Disk Space Planning

Estimate audit log size:

-- Formula:
log_size_per_day = queries_per_second * 3600 * 24 * avg_query_size

-- Example (10K queries/sec, 500 bytes/query):
= 10000 * 86400 * 500 bytes
= 432 GB/day

-- For 30 days retention:
= 432 * 30 = 12.6 TB

-- Tips to reduce size:
1. Exclude system keyspaces
2. Exclude monitoring users
3. Ship logs to external SIEM
4. Compress old logs
        

📊 What Gets Logged?

Every audit log entry includes:

1. Timestamp (When)

timestamp: 2024-01-15 14:32:47.123 UTC

-- Millisecond precision!
-- Used for timeline reconstruction
                

2. User (Who)

user: "john_doe"

-- The authenticated username
-- Track which account performed action
                

3. Source IP (Where From)

source: 192.168.1.100

-- Client IP address
-- Critical for detecting unauthorized access
                

4. Operation Type (What Category)

type: QUERY      ← SELECT
type: DML        ← INSERT/UPDATE/DELETE
type: DDL        ← CREATE/ALTER/DROP
type: DCL        ← GRANT/REVOKE
type: AUTH       ← Login
                

5. Full CQL Statement (What Exactly)

operation: "SELECT email, ssn FROM customers WHERE id=12345"

-- Complete query text
-- Including all parameters
                

6. Result (Success/Failure)

status: SUCCESS
OR
status: FAILED
error: "Unauthorized: User lacks SELECT permission"

-- Track both successful and failed attempts
                

Complete Audit Log Entry Example

-- Full log entry (JSON format):
{
  "timestamp": "2024-01-15T14:32:47.123Z",
  "user": "app_user",
  "source": "192.168.1.100:45678",
  "type": "QUERY",
  "category": "DML",
  "keyspace": "production",
  "table": "customers",
  "operation": "SELECT email, phone FROM customers WHERE user_id=?",
  "parameters": ["12345"],
  "status": "SUCCESS",
  "latency_ms": 3.2
}

-- Everything you need for forensics!
    

📖 Reading Audit Logs

Tools to query and analyze logs!

Method 1: auditlogviewer (Built-in Tool)

-- View recent logs:
auditlogviewer /var/log/cassandra/audit

-- Output (human-readable):
Type: QUERY | User: app_user@192.168.1.100 | 2024-01-15 14:32:47
SELECT * FROM customers WHERE id=12345

Type: DML | User: admin@10.0.0.5 | 2024-01-15 14:33:12
UPDATE users SET status='active' WHERE id=789

-- Filter by user:
auditlogviewer /var/log/cassandra/audit --user app_user

-- Filter by time range:
auditlogviewer /var/log/cassandra/audit \
  --start-time "2024-01-15 00:00:00" \
  --end-time "2024-01-15 23:59:59"

-- Filter by operation type:
auditlogviewer /var/log/cassandra/audit --type DDL

-- Search for specific query:
auditlogviewer /var/log/cassandra/audit | grep "DELETE"
    

Method 2: Direct Log Analysis

-- Logs are stored in binary format (.cq4 files)
-- Convert to readable format:

# 1. Find today's logs:
ls -lh /var/log/cassandra/audit/*.cq4 | tail -5

# 2. View specific log file:
auditlogviewer /var/log/cassandra/audit/BinLog-20240115-1400.cq4

# 3. Export to JSON for analysis:
auditlogviewer /var/log/cassandra/audit/*.cq4 --format json > audit.json

# 4. Analyze with jq:
cat audit.json | jq '.[] | select(.user=="app_user")'

# 5. Count queries by user:
cat audit.json | jq -r '.user' | sort | uniq -c | sort -rn
    

Method 3: Real-Time Monitoring

-- Watch logs in real-time (like tail -f):
auditlogviewer /var/log/cassandra/audit --follow

-- Monitor for suspicious activity:
auditlogviewer /var/log/cassandra/audit --follow | \
  grep -i "DROP\|DELETE\|ALTER"

-- Alert on failed auth:
auditlogviewer /var/log/cassandra/audit --follow | \
  grep -i "AUTH.*FAILED" | \
  while read line; do
    echo "ALERT: Failed login - $line"
    # Send to SIEM or alerting system
  done
    

🔍 Log Analysis & Security Monitoring

Detect attacks and suspicious behavior!

Common Security Queries

1. Find Failed Login Attempts

-- Show all failed logins:
auditlogviewer /var/log/cassandra/audit | \
  grep "AUTH" | \
  grep "FAILED"

-- Count failures by IP:
auditlogviewer /var/log/cassandra/audit | \
  grep "AUTH.*FAILED" | \
  awk '{print $4}' | \
  sort | uniq -c | sort -rn

/* Output:
    25 192.168.1.50  ← Brute force attack!
     3 192.168.1.100
     1 192.168.1.200
*/
                

2. Detect Data Exfiltration

-- Find large SELECT queries (> 10K rows):
auditlogviewer /var/log/cassandra/audit | \
  grep "SELECT" | \
  grep -E "LIMIT [0-9]{5,}"

-- Example suspicious query:
SELECT * FROM customers LIMIT 1000000  ← ALARM! 🚨

-- Find queries without WHERE clause (full table scan):
auditlogviewer /var/log/cassandra/audit | \
  grep "SELECT.*FROM" | \
  grep -v "WHERE"
                

3. Track Schema Changes

-- All DDL operations:
auditlogviewer /var/log/cassandra/audit --type DDL

-- Who dropped tables?
auditlogviewer /var/log/cassandra/audit | grep "DROP TABLE"

-- Who altered schemas?
auditlogviewer /var/log/cassandra/audit | grep "ALTER"

-- Example:
2024-01-15 03:47:00 | admin | DROP TABLE customers
↑ Suspicious: 3am DROP command!
                

4. Find Unusual Access Patterns

-- Access during off-hours (midnight-6am):
auditlogviewer /var/log/cassandra/audit | \
  awk '$3 ~ /0[0-5]:'

-- Access from unexpected IPs:
# Define known app server IPs:
KNOWN_IPS="192.168.1.10|192.168.1.11|192.168.1.12"

auditlogviewer /var/log/cassandra/audit | \
  grep -Ev "$KNOWN_IPS"

-- Access to sensitive tables:
auditlogviewer /var/log/cassandra/audit | \
  grep -E "credit_cards|ssn|passwords"
                

5. Detect Privilege Abuse

-- Track GRANT operations:
auditlogviewer /var/log/cassandra/audit | grep "GRANT"

-- Who became superuser?
auditlogviewer /var/log/cassandra/audit | \
  grep "SUPERUSER = true"

-- Unexpected permission changes:
auditlogviewer /var/log/cassandra/audit | \
  grep -E "GRANT|REVOKE" | \
  grep -v "admin_user"  ← Not from admin?
                

Automated Alert Script

#!/bin/bash
# audit_monitor.sh - Run every 5 minutes via cron

LOGDIR="/var/log/cassandra/audit"
ALERT_EMAIL="security@company.com"

# 1. Check for failed logins (> 5 in last hour)
FAILURES=$(auditlogviewer $LOGDIR --last 1h | \
           grep "AUTH.*FAILED" | wc -l)

if [ $FAILURES -gt 5 ]; then
  echo "ALERT: $FAILURES failed logins in last hour" | \
    mail -s "Security Alert: Failed Logins" $ALERT_EMAIL
fi

# 2. Check for large data exports
EXPORTS=$(auditlogviewer $LOGDIR --last 1h | \
          grep -E "LIMIT [0-9]{5,}" | wc -l)

if [ $EXPORTS -gt 0 ]; then
  echo "ALERT: Large data export detected" | \
    mail -s "Security Alert: Data Exfiltration?" $ALERT_EMAIL
fi

# 3. Check for DROP commands
DROPS=$(auditlogviewer $LOGDIR --last 1h | \
        grep "DROP" | wc -l)

if [ $DROPS -gt 0 ]; then
  echo "ALERT: DROP command executed" | \
    mail -s "Security Alert: Schema Change" $ALERT_EMAIL
fi

# 4. Check for off-hours access (midnight-6am)
HOUR=$(date +%H)
if [ $HOUR -lt 6 ]; then
  ACTIVITY=$(auditlogviewer $LOGDIR --last 1h | wc -l)
  if [ $ACTIVITY -gt 100 ]; then
    echo "ALERT: Unusual activity at $HOUR:00" | \
      mail -s "Security Alert: Off-Hours Access" $ALERT_EMAIL
  fi
fi
    

⚖️ Compliance Requirements

Meet regulatory standards!

🇪🇺

GDPR

Article 32: Security audit logs
Track personal data access
Log data exports
Record deletion requests
Retain logs 6+ months

Or €20M fine!

🏥

HIPAA

§164.312: Audit controls
Track PHI access
Log all queries
6 year retention
Regular audits

Or $1.5M penalty!

💳

PCI-DSS

Requirement 10: Track access
Log cardholder data
Daily log review
1 year retention
Tamper-proof logs

Or lose cert!

📊

SOX

Section 404: Internal controls
Track financial data
Audit trail required
7 year retention
Annual audits

Or criminal charges!

Compliance Checklist

Requirement	How to Achieve	Verify
Log all access	included_categories: ALL	auditlogviewer shows entries
Retain logs	max_log_size: adequate	Logs exist for required period
Tamper-proof	Read-only filesystem, SIEM export	chmod 400 on log files
Regular review	Automated monitoring scripts	Weekly security reports
Access tracking	Log user + IP + timestamp	Full audit trail available

💡 Audit Logging Best Practices

Do it right!

✅

DO

Enable on ALL nodes
Log ALL categories
Export to SIEM
Automate monitoring
Test log rotation
Secure log storage
Regular audits
Alert on anomalies

❌

DON'T

Skip audit logs
Exclude important categories
Let logs fill disk
Forget to monitor
Make logs writable
Delete too soon
Ignore alerts
Manual-only review

Log Retention Guidelines

Industry	Minimum Retention	Recommended
General	30 days	90 days
Financial (SOX)	7 years	7 years
Healthcare (HIPAA)	6 years	6 years
E-commerce (PCI-DSS)	1 year	2 years
EU (GDPR)	6 months	1 year

Performance Impact

Audit logging has minimal overhead:

📊 Latency: +0.5-2ms per query (negligible)
💾 Storage: ~500 bytes per query
⚡ Throughput: <1% impact
💻 CPU: <5% additional

The benefit far outweighs the cost!

🎉 Master Audit Logging!

You now know how to track everything in Cassandra!

🎓 What You Learned:

📖 Why audit: Detect breaches, compliance, forensics
⚙️ Setup: Enable in cassandra.yaml (5 minutes!)
📊 What's logged: User, IP, timestamp, query, result
📖 Reading logs: auditlogviewer + analysis scripts
🔍 Security monitoring: Detect attacks, data theft
⚖️ Compliance: GDPR, HIPAA, PCI-DSS, SOX
💡 Best practices: Export to SIEM, automate alerts

💡 Key Takeaways:

Enable immediately - Don't wait for a breach!
Log everything - Query, DML, DDL, DCL, AUTH
Monitor actively - Automated alerts on suspicious activity
Retain properly - Meet compliance requirements
Analyze regularly - Weekly security reviews
Export to SIEM - Centralized security monitoring

📋 Quick Setup (5 Minutes):

# 1. Edit cassandra.yaml
audit_logging_options:
  enabled: true
  logger:
    - class_name: BinAuditLogger
  audit_logs_dir: /var/log/cassandra/audit
  included_categories: QUERY,DML,DDL,DCL,AUTH,ERROR

# 2. Restart Cassandra
sudo systemctl restart cassandra

# 3. Verify logs
ls -lh /var/log/cassandra/audit/

# 4. View logs
auditlogviewer /var/log/cassandra/audit

# DONE! Now tracking everything! ✅
        

🚨 Security Alerts to Implement:

🔴 Failed logins (> 5 per hour)
🔴 Large data exports (LIMIT > 10K)
🔴 DROP/ALTER commands
🟡 Off-hours access (midnight-6am)
🟡 Access from unknown IPs
🟡 Sensitive table queries

📋 Remember Sarah: No logs = No evidence = $5M fine!
Audit logging = Your security insurance policy! 🛡️

📱 Responsive Ad 📱