Configuring an Audit Log to Capture Database Activities for Amazon RDS for MySQL and Amazon Aurora with MySQL Compatibility

Overview

More Info

Organizations enhance their security and monitoring capabilities through database audits to ensure adherence to well-architected frameworks. Security teams and database administrators often conduct comprehensive analyses of access and modification patterns related to data or metadata within their databases.

During an audit, you might consider the following questions:

• Who accessed or modified the data?
• When was the data accessed or modified?
• How did a specific user gain access to the data?
• Was the change to the database table sanctioned before implementation?
• Are privileged users misusing their superuser rights?

To address these questions during an audit, organizations must implement systems that monitor and ensure comprehensive logging in a format compatible 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 supports advanced auditing.

In this article, we will guide you through configuring audit logs to capture database activities for both Amazon RDS for MySQL and Amazon Aurora MySQL DB engines, complete with detailed examples. We will cover the following use cases for enabling audit events:

• Activating an audit for a single event, such as QUERY_DML
• Activating an audit for multiple events like CONNECT, QUERY, etc.

Prerequisites

Before proceeding, ensure you complete the following prerequisites:

• Create a database instance using one of the following AWS CloudFormation templates:
  • Amazon RDS for MySQL
  • Amazon Aurora for MySQL
• Be mindful that you will incur charges for any AWS resources (like Amazon RDS for MySQL and CloudWatch) created while using a CloudFormation template, just as you would if you created these resources manually. For guidance on creating the database via the AWS Management Console for either Amazon RDS for MySQL or Amazon Aurora MySQL, see Create a DB instance or Creating a DB cluster and connecting to a database on an Aurora MySQL DB cluster respectively.
• If using Amazon RDS for MySQL, create a custom option group to enable MARIADB_AUDIT_PLUGIN; if using Amazon Aurora MySQL, create a custom parameter group.
• Connect to a DB instance using the MySQL client.

Activating the MariaDB Audit Plugin for Amazon RDS for MySQL

By default, the option group for Amazon RDS for MySQL does not have audit configuration enabled. The default settings cannot be changed; therefore, a custom option group must be created to add an option.

1. In the Amazon RDS console, select Option groups.
2. Click Create option group.
3. For Name, input a name (e.g., custom-option-group-mysql).
4. For Description, provide a brief description.
5. For Engine, select mysql.
6. For Major Engine Version, choose your engine version (for this post, 7).
7. Click Create.

On the Option groups page, select your option group and choose Add option.

8. For Option name, select MARIADB_AUDIT_PLUGIN.
9. In the Option settings section, adjust the required parameter values as necessary.
10. For Apply immediately, select Yes. Applying these settings immediately does not require downtime.
11. Select Add option.

Next, associate this option group with an existing Amazon RDS for MySQL instance.

12. In the Amazon RDS console, select your instance.
13. From the Actions menu, click Modify.
14. In the Database options section, select your option group.
15. For When to apply modifications, choose Apply immediately to prevent unexpected downtime.
16. Click Modify DB instance.

After the instance restarts, you have successfully enabled the MariaDB audit plugin.

To verify 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 the following output:

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

For further details on global variables, refer to the SHOW VARIABLES Statement.

Activating Advanced Auditing in Amazon Aurora MySQL

To enable advanced auditing in Amazon Aurora MySQL, you first need to create a custom DB cluster parameter group if one does not already exist.

1. In the Amazon RDS console, choose Parameter groups.
2. Click Create parameter group.
3. For Parameter group family, select aurora-mysql5.7.
4. For Group name, provide a name (e.g., aurora-db-cluster-57).
5. Click Create.

Next, associate your DB cluster parameter group with an existing Amazon RDS instance.

6. In the Amazon RDS console, select your instance.
7. From the Actions menu, click Modify.
8. Under Additional configuration, select the parameter group you created for DB cluster parameter group.
9. For When to apply modifications, select Immediately to avoid waiting for the next maintenance window. Note that immediate changes will restart the database.
10. Click Modify cluster.

On the Parameter groups page, select your parameter group.

11. For Values, modify the parameter to enable or disable advanced auditing.
12. Click Save changes.

You have now successfully enabled advanced auditing.

To confirm the status, run the following SQL command on the MySQL console:

mysql> show variables like '%server_audit_logging%';

You should again see:

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

Configuring the Audit Log to Capture Database Activities

The configuration process for the audit option is similar for both Amazon RDS for MySQL and Amazon Aurora MySQL. This section details how to set up the audit option for various database activities. A database activity is defined as server_audit_events, which contains a comma-delimited list of events to log without any white space between the elements. You can log combinations of the following events:

• CONNECT – Logs successful, failed connections, and disconnections, including user information.
• QUERY – Logs all query text and query results in plain text, including failed queries due to syntax or permission errors.
• QUERY_DCL – Similar to QUERY, but logs only DCL-type queries (GRANT, REVOKE, etc.).
• QUERY_DDL – Similar to QUERY, but logs only DDL-type queries (CREATE, ALTER, etc.).
• QUERY_DML – Similar to QUERY, but logs only DML-type queries (INSERT, UPDATE, etc.).
• TABLE – Logs the tables affected by executing a query. This option is exclusively supported in advanced auditing for Amazon Aurora MySQL.

For more information on enhancing your database auditing procedures, you might also find it beneficial to explore job opportunities at Amazon, which offers excellent resources related to database management.

Location

Amazon IXD – VGT2
6401 E Howdy Wells Ave,
Las Vegas, NV 89115.