SQL 数据库审核日志格式SQL Database audit log format
Azure SQL 数据库
Azure SQL 托管实例
Azure Synapse Analytics
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.