Logging, Auditing and Security Analytics

Introduction

A secure database is not secured only by passwords, encryption, and access controls. It must also provide trustworthy evidence of what happened, when it happened, who or what caused it, and whether the activity was authorised. Logging, auditing, and security analytics provide this evidence and support detection, investigation, compliance, and continuous improvement.

Database activity may include successful and failed logins, privilege changes, schema modifications, queries involving sensitive data, exports, administrative actions, and changes to audit configuration. A useful security programme therefore treats logs as both operational data and potential evidence. Logs must be complete enough to support investigation, protected against unauthorised alteration, retained for an appropriate period, and analysed in a timely manner.

This chapter examines database audit trails, database activity monitoring, security information and event management (SIEM), baselines, indicators, alerting, anomaly detection, and evidential integrity. The focus is practical application at diploma level: designing a logging strategy, configuring controls, interpreting events, and responding responsibly to suspicious activity.

Learning Objectives

By the end of this chapter, a learner should be able to:

1. Explain the purposes and limitations of database logging and auditing.
2. Design an audit trail covering identity, action, object, time, source, and outcome.
3. Distinguish database-native auditing from database activity monitoring and SIEM correlation.
4. Collect, normalise, protect, and retain logs appropriately.
5. Establish behavioural baselines, indicators, and useful alerts.
6. Apply simple statistical and rule-based anomaly-detection techniques.
7. Preserve evidential integrity during collection, analysis, and reporting.
8. Evaluate privacy, governance, operational, and ethical issues in monitoring.

1. Foundations of Logging and Auditing

1.1 Logging, auditing, and monitoring

A log is a time-ordered record of an event. Examples include a login attempt, a SQL statement, a connection termination, or a change to a user account. Logging is the process of generating and recording such events.

An audit trail is a purposeful collection of records that supports accountability, reconstruction, compliance, or investigation. Auditing normally defines what must be recorded, who may review it, how long it is retained, and how it is protected.

Database activity monitoring (DAM) observes database activity and evaluates it against policies, often in near real time. DAM may use native audit records, network observation, agents, or a combination of methods. It can identify actions such as bulk extraction, access outside normal working hours, or use of privileged accounts.

A SIEM collects security events from multiple sources, normalises them, correlates related events, and presents alerts, searches, dashboards, and investigative timelines. A SIEM does not replace database auditing. It adds cross-system context—for example, correlating a database export with a VPN login, endpoint alert, and unusual cloud-console activity.

These functions are related but not identical:

1.2 The audit-event model

A useful audit record should identify at least:

• When: timestamp, preferably in UTC, with a known clock source and sufficient precision.
• Who: user account, service identity, role, authentication method, and privilege context.
• What: operation, SQL command category, object, row scope where appropriate, and outcome.
• Where: source address, host, application, session, connection, and database instance.
• Why or context: ticket number, transaction identifier, request identifier, or business process when available.
• Result: success, failure, error code, number of records affected, or bytes transferred.
• Integrity information: sequence number, source identifier, transport status, and cryptographic protection where implemented.

A record should be understandable without relying on volatile context. For example, “permission changed” is weak evidence. “Account analyst7, administrator session S4812, granted SELECT on customer_health, from management host 10.20.4.15, at 2025-04-12T09:31:16Z, success” is substantially more useful. Sensitive values such as passwords, access tokens, and full personal records should not be written into logs.

1.3 What should be audited?

A risk-based audit policy commonly covers:

1. Authentication successes and failures.
2. Creation, deletion, disabling, and modification of accounts and roles.
3. Granting and revoking privileges.
4. Changes to schemas, stored procedures, triggers, policies, and audit settings.
5. Access to high-value tables and sensitive columns.
6. Bulk reads, exports, backups, and replication operations.
7. Administrative commands and use of emergency or break-glass accounts.
8. Failed queries, permission denials, and unusual error patterns.
9. Data modifications where accountability or regulation requires them.
10. Database start-up, shutdown, configuration changes, and connection failures.

