Tutorial: Configure audit logs by using Azure Database for MySQL - Flexible Server

APPLIES TO: Azure Database for MySQL - Flexible Server

You can use Azure Database for MySQL flexible server to configure audit logs. Audit logs can be used to track database-level activity, including connection, administration, data definition language (DDL), and data manipulation language (DML) events. These types of logs are commonly used for compliance purposes. You ordinarily use database auditing to:

• Account for all actions that happen within a particular schema, table, or row, or that affect specific content.
• Prevent users (or others) from inappropriate actions based on their accountability.
• Investigate suspicious activity.
• Monitor and gather data about specific database activities.

This article discusses how to use MySQL audit logs, Log Analytics tools, or a workbook template to visualize auditing information for Azure Database for MySQL flexible server.

In this tutorial, you'll learn how to:

Prerequisites

• Create an Azure Database for MySQL flexible server instance.
• Create a Log Analytics workspace.

Configure auditing by using the Azure portal

1. Sign in to the Azure portal.

2. Select your flexible server instance.

3. On the left pane, under Settings, select Server parameters.

   

4. For the audit_log_enabled parameter, select ON.

   

5. For the audit_log_events parameter, in the dropdown list, select the event types to be logged.

   

6. For the audit_log_exclude_users and audit_log_include_users parameters, specify any MySQL users to be included or excluded from logging by providing their MySQL usernames.

   

7. Select Save.

   

Configure auditing by using the Azure CLI

Alternatively, you can enable and configure auditing for your flexible server from the Azure CLI by running the following command:

# Enable audit logs
az mysql flexible-server parameter set \
--name audit_log_enabled \
--resource-group myresourcegroup \
--server-name mydemoserver \
--value ON

Set up diagnostics

Audit logs are integrated with Azure Monitor diagnostics settings to allow you to pipe your logs to any of three data sinks:

• A Log Analytics workspace
• An event hub
• A storage account

1. On the left pane, under Monitoring, select Diagnostic settings.

2. On the Diagnostics settings pane, select Add diagnostic setting.

   

3. In the Name box, enter a name for the diagnostics setting.

   

4. Specify which destinations (Log Analytics workspace, an event hub, or a storage account) to send the audit logs to by selecting their corresponding checkboxes.

   Note

   For this tutorial, you'll send the audit logs to a Log Analytics workspace.

5. Under Log, for the log type, select the MySqlAuditLogs checkbox.

6. After you've configured the data sinks to pipe the audit logs to, select Save.

View audit logs by using Log Analytics

1. In Log Analytics, on the left pane, under Monitoring, select Logs.

2. Close the Queries window.

   

3. In the query window, you can write the query to be executed. For example, to find a summary of audited events on a particular server, we've used the following query:

   AzureDiagnostics
       |where Category =='MySqlAuditLogs' 
       |project TimeGenerated, Resource, event_class_s, event_subclass_s, event_time_t, user_s ,ip_s , sql_text_s 
       |summarize count() by event_class_s,event_subclass_s 
       |order by event_class_s 
   

   

View audit logs by using workbooks

The workbook template that you use for auditing requires you to create diagnostics settings to send platform logs.

1. In Azure Monitor, on the left pane, select Activity log, and then select Export Activity Logs.

   

2. On the Diagnostic setting pane, you can add a new setting or edit an existing one. Each setting can have no more than one of each destination type.

   

   Note

   You can access the slow query logs in the data sinks (Log Analytics workspace, storage account, or event hub) that you've already configured. It can take up to 10 minutes for the logs to appear.

3. In the Azure portal, on the left pane, under Monitoring for your Azure Database for MySQL flexible server instance, select Workbooks.

4. Select the Auditing workbook.

   

In the workbook, you can view the following visualizations:

The Administrative Actions on the service view gives you details on activity performed on the service. It helps to determine the what, who, and when for any write operations (PUT, POST, DELETE) that are performed on the resources in your subscription.

You can use other visualizations to help you understand the details of database activity. Database security has four parts:

• Server security: Responsible for preventing unauthorized personnel from accessing the database.
• Database connection: The administrator should check to see whether any database updates have been performed by authorized personnel.
• Table access control: Shows the access keys of the authorized users and what tables within the database each is authorized to handle.
• Database access restriction: Particularly important for those who've uploaded a database to the internet, and helps prevent outside sources from getting access to your database.

Next steps

• Learn more about Azure Monitor workbooks and their rich visualization options.
• Learn more about audit logs.