Auditing — Database Security | OneNoughtOne

0/131

Database Management SystemsDatabase Security

Database Security

LevelAdvanced

Duration90 mins

TopicDatabase Security

4 / 5

Auditing

The Evidence Trail

Authentication verifies identity. Authorization enforces access control. Encryption protects confidentiality. But how do we know if these controls are working? How do we detect when something goes wrong? How do we investigate incidents after they occur? Auditing is the answer—the systematic recording and analysis of database activities that creates an unimpeachable trail of evidence. Effective auditing answers critical questions: Who accessed what data? When did they access it? What did they do with it? Were there any anomalies? Database auditing isn't just about catching bad actors—though it certainly helps. It's about demonstrating due diligence to regulators, providing forensic evidence for investigations, detecting operational anomalies before they become incidents, and understanding how your database is really being used.

What You Will Learn

By the end of this page, you will understand the objectives and scope of database auditing, configure comprehensive audit policies across major database platforms, implement centralized log collection and retention strategies, analyze audit data to detect security threats and compliance violations, and design auditing architectures that balance security needs with performance impact.

Auditing Fundamentals

Database auditing is the process of monitoring, recording, and analyzing database activities. Unlike application logging which captures what apps intend to do, database auditing captures what actually happens at the data layer—including activities that bypass the application entirely. Why Database Auditing Matters:

Auditing Objectives

•Security Detection — Identify unauthorized access attempts, privilege escalation, unusual query patterns, and data exfiltration.
•Compliance Verification — Demonstrate adherence to regulatory requirements (PCI-DSS, HIPAA, SOX, GDPR) during audits.
•Forensic Investigation — Provide detailed evidence trail for incident response, determining what was accessed and by whom.
•Operational Insight — Understand actual usage patterns, identify performance bottlenecks, and validate application behavior.
•Accountability — Create clear record of who did what, removing ability to deny actions (non-repudiation).
•Policy Enforcement — Verify that access controls, separation of duties, and data handling policies are being followed.

Categories of Audit Events: Database auditing can capture various categories of events, each serving different security and compliance objectives:

Audit Event Categories
Category	Examples	Security Value	Volume Impact
Authentication	Login success/failure, logout, connection parameters	Detect brute-force, credential stuffing, unusual access times	Low
Authorization	Permission grants, revokes, role changes	Detect privilege escalation, unauthorized changes	Low
Schema Changes (DDL)	CREATE, ALTER, DROP of tables, indexes, procedures	Detect unauthorized schema modifications, backdoors	Low-Medium
Data Modification (DML)	INSERT, UPDATE, DELETE operations	Detect data tampering, mass deletions	High
Data Access (SELECT)	Read operations on sensitive tables	Detect data exfiltration, unauthorized access	Very High
Administrative	BACKUP, RESTORE, SHUTDOWN, configuration changes	Detect unauthorized maintenance, system tampering	Low
Security Events	Failed access attempts, privilege use, policy violations	Direct security threat detection	Medium

The SELECT Audit Challenge

Auditing SELECT statements is crucial for detecting data theft but generates enormous volumes of log data—potentially millions of events per day for busy databases. This creates storage, performance, and analysis challenges. Most organizations audit SELECTS only on specific sensitive tables rather than database-wide.

Configuring Database Auditing

Each database platform provides native auditing capabilities with different configuration options, granularity, and output formats. Understanding these differences is essential for implementing effective cross-platform auditing strategies.

audit_configuration.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
-- ================================================
-- SQL Server Audit Configuration
-- ================================================
 
-- Step 1: Create Server Audit (defines where to write)
CREATE SERVER AUDIT ProductionAudit
TO FILE (
    FILEPATH = 'C:\AuditLogs\',
    MAXSIZE = 100 MB,
    MAX_ROLLOVER_FILES = 50,  -- Keep 50 files (5GB total)
    RESERVE_DISK_SPACE = ON
)
WITH (
    QUEUE_DELAY = 1000,  -- 1 second max delay (for performance)
    ON_FAILURE = CONTINUE,  -- Don't stop database if audit fails
    AUDIT_GUID = NEWID()
);
 
-- Enable the audit
ALTER SERVER AUDIT ProductionAudit WITH (STATE = ON);
 
-- Step 2: Create Server Audit Specification (server-level events)
CREATE SERVER AUDIT SPECIFICATION ServerSecurityAudit
FOR SERVER AUDIT ProductionAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),  -- Audit changes to audits themselves!
ADD (BACKUP_RESTORE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP)
WITH (STATE = ON);
 
-- Step 3: Create Database Audit Specification (database-level events)
USE ProductionDB;
GO
 
CREATE DATABASE AUDIT SPECIFICATION DatabaseDataAudit
FOR SERVER AUDIT ProductionAudit
ADD (SELECT ON SCHEMA::dbo BY public),  -- All SELECTs on dbo schema
ADD (INSERT, UPDATE, DELETE ON dbo.Customers BY public),
ADD (INSERT, UPDATE, DELETE ON dbo.Payments BY public),
ADD (EXECUTE ON SCHEMA::dbo BY public),  -- All stored procedure executions
ADD (SCHEMA_OBJECT_CHANGE_GROUP),  -- DDL changes
ADD (DATABASE_PERMISSION_CHANGE_GROUP)
WITH (STATE = ON);
 
-- Step 4: Query Audit Log
SELECT 
    event_time,
    action_id,
    succeeded,
    session_id,
    server_principal_name AS [user],
    database_name,
    object_name,
    statement
FROM sys.fn_get_audit_file('C:\AuditLogs\ProductionAudit*.sqlaudit', DEFAULT, DEFAULT)
WHERE event_time > DATEADD(hour, -24, GETDATE())
ORDER BY event_time DESC;
 
-- Audit action groups reference:
-- https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions

Audit the Auditors

Always audit changes to the audit configuration itself (AUDIT_CHANGE_GROUP in SQL Server, DDL events in PostgreSQL). An attacker's first action after gaining access is often to disable or modify auditing to cover their tracks.

