Azure Database for MariaDB 中的审核日志Audit Logs in Azure Database for MariaDB

在 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

重要

建议仅记录审核所需的事件类型和用户,以确保服务器的性能不会受到严重影响。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. 若要启用它,请将 audit_log_enabled 设置为 ON。To enable it, set audit_log_enabled to ON.

可以调整的其他参数包括:Other parameters you can adjust include:

  • audit_log_events:控制要记录的事件。audit_log_events: controls the events to be logged. 请查看下表以了解具体的审核事件。See below table for specific audit events.
  • audit_log_include_users:要包含在日志记录中的 MariaDB 用户。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. 此参数的优先级高于 audit_log_exclude_usersThis has higher priority over audit_log_exclude_users. 此参数的最大长度为 512 个字符。Max length of the parameter is 512 characters.
  • audit_log_exclude_users:要从日志记录中排除的 MariaDB 用户。audit_log_exclude_users: MariaDB users to be excluded from logging. 最多允许对四个用户这样做。Allows for at most four users. 参数的最大长度为 256 个字符。Max length of the parameter is 256 characters.

备注

audit_log_include_users 的优先级高于 audit_log_exclude_usersaudit_log_include_users has higher priority over audit_log_exclude_users. 例如,如果 audit_log_include_users = demouser 并且 audit_log_exclude_users = demouser,则会将该用户包括在审核日志中,因为 audit_log_include_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 查询SELECT queries
DML_NONSELECT INSERT/DELETE/UPDATE 查询INSERT/DELETE/UPDATE queries
DML DML = DML_SELECT + DML_NONSELECTDML = DML_SELECT + DML_NONSELECT
DDL 类似“DROP DATABASE”的查询Queries like "DROP DATABASE"
DCL 类似“GRANT PERMISSION”的查询Queries like "GRANT PERMISSION"
ADMIN 类似“SHOW STATUS”的查询Queries like "SHOW STATUS"
GENERAL All in DML_SELECT, DML_NONSELECT, DML, DDL, DCL, and ADMINAll in DML_SELECT, DML_NONSELECT, DML, DDL, DCL, and ADMIN

访问审核日志Access audit logs

审核日志与 Azure Monitor 诊断日志集成。Audit logs are integrated with Azure Monitor Diagnostic Logs. 在 MariaDB 服务器上启用审核日志后,可以将它们发送到 Azure Monitor 日志、事件中心或 Azure 存储。Once you've enabled audit logs on your MariaDB server, you can emit them to Azure Monitor logs, Event Hubs, or Azure Storage. 若要详细了解如何在 Azure 门户中启用诊断日志,请参阅审核日志门户文章To learn more about how to enable diagnostic logs in the Azure portal, see the audit log portal article.

诊断日志架构Diagnostic Logs Schemas

以下部分介绍了 MariaDB 审核日志基于事件类型输出的内容。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 租户 IDYour tenant ID
SourceSystem Azure
TimeGenerated [UTC] 记录日志时的时间戳 (UTC)Time stamp when the log was recorded in UTC
Type 日志的类型。Type of the log. 始终是 AzureDiagnosticsAlways AzureDiagnostics
SubscriptionId 服务器所属的订阅的 GUIDGUID for the subscription that the server belongs to
ResourceGroup 服务器所属的资源组的名称Name of the resource group the server belongs to
ResourceProvider 资源提供程序的名称。Name of the resource provider. 始终是 MICROSOFT.DBFORMARIADBAlways MICROSOFT.DBFORMARIADB
ResourceType Servers
ResourceId 资源 URIResource URI
Resource 服务器的名称Name of the server
Category MySqlAuditLogs
OperationName LogEvent
event_class_s connection_log
event_subclass_s CONNECT, DISCONNECTCONNECT, DISCONNECT
connection_id_d MariaDB 生成的唯一连接 IDUnique connection ID generated by MariaDB
host_s 空白Blank
ip_s 连接到 MariaDB 的客户端的 IP 地址IP address of client connecting to MariaDB
user_s 执行查询的用户的名称Name of user executing the query
db_s 连接的数据库的名称Name of database connected to
\_ResourceId 资源 URIResource URI

常规General

下面的架构适用于 GENERAL、DML_SELECT、DML_NONSELECT、DML、DDL、DCL 和 ADMIN 事件类型。Schema below applies to GENERAL, DML_SELECT, DML_NONSELECT, DML, DDL, DCL, and ADMIN event types.

备注

对于 sql_text,如果日志超过 2048 个字符,则会截断日志。For sql_text, log will be truncated if it exceeds 2048 characters.

属性Property 说明Description
TenantId 租户 IDYour tenant ID
SourceSystem Azure
TimeGenerated [UTC] 记录日志时的时间戳 (UTC)Time stamp when the log was recorded in UTC
Type 日志的类型。Type of the log. 始终是 AzureDiagnosticsAlways AzureDiagnostics
SubscriptionId 服务器所属的订阅的 GUIDGUID for the subscription that the server belongs to
ResourceGroup 服务器所属的资源组的名称Name of the resource group the server belongs to
ResourceProvider 资源提供程序的名称。Name of the resource provider. 始终是 MICROSOFT.DBFORMARIADBAlways MICROSOFT.DBFORMARIADB
ResourceType Servers
ResourceId 资源 URIResource URI
Resource 服务器的名称Name of the server
Category MySqlAuditLogs
OperationName LogEvent
LogicalServerName_s 服务器的名称Name of the server
event_class_s general_log
event_subclass_s LOGERRORRESULTLOG, ERROR, RESULT
event_time 以 UNIX 时间戳表示的查询开始时的秒数Query start seconds in UNIX timestamp
error_code_d 查询失败时的错误代码。Error code if query failed. 0 意味着无错误0 means no error
thread_id_d 执行了查询的线程的 IDID of thread that executed the query
host_s 空白Blank
ip_s 连接到 MariaDB 的客户端的 IP 地址IP address of client connecting to MariaDB
user_s 执行查询的用户的名称Name of user executing the query
sql_text_s 完整查询文本Full query text
\_ResourceId 资源 URIResource URI

分析 Azure Monitor 日志中的日志Analyze logs in Azure Monitor Logs

将审核日志通过诊断日志以管道方式传送到 Azure Monitor 日志后,便可以对审核事件进行进一步分析。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.

  • 列出特定服务器上的 GENERAL 事件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 
    
  • 列出特定服务器上的 CONNECTION 事件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 
    
  • 列出已为审核日志启用诊断日志的所有 MariaDB 服务器上的已审核事件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