SQL Server 审计 第三篇:查看审计数据

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

 

参考文档:

SQL Server Audit (Database Engine)

 

posted @ 2020-07-07 11:14  悦光阴  阅读(1566)  评论(0编辑  收藏  举报