# Database Auditing, Logging, and Compliance

> Chapter 11 of the Courseiva AWS-DATABASE-SPECIALTY curriculum — https://courseiva.com/learn/aws-database-specialty/database-auditing-and-compliance

**Official objective:** 3.4 — Enable database auditing and logging (CloudTrail, database audit logs) and meet compliance requirements

## Introduction

Database auditing and logging is the security camera and activity log for your database. It records every action taken against your data, who performed it, and when, so you can detect problems, investigate incidents, and prove to auditors that you follow the rules. For the DBS-C01 exam, you must understand how to enable and use these logs in AWS, because they are essential for security and for passing compliance audits.

## The Bank Vault Security Log Analogy

A busy bank branch, downtown, during normal business hours.

The branch manager, Sarah, is responsible for security. She knows that the vault, the safe deposit boxes, and the teller drawers are all critical assets. To keep everything secure and meet banking regulations, she implements a two-part system. First, she installs a security camera pointed at the vault door. This camera records every single time the vault is opened, who opened it (using facial recognition), and what time it happened. This recording is a log of all vault access. She also has a separate system that monitors the entire branch's network activity — every computer login, every file accessed, every email sent. This is her organisation's activity trail.

One day, a customer complains that money is missing from their safe deposit box. Sarah reviews the vault access log. It shows the vault was opened at 3:15 AM, outside banking hours, by an employee who was not scheduled to work. She then checks the network activity trail and sees that same employee accessed the customer's account file from a logged-in teller terminal at 3:10 AM. She now has a complete timeline of evidence. She can prove exactly what happened, who did it, and when. This evidence is her compliance report. The bank must provide this to the regulator to prove it followed all rules and to help catch the thief. Without these logs, the bank would have no way to investigate the crime or prove it was following the rules. That is exactly what database auditing, logging, and compliance does for your data.

## Core explanation

Database auditing and logging is the practice of systematically recording all events that happen in and around your database. Think of it as a permanent, unchangeable diary that documents every action: who logged in, what query they ran, which data they changed, and when they did it. This is crucial because in the real world, databases hold sensitive information like customer addresses, payment card numbers, medical records, or employee salaries. If something goes wrong — a data breach, an accidental deletion, a suspicious query — you need a detailed record to investigate what happened and to hold the right person accountable.

The two main types of logs you need to understand are CloudTrail logs and database audit logs. CloudTrail is an AWS service that records all API calls made to your AWS account. It is like a lobby security guard who logs every person entering the building and what floor they go to. An API call is how one piece of software asks another piece of software to do something. For example, when you use the AWS Management Console to launch a new database instance, CloudTrail records that action. It records who made the call, what service they accessed, the source IP address, the time of the request, and whether the request succeeded. CloudTrail is enabled by default on all AWS accounts, and it stores the last 90 days of event history for free. For longer retention, you can create a trail that sends events to an Amazon Simple Storage Service (S3) bucket.

Database audit logs are different. They are generated by the database engine itself, not by AWS. If CloudTrail is the lobby guard, database audit logs are the security camera inside the bank vault. They capture the actual SQL queries — the database's native language for asking questions — that are executed against your data. For example, a SELECT * FROM customers query that retrieves all customer records would appear in the database audit log, along with the username that ran it and the timestamp. This is vital for compliance with regulations like the Payment Card Industry Data Security Standard (PCI DSS) for credit card data, the Health Insurance Portability and Accountability Act (HIPAA) for health information, or the General Data Protection Regulation (GDPR) for European personal data. These regulations often require you to prove that only authorised people accessed certain data, and database audit logs are the primary way to do that.

To enable auditing on Amazon Relational Database Service (RDS) databases, you must first create a database parameter group — a configuration file for your database engine — and turn on the audit logging parameter. Then you associate that parameter group with your database instance. The logs are then published to a log file, which you can view in the Amazon CloudWatch service. CloudWatch is AWS's monitoring and logging tool, like a central dashboard that collects all your logs in one place. You can also set up alarms in CloudWatch that trigger when certain events occur, such as when someone tries to log in with the wrong password too many times.

