Audit Logs in Azure Database for MariaDB

Important

Azure Database for MariaDB is on the retirement path. We strongly recommend that you migrate to Azure Database for MySQL. For more information about migrating to Azure Database for MySQL, see What's happening to Azure Database for MariaDB?.

In Azure Database for MariaDB, the audit log is available to users. The audit log can be used to track database-level activity and is commonly used for compliance.

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.

By default the audit log is disabled. To enable it, set audit_log_enabled to ON.

Other parameters you can adjust include:

• audit_log_events: controls the events to be logged. See below table for specific audit events.
• audit_log_include_users: MariaDB 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: MariaDB users to be excluded from logging. Allows for at most four users. Max length of the parameter is 256 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 reauthentication 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

Access audit logs

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

Diagnostic Logs Schemas

The following sections describe what's output by MariaDB 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.DBFORMARIADB
ResourceType	Servers
ResourceId	Resource URI
Resource	Name of the server
Category	MySqlAuditLogs
OperationName	LogEvent
event_class_s	connection_log
event_subclass_s	CONNECT, DISCONNECT
connection_id_d	Unique connection ID generated by MariaDB
host_s	Blank
ip_s	IP address of client connecting to MariaDB
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, 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.DBFORMARIADB
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
event_time	Query start seconds in UNIX 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 MariaDB
user_s	Name of user executing the query
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 MariaDB 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

• How to configure audit logs in the Azure portal