SQL Server 审计 第一篇:介绍(Audit)

SQL Server 审计系列:

 

审计(Audit)用于追踪和记录SQL Server实例,或者单个数据库中发生的事件(Event),审计运作的机制是通过捕获事件(Event),把事件包含的信息写入到事件日志(Event Log)或审计文件(Audit File)中,为review提供最真实详细的数据。

审计主要包含服务器审计对象(Server Audit,简称审计对象)、服务器级别的审计规范(Server Audit Specification)、数据库级别的审计规范(Database Audit Specification)和目标(Target)。

  • 审计对象是在服务器级别上创建的对象,必须启用,用于指定审计数据的存储方式和存储路径,并提供工具查看审计数据。
  • 服务器级别的审计规范用于记录服务器级别的事件(Event),
  • 数据库级别的审计规范用于记录数据库级别的事件,
  • Target是指用于存储Audit数据的媒介,可以是File、Windows Security Event Log 或 Windows Application Event Log,常用的Target是File。

SQL Server使用 Extended Events来帮助创建审计,也就是说,审计是在扩展事件的基础上设计的功能,专门用于审核数据库的安全。为了启用审计,首先需要创建一个SQL Server 实例级的审计对象,然后创建从属于它的“服务器审计规范”或“数据库审计规范”,审计输出的结果数据可以存储到审计文件(File)、安全日志(Security Log)和应用程序日志(Application Log)中。

一,审计对象的构成

构成审计的成分主要有4大类:审计对象、服务器级别的审计规范、数据库级别的审计规范和目标(Target)。

  • 审计对象可以包含一个或多个审计规范,用于指定输出结果存储的路径。审计对象创建时默认的状态是disable。
  • 服务器级别的审计规范,从属于审计对象,它包含服务器级别的审计动作组(Audit Action Group)或单个Audit Action,这些动作组或动作由Extended Events触发。
  • 数据库级别的审计规范,也从属于审计对象,它包含数据库级别的审计动作组(Audit Action Group)或单个Audit Action,这些动作组或动作由Extended Events触发。
  • Target(也称作Audit Destination)用于存储审计的结果,Target可以是一个file,Windows Security Event log 或者Windows application event log.

Action Group 是预定义的一组Action,每一个Action(也称作Action Event)都是一个原子事件,因此,Action Group也是由Action Event构成的组合。当Event(也称作Action)发生时,Event被发送到审计对象(audit)中,SQL Server把数据记录到Target中。关于审计的动作组和动作的详细信息,请阅读官方文档:SQL Server Audit Action Groups and Actions

二,审计对象

审计监控SQL Server实例,从服务器级别或数据库级别的Action或Action Group中收集这些Action发生时产生的数据。

当定义一个审计对象时,需要指定保存审计输出结果的路径,即audit的Target(审计的目的,audit destination)。新建的Audit默认处于disable状态,不能自动追踪和记录(审计)任何audit action。当Audit启用后,audit destination会接收从审计输出的数据。

创建审计对象的语法:

CREATE SERVER AUDIT audit_name  
{  
    TO { [ FILE (<file_options> [ , ...n ] ) ] | APPLICATION_LOG | SECURITY_LOG | URL | EXTERNAL_MONITOR }  
    [ WITH ( <audit_options> [ , ...n ] ) ]   
    [ WHERE <predicate_expression> ]  
}  [ ; ]  
  
<file_options>::=  
{  
        FILEPATH = 'os_file_path'  
    [ , MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ]  
    [ , { MAX_ROLLOVER_FILES = { integer | UNLIMITED } } | { MAX_FILES = integer } ]  
    [ , RESERVE_DISK_SPACE = { ON | OFF } ]   
}  
  
<audit_options>::=  
{  
    [   QUEUE_DELAY = integer ]  
    [ , ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION } ]  
    [ , AUDIT_GUID = uniqueidentifier ]  
}  

主要参数注释:

  • FILEPATH ='os_file_path':指定审计日志存储的目录,目录中包含的文件是基于审计名称和审计GUID产生的。
  • MAXSIZE = { max_size }:指定一个审计文件的最大容量,
  • MAX_ROLLOVER_FILES ={ integer | UNLIMITED }:指定审计包含的审计文件的最大数量,当文件数量达到限制的数量时,SQL Server会自动删除创建时间最早的审计文件。
  • MAX_FILES =integer:是指审计只能最多包含的审计文件的数量,当文件数量达到限制的数量时,不会删除最老的审计文件,当产生更多的审计数据时,SQL Server会抛出错误,并失败。
  • RESERVE_DISK_SPACE = { ON | OFF }:在Disk上为审计预先分配MAXSIZE的空间
  • QUEUE_DELAY =integer:单位是毫秒(千分之一秒),用于指定一个Audit Action从发生到被强制处理可以经过的时间间隔,默认值是1000,即1秒,这也是可以甚至的最小查询延迟。值为0表示同步传送,
  • ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION }:当Target不能继续写入审计日志时,审计对象的动作:
    •   CONTINUE,是指不保留审计记录,审计继续尝试记录事件,如果解决了故障情况,则继续进行审计。
    •   SHUTDOWN:如果SQL Server由于任何原因不能写入审计记录,那么强制关闭SQL Server实例
    •   FAIL_OPERATION:如果数据库操作导致审计事件的发生,那么执行的数据库操作将失败;不会导致审计事件发生的操作,将继续执行。