Auditing every query and every row may be technically possible but operationally expensive. The correct design balances risk, performance, storage, privacy, and investigative value. High-risk objects generally require more detailed auditing than low-risk reference tables.

2. Designing an Audit Architecture

2.1 Layers of collection

Database security evidence can be collected at several layers:

• Database-native audit: records commands, authentication, privilege changes, object access, and database outcomes. It usually has the best semantic understanding of database actions.
• Database transaction or change logs: support recovery and replication, but are not automatically complete security audit trails.
• Operating-system logs: record process, file, service, and host-level events.
• Network and proxy logs: show connections, source and destination, protocol metadata, and sometimes SQL statements. Encryption may limit visibility.
• Application logs: connect database actions to users, workflows, requests, and business functions.
• Cloud control-plane logs: record creation of database instances, policy changes, snapshots, keys, and network rules.

No single layer is sufficient in all environments. Native auditing may show that an account read a table but not whether the account was compromised through a stolen endpoint. Endpoint and identity logs can supply that missing context.

2.2 Centralisation and separation of duties

Audit records should normally be forwarded to a separate, access-controlled system. Storing the only copy on the monitored database creates a serious weakness: an administrator or attacker who controls the database may delete or alter the evidence. A robust pattern is:

1. Generate an event at the database.
2. Deliver it through an authenticated and encrypted channel.
3. Ingest it into a collector or SIEM.
4. Validate format, source identity, and time.
5. Store an immutable or append-only copy.
6. Restrict access and record all access to the logs.
7. Back up according to the retention and recovery policy.

The audit platform itself must be audited. A monitoring system that silently stops collecting, loses events, or allows analysts to alter records is not trustworthy.

2.3 Time synchronisation

Correlation depends on comparable timestamps. Database servers, identity providers, application hosts, and SIEM collectors should use an approved time source and record time zone or UTC consistently. Analysts should distinguish event time, collection time, and indexing time. Delayed delivery can otherwise be mistaken for delayed activity.

Time synchronisation does not prove that an event is genuine, but inconsistent clocks can make a correct investigation impossible. Systems should alert when clock drift exceeds the organisation’s defined tolerance.

2.4 Audit completeness and quality

Useful audit-quality measures include:

• Coverage: proportion of required event types that are generated.
• Delivery: proportion of generated events received centrally.
• Freshness: delay between occurrence and availability for analysis.
• Parse success: proportion correctly normalised by the collector.
• Integrity: ability to demonstrate that records were not altered.
• Retention: availability for the required period.

A simple delivery-rate measure is:

Here, both event counts must refer to the same time interval and event population. The measure is meaningful only when the source count is itself reliable. A high delivery rate does not prove that the correct events were configured in the first place.

3. Database Activity Monitoring and SIEM Correlation

3.1 DAM approaches

DAM may operate through:

• Native audit integration: consumes database audit records.
• Agent-based monitoring: runs a component close to the database and can observe local activity.
• Network-based monitoring: observes database traffic, where protocol visibility permits.
• Proxy or gateway monitoring: places a controlled intermediary between clients and databases.
• Hybrid monitoring: combines native, network, application, identity, and host evidence.

Network monitoring can be difficult when traffic is encrypted, when connections are pooled, or when applications use stored procedures. Native records may identify the database user but not the original human user unless application identity is propagated. A hybrid approach usually gives better context but increases cost and complexity.

3.2 SIEM event normalisation

Different products express the same event differently. One system may call a field src_ip, another clientAddress; one may record a result as ALLOW, another as SUCCESS. Normalisation maps source records into a common schema. Important fields commonly include event time, source, destination, account, action, object, result, severity, and event type.

Normalisation should preserve the original event. Analysts need both a consistent searchable representation and the raw record for verification. Parsing errors, unknown fields, truncation, and encoding problems should be reported rather than silently discarded.

3.3 Correlation examples

Correlation rules combine multiple observations. Examples include:

• A successful login from a new country followed by a large export.
• A privilege grant followed by access to a sensitive table.
• Several failed logins followed by a successful login and administrative activity.
• A database connection from a host that has an endpoint malware alert.
• A disabled audit policy followed by schema or data changes.
• A cloud snapshot creation followed by an unusual download.

Correlation reduces isolated-event noise, but it can also create false positives if rules ignore legitimate maintenance, service accounts, time zones, or scheduled jobs. Rules should be tested against historical data and reviewed after incidents.

4. Baselines, Indicators, and Alert Design

4.1 Establishing a baseline

A baseline describes normal activity for a defined environment and period. It might include normal login times, source networks, query volumes, accessed objects, export sizes, administrative actions, and service-account behaviour. A single global baseline is often misleading. A payroll system, development database, and 24-hour production service have different normal patterns.

A baseline should be segmented by:

• identity or role;
• application and database;
• source host or network;
• time of day and day of week;
• business process;
• maintenance and release schedules.

Baselines must be periodically recalculated because business activity changes. They should not be used to excuse clearly prohibited behaviour: an action can be common and still unauthorised.

4.2 Indicators and alert logic

An indicator is an observable condition associated with a security concern. It may be a single event, such as disabling auditing, or a combination, such as a new privileged account followed by bulk access.

A good alert is:

• actionable;
• specific enough to investigate;
• prioritised by business impact;
• linked to relevant evidence;
• resistant to trivial evasion;
• documented with an owner and response procedure.

Alert severity should consider confidence, asset criticality, data sensitivity, and potential impact. A failed login against a low-risk test system may be informational; the same pattern against a privileged account on a production database may require urgent investigation.

Alert fatigue occurs when analysts receive more alerts than they can assess. It is reduced through suppression of known benign events, threshold tuning, grouping related events, maintenance windows, risk-based prioritisation, and regular measurement of true and false positives.

5. Analytics and Anomaly Detection

5.1 Rule-based and statistical methods

Rule-based detection is transparent and easy to explain. A rule might alert when more than a defined number of failed logins occur from one source within a short interval. Its weakness is that attackers can remain below thresholds or imitate normal behaviour.

Statistical methods compare current activity with historical behaviour. For a numerical measure such as daily query count, the arithmetic mean is:

where x_i is an observation and n is the number of observations. The sample standard deviation is:

where x̄ is the sample mean. The denominator n − 1 is used when estimating population variability from a sample. These measures assume that the observations are reasonably comparable and that extreme values do not dominate interpretation.

A standardised score can be calculated as:

where x is the current observation, μ is the baseline mean, and σ is the baseline population standard deviation. If a sample standard deviation is used, it is often denoted by s instead. A large absolute score may indicate unusual activity, but it is not proof of an attack. Counts may be seasonal, skewed, or affected by deployments, and a normal-distribution assumption may not hold.

Worked example. A service account normally performs 100, 110, 90, 105, and 95 queries per hour. The mean is 100 queries. If the sample standard deviation is approximately 7.91 and the current count is 130, then:

This is unusual relative to the small baseline sample and warrants investigation. It does not by itself establish malicious activity. The analyst should check deployment schedules, batch jobs, query types, returned volume, source host, and data sensitivity.

5.2 More robust approaches

For skewed data, medians and percentiles may be more useful than means and standard deviations. A rolling baseline can adapt to gradual change, while a fixed reference period may better expose slow misuse. Detection systems can also use:

• peer-group comparison, such as comparing one analyst with similar analysts;
• sequence analysis, such as login → privilege change → export;
• entity and user behaviour analytics;
• clustering of similar activity;
• supervised machine learning where labelled examples are available.

Machine-learning output should be treated as a decision-support signal. Models can reproduce historical bias, misclassify new legitimate behaviour, and become less accurate when systems or user roles change. Explanations, human review, documented features, and controlled model updates are essential in security operations.

6. Evidential Integrity and Investigative Practice

6.1 Integrity controls

Evidence must be protected from unauthorised modification. Common controls include access restriction, append-only storage, write-once or immutable retention, separate administrative roles, encrypted transport, and cryptographic hashes.

A cryptographic hash is a fixed-length value computed from content. If the content changes, the hash should change with high probability. For a log file F, a digest can be represented as:

where H is a cryptographic hash function and h is the resulting digest. A later recomputation compares the new digest with the recorded digest. Hashes provide integrity evidence; they do not prove who created the file, when it was created, or that the original source was honest. Those questions require provenance, trusted timestamps, access records, and chain-of-custody controls.

6.2 Chain of custody

A chain-of-custody record documents the handling of evidence from collection to presentation. It should identify the evidence, collector, date and time, source, method, storage location, transfers, access, and integrity verification. Investigators should work from a preserved copy and retain the original in controlled storage.

Collection should minimise alteration. Where possible, export through approved interfaces, record tool versions and commands, preserve raw records, and document filtering or conversion. Analysts must distinguish facts observed in the evidence from interpretations and hypotheses.

6.3 Investigative workflow

A practical workflow is:

1. Define the question and scope.
2. Identify relevant systems, identities, objects, and time ranges.
3. Preserve volatile and high-risk evidence.
4. Acquire raw logs and verify integrity.
5. Normalise copies for searching.
6. Build a timeline using event, collection, and indexing times.
7. Correlate identity, endpoint, network, application, and database evidence.
8. Test alternative explanations, including maintenance and automation.
9. Record findings, uncertainty, gaps, and confidence.
10. Report, remediate, and improve controls.

7. Governance, Privacy, and Compliance

Monitoring is a security control but also a form of observation of people and organisations. Audit programmes should have a defined purpose, lawful basis where applicable, access restrictions, retention limits, and transparent governance. Privacy principles commonly relevant to logging include data minimisation, purpose limitation, accuracy, restricted access, and secure disposal.

Logs may contain usernames, addresses, query text, customer identifiers, or accidental sensitive data. Organisations should mask or tokenise values where full content is not necessary. They should avoid recording secrets, authentication tokens, or unnecessary personal information. Access to logs should be granted according to role, and investigators should be accountable for their own searches.

Regulatory obligations vary by jurisdiction and sector. Some frameworks require evidence of access review, privileged activity monitoring, retention, incident response, or protection of personal data. Compliance should not be reduced to collecting large quantities of logs. An organisation must be able to demonstrate that records are reliable, reviewed appropriately, and connected to risk management.

Segregation of duties is important. Database administrators should not be able unilaterally to disable auditing and erase evidence. Security analysts may investigate events but should not automatically receive unrestricted access to the underlying personal data. Emergency access should be time-limited, approved, and reviewed afterwards.

8. Illustrative Case: Unauthorised Customer-Data Export

Background

A fictional online retailer operates a production database containing customer contact information and order history. Database administrators use privileged accounts, while applications connect through pooled service identities. The organisation has native database auditing, a SIEM, endpoint monitoring, and immutable log storage.

Problem

The data-protection team receives a notification that a large file was downloaded from a reporting server at 02:14. The scheduled reporting window normally runs at 02:00, but the download is six times larger than the usual report.

Analysis

The SIEM first correlates the download with database events. A service account executed several queries against customer tables and then performed an export. The source host was a reporting server, so the activity initially appeared legitimate. Further analysis shows that, 18 minutes earlier, an administrator account logged in interactively to that server from a workstation not normally used for database administration.

Endpoint logs show a newly installed remote-access tool on the workstation. Identity records show that the administrator’s multi-factor authentication was approved from an unfamiliar device. Database audit records show a temporary privilege grant to the reporting service account. The native audit trail contains the statements and outcomes; the SIEM supplies identity and endpoint context.

Approach and solution

The response team preserves the relevant database, identity, endpoint, and network records. Hashes are calculated for exported evidence, and chain-of-custody entries are created. The team disables the affected credentials, isolates the workstation, revokes the temporary privilege, and blocks the remote-access tool. They verify whether the exported file was transferred outside the organisation.

The organisation then improves controls by requiring just-in-time privileged access, restricting reporting-server administration paths, alerting on interactive use of service accounts, limiting export volume, and correlating privilege grants with subsequent data access. It also modifies the application so that a human identity is recorded alongside the pooled database identity.

