数据库审核
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 sdon 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
gocreate database audit specification wcc_database_audit_specification
for server audit wcc_server_auditadd (DATABASE_PRINCIPAL_IMPERSONATION_GROUP), --跟踪所有模拟行为add (insert,update,delete on humanresources.departmentby 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 > 0then OBJECT_NAME(major_id,db_id()) +case when dasd.minor_id > 0then '.' + COL_NAME(major_id,minor_id)else ''endelse 'N/A'end object_column_name,dasd.audited_principal_idfrom sys.database_audit_specifications das
inner join sys.database_audit_specification_details dasdon 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')
gouse adventureworks
go--创建这个新的用户,在这个用户上下文中执行
create user TestAudit for login TestAuditexecute 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 aaon 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 aaon 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 aaon aa.action_id = af.action_id
where aa.name = 'backup'and aa.class_desc = N'database'select distinctaa.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 aaon aa.action_id = af.action_id--6.管理审核对象、审核规范
--6.1修改服务器审核规范
use master
goalter 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
goalter 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
goalter server audit wcc_server_audit
with (state = off)alter server audit wcc_server_audit
to application_logalter 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