【SQL Server学习笔记】数据库审核

数据库审核

SQL Server 2008引入了审核实例、数据库级别活动的能力。

使用服务器审核对象将活动捕获到目标数据目的地，这个对象定义将活动捕获到文件、windows应用程序事件日志、windows安全事件日志。

使用服务器审核规范定义在实例范围内希望捕获哪些事件。使用数据库审核规范对象可以在数据库范围内定义希望捕获哪些事件。

只允许一个服务器规范绑定到一个服务器审核规范对象上，而一个或多个数据库审核复返可以绑定到一个服务器审核对象上。一个服务器审核对象可以同时配置一个服务器审核规范、一个或多个数据库审核规范。

 

--1.1创建服务器审核对象的操作，必须在master数据库中执行
use master
go


--1.2创建服务器审核对象
create server audit wcc_server_audit
to file
 (
  filepath = 'E:\',         --审核日志的文件路径，或者:APPLICATION_LOG,SECURITY_LOG 
  maxsize  = 500MB,         --审核文件可以增大到的最大大小
  max_rollover_files = 10,  --保留在文件系统中，外加当前文件的最大文件数
  reserve_disk_space = off  --按MAXSIZE值为磁盘上的文件预先分配大小
 )
with 
 (
  queue_delay = 1000,     --确定在强制处理审核操作之前，可以延迟的毫秒数
  on_failure = continue   --当不能写入目标时，sql实例是否关闭
 )
 

--1.3查看服务器审核对象
select audit_id,
       name,        --服务器审核对象名
       audit_guid,
       
       principal_id,
       
       type_desc,         --日志类型
       on_failure_desc,   --当不能写入目标时，sql实例是否关闭
       is_state_enabled,  --是否启用
       queue_delay        --在审核过程操作之前，可以延迟的毫秒数
from sys.server_audits s
 

 

--2.1查看可以使用哪些审核组
select name,                 --审核操作或审核组的名称
       class_desc,           --应用审核操作对象的类的名称
       covering_action_name, --包含此行中所述的审核操作的审核操作或审核组的名称
       
       parent_class_desc,
       covering_parent_action_name,
       configuration_level,
       containing_group_name
from sys.dm_audit_actions
WHERE class_desc = 'server'              --审核操作的类
      and configuration_level = 'group'  --配置级别
ORDER BY name

 

--2.2创建服务器审核规范,捕获实例范围的事件
create server audit specification wcc_server_audit_specification 
for server audit wcc_server_audit 
 add (SERVER_ROLE_MEMBER_CHANGE_GROUP),
 ADD (DBCC_GROUP),
 ADD (BACKUP_RESTORE_GROUP) 
WITH (STATE = ON ) 

 

--2.3服务器审核中的服务器审核规范详细信息（操作）的信息
select s.server_specification_id,
       s.name,
       s.is_state_enabled,
       
       sd.audit_action_name,
       sd.is_group
from sys.server_audit_specifications s
inner join sys.server_audit_specification_details sd
        on sd.server_specification_id = s.server_specification_id

 


--3.1查看对象有哪些操作可以被审核
select name,                 --审核操作或审核组的名称
       class_desc,           --应用审核操作对象的类的名称
       covering_action_name, --包含此行中所述的审核操作的审核操作或审核组的名称
       
       parent_class_desc,
       covering_parent_action_name,
       configuration_level,
       containing_group_name
from sys.dm_audit_actions
WHERE class_desc = 'object'               --审核操作的类
      and configuration_level = 'action'  --配置级别
ORDER BY name

 

--3.2查看数据库范围有哪些操作可以被审核
select name,                 --审核操作或审核组的名称
       class_desc,           --应用审核操作对象的类的名称
       covering_action_name, --包含此行中所述的审核操作的审核操作或审核组的名称
       
       parent_class_desc,
       covering_parent_action_name,
       configuration_level,
       containing_group_name
from sys.dm_audit_actions
WHERE class_desc = 'database'               --审核操作的类
      and configuration_level = 'group'  --配置级别
ORDER BY name

 

--3.3创建数据库审核规范,捕获数据库范围的事件
use AdventureWorks
go

create database audit specification wcc_database_audit_specification
for server audit wcc_server_audit
 add (DATABASE_PRINCIPAL_IMPERSONATION_GROUP), --跟踪所有模拟行为
 add (insert,update,delete 
      on humanresources.department
      by public)
with 
 (state = on )

 

