SqlServer 通过扩展事件审计DDL操作

审计对象create、alter、drop操作，即object_created、object_altered、object_deleted事件。

设置过滤条件：

• ddl_phase=commit：只记录提交的ddl操作，对应 [equal_uint64]([ddl_phase],(1))
• database_id<>2：不记录临时对象操作，database_id=2为tempdb
• object_type<>statistics：不审计更新统计信息，对应 [object_type]<>(21587)

CREATE EVENT SESSION [audit_ddl] ON SERVER 
ADD EVENT sqlserver.object_altered(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[equal_uint64]([ddl_phase],(1)) AND [database_id]<>(2)) AND [object_type]<>(21587))),
ADD EVENT sqlserver.object_created(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[equal_uint64]([ddl_phase],(1)) AND [database_id]<>(2)) AND [object_type]<>(21587))),
ADD EVENT sqlserver.object_deleted(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[equal_uint64]([ddl_phase],(1)) AND [database_id]<>(2)) AND [object_type]<>(21587))) 
ADD TARGET package0.event_file(SET filename=N'audit_ddl.xel',max_file_size=(50),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

模拟测试

CREATE TABLE mytab (a int);    -- 普通表
CREATE TABLE #tmptab (a int);  -- 临时表
 
alter table mytab add CONSTRAINT PK_mytab PRIMARY KEY CLUSTERED (a);
alter table #tmptab add CONSTRAINT PK_tmptab PRIMARY KEY CLUSTERED (a);
 
DROP TABLE mytab;
DROP TABLE #tmptab;

查看对应日志记录即可~