Outcome

The investigation concludes that the service account activity was not a normal report, but an attacker-operated export enabled through compromised administrative access. The centralised and immutable audit records support a reliable timeline. The organisation cannot rely on the database username alone; identity propagation and cross-system correlation are essential.

Learning points

1. A normal source host does not make an action normal.
2. Service accounts require behavioural monitoring and strong governance.
3. Privilege changes are high-value correlation events.
4. Database, identity, endpoint, and network evidence complement one another.
5. Preserving raw records and documenting handling strengthens conclusions.

9. Implementation Best Practices

A practical implementation programme should:

• classify databases and data according to business risk;
• define audit requirements for authentication, privilege, schema, sensitive access, exports, and configuration;
• enable database-native auditing with tested policies;
• forward records to a separate, protected collector;
• synchronise clocks and retain source, event, and collection times;
• preserve raw events while normalising searchable copies;
• monitor the health of collection pipelines;
• establish role- and application-specific baselines;
• begin with high-confidence, high-impact alerts;
• tune alerts using measured false-positive and response data;
• protect logs with least privilege, encryption, immutability, and separation of duties;
• document retention, privacy, access, and disposal decisions;
• test investigations through exercises and controlled simulations;
• review audit rules after incidents, system changes, and regulatory updates.

Challenges and limitations

Logging has costs. Detailed auditing can consume storage, processing capacity, and network bandwidth. Poorly designed policies can degrade database performance or generate overwhelming noise. Encrypted traffic, connection pooling, shared accounts, clock errors, missing fields, and lost events can reduce investigative value. Attackers may attempt to disable logging, use legitimate credentials, perform low-and-slow activity, or exploit gaps between systems.

Analytics also has limitations. A statistically unusual action may be legitimate, while a carefully planned attack may resemble normal activity. Thresholds are context-dependent, and machine-learning systems require reliable data and ongoing validation. No logging programme compensates for weak access control, unpatched systems, or inadequate incident response.

Chapter Summary

Logging records events; auditing defines the evidence required for accountability; DAM evaluates database behaviour; and SIEM platforms correlate database events with identity, endpoint, application, network, and cloud evidence. Effective audit records identify time, identity, action, object, source, result, and relevant context. Centralisation, clock synchronisation, secure transport, immutable retention, and monitoring of the collection pipeline are fundamental controls.

Baselines should be segmented by role, application, source, and time. Rules, statistical measures, sequence analysis, and machine-learning techniques can identify suspicious behaviour, but anomalies require investigation rather than automatic assumptions of compromise. Hashes help demonstrate that records were not altered, while provenance and chain of custody establish how evidence was collected and handled. Privacy, minimisation, segregation of duties, and lawful governance must shape the entire design.

Key Terms

Audit trail: A structured record of events maintained for accountability, investigation, or compliance.

Baseline: A description of expected activity for a defined entity, system, and period.

Chain of custody: Documentation of evidence collection, handling, transfer, access, and preservation.

Correlation: Combining related events to obtain more meaningful security context.

Database activity monitoring (DAM): Monitoring and analysing activity involving databases.

Event time: The time at which an activity occurred at its source.

Hash: A fixed-length digest calculated from data for integrity comparison.

Indicator: An observable condition associated with a security concern.

Immutable storage: Storage designed to prevent alteration or deletion during a defined retention period.

Log: A record of an event generated by a system, application, or device.

Normalisation: Mapping different event formats into a common searchable structure.

Privilege escalation: Obtaining permissions beyond those previously authorised.

Raw event: The original record received from a source before analytical transformation.

Security information and event management (SIEM): A platform that collects, normalises, correlates, searches, and alerts on security events.

Service account: An identity used by an application, process, or automated service.

Standard deviation: A measure of the spread of numerical observations around their mean.

Time synchronisation: Coordinating system clocks with an approved time source.

True positive: An alert that correctly identifies activity of security interest.

False positive: An alert incorrectly identifying benign activity as suspicious.