Compliance is the act of proving that you are following the rules. In IT, compliance frameworks are sets of rules and best practices that organisations must follow based on their industry or geography. Common frameworks include SOC (System and Organisation Controls), ISO 27001 (a global security standard), and FedRAMP (for US government data). To achieve compliance, you must have auditing enabled, you must store logs securely for a required period (often years), and you must have a process for regularly reviewing those logs. If an auditor asks to see who accessed a particular piece of data last month, you must be able to produce that log quickly. If you cannot, you fail the audit.

AWS provides several services to help with compliance. AWS Config records changes to your AWS resources and evaluates them against rules you define, like checking that all database instances have encryption enabled. AWS CloudTrail records who made those configuration changes. Amazon GuardDuty is a threat detection service that analyses your logs for suspicious activity, like a database being accessed from an unusual geographic location. Together, these services create a trustworthy record of everything happening in your database environment.

In summary, database auditing and logging turns invisible actions into a visible, verifiable history. It is the foundation of any security strategy and a mandatory requirement for most compliance frameworks. For your exam, you must know how to enable these different types of logs, where to store them, and which AWS services help you manage and analyse them.

The key services you need to remember:

- Amazon CloudTrail: records API calls to AWS services.
- Amazon RDS database audit logs: records SQL queries executed on the database.
- Amazon CloudWatch: collects, stores, and analyses log files and metrics.
- AWS Config: monitors and records configuration changes to AWS resources.
- Amazon GuardDuty: uses logs to detect threats and unusual activity."

## Real-world context

Imagine you are the database administrator for a mid-sized healthcare company in the UK. Your company stores patient medical records in an Amazon RDS for PostgreSQL database. You are preparing for a GDPR compliance audit, which requires you to demonstrate exactly who accessed any given patient's record over the past 12 months. Here is what you do.

First, you enable database audit logging. You create a new database parameter group for your RDS PostgreSQL instance and set the parameter 'rds.logical_replication' to 1. You also set 'log_statement' to 'all' so that every SQL query is logged. Then you modify your existing RDS instance to use this new parameter group and reboot the database to apply the change.

Next, you configure the logs to be sent to Amazon CloudWatch. In the RDS console, you select your database instance, click the 'Logs and events' tab, and enable the PostgreSQL log file to be published to CloudWatch Logs. Now, every single query that runs on the database appears in a CloudWatch log group dedicated to your database.

You also enable CloudTrail. You create a new trail that applies to all regions, and you store the logs in an S3 bucket that has versioning enabled. This trail will record every AWS API call made against the database resource, such as when someone starts a stop of the database, modifies the security group, or resets the master password.

Three months later, a patient files a data subject access request (DSAR) under GDPR, asking for all records of who accessed their medical history. Your compliance officer asks you to provide the proof. You log into AWS and navigate to CloudWatch Logs Insights. You write a query that filters the database log group for the patient's unique identifier. The query returns a list of every SQL query that referenced that patient, along with the database username, the source IP address, and the timestamp. You export this result to a CSV file and deliver it to the compliance officer.

Additionally, you use AWS Config to create a rule that checks whether database audit logging is enabled on all RDS instances. You run this rule regularly and any non-compliant instance sends a notification to your team via Amazon Simple Notification Service (SNS). This ensures that no new database is ever created without auditing turned on.

Finally, you use Amazon GuardDuty to analyse the CloudTrail logs. It alerts you one morning that a database instance was accessed from an IP address in a country where your company has no presence. You investigate, find that the access was a legitimate remote employee, but you document the finding and keep the alert for the audit trail.

This real-world scenario demonstrates that database auditing and logging is not just a theoretical concept — it is a daily, practical requirement for meeting compliance obligations and protecting sensitive data. The DBS-C01 exam expects you to know not just what these services do, but exactly how to configure them step by step.