注意:审计对象(Server Audit)的create、alter、或 drop,需要ALTER ANY SERVER AUDIT或CONTROL SERVER的权限。当保存Audit Log时,为了保证审计数据的安全,需要限制无关人员的权限,禁止无关人员访问审计文件的存储目录。

举个例子,创建审计对象:

CREATE SERVER AUDIT [AuditMonitorQuery]
TO FILE 
(    FILEPATH = N'G:\AuditFiles\MonitorQuery\'
    ,MAXSIZE = 1 GB
    ,MAX_ROLLOVER_FILES = 128
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'xxx'
)
ALTER SERVER AUDIT [AuditMonitorQuery] WITH (STATE = ON)
GO

三,服务器级别的审计规范

服务器级别的审计规范属于Server Audit,服务器界别的审计规范从服务器级别的action group中收集数据。

CREATE SERVER AUDIT SPECIFICATION audit_specification_name  
FOR SERVER AUDIT audit_name  
{  
    { ADD ( { audit_action_group_name } )   
    } [, ...n]  
    [ WITH ( STATE = { ON | OFF } ) ]  
}  

注意:拥有ALTER ANY SERVER AUDIT权限的用户可以创建服务器级别的审计规范,拥有CONTROL SERVER权限或sysadmin可以查看审计数据。

举个例子:创建服务器级别的审计规范

CREATE SERVER AUDIT SPECIFICATION HIPAA_Audit_Specification  
FOR SERVER AUDIT HIPAA_Audit  
    ADD (FAILED_LOGIN_GROUP)  
    WITH (STATE=ON); 

四,数据库级别的审计规范

数据库级别的审计规范属于一个Server Audit, 数据库级别的审计规范从数据库级别的action group,或者audit event中收集数据。action group是预先定义的一组audit event,这些action产生的信息都会被发送到Audit,被存储到Target中。

CREATE DATABASE AUDIT SPECIFICATION audit_specification_name  
{  
    FOR SERVER AUDIT audit_name   
        [ { ADD ( { <audit_action_specification> | audit_action_group_name } ) } [, ...n] ]  
    [ WITH ( STATE = { ON | OFF } ) ]  
} [ ; ]  
<audit_action_specification>::= action [ ,...n ] ON [ class :: ] securable BY principal [ ,...n ]

重要参数注释:

  • action:数据库级别的action的名,
  • class:securable的class name
  • securable:数据库中审计作用(应用audit action 或audit action group)的table、view 等安全对象(Securable Object)。
  • principal:数据库中审计作用(应用audit action 或audit action group)的principal,如果审计所有的principal,请使用public。

注意:拥有 ALTER ANY DATABASE AUDIT 权限的用户可以创建数据库级别的审计规范,当数据库级别的审计规范被创建之后,可以被CONTROL SERVER和sysadmin产看。

举个例子,对数据库中的所有对象,记录select  和 

CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables  
FOR SERVER AUDIT HIPAA_Audit  
ADD (SELECT , INSERT, UPDATE, DELETE, EXEUCTE ON database :: db_name BY public )  
WITH (STATE = ON) ;

五,Target

审计输出的结果会被发送到Target,实际上,Target的作用是存储审计数据,可以是一个文件,Windows Security event log或Windows Application event log。

任何授权的用户都可以读写Windows Application event log,它的安全级别比Windows Security event log要低。通常情况下,审计数据都会存储到单独的文件中。

当把审计信息存储到一个file,为了保证审计数据的安全,可以通过以下方式限制对文件位置的访问:

  • SQL Server Service Account必须同时具有读写权限
  • Audit 管理员需要有审计文件的读写权限
  • Audit Reader:具有读取审计文件的权限

因为数据库引擎可以访问审计文件,拥有CONTROL SERVER权限的SQL Server Login都可以使用数据库引擎来访问审计文件。为了记录正在读取审计文件的用户,需要定义一个审计,追踪和记录用户对函数master.sys.fn_get_audit_file的调用记录,这个审计记录了拥有CONTROL SERVER权限的Login通过SQL Server访问审计文件的记录。