Review Questions

Short Answer Questions

1. Distinguish between a database log, an audit trail, DAM, and a SIEM.
2. List six fields that should normally appear in a useful database audit event.
3. Why should audit records be forwarded to a separate system?
4. What is the purpose of a behavioural baseline?
5. What can a cryptographic hash demonstrate, and what can it not demonstrate by itself?

Long Answer Questions

1. Explain how you would design an audit policy for a production database containing sensitive personal information.
2. Discuss the advantages and limitations of database-native, network-based, and agent-based monitoring.
3. Explain how SIEM correlation can connect a privilege change to a suspicious data export.
4. Describe an evidence-preservation workflow for a suspected database compromise.
5. Evaluate the privacy, governance, and segregation-of-duties issues involved in centralised security logging.

Analytical / Application Questions

1. A service account normally performs between 90 and 110 queries per hour but suddenly performs 600 queries. Identify the additional evidence you would collect before classifying the event as malicious.
2. A database audit trail shows a successful export by a legitimate administrator at 03:00. Construct a correlation plan using identity, endpoint, network, and change-management data.
3. Your SIEM receives millions of database events but analysts miss high-risk privilege changes. Propose a redesign covering event selection, normalisation, prioritisation, alert tuning, and performance measurement.

Multiple Choice Questions with Answers

Question 1

Which capability primarily correlates database events with identity, endpoint, and network events?

A. Database transaction logging

B. Security information and event management

C. Database indexing

D. Backup compression

Correct Answer: B. Security information and event management

Question 2

Which audit record is the most useful for accountability?

A. Permission changed

B. Query executed

C. Account analyst7 granted SELECT on customer_health by administrator session S4812 from 10.20.4.15 at a stated UTC time

D. Database active

Correct Answer: C. Account analyst7 granted SELECT on customer_health by administrator session S4812 from 10.20.4.15 at a stated UTC time

Question 3

Why should audit records normally be forwarded to a separate protected system?

A. To make SQL queries execute faster

B. To prevent the only evidence copy being altered on the database host

C. To eliminate the need for access control

D. To remove all personal information automatically

Correct Answer: B. To prevent the only evidence copy being altered on the database host

Question 4

What is a major limitation of a cryptographic hash of a log file?

A. It cannot detect any change to the file

B. It proves the identity and honesty of the original creator

C. It provides integrity comparison but does not by itself establish provenance

D. It prevents unauthorised access to the file

Correct Answer: C. It provides integrity comparison but does not by itself establish provenance

Question 5

A SIEM alert combines a new privilege grant, access to sensitive tables, and a large export. This is an example of what?

A. Event correlation

B. Clock synchronisation

C. Data minimisation

D. Storage deduplication

Correct Answer: A. Event correlation

Question 6

In the formula z = (x − μ) ÷ σ, what does μ represent?

A. The current observation

B. The baseline mean

C. The number of observations

D. The event-ingestion delay

Correct Answer: B. The baseline mean

Question 7

A monitoring system records database activity only by a pooled service account. What important investigative context may be missing?

A. The database schema version

B. The original human or application requester

C. The storage medium capacity

D. The encryption algorithm name

Correct Answer: B. The original human or application requester

Question 8

Which practice best reduces alert fatigue?

A. Alert on every successful query

B. Disable all low-severity alerts permanently

C. Group related events and tune rules using measured false-positive results

D. Ignore scheduled maintenance activity without documentation

Correct Answer: C. Group related events and tune rules using measured false-positive results

Question 9

What is the main purpose of time synchronisation in a logging architecture?

A. To encrypt database traffic

B. To make correlated event timelines comparable

C. To increase query throughput

D. To delete old audit records

Correct Answer: B. To make correlated event timelines comparable

Question 10

Which statement about anomaly detection is correct?

A. An anomaly automatically proves an attack

B. A normal event cannot be unauthorised

C. Anomaly results are signals that require contextual investigation

D. Statistical methods eliminate the need for audit policies

Correct Answer: C. Anomaly results are signals that require contextual investigation