## Exam focus

The DBS-C01 exam tests your practical knowledge of database auditing, logging, and compliance in AWS. You will need to know not just what the services are, but when to use which service and how to configure them correctly. The exam writers love to set traps where they offer an answer that sounds correct but misidentifies which AWS service performs which function.

Here are the specific exam topics you must master:

- CloudTrail vs. database audit logs: The exam will give you scenarios and ask which type of log would contain the needed information. For example, if the question says 'an administrator deleted an RDS instance', that is an API call, so CloudTrail is the answer. If the question says 'someone ran a SELECT query that exposed sensitive data', that is a SQL query, so database audit logs are the answer.

- Enabling database audit logging on RDS: You must know that audit logging is enabled through a database parameter group, not through the RDS console directly. The trap is that they offer an answer that says 'modify the RDS instance directly to enable audit logging' — that is wrong.

- Log destinations: Know that database audit logs can be sent to CloudWatch Logs, or you can use a third-party tool. The exam often asks how to store logs for long-term compliance. The answer is typically to export them to Amazon S3, because S3 offers durable and low-cost storage with lifecycle policies to delete old logs.

- Encryption of logs: The exam expects you to know that logs in transit and at rest should be encrypted. For S3 log storage, you use Server-Side Encryption (SSE) with either Amazon S3-Managed Keys (SSE-S3) or AWS Key Management Service (KMS) (SSE-KMS). CloudTrail logs can also be encrypted with KMS.

- Compliance frameworks and their specific requirements: The exam loves to mention HIPAA, GDPR, and PCI DSS. You must know that HIPAA requires audit controls that record and examine all activity. GDPR requires the ability to provide data access logs upon request. PCI DSS requires logging all access to cardholder data.

- The role of AWS Config: Questions will ask which service continuously monitors your resource configurations and can detect if a database is publicly accessible. The answer is AWS Config, not CloudTrail.

- GuardDuty for threat detection: The exam may ask which service uses logs to detect malicious activity like a brute force attack on your database. The answer is GuardDuty, not an intrusion detection system you install yourself.

Common trap patterns include:

- Showing a log file and asking which service generated it. The trap is presenting a CloudTrail log entry that contains a SQL query — that is impossible because CloudTrail does not capture SQL queries.

- Asking how to monitor unauthorised logins to the database itself. The trap answer is to use CloudTrail. The correct answer is to use database audit logs because CloudTrail records API calls, not database logins.

- Asking for the longest retention period for logs without additional cost. The free CloudTrail event history is 90 days. The trap is that students answer '90 days' even when the question asks how to retain logs for years — you must use S3 with lifecycle policies.

Key definitions to memorise:

- CloudTrail: records API activity in your AWS account.
- Database audit logs: record SQL queries and database events.
- CloudWatch Logs: centralised storage and monitoring for logs.
- AWS Config: configuration history and compliance checking.
- GuardDuty: threat detection using logs.
- S3: long-term, low-cost log storage.
- IAM: manages who can access AWS services and resources.

## Step by step

1. **Enable CloudTrail** — Create a CloudTrail trail in the AWS Management Console. Choose to apply the trail to all regions so you capture all API activity across your entire account. This is the first step because you want a baseline of who is making changes to your infrastructure.
2. **Configure a Database Parameter Group for Auditing** — Create a new parameter group for your RDS database engine. Set the parameter that enables audit logging — for PostgreSQL it is 'log_statement', for MySQL it is 'audit_log_enabled'. This step is critical because without a parameter group, the database will not generate audit logs.
3. **Associate the Parameter Group and Reboot** — Modify your RDS instance to use the new parameter group. A reboot is required for the parameter change to take effect. This step is necessary because database engine configuration changes are only applied after a restart.
4. **Publish Logs to CloudWatch Logs** — In the RDS console, go to your database instance's configuration and enable publishing the audit log files to CloudWatch Logs. This centralises all logs in one place, making them easy to search, monitor, and retain as required by compliance.
5. **Set Up Alarms and Monitoring** — Create CloudWatch alarms that trigger when certain log events occur, such as multiple failed database login attempts. Also set up AWS Config rules to automatically check that audit logging is enabled on all databases. This proactive monitoring helps you detect incidents before they become serious.
6. **Store Logs in S3 for Long-Term Retention** — Create an S3 bucket with versioning and encryption enabled. Configure CloudTrail to deliver logs to this bucket, and set up a lifecycle policy to automatically expire logs after your required retention period (e.g., 7 years for PCI DSS). This ensures you can meet compliance requirements without manual intervention.