--3.4数据库审核规范详细信息
select das.database_specification_id,
       das.name,
       das.is_state_enabled,
       
       dasd.audit_action_id,
       dasd.is_group,
       
       dasd.class_desc,
       case when dasd.major_id > 0
                 then OBJECT_NAME(major_id,db_id()) +
                      case when dasd.minor_id > 0
                                then '.' + COL_NAME(major_id,minor_id)
                           else ''
                      end
            else 'N/A'
       end object_column_name,
       
       dasd.audited_principal_id
       
from sys.database_audit_specifications das
inner join sys.database_audit_specification_details dasd
        on das.database_specification_id = dasd.database_specification_id

 

 

--注意:无法从用户数据库中对服务器审核执行更改,
--此操作必须在 master 数据库中执行。
use master
go

--5.1开启服务器审核
alter server audit [wcc_server_audit]
with (state = on )

 

--创建新的登录名
create login TestAudit 
with password = 'testaudit',
     check_expiration = off,
     check_policy = off
     

--给服务器角色增加成员
exec sp_addsrvrolemember
 @loginame = 'TestAudit',
 @rolename = 'bulkadmin'


--备份数据库
backup database adventureworks
to disk = 'c:\adventureworks_backup.bak'


--执行DBCC操作
dbcc checkdb('adventureworks')
go


use adventureworks
go

--创建这个新的用户,在这个用户上下文中执行
create user TestAudit for login TestAudit

execute as user = 'TestAudit'

--返回到原来的登录名
revert


--添加,修改,删除数据
insert into humanresources.department
(name,groupname)
values('Traffic','Advertising')

update humanresources.department
set name = 'Media planning'
where name ='Traffic'


delete humanresources.department
where name = 'Media planning'

 

--5.2查看服务器审核二进制文件的内容
select af.event_time,
       af.succeeded,
       af.target_server_principal_name,
       
       object_name
from fn_get_audit_file('E:\wcc_server_audit_*',
                       default,
                       default) af
inner join sys.dm_audit_actions aa
        on aa.action_id = af.action_id
where aa.name = 'ADD MEMEBER'
      and aa.class_desc = N'SERVER ROLE'


select af.event_time,     
       af.database_principal_name
from fn_get_audit_file('E:\wcc_server_audit_*',
                       default,
                       default) af
inner join sys.dm_audit_actions aa
        on aa.action_id = af.action_id
where aa.name = 'DELETE'
      and aa.class_desc = N'OBJECT'
      and af.schema_name = 'HumanResources'
      and af.object_name = 'department'
      

select af.event_time,    
       af.statement
from fn_get_audit_file('E:\wcc_server_audit_*',
                       default,
                       default) af
inner join sys.dm_audit_actions aa
        on aa.action_id = af.action_id
where aa.name = 'backup'
      and aa.class_desc = N'database'


select distinct
       aa.name,
       
       af.event_time,
       af.database_principal_name,
       af.target_server_principal_name,
       af.object_name
from fn_get_audit_file('E:\wcc_server_audit_*',
                       default,
                       default) af
inner join sys.dm_audit_actions aa
        on aa.action_id = af.action_id



--6.管理审核对象、审核规范
--6.1修改服务器审核规范
use master
go

alter server audit specification wcc_server_audit_specification
with (state = off)

alter server audit specification wcc_server_audit_specification
drop (backup_restore_group)    

alter server audit specification wcc_server_audit_specification
drop (login_change_password_group)
     
alter server audit specification wcc_server_audit_specification
with (state = on )


--6.2修改数据库审核规范
use AdventureWorks
go

alter database audit specification wcc_database_audit_specification
with (state = off)


alter database audit specification wcc_database_audit_specification
drop (insert on humanresources.department by public)


alter database audit specification wcc_database_audit_specification
add (database_role_member_change_group)

alter database audit specification wcc_database_audit_specification
with (state = on)


--6.3修改服务器审核对象
use master
go

alter server audit wcc_server_audit
with (state = off)

alter server audit wcc_server_audit
to application_log

alter server audit wcc_server_audit
with (state = on)


--6.4删除数据库审核规范
alter database audit wcc_database_audit_specification
with (state = off)

drop database audit wcc_database_audit_specification


--6.5删除服务器审核规范
alter server audit specification wcc_server_audit_specification
with (state = off)

drop server audit specification wcc_server_audit_specification


--6.6删除服务器审核对象
alter server audit wcc_server_audit
with (state = off)

drop server audit wcc_server_audit

 

转载于:https://www.cnblogs.com/momogua/p/8304563.html