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

备注

将要查看的是 Azure Database for MySQL 的新服务。You are viewing the new service of Azure Database for MySQL. 若要查看经典 MySQL Database for Azure 的文档,请访问此页To view the documentation for classic MySQL Database for Azure, please visit this page.

在 Azure Database for MySQL 中,审核日志可供用户使用。In Azure Database for MySQL, 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:要包括 MySQL 用户进行日志记录。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. 此参数的优先级高于 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:不对 MySQL 用户进行日志记录。audit_log_exclude_users: MySQL users to be excluded from logging. 此参数的最大长度为 512 个字符。Max length of the parameter is 512 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
TABLE_ACCESS - 仅适用于 MySQL 5.7- Only available for MySQL 5.7
- 表读取语句,例如 SELECT 或 INSERT INTO ...SELECT- Table read statements, such as SELECT or INSERT INTO ... SELECT
- 表删除语句,例如 DELETE 或 TRUNCATE TABLE- Table delete statements, such as DELETE or TRUNCATE TABLE
- 表插入语句,例如 INSERT 或 REPLACE- Table insert statements, such as INSERT or REPLACE
- 表更新语句,例如 UPDATE- Table update statements, such as UPDATE

访问审核日志Access audit logs

审核日志与 Azure Monitor 诊断日志集成。Audit logs are integrated with Azure Monitor Diagnostic Logs. 在 MySQL 服务器上启用审核日志后,可以将它们发送到 Azure Monitor 日志、事件中心或 Azure 存储。Once you've enabled audit logs on your MySQL 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

以下部分介绍基于事件类型的 MySQL 审核日志的输出是什么。The following sections describe what's output by 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 租户 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.DBFORMYSQLAlways MICROSOFT.DBFORMYSQL
ResourceType Servers
ResourceId 资源 URIResource URI
Resource 服务器的名称Name of the server
Category MySqlAuditLogs
OperationName LogEvent
LogicalServerName_s 服务器的名称Name of the server
event_class_s connection_log
event_subclass_s CONNECTDISCONNECTCHANGE USER(仅适用于 MySQL 5.7)CONNECT, DISCONNECT, CHANGE USER (only available for MySQL 5.7)
connection_id_d MySQL 生成的唯一连接 IDUnique connection ID generated by MySQL
host_s 空白Blank
ip_s 连接到 MySQL 的客户端的 IP 地址IP address of client connecting to MySQL
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.DBFORMYSQLAlways MICROSOFT.DBFORMYSQL
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 LOGERRORRESULT(仅适用于 MySQL 5.6)LOG, ERROR, RESULT (only available for MySQL 5.6)
event_time 查询开始时间(UTC 时间戳)Query start time in UTC 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 连接到 MySQL 的客户端的 IP 地址IP address of client connecting to MySQL
user_s 执行查询的用户的名称Name of user executing the query
sql_text_s 完整查询文本Full query text
\_ResourceId 资源 URIResource URI

表访问权限Table access

备注

只有 MySQL 5.7 输出表访问日志。Table access logs are only output for MySQL 5.7.
对于 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.DBFORMYSQLAlways MICROSOFT.DBFORMYSQL
ResourceType Servers
ResourceId 资源 URIResource 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 READINSERTUPDATEDELETEREAD, INSERT, UPDATE, or DELETE
connection_id_d MySQL 生成的唯一连接 IDUnique connection ID generated by MySQL
db_s 访问的数据库的名称Name of database accessed
table_s 访问的表的名称Name of table accessed
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 
    
  • 列出已为审核日志启用诊断日志的所有 MySQL 服务器上的已审核事件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