SQLSERVER2008新增的审核审计功能
SQLSERVER2008新增的审核/审计功能
很多时候我们都需要对数据库或者数据库服务器实例进⾏审核/审计
例如对失败的登录次数进⾏审计，某个数据库上的DDL语句进⾏审计，某个数据库表⾥⾯的delete语句进⾏审计
事实上，我们这些审计的需求基本上都是为了⼀个⽬的：防⿊客防⼊侵者防⾃家⼈
上⾯的这些审计需求⽆⾮就是看⼀下有哪些⼈试图⼊侵数据库服务器，⼊侵了之后是否有drop表，是否有delete数据
在SQLSERVER2008及以前版本可以选择的⽅案有
1、和数据库级别的DDL触发器（SQL2005及以上版本）以及DML触发器
2、⾃⼰⼿⼯从事务⽇志⾥读取操作记录，权威的书都会说事务⽇志不是审核⼯具，⼀般⼤型数据库都会设置为简单模式，事务⽇志截断
3、依靠SQLSERVER ERRORLOG来检查登录审核，导致SQLSERVER ERRORLOG login相关的⽇志泛滥导致SQL排错造成困难
4、事件通知：
5、更改跟踪：
6、变更数据捕获（CDC）：
我们⼀般都会把C2 审核跟踪和登录审核⾥⾯只限成功的登录，以防⽌SQL ERRORLOG⽇志泛滥，因为服务器是很久才重启⼀次的，如果不做修改很容易造成磁盘爆满
--禁⽤C2 审核跟踪和只限成功的登录
EXEC sys.sp_configure N'c2 audit mode', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO
USE[master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 1
GO
SQLSERVER2008新增的审核功能
在sqlserver2008新增了审核功能，可以对服务器级别和数据库级别的操作进⾏审核/审计，事实上，事件通知、更改跟踪、变更数据捕获（CDC）
都不是⽤来做审计的，只是某些⼈乱⽤这些功能，也正因为乱⽤这些功能导致踩坑
：性能跟踪
更改跟踪：⽤Sync Services来构建偶尔连接的系统
变更数据捕获（CDC）：数据仓库的ETL 中的数据抽取（背后使⽤logreader）
⽽审核是SQLSERVER专门针对数据库安全的进⾏的审核，记住，他是专门和专业的！
我们看⼀下审核的使⽤⽅法
审核对象
步骤⼀：创建审核对象，审核对象是跟保存路径关联的，所以如果你需要把审核操作⽇志保存到不同的路径就需要创建不同的审核对象
我们把审核操作⽇志保存在⽂件系统⾥，在创建之前我们还要在相关路径先创建好保存的⽂件夹，我们在D盘先创建sqlaudits⽂件夹，然后执⾏下⾯语句
--创建审核对象之前需要切换到master数据库
USE[master]
GO
CREATE SERVER AUDIT MyFileAudit TO FILE(FILEPATH='D:\sqlaudits') --这⾥指定⽂件夹不能指定⽂件，⽣成⽂件都会保存在这个⽂件夹
实际上，我们在创建审核对象的同时可以指定审核选项，下⾯是相关脚本
把⽇志放在磁盘的好处是可以使⽤新增的TVF:sys.[fn_get_audit_file] 来过滤和排序审核数据，如果把审核数据保存在Windows 事件⽇志⾥查询起来⾮常⿇烦
USE[master]
GO
CREATE SERVER AUDIT MyFileAudit TO FILE(
FILEPATH='D:\sqlaudits',
MAXSIZE=4GB,
MAX_ROLLOVER_FILES=6)
WITH (
ON_FAILURE=CONTINUE,
QUEUE_DELAY=1000);
ALTER SERVER AUDIT MyFileAudit WITH(STATE =ON)
MAXSIZE：指明每个审核⽇志⽂件的最⼤⼤⼩是4GB
MAX_ROLLOVER_FILES：指明滚动⽂件数⽬，类似于SQL ERRORLOG，达到多少个⽂件之后删除前⾯的历史⽂件，这⾥是6个⽂件
ON_FAILURE：指明当审核数据发⽣错误时的操作，这⾥是继续进⾏审核，如果指定shutdown，那么将会shutdown整个实例
queue_delay：指明审核数据写⼊的延迟时间，这⾥是1秒，最⼩值也是1秒，如果指定0表⽰是实时写⼊，当然性能也有⼀些影响
STATE：指明启动审核功能，STATE这个选项不能跟其他选项共⽤，所以只能单独⼀句
在修改审核选项的时候，需要先禁⽤审核，再开启审核
ALTER SERVER AUDIT MyFileAudit WITH(STATE =OFF)
ALTER SERVER AUDIT MyFileAudit WITH(QUEUE_DELAY =1000)
ALTER SERVER AUDIT MyFileAudit WITH(STATE =ON)
审核规范
在SQLSERVER审核⾥⾯有审核规范的概念，⼀个审核对象只能绑定⼀个审核规范，⽽⼀个审核规范可以绑定到多个审核对象
我们来看⼀下脚本
CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFilepagedown是什么意思
FOR SERVER AUDIT MyFileAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
GO
CREATE SERVER AUDIT MyAppAudit TO APPLICATION_LOG
GO
ALTER SERVER AUDIT MyAppAudit WITH(STATE =ON)
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=OFF)
GO
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile
FOR SERVER AUDIT MyAppAudit
mysql语句的执行顺序ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
GO
我们创建⼀个服务器级别的审核规范CaptureLoginsToFile，然后再创建多⼀个审核对象MyAppAudit ，这个审核对象会把审核⽇志保存到Windows事件⽇志的应⽤程序⽇志⾥
我们禁⽤审核规范CaptureLoginsToFile，修改审核规范CaptureLoginsToFile属于审核对象MyAppAudit ，修改成功
⽽如果要把多个审核规范绑定到同⼀个审核对象则会报错
CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFileApowder pink是什么颜
FOR SERVER AUDIT MyFileAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFileB
FOR SERVER AUDIT MyFileAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
GO
--消息 33230，级别 16，状态 1，第 86 ⾏
--审核 'MyFileAudit' 的审核规范已经存在。
这⾥要说⼀下：审核对象和审核规范的修改，⽆论是审核对象还是审核规范，在修改他们的相关参数之前，他必须要先禁⽤，后修改，再启⽤
--禁⽤审核对象
ALTER SERVER AUDIT MyFileAudit WITH(STATE =OFF)
--禁⽤服务器级审核规范
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=OFF)
GO
--禁⽤数据库级审核规范
ALTER DATABASE AUDIT SPECIFICATION CaptureDBLoginsToFile WITH (STATE=OFF)
GO
--相关修改选项操作
--启⽤审核对象
ALTER SERVER AUDIT MyFileAudit WITH(STATE =ON)
--启⽤服务器级审核规范
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=ON)
GO
--启⽤数据库级审核规范
ALTER DATABASE AUDIT SPECIFICATION CaptureDBLoginsToFile WITH (STATE=ON)
GO
审核服务器级别事件
审核服务级别事件，我们⼀般⽤得最多的就是审核登录失败的事件，下⾯的脚本就是审核登录成功事件和登录失败事件
CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFile
FOR SERVER AUDIT MyFileAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
GO
修改审核规范
--跟审核对象⼀样，更改审核规范时必须将其禁⽤
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE =OFF)
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile
ADD (login_change_password_gourp),
DROP (successful_login_group)
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE =ON)
GO
审核操作组
每个审核操作组对应⼀种操作，在SQLSERVER2008⾥⼀共有35个操作组，包括备份和还原操作，数据库所有权的更改，从服务器和数据库⾓⾊中添加或删除登录⽤户
添加审核操作组的只需在审核规范⾥使⽤ADD，下⾯语句添加了登录⽤户修改密码操作的操作组
ADD (login_change_password_gourp)
这⾥说⼀下服务器审核的内部实际上使⽤的是SQL2008新增的扩展事件⾥⾯的其中⼀个package：SecAudit package，当然他内部也是使⽤扩展事件来收集服务器信息
审核数据库级别事件
数据库审核规范存在于他们的数据库中，不能审核tempdb中的数据库操作
CREATE DATABASE AUDIT SPECIFICATION和ALTER DATABASE AUDIT SPECIFICATION
⼯作⽅式跟服务器审核规范⼀样
在SQLSERVER2008⾥⼀共有15个数据库级别的操作组
7个数据库级别的审核操作是：select ,insert,update,delete,execute,receive,references
相关脚本如下：
--创建审核对象
USE[master]
GO
CREATE SERVER AUDIT MyDBFileAudit TO FILE(FILEPATH='D:\sqldbaudits')
GO
ALTER SERVER AUDIT MyDBFileAudit WITH (STATE=ON)
GO
--创建数据库级别审核规范
USE[sss]
GO
CREATE DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLog
FOR SERVER AUDIT MyDBFileAudit
ADD (database_object_change_group),
ADD (SELECT ,INSERT,UPDATE,DELETE ON schema::dbo BY PUBLIC)
WITH (STATE =ON)
我们先在D盘创建sqldbaudits⽂件夹
第⼀个操作组对数据库中所有对象的DDL语句create，alter，drop等进⾏记录
第⼆个语句监视由任何public⽤户（也就是所有⽤户）对dbo架构的任何对象所做的DML操作
创建完毕之后可以在SSMS⾥看到相关的审核
数据库审核规范
服务器审核规范和审核对象
查看审核事件ldapsearch使用
被记录到⽂件系统的审核⽂件不是存储在可以利⽤记事本打开的⽂本⽂件中，⽽是采⽤⼆进制⽂件的⽅式
这⾥说⼀个，当磁盘空间不⾜的时候是可以直接删除这些SQLAUDIT⽂件
如果使⽤DDL触发器的⽅法：
⼀般都会在数据库⾥头创建⼀张表来保存审计数据，但是当表数据量达到很多的时候，DBA也需要去维护这张表
⼯作量⼜增加了，可能你会说，我需要审计的项⽬不多，所以审计的数据也不会太多，但对于某些⼤公司来说
他们要审计的数据是⾮常多的，有些需要归档，⽽有些不需要归档
对于不需要归档审计数据的情况，我⽐较喜欢这种⽅式，当磁盘容量不够的时候把最⽼的那个审计⽂件删除掉
当然，你可以把整个sqlaudits⽂件夹或某个sqlaudit⽂件进⾏备份，放到备份磁盘上，然后删除⼀些较⽼的sqlaudit⽂件备份了之后以后就有机会对之前的审计数据进⾏翻查，都⽐较灵活
我们有两种⽅法查看审核⽇志
⽅法⼀：对象资源管理器-》安全性-》审核-》选中某个审核对象-》右键-》查看审核⽇志
审核项⽬包括有：⽇期、时间戳记、服务器实例名称、操作ID、类类型、序列号、成功或失败、列权限、数据库主体ID、服务器主体名称、服务器主体SID、被执⾏的（或尝试）的实际语句等等
⽅法⼆：使⽤新的表值函数sys.[fn_get_audit_file]()
此函数接受⼀个或多个审核⽂件的参数（使⽤通配符模式匹配）
并利⽤另外两个附加参数可以指定要处理的起始⽂件，以及开始读取审核的已知偏移位置
这两个参数都是可选的，但依然必须使⽤关键字default指定，此函数随后从⽂件中读取⼆进制数据，并将格式化这些审核项⽬
服务器级别审核
根据最近时间的那个sqlaudit⽂件，查询这个⽂件⾥⾯的信息
SELECT[event_time]AS'触发审核的⽇期和时间' ,
sequence_number AS'单个审核记录中的记录顺序' ,
action_id AS'操作的 ID' ,
succeeded AS'触发事件的操作是否成功' ,
permission_bitmask AS'权限掩码' ,
is_column_permission AS'是否为列级别权限' ,
session_id AS'发⽣该事件的会话的 ID' ,
server_principal_id AS'执⾏操作的登录上下⽂ ID' ,
database_principal_id AS'执⾏操作的数据库⽤户上下⽂ ID' ,
target_server_principal_id AS'执⾏ GRANT/DENY/REVOKE 操作的服务器主体' ,
target_database_principal_id AS'执⾏ GRANT/DENY/REVOKE 操作的数据库主体' ,
object_id AS'发⽣审核的实体的 ID(服务器对象，DB，数据库对象，架构对象)' ,
class_type AS'可审核实体的类型' ,
session_server_principal_name AS'会话的服务器主体' ,
server_principal_name AS'当前登录名' ,
server_principal_sid AS'当前登录名 SID' ,
database_principal_name AS'当前⽤户' ,
target_server_principal_name AS'操作的⽬标登录名' ,
target_server_principal_sid AS'⽬标登录名的 SID' ,
target_database_principal_name AS'操作的⽬标⽤户' ,
server_instance_name AS'审核的服务器实例的名称' ,
database_name AS'发⽣此操作的数据库上下⽂' ,
schema_name AS'此操作的架构上下⽂' ,
object_name AS'审核的实体的名称' ,
statement AS'TSQL 语句（如果存在）' ,
窗帘的种类additional_information AS'单个事件的唯⼀信息，以 XML 的形式返回' ,
sdkfile_name AS'记录来源的审核⽇志⽂件的路径和名称' ,
audit_file_offset AS'包含审核记录的⽂件中的缓冲区偏移量' ,
user_defined_event_id AS'作为 sp_audit_write 参数传递的⽤户定义事件 ID' ,
user_defined_information AS'于记录⽤户想要通过使⽤ sp_audit_write 存储过程记录在审核⽇志中的任何附加信息'
FROM sys.[fn_get_audit_file]('D:\sqlaudits\MyFileAudit_F0BCDC6F-0A89-459D-B345-9DDEB036CC39_0_130595725124220000.sqlaudit',
DEFAULT, DEFAULT)
WHERE[event_time]BETWEEN'2014-11-04 11:02:00'
AND'2014-11-04 11:18:00'
数据库级别审核
先执⾏下⾯脚本查询⼀些数据
USE[sss]
GO
SELECT*FROM[dbo].[nums]
SELECT[event_time]AS'触发审核的⽇期和时间' ,
sequence_number AS'单个审核记录中的记录顺序' ,
action_id AS'操作的 ID' ,
succeeded AS'触发事件的操作是否成功' ,
permission_bitmask AS'权限掩码' ,
is_column_permission AS'是否为列级别权限' ,
session_id AS'发⽣该事件的会话的 ID' ,
server_principal_id AS'执⾏操作的登录上下⽂ ID' ,
database_principal_id AS'执⾏操作的数据库⽤户上下⽂ ID' ,
target_server_principal_id AS'执⾏ GRANT/DENY/REVOKE 操作的服务器主体' ,
target_database_principal_id AS'执⾏ GRANT/DENY/REVOKE 操作的数据库主体' ,
object_id AS'发⽣审核的实体的 ID(服务器对象，DB，数据库对象，架构对象)' ,
class_type AS'可审核实体的类型' ,
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论