Track database activity with Audit Logs in Azure Database for MySQL Flexible Server

APPLIES TO: Azure Database for MySQL - Flexible Server

Azure Database for MySQL Flexible Server provides users with the ability to configure audit logs. Audit logs can be used to track database-level activity including connection, admin, DDL, and DML events. These types of logs are commonly used for compliance purposes.

Configure audit logging

Important

It is recommended to only log the event types and users required for your auditing purposes to ensure your server's performance is not heavily impacted and minium amount of data is collected.

By default, audit logs are disabled. To enable them, set the audit_log_enabled server parameter to ON. This can be configured using the Azure portal or Azure CLI .

Other parameters you can adjust to control audit logging behavior include:

  • audit_log_events: controls the events to be logged. See below table for specific audit events.
  • audit_log_include_users: MySQL users to be included for logging. The default value for this parameter is empty, which will include all the users for logging. This has higher priority over audit_log_exclude_users. Max length of the parameter is 512 characters.
  • audit_log_exclude_users: MySQL users to be excluded from logging. Max length of the parameter is 512 characters.

Note

audit_log_include_users has higher priority over audit_log_exclude_users. For example, if audit_log_include_users = demouser and audit_log_exclude_users = demouser, the user will be included in the audit logs because audit_log_include_users has higher priority.

Event Description
CONNECTION - Connection initiation (successful or unsuccessful)
- User re-authentication with different user/password during session
- Connection termination
DML_SELECT SELECT queries
DML_NONSELECT INSERT/DELETE/UPDATE queries
DML DML = DML_SELECT + DML_NONSELECT
DDL Queries like "DROP DATABASE"
DCL Queries like "GRANT PERMISSION"
ADMIN Queries like "SHOW STATUS"
GENERAL All in DML_SELECT, DML_NONSELECT, DML, DDL, DCL, and ADMIN
TABLE_ACCESS - Table read statements, such as SELECT or INSERT INTO ... SELECT
- Table delete statements, such as DELETE or TRUNCATE TABLE
- Table insert statements, such as INSERT or REPLACE
- Table update statements, such as UPDATE

Access audit logs

Audit logs are integrated with Azure Monitor diagnostic settings. Once you've enabled audit logs on your MySQL flexible server, you can emit them to Azure Monitor logs, Event Hubs, or Azure Storage. To learn more about diagnostic settings, see the diagnostic logs documentation. To learn more about how to enable diagnostic settings in the Azure portal, see the audit log portal article.

The following sections describe the output of MySQL audit logs based on the event type. Depending on the output method, the fields included and the order in which they appear may vary.

Connection

Property Description
TenantId Your tenant ID
SourceSystem Azure
TimeGenerated [UTC] Time stamp when the log was recorded in UTC
Type Type of the log. Always AzureDiagnostics
SubscriptionId GUID for the subscription that the server belongs to
ResourceGroup Name of the resource group the server belongs to
ResourceProvider Name of the resource provider. Always MICROSOFT.DBFORMYSQL
ResourceType Servers
ResourceId Resource URI
Resource Name of the server
Category MySqlAuditLogs
OperationName LogEvent
LogicalServerName_s Name of the server
event_class_s connection_log
event_subclass_s CONNECT, DISCONNECT, CHANGE USER
connection_id_d Unique connection ID generated by MySQL
host_s Blank
ip_s IP address of client connecting to MySQL
user_s Name of user executing the query
db_s Name of database connected to
\_ResourceId Resource URI

General

Schema below applies to GENERAL, DML_SELECT, DML_NONSELECT, DML, DDL, DCL, and ADMIN event types.

Note

For sql_text_s, log will be truncated if it exceeds 2048 characters.

Property Description
TenantId Your tenant ID
SourceSystem Azure
TimeGenerated [UTC] Time stamp when the log was recorded in UTC
Type Type of the log. Always AzureDiagnostics
SubscriptionId GUID for the subscription that the server belongs to
ResourceGroup Name of the resource group the server belongs to
ResourceProvider Name of the resource provider. Always MICROSOFT.DBFORMYSQL
ResourceType Servers
ResourceId Resource URI
Resource Name of the server
Category MySqlAuditLogs
OperationName LogEvent
LogicalServerName_s Name of the server
event_class_s general_log
event_subclass_s LOG, ERROR, RESULT (only available for MySQL 5.6)
event_time Query start time in UTC timestamp
error_code_d Error code if query failed. 0 means no error
thread_id_d ID of thread that executed the query
host_s Blank
ip_s IP address of client connecting to MySQL
user_s Name of user executing the query
sql_text_s Full query text
\_ResourceId Resource URI

Table access

Note

For sql_text_s, log will be truncated if it exceeds 2048 characters.

Property Description
TenantId Your tenant ID
SourceSystem Azure
TimeGenerated [UTC] Time stamp when the log was recorded in UTC
Type Type of the log. Always AzureDiagnostics
SubscriptionId GUID for the subscription that the server belongs to
ResourceGroup Name of the resource group the server belongs to
ResourceProvider Name of the resource provider. Always MICROSOFT.DBFORMYSQL
ResourceType Servers
ResourceId Resource URI
Resource Name of the server
Category MySqlAuditLogs
OperationName LogEvent
LogicalServerName_s Name of the server
event_class_s table_access_log
event_subclass_s READ, INSERT, UPDATE, or DELETE
connection_id_d Unique connection ID generated by MySQL
db_s Name of database accessed
table_s Name of table accessed
sql_text_s Full query text
\_ResourceId Resource URI

Analyze logs in Azure Monitor Logs

Once your audit logs are piped to Azure Monitor Logs through Diagnostic Logs, you can perform further analysis of your audited events. Below are some sample queries to help you get started. Make sure to update the below with your server name.

  • List GENERAL events on a particular server

    AzureDiagnostics
    | where LogicalServerName_s == '<your server name>'
    | where Category == 'MySqlAuditLogs' and event_class_s == "general_log"
    | project TimeGenerated, LogicalServerName_s, event_class_s, event_subclass_s, event_time_t, user_s , ip_s , sql_text_s
    | order by TimeGenerated asc nulls last
    
  • List CONNECTION events on a particular server

    AzureDiagnostics
    | where LogicalServerName_s == '<your server name>'
    | where Category == 'MySqlAuditLogs' and event_class_s == "connection_log"
    | project TimeGenerated, LogicalServerName_s, event_class_s, event_subclass_s, event_time_t, user_s , ip_s , sql_text_s
    | order by TimeGenerated asc nulls last
    
  • Summarize audited events on a particular server

    AzureDiagnostics
    | where LogicalServerName_s == '<your server name>'
    | where Category == 'MySqlAuditLogs'
    | project TimeGenerated, LogicalServerName_s, 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, user_s, ip_s
    
  • Graph the audit event type distribution on a particular server

    AzureDiagnostics
    | where LogicalServerName_s == '<your server name>'
    | where Category == 'MySqlAuditLogs'
    | project TimeGenerated, LogicalServerName_s, event_class_s, event_subclass_s, event_time_t, user_s , ip_s , sql_text_s
    | summarize count() by LogicalServerName_s, bin(TimeGenerated, 5m)
    | render timechart
    
  • List audited events across all MySQL servers with Diagnostic Logs enabled for audit logs

    AzureDiagnostics
    | where Category == 'MySqlAuditLogs'
    | project TimeGenerated, LogicalServerName_s, event_class_s, event_subclass_s, event_time_t, user_s , ip_s , sql_text_s
    | order by TimeGenerated asc nulls last
    

Next steps