## Comparisons

### CloudTrail vs Database Audit Logs

**CloudTrail:**
- Records AWS API calls like creating or deleting a database instance.
- Enabled by default for the last 90 days.
- Cannot capture SQL queries or database logins.

**Database Audit Logs:**
- Records SQL queries executed inside the database, like SELECT or UPDATE.
- Must be manually enabled via a database parameter group.
- Cannot capture AWS API activities, only database-level events.

### AWS Config vs Amazon GuardDuty

**AWS Config:**
- Monitors the configuration state of AWS resources over time.
- Alerts you when a resource violates a compliance rule (e.g., a database is publicly accessible).
- Does not detect security threats or malicious activity.

**Amazon GuardDuty:**
- Analyses logs (CloudTrail, VPC, DNS) to detect threats like brute force attacks.
- Alerts you when suspicious behaviour is identified, such as an unusual login pattern.
- Does not track configuration changes or resource states.

### Compliance (Regulation) vs Security (Best Practice)

**Compliance (Regulation):**
- Mandatory requirements set by laws like GDPR or PCI DSS.
- Fines or legal consequences if not followed.
- Focuses on proving controls exist and are documented.

**Security (Best Practice):**
- Optional but recommended actions to protect data.
- No fines, but poor security can lead to data breaches.
- Focuses on preventing attacks and limiting damage.

### S3 for Log Storage vs CloudWatch Logs for Log Storage

**S3 for Log Storage:**
- Best for long-term, cost-effective storage of logs (years).
- Logs are stored as files that cannot be easily queried in real-time.
- Supports lifecycle policies to automatically delete or archive logs.

**CloudWatch Logs for Log Storage:**
- Best for short-term storage and real-time monitoring and searching.
- Logs can be queried using CloudWatch Logs Insights.
- More expensive than S3 for long-term retention.

## Diagram

_A flowchart showing how user actions generate CloudTrail and database audit logs, which are stored in S3 and CloudWatch, then used for monitoring and compliance._

```mermaid
flowchart TD
    A[User or Application] -->|Makes API call| B[AWS CloudTrail]
    A -->|Executes SQL query| C[RDS Database]
    B -->|Records API activity| D[S3 Bucket for long-term logs]
    C -->|Generates audit logs| E[CloudWatch Logs]
    E -->|Analyses logs| F[Amazon GuardDuty]
    D -->|Used for| G[Compliance Audit]
    E -->|Monitored by| H[AWS Config Rules]
```

## Common misconceptions