Granular Audit Policies

Effective auditing requires balancing comprehensive coverage against performance impact and log volume. Granular policies enable targeted auditing of high-risk activities without overwhelming storage or analysis capabilities. Designing Audit Policies:

Audit Policy Design Guidelines
Data Classification	Audit Scope	Rationale
Public data	DDL only, no DML/SELECT	Low risk, minimize overhead
Internal data	DDL, failed access, privilege changes	Balance visibility with volume
Confidential data	All modifications (INSERT/UPDATE/DELETE)	Track data changes for integrity
Highly sensitive (PII, PCI)	All access including SELECT	Full visibility despite volume
System tables	All access by non-system accounts	Detect privilege escalation attempts

granular_auditing.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- ================================================
-- SQL Server Fine-Grained Auditing Example
-- ================================================
 
-- Scenario: E-commerce database with varied sensitivity
 
-- 1. Audit ONLY sensitive tables for SELECT
CREATE DATABASE AUDIT SPECIFICATION SensitiveDataAudit
FOR SERVER AUDIT ProductionAudit
ADD (SELECT ON dbo.Customers BY public),      -- PII
ADD (SELECT ON dbo.PaymentMethods BY public), -- PCI
ADD (SELECT ON dbo.HealthRecords BY public),  -- PHI
ADD (SELECT ON dbo.EmployeeSalaries BY public) -- Sensitive HR
WITH (STATE = ON);
 
-- 2. Audit modifications on more tables
CREATE DATABASE AUDIT SPECIFICATION DataModificationAudit
FOR SERVER AUDIT ProductionAudit
ADD (INSERT, UPDATE, DELETE ON SCHEMA::dbo BY public),
ADD (INSERT, UPDATE, DELETE ON SCHEMA::orders BY public)
WITH (STATE = ON);
 
-- 3. Use Filtered Audit (SQL Server 2016+) for specific users
CREATE DATABASE AUDIT SPECIFICATION AdminActivityAudit
FOR SERVER AUDIT ProductionAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo BY db_owner),
ADD (EXECUTE ON SCHEMA::dbo BY db_owner);
-- This audits everything done by members of db_owner role
 
-- ================================================
-- Oracle Fine-Grained Auditing (FGA)
-- ================================================
 
-- Note: Oracle syntax, not SQL Server
 
-- FGA for specific conditions (e.g., salary access over threshold)
-- BEGIN
--   DBMS_FGA.ADD_POLICY(
--     object_schema   => 'HR',
--     object_name     => 'EMPLOYEES',
--     policy_name     => 'HIGH_SALARY_ACCESS',
--     audit_condition => 'SALARY > 100000',
--     audit_column    => 'SALARY, SSN',
--     handler_schema  => 'SECURITY',
--     handler_module  => 'ALERT_SECURITY_TEAM',
--     enable          => TRUE,
--     statement_types => 'SELECT, UPDATE'
--   );
-- END;
 
-- This creates an alert when anyone accesses records
-- where salary > $100k

Trigger-Based Auditing Limitations

Trigger-based auditing can be bypassed by superusers or by disabling triggers (ALTER TABLE ... DISABLE TRIGGER). It also impacts write performance. Use triggers as a supplement to, not a replacement for, database-native auditing. Native audit logs are harder to tamper with and have predictable performance impact.

Centralized Log Management

Audit logs stored on the database server itself are vulnerable to tampering by anyone with server access—including the attackers we're trying to detect. Centralized log management addresses this by shipping logs to a separate, secure system in near-real-time. Benefits of Centralized Logging:

Why Centralize Audit Logs

•Tamper Resistance — Logs stored separately can't be modified by database administrators or server compromises.
•Consolidated Analysis — Correlate events across multiple databases to detect coordinated attacks.
•Long-term Retention — Meet compliance requirements (often 7+ years) without impacting database storage.
•Real-time Alerting — SIEM integration enables immediate notification of security events.
•Efficient Search — Specialized log platforms index and query more efficiently than database tables.

Converting Mermaid diagram...

log_shipping.yaml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
# filebeat.yml - PostgreSQL Audit Log Shipping
 
filebeat.inputs:
  # PostgreSQL log files
  - type: log
    enabled: true
    paths:
      - /var/log/postgresql/postgresql-*.log
    
    # Multi-line log handling
    multiline.pattern: '^\d{4}-\d{2}-\d{2}'
    multiline.negate: true
    multiline.match: after
    
    # Add metadata
    fields:
      log_type: postgresql
      environment: production
      database_cluster: pg-prod-01
    
    # Parse pgAudit format
    processors:
      - dissect:
          tokenizer: '%{timestamp} [%{pid}]: user=%{user},db=%{database},app=%{app},client=%{client} %{level}: AUDIT: %{audit_type},%{session_id},%{command_tag},%{class},%{command},%{object_type},%{object_name},"%{statement}"'
          field: "message"
          target_prefix: "pg"
 
output.elasticsearch:
  hosts: ["https://elasticsearch.corp.com:9200"]
  username: "filebeat"
  password: "${ES_PASSWORD}"
  ssl.certificate_authorities: ["/etc/pki/ca/ca.crt"]
  index: "database-audit-%{+yyyy.MM.dd}"
 
# Queue for reliability
queue.mem:
  events: 4096
  flush.min_events: 512
  flush.timeout: 5s
 
# Enable monitoring
monitoring:
  enabled: true
  elasticsearch:
    hosts: ["https://elasticsearch.corp.com:9200"]

Log Retention Policy

Define retention periods based on regulatory requirements: PCI-DSS requires 1 year online + archives; HIPAA requires 6 years; SOX requires 7 years. Use tiered storage (hot/warm/cold) to manage costs—recent logs in fast storage for analysis, archived logs in cheaper object storage for compliance.

Audit Data Analysis

Collecting audit data is only valuable if you analyze it effectively. Raw audit logs are overwhelming—a busy database can generate millions of events daily. Effective analysis requires both automated detection and structured review processes. Analysis Approaches:

Audit Analysis Methods

•Real-time Detection — SIEM rules trigger alerts on specific patterns (failed logins, privilege escalation, after-hours access).
•Baseline Comparison — Machine learning establishes "normal" behavior; anomalies trigger investigation.
•Periodic Review — Security team manually reviews samples of high-risk access patterns.
•Incident Investigation — Forensic analysis when a breach or suspicious activity is detected.
•Compliance Reporting — Automated reports demonstrating access controls, separation of duties, etc.

audit_analysis_queries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- ================================================
-- SQL Server Audit Analysis Queries
-- ================================================
 
-- 1. Failed Login Summary (Brute Force Detection)
SELECT 
    server_principal_name AS attempted_user,
    COUNT(*) AS failed_attempts,
    MIN(event_time) AS first_attempt,
    MAX(event_time) AS last_attempt,
    DATEDIFF(MINUTE, MIN(event_time), MAX(event_time)) AS duration_minutes
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'LGIF'  -- Login Failed
  AND event_time > DATEADD(HOUR, -24, GETDATE())
GROUP BY server_principal_name
HAVING COUNT(*) > 10
ORDER BY failed_attempts DESC;
 
-- 2. Unusual Access Times (After-Hours Detection)
SELECT 
    server_principal_name AS [user],
    database_name,
    COUNT(*) AS queries,
    DATEPART(HOUR, event_time) AS hour_of_day
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE event_time > DATEADD(DAY, -7, GETDATE())
  AND (DATEPART(HOUR, event_time) < 6 OR DATEPART(HOUR, event_time) > 22)
  AND action_id IN ('SL', 'IN', 'UP', 'DL')  -- SELECT, INSERT, UPDATE, DELETE
GROUP BY server_principal_name, database_name, DATEPART(HOUR, event_time)
ORDER BY COUNT(*) DESC;
 
-- 3. Large Data Extraction (Exfiltration Detection)
SELECT 
    server_principal_name AS [user],
    database_name,
    object_name AS table_name,
    COUNT(*) AS select_count,
    SUM(CAST(affected_rows AS BIGINT)) AS total_rows_accessed
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'SL'  -- SELECT
  AND event_time > DATEADD(HOUR, -24, GETDATE())
GROUP BY server_principal_name, database_name, object_name
HAVING SUM(CAST(affected_rows AS BIGINT)) > 100000
ORDER BY total_rows_accessed DESC;
 
-- 4. Privilege Escalation Detection
SELECT 
    event_time,
    server_principal_name AS granted_by,
    statement,
    additional_information
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id IN ('G', 'ADGR', 'DAGR')  -- Grant, Add Group, Database Add Group
  AND event_time > DATEADD(DAY, -30, GETDATE())
ORDER BY event_time DESC;
 
-- 5. Schema Changes (Backdoor Detection)
SELECT 
    event_time,
    server_principal_name AS [user],
    database_name,
    schema_name,
    object_name,
    statement
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id IN ('CR', 'AL', 'DR')  -- Create, Alter, Drop
  AND object_name NOT LIKE '#%'  -- Exclude temp objects
  AND event_time > DATEADD(DAY, -7, GETDATE())
ORDER BY event_time DESC;

Behavioral Baselines

Machine learning-based anomaly detection (available in modern SIEMs like Elastic SIEM, Splunk UBA, Microsoft Sentinel) can learn normal access patterns per user/application and alert on deviations. This catches attacks that don't match predefined rules but represent unusual behavior for that entity.

Compliance Auditing

Regulatory compliance often mandates specific auditing requirements. Understanding these requirements ensures your audit configuration satisfies auditors and avoids costly findings. Compliance-Specific Audit Requirements:

Audit Requirements by Regulation
Regulation	Required Audit Events	Retention	Review Frequency
PCI-DSS (Req 10)	All access to cardholder data, authentication, privilege changes, system events	1 year (3 months immediately accessible)	Daily log review
HIPAA (§164.312)	Access to PHI, login attempts, changes to audit controls	6 years	Regular review
SOX (PCAOB)	Access to financial data, changes to financial systems, user account changes	7 years	Quarterly at minimum
GDPR (Art 30)	Processing activities, consent changes, data access	Duration of processing	On-demand for audits
NIST 800-53 (AU)	Audit generation, content, storage, protection, retention	Defined by organization	Per security plan

PCI-DSS Section 10 Deep Dive: Payment Card Industry requirements are among the most specific. Let's map them to database audit configurations:

pci_dss_audit.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
-- ================================================
-- PCI-DSS Requirement 10 Compliance Audit Configuration
-- ================================================
 
-- 10.2.1: All individual user accesses to cardholder data
-- 10.2.2: All actions taken by any individual with admin privileges
-- 10.2.3: Access to all audit trails
-- 10.2.4: Invalid logical access attempts
-- 10.2.5: Use of ID and authentication mechanisms
-- 10.2.6: Initialization, stopping, or pausing of audit logs
-- 10.2.7: Creation and deletion of system-level objects
 
-- Server-level audit for PCI compliance
CREATE SERVER AUDIT PCIAudit
TO FILE (FILEPATH = 'C:\PCIAuditLogs\', MAXSIZE = 200MB, MAX_ROLLOVER_FILES = 100)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = SHUTDOWN);  -- Critical: fail-closed
 
CREATE SERVER AUDIT SPECIFICATION PCI_ServerSpec
FOR SERVER AUDIT PCIAudit
-- 10.2.4, 10.2.5: Authentication events
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP),
-- 10.2.2: Admin privilege use
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
-- 10.2.6: Audit log changes
ADD (AUDIT_CHANGE_GROUP),
-- 10.2.7: System-level objects
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP)
WITH (STATE = ON);
 
-- Database-level for cardholder data (10.2.1)
USE PaymentDB;
GO
CREATE DATABASE AUDIT SPECIFICATION PCI_DbSpec
FOR SERVER AUDIT PCIAudit
-- All access to cardholder data tables
ADD (SELECT ON dbo.CardholderData BY public),
ADD (INSERT, UPDATE, DELETE ON dbo.CardholderData BY public),
ADD (SELECT ON dbo.TransactionLog BY public),
ADD (INSERT, UPDATE, DELETE ON dbo.TransactionLog BY public),
-- 10.2.3: Access to audit trails
ADD (SELECT ON dbo.AuditLog BY public),
-- Schema changes
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
 
ALTER SERVER AUDIT PCIAudit WITH (STATE = ON);
 
-- 10.3: Record required attributes for each event
-- SQL Server audit automatically includes:
-- 10.3.1: User ID (server_principal_name)
-- 10.3.2: Event type (action_id)
-- 10.3.3: Date/time (event_time)
-- 10.3.4: Success/failure (succeeded)
-- 10.3.5: Origin (client_ip, application_name)
-- 10.3.6: Identity/name of affected data (object_name, statement)
 
-- 10.5: Secure audit trails
-- - Store on separate secured system (centralized SIEM)
-- - Restrict access to audit files (NTFS permissions)
-- - Use checksums/digital signatures (third-party tools)
 
-- 10.7: Retain for at least one year, 3 months immediately available
-- Configure in centralized log system, not here

Audit Failure Handling

For high-compliance environments (PCI-DSS Level 1), consider ON_FAILURE = SHUTDOWN which stops the database if auditing fails. This ensures security events can't occur unlogged. For most environments, ON_FAILURE = CONTINUE with aggressive alerting on audit failures is more appropriate to avoid availability impact.

Audit Performance and Protection

Auditing isn't free—every logged event consumes CPU, memory, and disk I/O. Additionally, audit logs themselves become sensitive data requiring protection. Balancing these concerns is essential for sustainable auditing. Performance Considerations:

Audit Performance Impact Factors
Factor	Impact	Mitigation
Event volume	High	Audit selectively; focus on sensitive operations
Synchronous vs async	Medium-High	Use async where possible (QUEUE_DELAY)
Local vs remote storage	Medium	Buffer locally, batch ship to central system
Statement logging	High	Log statements only for sensitive tables
Parameter logging	Medium	Enable only when needed for investigation
Log file I/O	Medium	Use fast storage; separate from data files

Protecting Audit Logs: Audit logs contain sensitive information about your database operations and are prime targets for attackers wanting to cover their tracks. Protection measures include:

Audit Log Protection Checklist

•Separate storage — Write audit logs to a different disk/partition than data files. Ideally, ship to a completely separate system.
•Immutable storage — Use write-once storage or append-only destinations where logs can't be modified after writing.
•Encryption — Encrypt audit logs at rest. Use TLS for log shipping. Especially important for logs containing query parameters.
•Access control — Restrict who can read and especially who can delete audit logs. Use separate credentials from database access.
•Integrity verification — Hash or digitally sign log files to detect tampering. Some SIEM platforms do this automatically.
•Multi-destination logging — Send logs to multiple independent destinations so compromise of one doesn't eliminate the trail.
•Time synchronization — Ensure all systems use NTP; accurate timestamps are essential for forensic timeline analysis.

audit_protection.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
-- ================================================
-- SQL Server Audit Protection Configuration
-- ================================================
 
-- 1. Use signed audit files (tamper detection)
CREATE SERVER AUDIT SignedAudit
TO FILE (
    FILEPATH = 'C:\SecureAuditLogs\',
    MAXSIZE = 100 MB,
    MAX_ROLLOVER_FILES = 50
)
WITH (
    QUEUE_DELAY = 1000,
    ON_FAILURE = CONTINUE
);
 
-- Verify audit files haven't been tampered with
SELECT 
    name AS AuditName,
    status_desc AS Status,
    is_state_enabled AS Enabled,
    queue_delay AS QueueDelayMs,
    on_failure_desc AS OnFailure
FROM sys.server_audits;
 
-- 2. Restrict access to audit specification
-- Only sysadmin can modify server audits by default
-- Create audit of audit changes
CREATE SERVER AUDIT AuditTheAudit
TO FILE (FILEPATH = 'D:\MetaAuditLogs\');
 
CREATE SERVER AUDIT SPECIFICATION AuditAuditChanges
FOR SERVER AUDIT AuditTheAudit
ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);
 
ALTER SERVER AUDIT AuditTheAudit WITH (STATE = ON);
 
-- 3. Monitor for audit failures
-- Create alert on Audit event
EXEC msdb.dbo.sp_add_alert 
    @name = N'Audit Failure Alert',
    @message_id = 0,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 60,
    @include_event_description_in = 1,
    @event_description_keyword = N'Audit failure',
    @job_id = N'00000000-0000-0000-0000-000000000000',
    @notification_message = N'SQL Server Audit has failed!';
 
-- 4. File system protections (run as administrator)
-- icacls "C:\SecureAuditLogs" /inheritance:d
-- icacls "C:\SecureAuditLogs" /remove:g "Everyone"
-- icacls "C:\SecureAuditLogs" /remove:g "Users"
-- icacls "C:\SecureAuditLogs" /grant "SQL Service Account":(OI)(CI)F
-- icacls "C:\SecureAuditLogs" /grant "Security Team":(OI)(CI)R
 
-- 5. Use Windows permissions to make logs append-only
-- (Use commercial solutions like LogRhythm, Splunk for true immutability)

WORM Storage for Compliance

For regulatory compliance requiring tamper-proof logs (SEC 17a-4, FINRA), use Write-Once-Read-Many (WORM) storage. AWS S3 Object Lock, Azure Immutable Blob Storage, and dedicated WORM appliances provide regulatory-grade immutability that satisfies auditor requirements for log integrity.

Database Management SystemsDatabase Security

Database Security

LevelAdvanced

Duration90 mins

TopicDatabase Security

4 / 5

Auditing

The Evidence Trail

Authentication verifies identity. Authorization enforces access control. Encryption protects confidentiality. But how do we know if these controls are working? How do we detect when something goes wrong? How do we investigate incidents after they occur? Auditing is the answer—the systematic recording and analysis of database activities that creates an unimpeachable trail of evidence. Effective auditing answers critical questions: Who accessed what data? When did they access it? What did they do with it? Were there any anomalies? Database auditing isn't just about catching bad actors—though it certainly helps. It's about demonstrating due diligence to regulators, providing forensic evidence for investigations, detecting operational anomalies before they become incidents, and understanding how your database is really being used.

What You Will Learn

By the end of this page, you will understand the objectives and scope of database auditing, configure comprehensive audit policies across major database platforms, implement centralized log collection and retention strategies, analyze audit data to detect security threats and compliance violations, and design auditing architectures that balance security needs with performance impact.

Auditing Fundamentals

Database auditing is the process of monitoring, recording, and analyzing database activities. Unlike application logging which captures what apps intend to do, database auditing captures what actually happens at the data layer—including activities that bypass the application entirely. Why Database Auditing Matters:

Auditing Objectives

•Security Detection — Identify unauthorized access attempts, privilege escalation, unusual query patterns, and data exfiltration.
•Compliance Verification — Demonstrate adherence to regulatory requirements (PCI-DSS, HIPAA, SOX, GDPR) during audits.
•Forensic Investigation — Provide detailed evidence trail for incident response, determining what was accessed and by whom.
•Operational Insight — Understand actual usage patterns, identify performance bottlenecks, and validate application behavior.
•Accountability — Create clear record of who did what, removing ability to deny actions (non-repudiation).
•Policy Enforcement — Verify that access controls, separation of duties, and data handling policies are being followed.

Categories of Audit Events: Database auditing can capture various categories of events, each serving different security and compliance objectives:

Audit Event Categories
Category	Examples	Security Value	Volume Impact
Authentication	Login success/failure, logout, connection parameters	Detect brute-force, credential stuffing, unusual access times	Low
Authorization	Permission grants, revokes, role changes	Detect privilege escalation, unauthorized changes	Low
Schema Changes (DDL)	CREATE, ALTER, DROP of tables, indexes, procedures	Detect unauthorized schema modifications, backdoors	Low-Medium
Data Modification (DML)	INSERT, UPDATE, DELETE operations	Detect data tampering, mass deletions	High
Data Access (SELECT)	Read operations on sensitive tables	Detect data exfiltration, unauthorized access	Very High
Administrative	BACKUP, RESTORE, SHUTDOWN, configuration changes	Detect unauthorized maintenance, system tampering	Low
Security Events	Failed access attempts, privilege use, policy violations	Direct security threat detection	Medium

The SELECT Audit Challenge

Auditing SELECT statements is crucial for detecting data theft but generates enormous volumes of log data—potentially millions of events per day for busy databases. This creates storage, performance, and analysis challenges. Most organizations audit SELECTS only on specific sensitive tables rather than database-wide.

Configuring Database Auditing

Each database platform provides native auditing capabilities with different configuration options, granularity, and output formats. Understanding these differences is essential for implementing effective cross-platform auditing strategies.

audit_configuration.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
-- ================================================
-- SQL Server Audit Configuration
-- ================================================
 
-- Step 1: Create Server Audit (defines where to write)
CREATE SERVER AUDIT ProductionAudit
TO FILE (
    FILEPATH = 'C:\AuditLogs\',
    MAXSIZE = 100 MB,
    MAX_ROLLOVER_FILES = 50,  -- Keep 50 files (5GB total)
    RESERVE_DISK_SPACE = ON
)
WITH (
    QUEUE_DELAY = 1000,  -- 1 second max delay (for performance)
    ON_FAILURE = CONTINUE,  -- Don't stop database if audit fails
    AUDIT_GUID = NEWID()
);
 
-- Enable the audit
ALTER SERVER AUDIT ProductionAudit WITH (STATE = ON);
 
-- Step 2: Create Server Audit Specification (server-level events)
CREATE SERVER AUDIT SPECIFICATION ServerSecurityAudit
FOR SERVER AUDIT ProductionAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),  -- Audit changes to audits themselves!
ADD (BACKUP_RESTORE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP)
WITH (STATE = ON);
 
-- Step 3: Create Database Audit Specification (database-level events)
USE ProductionDB;
GO
 
CREATE DATABASE AUDIT SPECIFICATION DatabaseDataAudit
FOR SERVER AUDIT ProductionAudit
ADD (SELECT ON SCHEMA::dbo BY public),  -- All SELECTs on dbo schema
ADD (INSERT, UPDATE, DELETE ON dbo.Customers BY public),
ADD (INSERT, UPDATE, DELETE ON dbo.Payments BY public),
ADD (EXECUTE ON SCHEMA::dbo BY public),  -- All stored procedure executions
ADD (SCHEMA_OBJECT_CHANGE_GROUP),  -- DDL changes
ADD (DATABASE_PERMISSION_CHANGE_GROUP)
WITH (STATE = ON);
 
-- Step 4: Query Audit Log
SELECT 
    event_time,
    action_id,
    succeeded,
    session_id,
    server_principal_name AS [user],
    database_name,
    object_name,
    statement
FROM sys.fn_get_audit_file('C:\AuditLogs\ProductionAudit*.sqlaudit', DEFAULT, DEFAULT)
WHERE event_time > DATEADD(hour, -24, GETDATE())
ORDER BY event_time DESC;
 
-- Audit action groups reference:
-- https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions

Audit the Auditors

Always audit changes to the audit configuration itself (AUDIT_CHANGE_GROUP in SQL Server, DDL events in PostgreSQL). An attacker's first action after gaining access is often to disable or modify auditing to cover their tracks.

Granular Audit Policies

Effective auditing requires balancing comprehensive coverage against performance impact and log volume. Granular policies enable targeted auditing of high-risk activities without overwhelming storage or analysis capabilities. Designing Audit Policies:

Audit Policy Design Guidelines
Data Classification	Audit Scope	Rationale
Public data	DDL only, no DML/SELECT	Low risk, minimize overhead
Internal data	DDL, failed access, privilege changes	Balance visibility with volume
Confidential data	All modifications (INSERT/UPDATE/DELETE)	Track data changes for integrity
Highly sensitive (PII, PCI)	All access including SELECT	Full visibility despite volume
System tables	All access by non-system accounts	Detect privilege escalation attempts

granular_auditing.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- ================================================
-- SQL Server Fine-Grained Auditing Example
-- ================================================
 
-- Scenario: E-commerce database with varied sensitivity
 
-- 1. Audit ONLY sensitive tables for SELECT
CREATE DATABASE AUDIT SPECIFICATION SensitiveDataAudit
FOR SERVER AUDIT ProductionAudit
ADD (SELECT ON dbo.Customers BY public),      -- PII
ADD (SELECT ON dbo.PaymentMethods BY public), -- PCI
ADD (SELECT ON dbo.HealthRecords BY public),  -- PHI
ADD (SELECT ON dbo.EmployeeSalaries BY public) -- Sensitive HR
WITH (STATE = ON);
 
-- 2. Audit modifications on more tables
CREATE DATABASE AUDIT SPECIFICATION DataModificationAudit
FOR SERVER AUDIT ProductionAudit
ADD (INSERT, UPDATE, DELETE ON SCHEMA::dbo BY public),
ADD (INSERT, UPDATE, DELETE ON SCHEMA::orders BY public)
WITH (STATE = ON);
 
-- 3. Use Filtered Audit (SQL Server 2016+) for specific users
CREATE DATABASE AUDIT SPECIFICATION AdminActivityAudit
FOR SERVER AUDIT ProductionAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo BY db_owner),
ADD (EXECUTE ON SCHEMA::dbo BY db_owner);
-- This audits everything done by members of db_owner role
 
-- ================================================
-- Oracle Fine-Grained Auditing (FGA)
-- ================================================
 
-- Note: Oracle syntax, not SQL Server
 
-- FGA for specific conditions (e.g., salary access over threshold)
-- BEGIN
--   DBMS_FGA.ADD_POLICY(
--     object_schema   => 'HR',
--     object_name     => 'EMPLOYEES',
--     policy_name     => 'HIGH_SALARY_ACCESS',
--     audit_condition => 'SALARY > 100000',
--     audit_column    => 'SALARY, SSN',
--     handler_schema  => 'SECURITY',
--     handler_module  => 'ALERT_SECURITY_TEAM',
--     enable          => TRUE,
--     statement_types => 'SELECT, UPDATE'
--   );
-- END;
 
-- This creates an alert when anyone accesses records
-- where salary > $100k

Trigger-Based Auditing Limitations

Trigger-based auditing can be bypassed by superusers or by disabling triggers (ALTER TABLE ... DISABLE TRIGGER). It also impacts write performance. Use triggers as a supplement to, not a replacement for, database-native auditing. Native audit logs are harder to tamper with and have predictable performance impact.

Centralized Log Management

Audit logs stored on the database server itself are vulnerable to tampering by anyone with server access—including the attackers we're trying to detect. Centralized log management addresses this by shipping logs to a separate, secure system in near-real-time. Benefits of Centralized Logging:

Why Centralize Audit Logs

•Tamper Resistance — Logs stored separately can't be modified by database administrators or server compromises.
•Consolidated Analysis — Correlate events across multiple databases to detect coordinated attacks.
•Long-term Retention — Meet compliance requirements (often 7+ years) without impacting database storage.
•Real-time Alerting — SIEM integration enables immediate notification of security events.
•Efficient Search — Specialized log platforms index and query more efficiently than database tables.

Converting Mermaid diagram...

log_shipping.yaml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
# filebeat.yml - PostgreSQL Audit Log Shipping
 
filebeat.inputs:
  # PostgreSQL log files
  - type: log
    enabled: true
    paths:
      - /var/log/postgresql/postgresql-*.log
    
    # Multi-line log handling
    multiline.pattern: '^\d{4}-\d{2}-\d{2}'
    multiline.negate: true
    multiline.match: after
    
    # Add metadata
    fields:
      log_type: postgresql
      environment: production
      database_cluster: pg-prod-01
    
    # Parse pgAudit format
    processors:
      - dissect:
          tokenizer: '%{timestamp} [%{pid}]: user=%{user},db=%{database},app=%{app},client=%{client} %{level}: AUDIT: %{audit_type},%{session_id},%{command_tag},%{class},%{command},%{object_type},%{object_name},"%{statement}"'
          field: "message"
          target_prefix: "pg"
 
output.elasticsearch:
  hosts: ["https://elasticsearch.corp.com:9200"]
  username: "filebeat"
  password: "${ES_PASSWORD}"
  ssl.certificate_authorities: ["/etc/pki/ca/ca.crt"]
  index: "database-audit-%{+yyyy.MM.dd}"
 
# Queue for reliability
queue.mem:
  events: 4096
  flush.min_events: 512
  flush.timeout: 5s
 
# Enable monitoring
monitoring:
  enabled: true
  elasticsearch:
    hosts: ["https://elasticsearch.corp.com:9200"]

Log Retention Policy

Define retention periods based on regulatory requirements: PCI-DSS requires 1 year online + archives; HIPAA requires 6 years; SOX requires 7 years. Use tiered storage (hot/warm/cold) to manage costs—recent logs in fast storage for analysis, archived logs in cheaper object storage for compliance.

Audit Data Analysis

Collecting audit data is only valuable if you analyze it effectively. Raw audit logs are overwhelming—a busy database can generate millions of events daily. Effective analysis requires both automated detection and structured review processes. Analysis Approaches:

Audit Analysis Methods

