Auditing an Amazon Aurora Cluster

At re:Invent, we announced a number of new features for Amazon Aurora, the MySQL-compatible relational database engine that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases.

Today we are happy to announce that our advanced auditing feature is now available to all customers.

What is advanced auditing, and why is it important?

Auditing refers to logging a specified set of events and publishing these logs in a way that can be consumed either manually or by another application. These logs can form the bedrock of your governance and regulatory compliance programs. Examples of advanced auditing include log analysis, auditing of user actions (both past events and near real-time threat detection), and support for configurable alarms set on security-related events. Aurora advanced auditing is designed to provide this functionality with minimal impact on database performance.

Getting started with advanced auditing
To get started, enable advanced auditing and then take a look at the audit logs.

Enabling advanced auditing

You can enable and configure advanced auditing by setting the parameters listed following in the DB cluster parameter group. Modifying these parameters doesn’t require a DB cluster restart, and the behaviors are the same as for Aurora DB instance parameters.

You use the server_audit_logging parameter to enable or disable auditing, and the server_audit_events parameter to specify what events to log.

You use the server_audit_excl_users and server_audit_incl_users parameters to specify who gets audited:

• 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, then 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, then only those users added to server_audit_excl_users are not audited, and all other users are.
• If you add the same users to both server_audit_excl_users and server_audit_incl_users, then those users are audited because server_audit_incl_users is given higher priority.

The following list describes each of the advanced auditing parameters in more detail.

server_audit_logging enables or disables audit logging. It defaults to OFF; set it to ON to enable logging.

• Scope: Global
• Dynamic: Yes
• Data type: Boolean
• Default value: OFF (disabled)

server_audit_events contains the comma-delimited list of events to log. There should be no white space between the list elements.

• Scope: Global
• Dynamic: Yes
• Data type: String
• Default value: Empty string
• Valid values: 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 query execution.

server_audit_excl_users contains the comma-delimited list of users whose activity isn’t logged. There should be no white space between the list elements. Connect and disconnect events aren’t affected by this variable; they are always logged if logging for them is specified. Even if a user is listed in server_audit_excl_users, that user’s actions are logged if that user is also specified in server_audit_incl_users, because that setting has higher priority than server_audit_excl_users.

• Scope: Global
• Dynamic: Yes
• Data type: String
• Default value: Empty string

server_audit_incl_users contains the comma-delimited list of users whose activity is logged. There should be no white space between the list elements. Connect and disconnect events aren’t affected by this variable; they are always logged if logging for them is specified. If a user is listed in server_audit_incl_users, that user is logged even if that user is also specified in server_audit_excl_users, because server_audit_incl_users has higher priority.

• Scope: Global
• Dynamic: Yes
• Data type: String
• Default value: Empty string

Viewing audit logs

You can view the audit logs by using the AWS Management Console. On the Instances page, select and expand the DB cluster, then choose Logs.

If you are familiar with the MariaDB Audit Plugin, you will notice a few differences in Aurora’s approach to auditing.

First, logs are presented somewhat differently:

• The Aurora advanced auditing time stamp is formatted in Unix time format.
• Log events are written to multiple files, and the log records aren’t in sequential order. Customers can concatenate and sort the files using the timestamp and query_id fields to produce a single file with ordered events. You can do this in Unix as follows: cat audit.log.* | sort -t”,” -k1,1 –k6,6
• The number of files might vary based on DB instance size.
• File rotation is fixed at 100 MB and isn’t configurable.

Also, enabling Aurora advanced auditing after migration from MySQL works differently. Audit log configuration is set using the parameter group for the DB cluster.

How does Aurora implement advanced auditing?

Although auditing capability is commonly available in both commercial databases and some open source databases, auditing in these databases typically has a significant impact on performance, especially when the load is high. One of our Aurora implementation’s main goals is to provide users with a rich set of information but without sacrificing performance.

Maintaining performance
To understand how we achieve our performance objective, let’s compare our implementation of advanced auditing to that of the MariaDB Audit Plugin. We use this as our comparison point because MySQL Community Edition doesn’t have a native audit log, and the MariaDB Audit Plugin has emerged as the most popular option to fill this void in the open source community.

MariaDB Audit Plugin uses a single thread with a single mutex for processing and writing each event. Although the design strictly preserves the order of events, it can lead to a performance drop due to the bottleneck in writing logs. If we had used a similar approach for Aurora, the performance impact would have been even more significant due to the much higher scalability and expected throughput of the engine.

To maintain our high-performance advantage, we redesigned the event processing and event writing logic. On the input side, we used a latch-free queue to store audit events without blocking other threads. On the output side, we use multiple threads writing events from the latch-free queue to multiple files. The files can be post-processed to present a complete audit log with events in order.

Log format
The audit log is stored separately on each instance’s local (ephemeral) storage. Each Aurora instance spreads log writes across four log files at a time:

• Encoding: UTF-8
• File name pattern: audit.log.[0-3].[%Y-%m-%d-%H-%M][-rotation]
• Location: /rdsdbdata/log/audit/ (on each host)
• Rotation: Maximum size 100 MB per log file, currently not customer-configurable. When the largest of the four log files reaches 100 MB, the system rotates to a new set of log files.
• Clean-up: The system will clean up older audit files to free up space past a certain space consumption and/or age.
• Log format: [timestamp],[serverhost],[username],[host],[connectionid],[queryid],[operation],[database],[object],[retcode]

How does our approach compare to the alternatives?

As mentioned above, many databases offer audit log capability, but suffer from poor performance when auditing is enabled. We compared the performance of Aurora against MySQL 5.7 with MariaDB Audit Plugin for a select-only workload on an 8xlarge instance. As the results following show, MySQL performance degrades significantly when auditing is turned on, where Aurora only sees modest reduction in performance. There’s a 65% reduction in throughput for MySQL 5.7 as opposed to just 15% for Aurora. In effect, Aurora’s performance edge over MySQL 5.7 more than doubles to over an order of magnitude when auditing is turned on.

Advanced auditing is available now, and you can start using it today! You can learn more about this feature in the advanced auditing documentation.

About the Author

Sirish Chandrasekaran is a product manager for Amazon Aurora at Amazon Web Services.