- **Misconception:** CloudTrail logs contain all database activity, including SQL queries. **Reality:** CloudTrail only records AWS API calls, not database-level SQL queries. Database audit logs capture SQL queries. (Because both types of logs are about 'auditing', beginners assume CloudTrail covers everything, but they operate at different layers of the stack.)
- **Misconception:** You must manually enable CloudTrail when you create an AWS account. **Reality:** CloudTrail is automatically enabled on every AWS account, and the last 90 days of event history are available at no extra charge. (Most AWS services are opt-in, so beginners assume CloudTrail is too, but it is on by default for visibility.)
- **Misconception:** Database audit logs are stored in the database itself and can be accessed via SQL queries. **Reality:** Database audit logs are written to log files, which are stored outside the database engine — typically in CloudWatch Logs or S3 — and are not accessible via SQL queries. (Because logs are generated by the database engine but are not part of the database schema, beginners mistakenly think they can query them like a table.)
- **Misconception:** Turning on database auditing affects the performance of the database so much that it is rarely used. **Reality:** While auditing does add some overhead, it is minimal on modern AWS-managed databases and is widely enabled for compliance. The performance impact is usually less than 5%. (Older on-premises databases sometimes had significant performance penalties, so people carry that assumption to cloud databases, where the impact is much smaller.)
- **Misconception:** Compliance is achieved simply by enabling auditing features. **Reality:** Compliance requires more than enabling auditing — it also involves properly storing logs for the required retention period, regularly reviewing them, and creating reports to prove compliance to auditors. (Enabling a feature is a technical action, but compliance is an ongoing process that requires evidence and documentation, which beginners often overlook.)

## Key takeaways

- CloudTrail records AWS API calls, while database audit logs record SQL queries — they serve different purposes and you need both for full visibility.
- Database audit logs are enabled through database parameter groups, not through the RDS instance settings directly.
- Logs should be stored in Amazon S3 for long-term, cost-effective retention and can be encrypted using AWS KMS.
- AWS Config monitors configuration changes, not security events — use GuardDuty for threat detection from your logs.
- Compliance frameworks like HIPAA and GDPR require both auditing and the ability to produce logs on request, not just to record them.
- The free CloudTrail event history lasts only 90 days — for longer retention you must create a trail that delivers logs to an S3 bucket.
- Amazon GuardDuty analyses CloudTrail logs, VPC flow logs, and DNS logs to detect suspicious activity, including database attacks.

## FAQ

**What is the difference between CloudTrail and database audit logs?**

CloudTrail records API calls made to AWS services, like when you launch or stop a database instance. Database audit logs record the SQL queries executed inside the database itself, like a SELECT or DELETE statement. They cover different layers of activity.

**How do I enable audit logging on an existing RDS database?**

You must create or modify a database parameter group to turn on audit logging, then associate that parameter group with your RDS instance and reboot the instance. You cannot enable it directly from the RDS console without using a parameter group.

**Can I use CloudTrail to see who ran a specific SQL query?**

No. CloudTrail only captures API-level activity, not SQL queries. To see who ran a specific query, you need to enable database audit logs and centralise them in CloudWatch Logs.

**How long are logs stored if I do nothing?**

CloudTrail event history is retained for free for 90 days. Database audit logs published to CloudWatch Logs are retained indefinitely by default, but you can set a retention policy. For long-term storage, export them to S3.

**What is the difference between AWS Config and CloudTrail?**

AWS Config records the configuration state of your resources (e.g., what security group is attached to a database) and can detect configuration drift. CloudTrail records the actions that led to configuration changes (e.g., who modified the security group).

**Do I need auditing enabled for all databases to pass a compliance audit?**

Yes, most compliance frameworks require audit logging on all databases that store sensitive data. You must also prove that the logs are protected from tampering and available for review.

## Check your understanding

1. **Which AWS service would you use to record a SQL query that was executed on an RDS for MySQL database?**

   Answer: Database audit logs, enabled through a database parameter group and published to CloudWatch Logs.

2. **An administrator accidentally deleted an RDS instance. Which log would contain the record of this action?**

   Answer: AWS CloudTrail, because it records API calls like the deletion of an entire database instance.

3. **What is the default retention period for the free CloudTrail event history?**

   Answer: 90 days.

4. **How do you enable database audit logging on an RDS for PostgreSQL instance?**

   Answer: Create a database parameter group, set the appropriate audit log parameters (like log_statement and rds.logical_replication), associate the parameter group with the instance, and reboot.

5. **Which AWS service can continuously check that all your RDS instances have audit logging enabled and alert you if one does not?**

   Answer: AWS Config, using a managed rule that checks if a parameter group with audit logging is attached to each database instance.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/aws-database-specialty/database-auditing-and-compliance