•Real-time Detection — SIEM rules trigger alerts on specific patterns (failed logins, privilege escalation, after-hours access).
•Baseline Comparison — Machine learning establishes "normal" behavior; anomalies trigger investigation.
•Periodic Review — Security team manually reviews samples of high-risk access patterns.
•Incident Investigation — Forensic analysis when a breach or suspicious activity is detected.
•Compliance Reporting — Automated reports demonstrating access controls, separation of duties, etc.

audit_analysis_queries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- ================================================
-- SQL Server Audit Analysis Queries
-- ================================================
 
-- 1. Failed Login Summary (Brute Force Detection)
SELECT 
    server_principal_name AS attempted_user,
    COUNT(*) AS failed_attempts,
    MIN(event_time) AS first_attempt,
    MAX(event_time) AS last_attempt,
    DATEDIFF(MINUTE, MIN(event_time), MAX(event_time)) AS duration_minutes
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'LGIF'  -- Login Failed
  AND event_time > DATEADD(HOUR, -24, GETDATE())
GROUP BY server_principal_name
HAVING COUNT(*) > 10
ORDER BY failed_attempts DESC;
 
-- 2. Unusual Access Times (After-Hours Detection)
SELECT 
    server_principal_name AS [user],
    database_name,
    COUNT(*) AS queries,
    DATEPART(HOUR, event_time) AS hour_of_day
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE event_time > DATEADD(DAY, -7, GETDATE())
  AND (DATEPART(HOUR, event_time) < 6 OR DATEPART(HOUR, event_time) > 22)
  AND action_id IN ('SL', 'IN', 'UP', 'DL')  -- SELECT, INSERT, UPDATE, DELETE
GROUP BY server_principal_name, database_name, DATEPART(HOUR, event_time)
ORDER BY COUNT(*) DESC;
 
-- 3. Large Data Extraction (Exfiltration Detection)
SELECT 
    server_principal_name AS [user],
    database_name,
    object_name AS table_name,
    COUNT(*) AS select_count,
    SUM(CAST(affected_rows AS BIGINT)) AS total_rows_accessed
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'SL'  -- SELECT
  AND event_time > DATEADD(HOUR, -24, GETDATE())
GROUP BY server_principal_name, database_name, object_name
HAVING SUM(CAST(affected_rows AS BIGINT)) > 100000
ORDER BY total_rows_accessed DESC;
 
-- 4. Privilege Escalation Detection
SELECT 
    event_time,
    server_principal_name AS granted_by,
    statement,
    additional_information
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id IN ('G', 'ADGR', 'DAGR')  -- Grant, Add Group, Database Add Group
  AND event_time > DATEADD(DAY, -30, GETDATE())
ORDER BY event_time DESC;
 
-- 5. Schema Changes (Backdoor Detection)
SELECT 
    event_time,
    server_principal_name AS [user],
    database_name,
    schema_name,
    object_name,
    statement
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id IN ('CR', 'AL', 'DR')  -- Create, Alter, Drop
  AND object_name NOT LIKE '#%'  -- Exclude temp objects
  AND event_time > DATEADD(DAY, -7, GETDATE())
ORDER BY event_time DESC;

Behavioral Baselines

Machine learning-based anomaly detection (available in modern SIEMs like Elastic SIEM, Splunk UBA, Microsoft Sentinel) can learn normal access patterns per user/application and alert on deviations. This catches attacks that don't match predefined rules but represent unusual behavior for that entity.

Compliance Auditing

Regulatory compliance often mandates specific auditing requirements. Understanding these requirements ensures your audit configuration satisfies auditors and avoids costly findings. Compliance-Specific Audit Requirements:

Audit Requirements by Regulation
Regulation	Required Audit Events	Retention	Review Frequency
PCI-DSS (Req 10)	All access to cardholder data, authentication, privilege changes, system events	1 year (3 months immediately accessible)	Daily log review
HIPAA (§164.312)	Access to PHI, login attempts, changes to audit controls	6 years	Regular review
SOX (PCAOB)	Access to financial data, changes to financial systems, user account changes	7 years	Quarterly at minimum
GDPR (Art 30)	Processing activities, consent changes, data access	Duration of processing	On-demand for audits
NIST 800-53 (AU)	Audit generation, content, storage, protection, retention	Defined by organization	Per security plan

PCI-DSS Section 10 Deep Dive: Payment Card Industry requirements are among the most specific. Let's map them to database audit configurations:

pci_dss_audit.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
-- ================================================
-- PCI-DSS Requirement 10 Compliance Audit Configuration
-- ================================================
 
-- 10.2.1: All individual user accesses to cardholder data
-- 10.2.2: All actions taken by any individual with admin privileges
-- 10.2.3: Access to all audit trails
-- 10.2.4: Invalid logical access attempts
-- 10.2.5: Use of ID and authentication mechanisms
-- 10.2.6: Initialization, stopping, or pausing of audit logs
-- 10.2.7: Creation and deletion of system-level objects
 
-- Server-level audit for PCI compliance
CREATE SERVER AUDIT PCIAudit
TO FILE (FILEPATH = 'C:\PCIAuditLogs\', MAXSIZE = 200MB, MAX_ROLLOVER_FILES = 100)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = SHUTDOWN);  -- Critical: fail-closed
 
CREATE SERVER AUDIT SPECIFICATION PCI_ServerSpec
FOR SERVER AUDIT PCIAudit
-- 10.2.4, 10.2.5: Authentication events
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP),
-- 10.2.2: Admin privilege use
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
-- 10.2.6: Audit log changes
ADD (AUDIT_CHANGE_GROUP),
-- 10.2.7: System-level objects
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP)
WITH (STATE = ON);
 
-- Database-level for cardholder data (10.2.1)
USE PaymentDB;
GO
CREATE DATABASE AUDIT SPECIFICATION PCI_DbSpec
FOR SERVER AUDIT PCIAudit
-- All access to cardholder data tables
ADD (SELECT ON dbo.CardholderData BY public),
ADD (INSERT, UPDATE, DELETE ON dbo.CardholderData BY public),
ADD (SELECT ON dbo.TransactionLog BY public),
ADD (INSERT, UPDATE, DELETE ON dbo.TransactionLog BY public),
-- 10.2.3: Access to audit trails
ADD (SELECT ON dbo.AuditLog BY public),
-- Schema changes
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
 
