SQL 数据库审核日志格式SQL Database audit log format

适用于: 是Azure SQL 数据库是Azure SQL 托管实例是Azure Synapse Analytics (SQL DW) APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance yes Azure Synapse Analytics (SQL DW)

Azure SQL 数据库审核跟踪数据库事件并将其写入 Azure 存储帐户中的审核日志,或者将其发送到事件中心或 Log Analytics 供下游处理和分析。Azure SQL Database auditing tracks database events and writes them to an audit log in your Azure storage account, or sends them to Event Hub or Log Analytics for downstream processing and analysis.

命名约定Naming conventions

Blob 审核Blob audit

Azure Blob 存储中存储的审核日志存储在 Azure 存储帐户中名为 sqldbauditlogs 的容器内。Audit logs stored in Azure Blob storage are stored in a container named sqldbauditlogs in the Azure storage account. 该容器中的目录层次结构采用 <ServerName>/<DatabaseName>/<AuditName>/<Date>/ 格式。The directory hierarchy within the container is of the form <ServerName>/<DatabaseName>/<AuditName>/<Date>/. Blob 文件名的格式为 <CreationTime>_<FileNumberInSession>.xel,其中,CreationTime 采用 UTC hh_mm_ss_ms 格式,FileNumberInSession 是运行的索引(如果会话日志跨多个 Blob 文件)。The Blob file name format is <CreationTime>_<FileNumberInSession>.xel, where CreationTime is in UTC hh_mm_ss_ms format, and FileNumberInSession is a running index in case session logs spans across multiple Blob files.

例如,对于 Server1 上的数据库 Database1,下面是可能的有效路径:For example, for database Database1 on Server1 the following is a possible valid path:

Server1/Database1/SqlDbAuditing_ServerAudit_NoRetention/2019-02-03/12_23_30_794_0.xel

只读副本审核日志存储在同一容器中。Read-only Replicas audit logs are stored in the same container. 该容器中的目录层次结构采用 <ServerName>/<DatabaseName>/<AuditName>/<Date>/RO/ 格式。The directory hierarchy within the container is of the form <ServerName>/<DatabaseName>/<AuditName>/<Date>/RO/. Blob 文件名共享相同的格式。The Blob file name shares the same format. 只读副本的审核日志存储在同一容器中。The Audit Logs of Read-only Replicas are stored in the same container.

事件中心Event Hub

审核事件将写入命名空间以及配置审核期间定义的事件中心,在 Apache Avro 事件的正文中捕获,并使用 UTF-8 编码的 JSON 格式进行存储。Audit events are written to the namespace and event hub that was defined during auditing configuration, and are captured in the body of Apache Avro events and stored using JSON formatting with UTF-8 encoding. 若要读取审核日志,可以使用 Avro 工具或处理此格式的类似工具。To read the audit logs, you can use Avro Tools or similar tools that process this format.

Log AnalyticsLog Analytics

审核事件将写入配置审核期间定义的 Log Analytics 工作区,并写入 SQLSecurityAuditEvents 类别的 AzureDiagnostics 表。Audit events are written to Log Analytics workspace defined during auditing configuration, to the AzureDiagnostics table with the category SQLSecurityAuditEvents. 有关 Log Analytics 搜索语言和命令的其他有用信息,请参阅 Log Analytics 搜索参考For additional useful information about Log Analytics search language and commands, see Log Analytics search reference.

审核日志字段Audit log fields

