SQL Server 审计系列:
审计对象,可以通过动态管理视图和函数来查看
一,查看服务器审计对象
查看审计服务器审计对象的信息,这是审计对象的元数据:
select a.audit_id
,a.audit_guid
,a.name
,a.create_date
,a.modify_date
,a.principal_id as owner_prinicpal_id
,a.type
,a.type_desc
,a.on_failure
,a.on_failure_desc
,a.is_state_enabled
,a.queue_delay
,a.predicate
from sys.server_audits a
查看审计对象的当前状态,以及跟审计对象相关联的Extended Events session
select s.audit_id
,s.name
,s.status
,s.status_desc
,s.status_time
,s.audit_file_path
,s.audit_file_size
,s.event_session_address
,e.name as xe_session_name
,e.pending_buffers
,e.total_regular_buffers
,e.regular_buffer_size
,e.large_buffer_size
,e.total_buffer_size
,e.buffer_policy_flags
,e.buffer_policy_desc
,e.flags
,e.flag_desc
,e.dropped_event_count
,e.dropped_buffer_count
,e.blocked_event_fire_time
,e.create_time
,e.largest_event_dropped_size
from sys.dm_server_audit_status s
inner join sys.dm_xe_sessions as e
on s.event_session_address=e.address
二,审计规范
审计规范分为服务器级别的审计规范和数据库级别的审计规范,以下脚本用于查看数据库级别的审计规范:
select s.audit_guid
,s.name as audit_specification
,s.create_date
,s.modify_date
,s.is_state_enabled
,d.audit_action_id
,d.audit_action_name
,d.class
,d.class_desc
,d.major_id
,d.minor_id
,d.audited_principal_id
,d.audited_result
,d.is_group
from sys.database_audit_specifications s
inner join sys.database_audit_specification_details as d
on s.database_specification_id=d.database_specification_id
三,审计动作
审计动作实际上是可被审计的事件被触发,审计动作是创建审计需要监控的对象:
select a.action_id
,a.action_in_log
,a.name as action_name
,m.class_type
,a.class_desc
,m.securable_class_desc
,a.parent_class_desc
,a.covering_action_name
,a.configuration_level
,a.containing_group_name
from sys.dm_audit_actions a
inner join sys.dm_audit_class_type_map m
on a.class_desc=m.class_type_desc
四,审计数据
审计数据是我们创建审计的目的,通过审计数据追踪系统发生的事件,并把跟事件相关的信息记录下来。
审计数据是通过函数 fn_get_audit_file()获得的,返回的字段主要有以下两类,第一类是跟数据库的环境相关:
- application_name:客户端应用程序的名称,该程序执行SQL语句触发了审计事件
- server_instance_name:审计发生的SQL Server实例名称
- database_name:审计动作发生的数据库
- database_principal_id 和 database_principal_name:执行审计动作的用户
- server_principal_id和server_principal_name:主席那个审计作用的Login
- server_principal_sid:login的sid
- session_id:审计动作发生的session
- session_server_principal_name:审计动作发生的session的login
第二类是事件和事件关联的数据:
- action_id:审计动作ID
- event_time:审计动作(Audit Action)触发的时间
- class_type:审计作用的对象的类型
- schema_name:审计作用的对象的schema名称
- object_id和object_name:审计作用的对象的ID和名称
- statement:执行的SQL 语句
- succeeded:指示审计动作是否执行成功
- sequence_group_id和sequence_number:如果单个审计记录(audit record)的size太大,那么会把该审计分为一组,通过sequence_number来标记顺序
可以通过以下脚本来查看审计追踪的数据:
select f.event_time
,f.sequence_group_id
,f.sequence_number
,f.action_id
,a.name as action_name
,f.succeeded
,f.server_principal_name
,f.database_principal_name
,f.database_name
,f.object_id
,f.schema_name
,f.object_name
,f.class_type
,m.class_type_desc
,f.statement
,f.session_id
,f.application_name
from sys.fn_get_audit_file('G:\AuditFiles\MonitorQuery\*',default,default) f
inner join sys.dm_audit_actions a
on f.action_id=a.action_id
inner join sys.dm_audit_class_type_map m
on f.class_type=m.class_type
order by f.event_time
参考文档: