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

来源：互联网发布：剑魂轻甲精通数据编辑：程序博客网时间：2024/06/05 06:23
数据库审核
SQL Server 2008引入了审核实例、数据库级别活动的能力。
使用服务器审核对象将活动捕获到目标数据目的地，这个对象定义将活动捕获到文件、windows应用程序事件日志、windows安全事件日志。
使用服务器审核规范定义在实例范围内希望捕获哪些事件。使用数据库审核规范对象可以在数据库范围内定义希望捕获哪些事件。
只允许一个服务器规范绑定到一个服务器审核规范对象上，而一个或多个数据库审核复返可以绑定到一个服务器审核对象上。一个服务器审核对象可以同时配置一个服务器审核规范、一个或多个数据库审核规范。
--1.1创建服务器审核对象的操作，必须在master数据库中执行use mastergo--1.2创建服务器审核对象create server audit wcc_server_auditto 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_namefrom sys.dm_audit_actionsWHERE 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_groupfrom sys.server_audit_specifications sinner 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_namefrom sys.dm_audit_actionsWHERE 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_namefrom sys.dm_audit_actionsWHERE class_desc = 'database'               --审核操作的类      and configuration_level = 'group'  --配置级别ORDER BY name --3.3创建数据库审核规范,捕获数据库范围的事件use AdventureWorksgocreate database audit specification wcc_database_audit_specificationfor 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 dasinner join sys.database_audit_specification_details dasd        on das.database_specification_id = dasd.database_specification_id  --注意:无法从用户数据库中对服务器审核执行更改,--此操作必须在 master 数据库中执行。use mastergo--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 adventureworksto disk = 'c:\adventureworks_backup.bak'--执行DBCC操作dbcc checkdb('adventureworks')gouse adventureworksgo--创建这个新的用户,在这个用户上下文中执行create user TestAudit for login TestAuditexecute as user = 'TestAudit'--返回到原来的登录名revert--添加,修改,删除数据insert into humanresources.department(name,groupname)values('Traffic','Advertising')update humanresources.departmentset name = 'Media planning'where name ='Traffic'delete humanresources.departmentwhere name = 'Media planning' --5.2查看服务器审核二进制文件的内容select af.event_time,       af.succeeded,       af.target_server_principal_name,              object_namefrom fn_get_audit_file('E:\wcc_server_audit_*',                       default,                       default) afinner join sys.dm_audit_actions aa        on aa.action_id = af.action_idwhere aa.name = 'ADD MEMEBER'      and aa.class_desc = N'SERVER ROLE'select af.event_time,            af.database_principal_namefrom fn_get_audit_file('E:\wcc_server_audit_*',                       default,                       default) afinner join sys.dm_audit_actions aa        on aa.action_id = af.action_idwhere aa.name = 'DELETE'      and aa.class_desc = N'OBJECT'      and af.schema_name = 'HumanResources'      and af.object_name = 'department'      select af.event_time,           af.statementfrom fn_get_audit_file('E:\wcc_server_audit_*',                       default,                       default) afinner join sys.dm_audit_actions aa        on aa.action_id = af.action_idwhere 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_namefrom fn_get_audit_file('E:\wcc_server_audit_*',                       default,                       default) afinner join sys.dm_audit_actions aa        on aa.action_id = af.action_id--6.管理审核对象、审核规范--6.1修改服务器审核规范use mastergoalter server audit specification wcc_server_audit_specificationwith (state = off)alter server audit specification wcc_server_audit_specificationdrop (backup_restore_group)    alter server audit specification wcc_server_audit_specificationdrop (login_change_password_group)     alter server audit specification wcc_server_audit_specificationwith (state = on )--6.2修改数据库审核规范use AdventureWorksgoalter database audit specification wcc_database_audit_specificationwith (state = off)alter database audit specification wcc_database_audit_specificationdrop (insert on humanresources.department by public)alter database audit specification wcc_database_audit_specificationadd (database_role_member_change_group)alter database audit specification wcc_database_audit_specificationwith (state = on)--6.3修改服务器审核对象use mastergoalter server audit wcc_server_auditwith (state = off)alter server audit wcc_server_auditto application_logalter server audit wcc_server_auditwith (state = on)--6.4删除数据库审核规范alter database audit wcc_database_audit_specificationwith (state = off)drop database audit wcc_database_audit_specification--6.5删除服务器审核规范alter server audit specification wcc_server_audit_specificationwith (state = off)drop server audit specification wcc_server_audit_specification--6.6删除服务器审核对象alter server audit wcc_server_auditwith (state = off)drop server audit wcc_server_audit