名称 (Blob)Name (blob) 名称(事件中心/Log Analytics)Name (Event Hubs/Log Analytics) 说明Description Blob 类型Blob type 事件中心/Log Analytics 类型Event Hubs/Log Analytics type
action_idaction_id action_id_saction_id_s 操作的 IDID of the action varchar(4)varchar(4) stringstring
action_nameaction_name action_name_saction_name_s 操作的名称Name of the action 空值N/A stringstring
additional_informationadditional_information additional_information_sadditional_information_s 有关事件的任何附加信息,以 XML 形式存储Any additional information about the event, stored as XML nvarchar(4000)nvarchar(4000) stringstring
affected_rowsaffected_rows affected_rows_daffected_rows_d 查询影响的行数Number of rows affected by the query bigintbigint intint
application_nameapplication_name application_name_sapplication_name_s 客户端应用程序的名称Name of client application nvarchar(128)nvarchar(128) stringstring
audit_schema_versionaudit_schema_version audit_schema_version_daudit_schema_version_d 始终为 1Always 1 intint intint
class_typeclass_type class_type_sclass_type_s 发生审核的可审核实体的类型Type of auditable entity that the audit occurs on varchar(2)varchar(2) stringstring
class_type_descclass_type_desc class_type_description_sclass_type_description_s 发生审核的可审核实体的说明Description of auditable entity that the audit occurs on 空值N/A stringstring
client_ipclient_ip client_ip_sclient_ip_s 客户端应用程序的源 IPSource IP of the client application nvarchar(128)nvarchar(128) stringstring
connection_idconnection_id 空值N/A 服务器中的连接的 IDID of the connection in the server GUIDGUID 空值N/A
data_sensitivity_informationdata_sensitivity_information data_sensitivity_information_sdata_sensitivity_information_s 受审核查询根据数据库中分类的列返回的信息类型和敏感度标签。Information types and sensitivity labels returned by the audited query, based on the classified columns in the database. 详细了解 Azure SQL 数据库数据发现和分类Learn more about Azure SQL Database data discover and classification nvarchar(4000)nvarchar(4000) stringstring
database_namedatabase_name database_name_sdatabase_name_s 在其中执行操作的数据库上下文The database context in which the action occurred sysnamesysname stringstring
database_principal_iddatabase_principal_id database_principal_id_ddatabase_principal_id_d 在其中执行操作的数据库用户上下文的 IDID of the database user context that the action is performed in intint intint
database_principal_namedatabase_principal_name database_principal_name_sdatabase_principal_name_s 在其中执行操作的数据库用户上下文的名称Name of the database user context in which the action is performed sysnamesysname stringstring
duration_millisecondsduration_milliseconds duration_milliseconds_dduration_milliseconds_d 查询执行持续时间,以毫秒为单位Query execution duration in milliseconds bigintbigint intint
event_timeevent_time event_time_tevent_time_t 激发可审核操作的日期和时间Date and time when the auditable action is fired datetime2datetime2 datetimedatetime
host_namehost_name 空值N/A 客户端主机名Client host name stringstring 空值N/A
is_column_permissionis_column_permission is_column_permission_sis_column_permission_s 指示是否为列级权限的标志。Flag indicating if this is a column level permission. 1 = true,0 = false1 = true, 0 = false bitbit stringstring
空值N/A is_server_level_audit_sis_server_level_audit_s 指示此项审核是否在服务器级别发生的标志Flag indicating if this audit is at the server level 空值N/A stringstring
object_idobject_ id object_id_dobject_id_d 发生审核的实体的 ID。The ID of the entity on which the audit occurred. 包括:服务器对象、数据库、数据库对象和架构对象。This includes the : server objects, databases, database objects, and schema objects. 如果实体是服务器本身,或者审核不是在对象级别执行的,则值为 00 if the entity is the server itself or if the audit is not performed at an object level intint intint
object_nameobject_name object_name_sobject_name_s 发生审核的实体的名称。The name of the entity on which the audit occurred. 包括:服务器对象、数据库、数据库对象和架构对象。This includes the : server objects, databases, database objects, and schema objects. 如果实体是服务器本身,或者审核不是在对象级别执行的,则值为 00 if the entity is the server itself or if the audit is not performed at an object level sysnamesysname stringstring
permission_bitmaskpermission_bitmask permission_bitmask_spermission_bitmask_s 在适用的情况下,显示已授予、已拒绝或已撤销的权限When applicable, shows the permissions that were granted, denied, or revoked varbinary(16)varbinary(16) stringstring
response_rowsresponse_rows response_rows_dresponse_rows_d 在结果集中返回的行数Number of rows returned in the result set bigintbigint intint
schema_nameschema_name schema_name_sschema_name_s 在其中执行操作的架构上下文。The schema context in which the action occurred. 如果审核在架构外部发生,则值为 NULLNULL for audits occurring outside a schema sysnamesysname stringstring
空值N/A securable_class_type_ssecurable_class_type_s 映射到正在审核的 class_type 的安全对象Securable object that maps to the class_type being audited 空值N/A stringstring
sequence_group_idsequence_group_id sequence_group_id_gsequence_group_id_g 唯一标识符Unique identifier varbinaryvarbinary GUIDGUID
sequence_numbersequence_number sequence_number_dsequence_number_d 跟踪大小过大、以致无法装入审核写入缓冲区的单个审核记录中的记录序列Tracks the sequence of records within a single audit record that was too large to fit in the write buffer for audits intint intint
server_instance_nameserver_instance_name server_instance_name_sserver_instance_name_s 发生审核的服务器实例的名称Name of the server instance where the audit occurred sysnamesysname stringstring
server_principal_idserver_principal_id server_principal_id_dserver_principal_id_d 在其中执行操作的登录上下文的 IDID of the login context in which the action is performed intint intint
server_principal_nameserver_principal_name server_principal_name_sserver_principal_name_s 当前登录名Current login sysnamesysname stringstring
server_principal_sidserver_principal_sid server_principal_sid_sserver_principal_sid_s 当前登录名 SIDCurrent login SID varbinaryvarbinary stringstring
session_idsession_id session_id_dsession_id_d 发生事件的会话的 IDID of the session on which the event occurred smallintsmallint intint
session_server_principal_namesession_server_principal_name session_server_principal_name_ssession_server_principal_name_s 会话的服务器主体Server principal for session sysnamesysname stringstring
statementstatement statement_sstatement_s 执行的 T-SQL 语句(如果有)T-SQL statement that was executed (if any) nvarchar(4000)nvarchar(4000) stringstring
succeededsucceeded succeeded_ssucceeded_s 指示触发事件的操作是否成功。Indicates whether the action that triggered the event succeeded. 对于除登录和批处理以外的事件,此字段仅报告权限检查是成功还是失败,而不会报告操作结果。For events other than login and batch, this only reports whether the permission check succeeded or failed, not the operation. 1 = 成功,0 = 失败1 = success, 0 = fail bitbit stringstring
target_database_principal_idtarget_database_principal_id target_database_principal_id_dtarget_database_principal_id_d 执行 GRANT/DENY/REVOKE 操作的数据库主体。The database principal the GRANT/DENY/REVOKE operation is performed on. 如果不适用,则值为 00 if not applicable intint intint
target_database_principal_nametarget_database_principal_name target_database_principal_name_starget_database_principal_name_s 操作的目标用户。Target user of action. 如果不适用,则值为 NULLNULL if not applicable stringstring stringstring
target_server_principal_idtarget_server_principal_id target_server_principal_id_dtarget_server_principal_id_d 执行 GRANT/DENY/REVOKE 操作的服务器主体。Server principal that the GRANT/DENY/REVOKE operation is performed on. 如果不适用,则返回 0Returns 0 if not applicable intint intint
target_server_principal_nametarget_server_principal_name target_server_principal_name_starget_server_principal_name_s 操作的目标登录名。Target login of action. 如果不适用,则值为 NULLNULL if not applicable sysnamesysname stringstring
target_server_principal_sidtarget_server_principal_sid target_server_principal_sid_starget_server_principal_sid_s 目标登录名的 SID。SID of target login. 如果不适用,则值为 NULLNULL if not applicable varbinaryvarbinary stringstring
transaction_idtransaction_id transaction_id_dtransaction_id_d 仅适用于 SQL Server(2016 和更高版本)- 对于 Azure SQL 数据库,值为 0SQL Server only (starting with 2016) - 0 for Azure SQL Database bigintbigint intint
user_defined_event_iduser_defined_event_id user_defined_event_id_duser_defined_event_id_d 用户定义的事件 ID,作为参数传递给 sp_audit_write。User defined event ID passed as an argument to sp_audit_write. 对于系统事件,值为 NULL(默认值);对于用户定义的事件,值为非零值。NULL for system events (default) and non-zero for user-defined event. 有关详细信息,请参阅 sp_audit_write (Transact-SQL)For more information, see sp_audit_write (Transact-SQL) smallintsmallint intint
user_defined_informationuser_defined_information user_defined_information_suser_defined_information_s 用户定义的信息,作为参数传递给 sp_audit_write。User defined information passed as an argument to sp_audit_write. 对于系统事件,值为 NULL(默认值);对于用户定义的事件,值为非零值。NULL for system events (default) and non-zero for user-defined event. 有关详细信息,请参阅 sp_audit_write (Transact-SQL)For more information, see sp_audit_write (Transact-SQL) nvarchar(4000)nvarchar(4000) stringstring

后续步骤Next steps

详细了解 Azure SQL 数据库审核Learn more about Azure SQL Database auditing.