Configuring an audit log to capture database activities for Amazon RDS for MySQL and Amazon Aurora with MySQL compatibility

September 2022: This post was reviewed for accuracy.

Organizations improve security and tracing postures by going through database audits to check that they’re following and provisioning well-architected frameworks. Security teams and database administrators often perform in-depth analysis of access and modification patterns against data or meta-data in their databases.

During auditing, you may raise 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 approved before the change was made?
• Are privileged users abusing their superuser privileges?

To answer these kinds of questions during an audit, your organization needs to have systems that monitor and ensure that sufficient data logging is in place in a format that external systems can consume, such as Amazon CloudWatch. For database auditing, Amazon Relational Database Service (Amazon RDS) for MySQL supports the MariaDB audit plugin and Amazon Aurora MySQL-Compatible Edition supports advanced auditing.

In this post we show you how to configure audit logs to capture the database activities for Amazon RDS for MySQL and Amazon Aurora MySQL DB engines with detailed examples.  We explain the steps for both DB engines and look at 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, and so on.

Prerequisites

Before getting started, make sure you complete the following prerequisites:

1. Create a database instance using either of the following AWS CloudFormation templates:
   • Amazon RDS for MySQL
   • Amazon Aurora for MySQL

Be aware that you pay for any AWS resources (such as Amazon RDS for MySQL and CloudWatch) created when using a CloudFormation template, the same as when you create the resources manually.

For instructions on creating the database on 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.

2. If you are using Amazon RDS for MySQL, create a custom option group to choose MARIADB_AUDIT_PLUGIN or if you are using Amazon Aurora MySQL, create a custom parameter group.
3. Connect to a DB instance using the MySQL client.

Turning on the MariaDB audit plugin for Amazon RDS for MySQL

For Amazon RDS for MySQL, the default option group doesn’t have audit configuration enabled. The default settings are immutable; to make changes to your instance, you need to create a custom option group and add an option.

1. On the Amazon RDS console, choose Option groups.
2. Choose Create option group.
3. For Name, enter a name (for example, custom-option-group-mysql).
4. For Description, enter a description.
5. For Engine, choose mysql.
6. For Major Engine Version, choose your engine version (for this post, 7).
7. Choose Create.

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

9. For Option name, choose MARIADB_AUDIT_PLUGIN.

10. In the Option settings section, modify the require parameter values as needed.
11. For Apply immediately, select Yes.

Choosing to apply the settings immediately doesn’t require any downtime.

12. Choose Add option.

You now associate an option group with an existing Amazon RDS for MySQL instance.

13. On the Amazon RDS console, select your instance.
14. On the Actions menu, choose Modify.
15. In the Database options section, for Option group, choose your group.

16. For When to apply modifications, select Apply immediately to avoid any unexpected downtime.
17. Choose Modify DB instance.

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

18. To verify the audit plugin status, run the following query in the MySQL command line:

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

For more information about global variables, see SHOW VARIABLES Statement.

Turning on advanced auditing in Amazon Aurora MySQL

To enable the advanced audit in Amazon Aurora MySQL, you must first create a custom DB cluster parameter group, if you don’t already have one.

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

You now associate your DB cluster parameter group with an existing Amazon RDS for MySQL instance.

6. On the Amazon RDS console, select your instance.
7. On the Actions menu, choose Modify.
8. Under Additional configuration, for DB cluster parameter group, choose the parameter group you created.

9. For When to apply modifications, select Immediately to avoid waiting until the next maintenance window.

Be aware that applying the changes immediately restarts the database.

10. Choose 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. Choose Save changes.

You have successfully turned on the advanced auditing.

14. To verify the status, run 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

Configuring the audit option is similar for both Amazon RDS for MySQL and Amazon Aurora MySQL. This section explains how to configure the audit option for different database activities. A database activity is defined as server_audit_events, which contains the comma-delimited list of events to log. There should be no white space between the list elements. You can log any combination of the following events:

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

Use the server_audit_excl_users and server_audit_incl_users parameters to specify which DB users can be audited or excluded from auditing. The following are the possible combinations:

• If server_audit_excl_users and server_audit_incl_users are empty (the default), all users are audited
• If you add users to server_audit_incl_users and leave server_audit_excl_users empty, only those users added to server_audit_incl_users are audited
• If you add users to server_audit_excl_users and leave server_audit_incl_users empty, only those users added to server_audit_excl_users are not audited, and all other users are
• If you add the user to both server_audit_excl_users and server_audit_incl_users, the user is audited as specified in server_audit_incl_users, which takes precedence over server_audit_excl_users

CONNECT events are logged for all users even though the specified user is in the server_audit_excl_users or server_audit_incl_users list.

Enabling an audit for a single event

In this use case, we will enable the audit option for a single audit event: QUERY_DML. To audit DML-type queries, modify the option group for the MariaDB audit plugin (Amazon RDS for MySQL) or parameter group for advanced auditing with server_audit_events as QUERY_DML (Amazon Aurora MySQL).

Amazon RDS for MySQL

To enable an audit for a single event using Amazon RDS for MySQL, complete the following steps:

1. On the Amazon RDS console, choose Option groups.
2. Choose your option group.
3. Under Option setting, for Value, choose QUERY_DML.
4. For Apply immediately, choose Yes.
5. Choose Modify option.

Amazon Aurora MySQL

To enable an audit for a single event using Amazon Aurora MySQL, complete the following steps:

1. On the Amazon RDS console, choose Parameter groups.
2. Choose your parameter group.
3. Under Parameters, for Values, choose QUERY_DML.
4. Choose Save changes.

