SQL Server的审核/审计功能

SQLServer走起 2020-03-02

4098

最近微联盟事件闹的沸沸扬扬，由于腾讯云的兜底，最终数据全部找回，估计腾讯云也派了专家到微盟自建机房去恢复数据

然后事件造成的直接影响是公司股价下跌几个亿，还要赔付1.5亿元人民币，实打实的真金白银流出

后续的保障措施就是，跟腾讯云进行非常深度合作，走起君估计，该公司可能要干掉运维部和所有运维人员，只留一个运维人员点一下鼠标，按一下腾讯云的界面按钮得了

然后上个星期各个新闻平台大肆报道，加重了事件的影响，再然后就是各个卖兜底设备的厂商比如某墨、某趣等，开始在公众号大肆打广告，再再然后就是各个技术专家自媒体开始写《数据安全保障手段》的文章攒一波流量

这里说一下SQL Server自带的审计功能

SQL Server在2008版本的时候就已经自带了审计功能，可以用来审计是否有drop表，是否有delete数据的相关操作

在sqlserver2008新增了审核功能，可以对服务器级别和数据库级别的操作进行审核/审计

我们看一下审核的使用方法

审核对象

步骤一：创建审核对象，审核对象是跟保存路径关联的，所以如果你需要把审核操作日志保存到不同的路径就需要创建不同的审核对象

我们把审核操作日志保存在文件系统里，在创建之前我们还要在相关路径先创建好保存的文件夹，我们在D盘先创建sqlaudits文件夹，然后执行下面语句

--创建审核对象之前需要切换到master数据库
USE [master]
GO
CREATE SERVER AUDIT MyFileAudit TO FILE(FILEPATH='D:\sqlaudits') --这里指定文件夹不能指定文件，生成文件都会保存在这个文件夹
GO

实际上，我们在创建审核对象的同时可以指定审核选项，下面是相关脚本

把日志放在磁盘的好处是可以使用新增的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 CaptureLoginsToFile
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
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
GO

我们创建一个服务器级别的审核规范CaptureLoginsToFile，然后再创建多一个审核对象MyAppAudit ，这个审核对象会把审核日志保存到Windows事件日志的应用程序日志里

我们禁用审核规范CaptureLoginsToFile，修改审核规范CaptureLoginsToFile属于审核对象MyAppAudit ，修改成功

而如果要把多个审核规范绑定到同一个审核对象则会报错

CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFileA
FOR SERVER AUDIT MyFileAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
GO


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里看到相关的审核

数据库审核规范

服务器审核规范和审核对象

查看审核事件

被记录到文件系统的审核文件不是存储在可以利用记事本打开的文本文件中，而是采用二进制文件的方式

这里说一个，当磁盘空间不足的时候是可以直接删除这些SQLAUDIT文件

当然，你可以把整个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 的形式返回' ,
        file_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 '可审核实体的类型' ,
        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 的形式返回' ,
        file_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:\sqldbaudits\MyDBFileAudit_698BA060-CC40-4A3C-B19D-12B370712404_0_130595753193920000.sqlaudit',
                                DEFAULT, DEFAULT)

将审核日志保存到文件系统的好处就是可以使用TVP里通过where 和order by对审核数据进行筛选和排序

和审核相关的视图

--查询审核相关视图
SELECT * FROM sys.[server_file_audits]
SELECT * FROM sys.[server_audit_specifications]
SELECT * FROM sys.[server_audit_specification_details]
SELECT * FROM sys.[database_audit_specifications]
SELECT * FROM sys.[database_audit_specification_details]
SELECT * FROM sys.[dm_server_audit_status]
SELECT * FROM sys.[dm_audit_actions]
SELECT * FROM sys.[dm_audit_class_type_map]

删除相关对象

--删除顺序
--删除数据库审核规范
USE [sss]
GO
ALTER DATABASE AUDIT SPECIFICATION [CaptureDBActionToEventLog] WITH (STATE=OFF)
GO
DROP DATABASE AUDIT SPECIFICATION [CaptureDBActionToEventLog]
GO


--删除服务器审核规范
USE [master]
GO
ALTER SERVER  AUDIT SPECIFICATION [CaptureLoginsToFile] WITH (STATE=OFF)
GO
DROP SERVER AUDIT SPECIFICATION [CaptureLoginsToFile]
GO


--删除审核对象
ALTER SERVER AUDIT [MyFileAudit] WITH (STATE=OFF)
GO
ALTER SERVER AUDIT [MyAppAudit] WITH (STATE=OFF)
GO
ALTER SERVER AUDIT [MyEventLogAudit] WITH (STATE=OFF)
GO
DROP SERVER AUDIT [MyAppAudit]
GO
DROP SERVER AUDIT [MyFileAudit]
GO
DROP SERVER AUDIT [MyEventLogAudit]
GO

要查看是否打开了审核引擎使用下面SQL语句

“XE engine initialized已经初始化”表示审核引擎已经打开，扩展事件可以捕获审核事件

SELECT * FROM sys.[dm_os_ring_buffers] WHERE [ring_buffer_type]='RING_BUFFER_XE_LOG'


<Record id = "0" type ="RING_BUFFER_XE_LOG" time ="106735">
<XE_LogRecord message="XE engine initialized"></XE_LogRecord></Record>

图片来源于网络，侵权即删

微信公众号能置顶啦，喜欢我的小伙伴们请将我置顶吧，这样就不用担心找不到我了哦~

sql server

最后修改时间：2020-03-02 13:46:41

文章转载自SQLServer走起，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL Server的审核/审计功能

评论