ALTER SERVER AUDIT PCIAudit WITH (STATE = ON);
 
-- 10.3: Record required attributes for each event
-- SQL Server audit automatically includes:
-- 10.3.1: User ID (server_principal_name)
-- 10.3.2: Event type (action_id)
-- 10.3.3: Date/time (event_time)
-- 10.3.4: Success/failure (succeeded)
-- 10.3.5: Origin (client_ip, application_name)
-- 10.3.6: Identity/name of affected data (object_name, statement)
 
-- 10.5: Secure audit trails
-- - Store on separate secured system (centralized SIEM)
-- - Restrict access to audit files (NTFS permissions)
-- - Use checksums/digital signatures (third-party tools)
 
-- 10.7: Retain for at least one year, 3 months immediately available
-- Configure in centralized log system, not here

Audit Failure Handling

For high-compliance environments (PCI-DSS Level 1), consider ON_FAILURE = SHUTDOWN which stops the database if auditing fails. This ensures security events can't occur unlogged. For most environments, ON_FAILURE = CONTINUE with aggressive alerting on audit failures is more appropriate to avoid availability impact.

Audit Performance and Protection

Auditing isn't free—every logged event consumes CPU, memory, and disk I/O. Additionally, audit logs themselves become sensitive data requiring protection. Balancing these concerns is essential for sustainable auditing. Performance Considerations:

Audit Performance Impact Factors
Factor	Impact	Mitigation
Event volume	High	Audit selectively; focus on sensitive operations
Synchronous vs async	Medium-High	Use async where possible (QUEUE_DELAY)
Local vs remote storage	Medium	Buffer locally, batch ship to central system
Statement logging	High	Log statements only for sensitive tables
Parameter logging	Medium	Enable only when needed for investigation
Log file I/O	Medium	Use fast storage; separate from data files

Protecting Audit Logs: Audit logs contain sensitive information about your database operations and are prime targets for attackers wanting to cover their tracks. Protection measures include:

Audit Log Protection Checklist

•Separate storage — Write audit logs to a different disk/partition than data files. Ideally, ship to a completely separate system.
•Immutable storage — Use write-once storage or append-only destinations where logs can't be modified after writing.
•Encryption — Encrypt audit logs at rest. Use TLS for log shipping. Especially important for logs containing query parameters.
•Access control — Restrict who can read and especially who can delete audit logs. Use separate credentials from database access.
•Integrity verification — Hash or digitally sign log files to detect tampering. Some SIEM platforms do this automatically.
•Multi-destination logging — Send logs to multiple independent destinations so compromise of one doesn't eliminate the trail.
•Time synchronization — Ensure all systems use NTP; accurate timestamps are essential for forensic timeline analysis.

audit_protection.sql
SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
-- ================================================
-- SQL Server Audit Protection Configuration
-- ================================================
 
-- 1. Use signed audit files (tamper detection)
CREATE SERVER AUDIT SignedAudit
TO FILE (
    FILEPATH = 'C:\SecureAuditLogs\',
    MAXSIZE = 100 MB,
    MAX_ROLLOVER_FILES = 50
)
WITH (
    QUEUE_DELAY = 1000,
    ON_FAILURE = CONTINUE
);
 
-- Verify audit files haven't been tampered with
SELECT 
    name AS AuditName,
    status_desc AS Status,
    is_state_enabled AS Enabled,
    queue_delay AS QueueDelayMs,
    on_failure_desc AS OnFailure
FROM sys.server_audits;
 
-- 2. Restrict access to audit specification
-- Only sysadmin can modify server audits by default
-- Create audit of audit changes
CREATE SERVER AUDIT AuditTheAudit
TO FILE (FILEPATH = 'D:\MetaAuditLogs\');
 
CREATE SERVER AUDIT SPECIFICATION AuditAuditChanges
FOR SERVER AUDIT AuditTheAudit
ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);
 
ALTER SERVER AUDIT AuditTheAudit WITH (STATE = ON);
 
-- 3. Monitor for audit failures
-- Create alert on Audit event
EXEC msdb.dbo.sp_add_alert 
    @name = N'Audit Failure Alert',
    @message_id = 0,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 60,
    @include_event_description_in = 1,
    @event_description_keyword = N'Audit failure',
    @job_id = N'00000000-0000-0000-0000-000000000000',
    @notification_message = N'SQL Server Audit has failed!';
 
-- 4. File system protections (run as administrator)
-- icacls "C:\SecureAuditLogs" /inheritance:d
-- icacls "C:\SecureAuditLogs" /remove:g "Everyone"
-- icacls "C:\SecureAuditLogs" /remove:g "Users"
-- icacls "C:\SecureAuditLogs" /grant "SQL Service Account":(OI)(CI)F
-- icacls "C:\SecureAuditLogs" /grant "Security Team":(OI)(CI)R
 
-- 5. Use Windows permissions to make logs append-only
-- (Use commercial solutions like LogRhythm, Splunk for true immutability)

WORM Storage for Compliance

For regulatory compliance requiring tamper-proof logs (SEC 17a-4, FINRA), use Write-Once-Read-Many (WORM) storage. AWS S3 Object Lock, Azure Immutable Blob Storage, and dedicated WORM appliances provide regulatory-grade immutability that satisfies auditor requirements for log integrity.

Auditing

The Evidence Trail

Auditing Fundamentals

Configuring Database Auditing

Granular Audit Policies

Centralized Log Management

Audit Data Analysis

Compliance Auditing

Audit Performance and Protection

There’s more to understand.

Auditing

The Evidence Trail

Auditing Fundamentals

Configuring Database Auditing

Granular Audit Policies

Centralized Log Management

Audit Data Analysis

Compliance Auditing

Audit Performance and Protection

There’s more to understand.