Edit

Share via

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

  • Article

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:

  • Configure auditing by using the Azure portal or the Azure CLI
  • Set up diagnostics
  • View audit logs by using Log Analytics
  • View audit logs by using workbooks

Prerequisites

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.

    Screenshot showing the 'Server parameters' list.

  4. For the audit_log_enabled parameter, select ON.

    Screenshot showing the 'audit_log_enabled' parameter switched to 'ON'.

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

    Screenshot of the event options in the 'audit_log_events' dropdown list.

  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.

    Screenshot showing the MySQL usernames to be included or excluded from logging.

  7. Select Save.

    Screenshot of the 'Save' button for saving changes in the parameter values.

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

Note

You should create your data sinks before you configure the diagnostics settings. You can access the audit logs in the data sinks you've configured. It can take up to 10 minutes for the logs to appear.

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

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

    Screenshot of the 'Add diagnostic setting' link on the 'Diagnostic settings' pane.

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

    Screenshot of the 'Diagnostics settings' pane for selecting configuration options.

  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.

    Screenshot of the Log Analytics 'Queries' pane.

  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

    Screenshot of an example Log Analytics query seeking to find a summary of audited events on a particular server.

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 Diagnostics settings.

    Screenshot showing the 'Diagnostics settings' tab on the Azure Monitor 'Activity log' pane.

  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.

    Screenshot of the Azure Monitor 'Diagnostic setting' pane for selecting log destinations.

    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.

    Screenshot showing all workbooks in the workbook gallery.

In the workbook, you can view the following visualizations:

  • Administrative Actions on the service
  • Audit Summary
  • Audit Connection Events Summary
  • Audit Connection Events
  • Table Access Summary
  • Errors Identified

Screenshot of workbook template 'Administrative Actions on the service'.

Screenshot of workbook template 'Audit Connection Events'.

Note

  • You can also edit these templates and customize them according to your requirements. For more information, see the "Editing mode" section of Azure Workbooks.
  • For a quick view, you can also pin the workbooks or Log Analytics query to your dashboard. For more information, see Create a dashboard in the Azure portal.

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