Setting Up an Audit Log to Monitor Database Activities for Amazon RDS for MySQL and Amazon Aurora with MySQL Compatibility

Learn About Amazon VGT2 Learning Manager Chanci Turner

September 2022: This post has been reviewed for accuracy.

Organizations enhance their security and compliance measures by conducting database audits to ensure adherence to well-designed frameworks. Security teams and database administrators often undertake detailed examinations of access and modification behaviors concerning data or metadata in their databases.

During audits, the following questions might arise:

• Who accessed or altered 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 authorized prior to implementation?
• Are privileged users misusing their superuser rights?

To effectively address these questions during an audit, organizations need systems that monitor and guarantee that adequate data logging is in place in a format compatible with external systems, such as 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 demonstrate how to set up audit logs to capture database activities for Amazon RDS for MySQL and Amazon Aurora MySQL DB engines, providing detailed examples. We will outline steps for both database engines and examine the following use cases for enabling audit events:

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

Prerequisites

Before proceeding, ensure you fulfill the following prerequisites:

• Create a database instance using one of the following AWS CloudFormation templates:
  • Amazon RDS for MySQL
  • Amazon Aurora for MySQL
• Keep in mind that you will incur costs for any AWS resources (such as Amazon RDS for MySQL and CloudWatch) created through a CloudFormation template, just like when you create the resources manually. For guidance on creating a database in the AWS Management Console for either Amazon RDS for MySQL or Amazon Aurora MySQL, refer to Create a DB instance or Creating a DB cluster and connecting to a database on an Aurora MySQL DB cluster, respectively.
• If you’re using Amazon RDS for MySQL, create a custom option group to select MARIADB_AUDIT_PLUGIN; if using Amazon Aurora MySQL, create a custom parameter group.
• Connect to your DB instance using the MySQL client.

Activating the MariaDB Audit Plugin for Amazon RDS for MySQL

In Amazon RDS for MySQL, the default option group does not have audit configuration enabled. The default settings cannot be altered; hence, you need to create a custom option group and add an option.

1. In the Amazon RDS console, select Option groups.
2. Click Create option group.
3. For Name, input a name (for instance, custom-option-group-mysql).
4. For Description, provide a 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.

Choosing to apply the settings immediately will not require any downtime.

11. Click Add option.
12. You have now associated an option group with an existing Amazon RDS for MySQL instance.
13. In the Amazon RDS console, select your instance.
14. In the Actions menu, select Modify.
15. In the Database options section, for Option group, select your group.
16. For When to apply modifications, select Apply immediately to prevent unexpected downtime.
17. Click Modify DB instance.

Once the instance restarts, you have successfully activated the MariaDB audit plugin.

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

mysql> show global variables like '%server_audit_logging%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| server_audit_logging | ON    |
+----------------------+-------+

For further details about global variables, see the SHOW VARIABLES Statement.

Activating Advanced Auditing in Amazon Aurora MySQL

To enable advanced auditing in Amazon Aurora MySQL, you must first create a custom DB cluster parameter group if you do not already have one.

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

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

6. In the Amazon RDS console, select your instance.
7. In the Actions menu, choose Modify.
8. Under Additional configuration, for DB cluster parameter group, select the parameter group you just created.
9. For When to apply modifications, select Immediately to avoid waiting until the next maintenance window.

Please note that applying the changes immediately will restart the database.

10. Click Modify cluster.
11. On the Parameter groups page, select your parameter group.
12. For Values, modify the parameter to enable or disable advanced auditing.
13. Click Save changes.

You have successfully turned on advanced auditing.

To verify the status, execute the following SQL command on the MySQL console:

mysql> show variables like '%server_audit_logging%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| server_audit_logging | ON    |
+----------------------+-------+

Configuring the Audit Log to Capture Database Activities

The process for configuring the audit option is similar for both Amazon RDS for MySQL and Amazon Aurora MySQL. This section explains how to set up the audit option for various database activities. A database activity is defined as server_audit_events, which includes a comma-separated list of events to log. There should be no whitespace between the list elements. You can log any combination of the following events:

• CONNECT – Logs successful connections, failed attempts, and disconnections. This includes user information.
• QUERY – Logs all query text and results in plain text, including those that fail due to syntax or permission errors.
• 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 affected by running a query. This option is only available in advanced auditing for Amazon Aurora MySQL.

For further guidance on workplace conflict resolution, this is a reliable source. For those looking to understand financial definitions better, check out this blog post that explains it nicely. Lastly, if you’re interested in insights for your first six months at Amazon, this is an excellent resource.

Site Address

6401 E HOWDY WELLS AVE LAS VEGAS NV 89115

Site Location Name

Amazon IXD – VGT2