Verifying event status and logging an event

To verify the event status, run the following query at the MySQL command line:

mysql> SHOW VARIABLES LIKE '%server_audit_events%';
+---------------------+-----------+
| Variable_name       | Value     |
+---------------------+-----------+
| server_audit_events | QUERY_DML |
+---------------------+-----------+

The following code logs the DML audit event:

$ mysql -h <endpoint> -u <user name> -p
Enter password: 
mysql>CREATE database audit_log;
mysql>USE audit_log;
mysql> CREATE TABLE IF NOT EXISTS auditlogtab (id varchar(32),name varchar(100));
mysql> ALTER TABLE auditlogtab CHANGE name name VARCHAR(128);
mysql> SELECT COUNT(*) FROM auditlogtab;
mysql> TRUNCATE TABLE auditlogtab;
mysql> INSERT INTO auditlogtab (id,name) VALUES('I12', 'AUDIT TRAIL');
mysql> UPDATE auditlogtab SET name ='AUDIT TRAIL TEST' WHERE id='I12';
mysql> CREATE USER auditloguser IDENTIFIED BY 'Audit_Pwd@187';
mysql> DROP USER auditloguser;
mysql> DROP TABLE auditlogtab;
mysql> DROP DATABASE audit_log;
mysql> \q

Verifying logs in Amazon RDS for MySQL

To verify your logs for Amazon RDS for MySQL, complete the following steps:

1. On the Amazon RDS console, choose Databases.
2. Choose your DB instance.
3. Under Logs, select your log (audit/server_audit.log).
4. Choose View.

The following screenshot shows the view of your audit log file.

Verifying logs in Amazon Aurora MySQL

To verify the logs for an advanced audit in Amazon Aurora MySQL, complete the following steps:

1. On the Amazon RDS console, choose Databases.
2. Choose your DB instance.
3. Under Logs, select your log (audit/audit.log).
4. Choose View.

The following screenshot shows the view of one of your audit log files.

Enabling an audit for multiple events

In this use case, we will enable the audit option for multiple audit events.

Amazon RDS for MySQL

To log multiple events in Amazon RDS for MySQL, modify the option group for the MariaDB audit plugin. See the previous section for instructions.

Amazon Aurora MySQL

To log multiple events in Amazon Aurora MySQL, modify the parameter group with server_audit_events as CONNECT, QUERY, TABLE, QUERY_DDL, QUERY_DML, and QUERY_DCL. See the previous section for instructions to configure these values.

Verifying event status and logging multiple events

To verify the event status, run the following query at the MySQL command line:


mysql> show variables like '%server_audit_events%';
+---------------------+---------------------------------------------------
| Variable_name | Value
+---------------------+---------------------------------------------------
| server_audit_events | CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL
+---------------------+--------------------------------------------------+

Log multiple audit events with the following code:

$ mysql -h <endpoint> -u <user name> -p
Enter password: 
mysql>CREATE database audit_log;
mysql>USE audit_log;
mysql> CREATE TABLE IF NOT EXISTS auditlogtab (id varchar(32),name varchar(100));
mysql> SELECT count(*) from auditlogtab;
mysql> create view v_auditlogtab as SELECT count(*) from auditlogtab;
mysql> select * from v_auditlogtab;
mysql> TRUNCATE TABLE auditlogtab;
mysql> INSERT INTO auditlogtab (id,name) VALUES('I12', 'AUDIT TRAIL');
mysql> UPDATE auditlogtab SET name ='AUDIT TRAIL TEST' WHERE id='I12';
mysql> CREATE USER auditloguser IDENTIFIED BY 'Audit_Pwd@187';
mysql> DROP USER auditloguser;
mysql> DROP TABLE auditlogtab;
mysql> DROP DATABASE audit_log;
mysql> \q

Verifying logs in Amazon RDS for MySQL

To verify the logs for the MariaDB audit in Amazon RDS for MySQL, complete the following steps:

1. On the Amazon RDS console, choose Databases.
2. Choose your DB instance.
3. Under Logs, select your log (audit/server_audit.log).
4. Choose View.

The following screenshot shows a view of your log file.

Verifying logs in Amazon Aurora MySQL

To verify the logs for an advanced audit in Amazon Aurora MySQL, complete the following steps:

1. On the Amazon RDS console, choose Databases.
2. Choose your DB instance.
3. Under Logs, select your log (audit/audit.log).
4. Choose View.

The following screenshot shows the view of one of your audit log files.

Summary

In this post, we described how to use the MariaDB audit plugin with the different available options to log database activities in an Amazon RDS for MySQL instance. For an Amazon Aurora database, we used a parameter group to enable the auditing feature with the different available options to log database activities. These are proven and tested steps and we hope this post has provided you the basic instructions to enable auditing, improve the security and tracing postures. Give it a try, and let us know what you think through comments on this post. We’re always looking forward to your feedback, either through your usual AWS support contacts, or on the AWS Forum for Amazon RDS.

About the Authors

Shailesh K Mishra is working as Enterprise Solutions Architect with the Global Enterprise team at Amazon Web Services and Area of Depth in Database and migrations. He focuses on database migrations to AWS and helping customers to build well-architected solutions.

Babaiah Valluru is working as Associate Consultant with the Professional Services team at AWS based out of Hyderabad, India and specializes in database migrations. In addition to helping customers in their transformation journey to cloud, his current passion is to explore and learn ML services. He has a keen interest in open source databases like MySQL, PostgreSQL and MongoDB. He likes to travel, and spend time with family and friends in his free time.