如果审计管理员(Audit Admin)把文件复制到其他位置(处于存档等目的),那么需要将新位置上的权限降级为:

  • Audit Administrator - Read / Write
  • Audit Reader - Read

注意:为了保护审计文件,应该对审计文件进行加密,对存储审计文件的目录使用Windows BitLocker Drive Encryption或Windows Encrypting File System,从而防止未经授权的访问。

六,审计动作

审计需要记录服务器级别的Action Group,或者数据库级别的Action Group或者单个Action Event。

审计Action主要分为三类:

  • 服务器级别:这类Action主要包含服务器操作,包含登录和登出操作
  • 数据库级别:这类Action主要包括数据操作语言(DML)和数据定义语言(DDL)。
  • 审计级别:在审计过程中的动作,Action Group只有一个:AUDIT_CHANGE_GROUP

详细描述,请阅读官方手册:SQL Server Audit Action Groups and Actions

我列出比较重要的审计动作或审计动作组。

1,服务器级别的审计动作组

  • AUDIT_CHANGE_GROUP:当Audit被创建、修改和删除时,触发该事件
  • BATCH_COMPLETED_GROUP:当任何batch、sp或事务完成执行时,触发该事件,并记录下执行的命令的文本。
  • BATCH_STARTED_GROUP:当任何batch、sp或事务开始执行时,触发该事件,并记录下执行的命令的文本。
  • DATABASE_OBJECT_CHANGE_GROUP:当任何数据库中的对象执行create、alter或drop命令时,触发该事件,可能会产生巨大的审计日志
  • FAILED_LOGIN_GROUP:当一个principal尝试登录SQL Server,并失败时,触发该事件
  • FAILED_DATABASE_AUTHENTICATION_GROUP:当一个Principal尝试登录数据库,并失败时,触发该事件
  • SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP:当一个principal成功登录SQL Server中的数据库时,触发该事件
  • SUCCESSFUL_LOGIN_GROUP:当一个principal成功登录SQL Server时,触发该事件

2,数据库级别的审计动作组

  • BATCH_COMPLETED_GROUP
  • BATCH_STARTED_GROUP
  • DATABASE_OBJECT_CHANGE_GROUP:当对数据库对象执行CREATE、ALTER、 或 DROP命令时,触发该事件
  • DATABASE_OBJECT_PERMISSION_CHANGE_GROUP:当数据库对象的权限(执行GRANT, REVOKE, or DENY)发生变化时,触发该事件
  • DATABASE_PRINCIPAL_CHANGE_GROUP:当数据库中的user,role等发生变化时,触发该事件
  • SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP:当一个Schema中的对象的权限(执行GRANT, REVOKE, or DENY)发生变化时,触发该事件
  • SCHEMA_OBJECT_CHANGE_GROUP:当对Schema中的对象执行CREATE、ALTER、 或 DROP命令时,触发该事件
  • SCHEMA_OBJECT_ACCESS_GROUP:当schema中的对象的权限发生变化时,触发该事件

3,数据库级别的单个审计动作

数据库级别的审计动作(Audit Action),分别在执行查询、修改、插入、删除、执行、等命令时触发

  • SELECT:This event is raised whenever a SELECT is issued.
  • UPDATE:
  • INSERT:
  • DELETE:
  • EXECUTE:
  • RECEIVE:This event is raised whenever a RECEIVE is issued.
  • REFERENCES:This event is raised whenever a REFERENCES permission is checked.

RECEIVE 是指 Service Broker queues。

七,跟审计相关的权限控制 

创建、修改或删除 Server Audit 或者 服务器级别的审计规范,需要服务器级别的权限,ALTER ANY SERVER AUDIT 或者CONTROL SERVER的权限。

创建、修改或删除数据库级别的审计规范,不仅需要数据库的权限:ALTER ANY DATABASE AUDIT、ALTER,或者 CONTROL权限,而且还需要服务器级别的权限:ALTER ANY SERVER AUDIT 或者CONTROL SERVER的权限。

注意,sysadmin角色可以修改任何审计组件,db_owner角色可以修改数据库级别的审计规范。

查看Audit Log需要CONTROL SERVER的权限,用户既可以通过SSMS的View Audit Logs来查看审计日志,也可以通过函数sys.fn_get_audit_file (Transact-SQL) 来查看审计日志,这两种方式都需要具有CONTROL SERVER的权限。

 

 

参考文档:

SQL Server Audit (Database Engine)

posted @ 2021-04-25 15:32  悦光阴  阅读(1884)  评论(0编辑  收藏  举报