Configuring Audit Logs to Monitor Database Activities for Amazon RDS for MySQL and Amazon Aurora with MySQL Compatibility

Learn About Amazon VGT2 Learning Manager Chanci Turner

In today’s digital landscape, organizations enhance their security and compliance measures by conducting database audits to ensure they adhere to well-established frameworks. Security teams and database administrators frequently analyze access and modification patterns related to data and meta-data within their databases.

During audits, you might consider questions such as:

• Who accessed or modified the data?
• When was the data accessed or modified?
• How did a particular user gain access to the data?
• Was the modification to the database table authorized prior to being made?
• Are privileged users misusing their superuser access?

To effectively address these inquiries during an audit, organizations need systems in place that monitor activities and guarantee adequate data logging in a format that can be integrated with external systems like Amazon CloudWatch. For database auditing, Amazon Relational Database Service (Amazon RDS) for MySQL utilizes the MariaDB audit plugin, while Amazon Aurora MySQL-Compatible Edition offers advanced auditing capabilities.

In this article, we will guide you through the configuration of audit logs to track database activities for Amazon RDS for MySQL and Amazon Aurora MySQL engines, complete with detailed examples. We will cover the steps for both database engines and explore use cases for enabling audit events, including:

• Activating an audit for a single event such as QUERY_DML.
• Enabling audits for multiple events, including CONNECT and QUERY.

Prerequisites

Before proceeding, ensure you complete the following steps:

1. Create a database instance using one of the following AWS CloudFormation templates:
   • Amazon RDS for MySQL
   • Amazon Aurora for MySQL
2. If you are using Amazon RDS for MySQL, establish a custom option group to select MARIADB_AUDIT_PLUGIN. For Amazon Aurora MySQL users, create a custom parameter group.
3. Connect to a DB instance using the MySQL client.

Activating the MariaDB Audit Plugin for Amazon RDS for MySQL

For Amazon RDS for MySQL, the default option group does not have audit configuration enabled. Since the default settings cannot be altered, you must create a custom option group and add the necessary options.

1. In the Amazon RDS console, select “Option groups.”
2. Click on “Create option group.”
3. Enter a name (e.g., custom-option-group-mysql) and a description.
4. Choose “mysql” for Engine and select your engine version (e.g., 7).
5. Click “Create.”

Next, add the audit option:

1. On the Option groups page, select your option group and click “Add option.”
2. For Option name, select MARIADB_AUDIT_PLUGIN.
3. Modify the required parameter values in the Option settings section as needed. For “Apply immediately,” select Yes.
4. Click “Add option.”

Now, link the option group to your existing Amazon RDS for MySQL instance:

1. In the Amazon RDS console, choose your instance.
2. From the Actions menu, select “Modify.”
3. In the Database options section, for Option group, select your group.
4. Choose “Apply immediately” to prevent any unexpected downtime.
5. Click “Modify DB instance.”

After the instance restarts, the MariaDB audit plugin will be activated.

To confirm the status of the audit plugin, execute the following query in the MySQL command line:

mysql> show global variables like '%server_audit_logging%';

You should see:

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| server_audit_logging | ON    |
+----------------------+-------+

Enabling Advanced Auditing in Amazon Aurora MySQL

To activate advanced auditing in Amazon Aurora MySQL, you must first create a custom DB cluster parameter group if you haven’t done so already.

1. In the Amazon RDS console, select “Parameter groups.”
2. Click “Create parameter group.”
3. For Parameter group family, select aurora-mysql5.7.
4. Enter a name (e.g., aurora-db-cluster-57) and click “Create.”

Next, associate your DB cluster parameter group with an existing RDS for MySQL instance:

1. In the Amazon RDS console, select your instance.
2. From the Actions menu, click “Modify.”
3. Under Additional configuration, choose the parameter group you created.
4. Select “Immediately” for When to apply modifications to avoid waiting for the next maintenance window (note that this will restart the database).
5. Click “Modify cluster.”

On the Parameter groups page, select your parameter group and modify the parameter to enable or disable advanced auditing. Click “Save changes.”

To verify that advanced auditing is activated, run the following SQL command:

mysql> show variables like '%server_audit_logging%';

You should receive a similar output as before, confirming that the feature is enabled.

Configuring the Audit Log to Capture Database Activities

Setting up the audit option is quite similar for both Amazon RDS for MySQL and Amazon Aurora MySQL. This section outlines how to configure the audit option for various database activities. A database activity is defined by server_audit_events, which is a comma-separated list of events to log, without whitespace between the items. You can log a combination of the following events:

• CONNECT – Logs successful and failed connections as well as disconnections, including user information.
• QUERY – Captures all query text and results in plain text, encompassing queries that may fail due to syntax or permission issues.
• QUERY_DCL – Similar to QUERY, but logs only DCL-type queries (e.g., GRANT, REVOKE).
• QUERY_DDL – Similar to QUERY, but logs only DDL-type queries (e.g., CREATE, ALTER).
• QUERY_DML – Similar to QUERY, but logs only DML-type queries (e.g., INSERT, UPDATE).
• TABLE – Logs the tables impacted by queries, applicable only in advanced auditing for Amazon Aurora MySQL.

For further insights on diversity and accountability, refer to this informative source that provides authoritative content on the subject. Additionally, for a practical guide, check out this excellent resource.

SEO Metadata