39、SQL Server 审计全解析

SQL Server 审计全解析

在 SQL Server 中，审计是一项至关重要的功能，它可以帮助我们捕获和存储关键信息，以便对服务器和数据库的活动进行监控和分析。下面将详细介绍如何使用 T-SQL 和 GUI 来创建 SQL Server 审计。

1. 使用 T-SQL 创建 SQL Server 审计

在定义服务器级或数据库级的审计操作之前，我们需要先创建一个 SQL Server 审计。创建 SQL Server 审计的语法如下：

CREATE SERVER AUDIT audit_name
TO { [ FILE (<file_options> [, ...n]) ] | APPLICATION_LOG | SECURITY_LOG }
[ WITH ( <audit_options> [, ...n] ) ]
WHERE [ < predicate_expresssions]

从这个语法中可以看出，创建 SQL Server 审计主要是定义审计的设置信息，它本身并不包含数据库级或服务器级的具体操作信息。实际上，需要将服务器级和数据库级的审计添加到 SQL Server 审计中，才能明确信息的捕获和存储方式。

创建审计时，需要考虑以下几个方面：
- 审计数据的存储位置 ：可以选择将捕获的数据写入文件、应用程序日志或安全日志。如果选择写入文件，需要指定文件路径和名称、文件的最大大小、滚动文件的数量，以及是否要在磁盘上预留最大文件大小。
- 可配置的审计选项 ：包括 QUEUE_DELAY 、 ON_FAILURE 和 AUDIT_GUID 。
- QUEUE_DELAY ：设置审计操作处理前可以经过的时间，以毫秒为单位，最小值和默认值为 1000 毫秒（即 1 秒）。
- ON_FAILURE ：决定当审计目标（审计文件的位置）不可达时的处理方式，可配置选项为 CONTINUE 和 SHUTDOWN ，默认值为 CONTINUE 。
- AUDIT_GUID ：允许指定现有审计的全局唯一标识符（GUID），用于在不同环境中保持 GUID 一致的情况。

以下是一个创建 SQL Server 审计的示例：

USE master;
GO
CREATE SERVER AUDIT exampleAudit
TO FILE
( FILEPATH = 'C:\', MAXSIZE = 1 GB )
WITH( ON_FAILURE = CONTINUE)
GO

2. 创建服务器审计规范

为了审计服务器级别的信息，需要创建服务器审计规范。服务器审计规范由服务器级操作组组成，这些操作组用于识别从服务器级别要审计的内容。服务器审计规范会在整个 SQL Server 实例中进行跟踪，没有边界限制，因此无法在服务器审计中过滤到特定的数据库。

创建服务器审计规范的语法如下：

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

创建服务器审计规范的步骤如下：
1. 指定要关联的 SQL Server 审计。
2. 将服务器级操作组名称添加到服务器审计规范中。
3. 确定在创建时是否启用审计。如果未启用，则在准备好捕获审计操作时必须启用它。

3. 服务器级操作组

服务器级操作组是预定义的组，用于从服务器角度审计服务器。由于这些操作组是预定义的，因此无法自定义每个组捕获的操作。不过，可以通过选择要添加到审计中的服务器级操作组来进行一定程度的定制。

以下是一些常用的服务器级操作组：
| 操作组名称 | 描述 |
| — | — |
| Successful_Login_Group | 跟踪成功登录到 SQL Server 实例的主体。 |
| Failed_Login_Group | 识别登录到 SQL Server 实例失败的主体。 |
| Server_Role_Member_Change_Group | 捕获固定服务器角色中登录的添加和删除操作。 |
| Database_Role_Member_Change_Group | 跟踪数据库角色中登录的添加和删除操作。 |
| Server_Object_Change_Group | 捕获服务器对象的创建、更改或删除权限操作。 |
| Server_Principal_Change_Group | 跟踪服务器主体的创建、删除或更改操作。 |
| Database_Change_Group | 识别数据库的创建、更改或删除操作。 |
| Database_Object_Change_Group | 捕获数据库内对象的创建、更改或删除操作。 |
| Database_Principal_Change_Group | 跟踪数据库主体的创建、修改或删除操作。 |
| Server_Permission_Change_Group | 识别主体对服务器对象授予、撤销或拒绝权限的操作。 |
| Database_Object_Permission_Change_Group | 捕获对数据库对象授予、撤销或拒绝权限的更改操作。 |

以下是一个创建服务器审计规范的示例：

CREATE SERVER AUDIT SPECIFICATION serverSpec
FOR SERVER AUDIT exampleAudit
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP)
GO

4. 测试服务器审计规范

为了演示服务器审计规范如何与 SQL Server 审计协同工作，下面给出一个示例。该示例创建了一个 SQL Server 审计和一个服务器审计规范，并包含触发审计的代码。

USE master;
GO
-- 创建服务器审计
CREATE SERVER AUDIT permissionChanges
TO FILE ( FILEPATH = 'C:\',MAXSIZE = 1 GB)
WITH( ON_FAILURE = CONTINUE)
GO
-- 创建服务器审计规范
CREATE SERVER AUDIT SPECIFICATION serverPermissionChanges
FOR SERVER AUDIT permissionChanges
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP)
GO
-- 启用审计和服务器审计规范
ALTER SERVER AUDIT permissionChanges WITH (STATE = ON)
GO
ALTER SERVER AUDIT SPECIFICATION serverPermissionChanges
WITH (STATE = ON)
GO
-- 创建审计将捕获的操作
CREATE LOGIN auditTest
WITH PASSWORD = 'Test123!'
GO
ALTER SERVER ROLE sysadmin
ADD MEMBER auditTest
GO
ALTER SERVER ROLE sysadmin
DROP MEMBER auditTest
GO
ALTER SERVER ROLE serveradmin
ADD MEMBER auditTest
GO
ALTER SERVER ROLE processAdmin
ADD MEMBER auditTest

要查看审计的内容，可以使用 fn_get_audit_file 函数，示例代码如下：

USE master;
GO
SELECT event_time,server_principal_name, object_name, statement,*
FROM fn_get_audit_file ('C:\perm*',NULL, NULL)

5. 创建数据库审计规范

如果需要审计数据库级别的信息，则需要创建数据库审计规范。数据库审计规范由数据库级审计操作组和/或数据库级审计操作组成。

创建数据库审计规范的语法如下：

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 ]

创建数据库审计规范的步骤与创建服务器审计规范类似，需要关联一个服务器审计，并添加数据库级审计操作组或审计操作规范。

6. 数据库级审计操作组

数据库级审计操作组由针对特定数据库的操作组成，用于监控数据库、架构和数据库内架构对象的操作。这些操作组是预定义的，无法进行自定义。

以下是一些常用的数据库级审计操作组：
| 操作组名称 | 描述 |
| — | — |
| Database_Role_Member_Change_Group | 跟踪数据库角色中登录的添加和删除操作。 |
| Database_Object_Change_Group | 识别对数据库对象执行的 CREATE 、 ALTER 或 DROP 语句。 |
| Database_Principal_Change_Group | 跟踪数据库主体的创建、更改和删除操作。 |
| Database_Permission_Change_Group | 跟踪数据库用户权限的更改。 |
| Database_Object_Permission_Change_Group | 识别对数据库对象授予、拒绝或撤销权限的操作。 |
| Schema_Object_Change_Group | 跟踪对架构对象执行的 CREATE 、 ALTER 和 DELETE 语句。 |

以下是一个添加数据库级审计操作组到数据库审计规范的示例：

USE master;
GO
CREATE SERVER AUDIT exampleAudit2
TO FILE ( FILEPATH = 'C:\',MAXSIZE = 1 GB)
WITH( ON_FAILURE = CONTINUE)
GO
USE AdventureWorks2012
GO
CREATE DATABASE AUDIT SPECIFICATION databaseActionGroup
FOR SERVER AUDIT exampleAudit2
ADD (DATABASE_PERMISSION_CHANGE_GROUP)
GO

7. 数据库级审计操作

数据库级审计操作允许对数据库内的特定、可定制操作进行审计。以下是一些常见的数据库级审计操作及其简要描述：
| 操作名称 | 描述 |
| — | — |
| Select | 跟踪 SELECT 语句的执行。 |
| Insert | 确定 INSERT 语句的执行时间。 |
| Delete | 识别 DELETE 语句的执行。 |
| Update | 跟踪对数据库执行的 UPDATE 语句。 |
| Execute | 确定 EXECUTE 语句的运行时间。 |
| Receive | 跟踪 RECEIVE 语句的发出。 |
| References | 识别权限引用的检查时间。 |

添加数据库级审计操作时，需要指定操作、可审计对象（如表格、视图等）和主体（如用户）。以下是一个添加数据库级审计操作到数据库审计规范的示例：

USE AdventureWorks2012
GO
CREATE DATABASE AUDIT SPECIFICATION databaseActionGroup
FOR SERVER AUDIT exampleAudit2
ADD (INSERT,UPDATE,DELETE ON Person.Person by dbo),

8. 测试数据库审计规范

下面的示例创建了一个数据库审计规范，并包含一些会触发审计事件的语句。

USE master;
GO
-- 创建服务器审计
CREATE SERVER AUDIT salaryViewing
TO FILE ( FILEPATH = 'C:\', MAXSIZE = 1 GB)
GO
-- 在要审计的数据库中创建数据库审计规范
USE AdventureWorks2012
GO
CREATE DATABASE AUDIT SPECIFICATION salaryQueries
FOR SERVER AUDIT salaryViewing
ADD (SELECT,UPDATE ON HumanResources.EmployeePayHistory by dbo),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP)
GO
USE master;
GO
ALTER SERVER AUDIT salaryViewing
WITH (STATE = ON)
GO
USE AdventureWorks2012
GO
ALTER DATABASE AUDIT SPECIFICATION salaryQueries
WITH (STATE = ON)
GO
-- 创建触发审计的操作
SELECT TOP 10 *
FROM AdventureWorks2012.HumanResources.EmployeePayHistory
SELECT JobTitle, Rate,RateChangeDate
FROM AdventureWorks2012.HumanResources.Employee e
JOIN AdventureWorks2012.HumanResources.EmployeePayHistory eh
ON e.BusinessEntityID = eh.BusinessEntityID ORDER BY JobTitle, RateChangeDate DESC
SELECT JobTitle, Rate,RateChangeDate
FROM AdventureWorks2012.HumanResources.Employee e
JOIN AdventureWorks2012.HumanResources.EmployeePayHistory eh
ON e.BusinessEntityID = eh.BusinessEntityID WHERE rate > 50.
ORDER BY JobTitle, RateChangeDate DESC
GO
CREATE LOGIN auditTest with password = 'abc123'
GO
USE AdventureWorks2012
GO
CREATE USER sneakyUser FOR LOGIN auditTest
GO

要查看数据库审计规范的结果，可以使用以下查询：

SELECT event_time,server_principal_name,database_principal_name,
object_name, statement,*
FROM fn_get_audit_file ('C:\sal*',NULL, NULL)

9. 使用 GUI 创建 SQL Server 审计

除了使用 T-SQL，还可以使用 SQL Server Management Studio 的 GUI 来创建 SQL Server 审计和数据库审计规范。

9.1 使用 GUI 创建 SQL Server 审计

步骤如下：
1. 连接到 SQL Server 实例。
2. 导航到“Security”文件夹，展开该文件夹。
3. 右键单击“Audits”文件夹，选择“New Audit”。
4. 在弹出的“Create Audit”对话框中：
- 为审计命名。
- 设置队列延迟（以毫秒为单位）。
- 如果希望在审计无法将数据记录到目标时关闭 SQL Server 实例，请选中“Shut Down Server on Audit Log Failure”旁边的复选框。
- 确定审计目标。如果选择“File”，则需要指定文件路径、最大文件大小、最大滚动文件数量，以及是否要预留磁盘空间。
- 填写完所有值后，点击“OK”创建审计。

9.2 使用 GUI 创建数据库审计规范

步骤如下：
1. 创建审计后，导航到要创建数据库审计规范的数据库。
2. 深入到“Security”文件夹，右键单击“Database Audit Specifications”，选择“New Database Audit Specification”。
3. 在弹出的“Create Database Audit Specification”对话框中：
- 为数据库审计规范命名，并选择要关联的审计。
- 有四个可选择的选项来创建数据库规范的操作：“Audit Action type”、“Object Class”、“Object Name” 和 “Principal Name”。
- 在“Audit Action Type”下拉列表中，包含数据库级审计操作组和数据库级审计操作。如果选择数据库级审计操作组，则无需选择其他选项，因为操作组的操作已经预定义。选择数据库级审计操作则可以灵活定义要审计的内容。
- “Object Class” 下拉列表包含三个选项：“Schema”、“Database” 和 “Object”，用于指定要审计的对象类型。

通过以上介绍，我们可以看到 SQL Server 提供了丰富的审计功能，无论是使用 T-SQL 还是 GUI，都可以方便地创建和配置审计，以满足不同的监控和分析需求。

SQL Server 审计全解析

10. 审计操作的流程图总结

为了更清晰地展示 SQL Server 审计的创建和配置过程，下面给出 mermaid 格式的流程图：

graph LR
    classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px
    classDef decision fill:#FFF6CC,stroke:#FFBC52,stroke-width:2px

    A([开始]):::startend --> B(创建 SQL Server 审计):::process
    B --> C{选择审计目标}:::decision
    C -->|文件| D(指定文件路径、大小等):::process
    C -->|应用程序日志| E(选择应用程序日志):::process
    C -->|安全日志| F(选择安全日志):::process
    D --> G(设置审计选项):::process
    E --> G
    F --> G
    G --> H(创建服务器审计规范或数据库审计规范):::process
    H --> I{选择规范类型}:::decision
    I -->|服务器审计规范| J(添加服务器级操作组):::process
    I -->|数据库审计规范| K(添加数据库级操作组或操作):::process
    J --> L(启用审计和规范):::process
    K --> L
    L --> M(执行审计操作):::process
    M --> N(查看审计结果):::process
    N --> O([结束]):::startend

这个流程图展示了从开始创建 SQL Server 审计，到选择审计目标、设置审计选项，再到创建不同类型的审计规范，最后执行审计操作并查看结果的整个过程。

11. 审计的注意事项和最佳实践

在进行 SQL Server 审计时，有一些注意事项和最佳实践可以帮助我们更好地使用审计功能：
- 性能影响 ：审计操作会对 SQL Server 的性能产生一定的影响，尤其是在高并发环境下。因此，需要根据实际情况合理选择要审计的操作和频率。例如，只审计关键的操作，避免对所有操作进行审计。
- 审计文件管理 ：如果选择将审计数据存储在文件中，需要定期清理和管理审计文件，避免文件占用过多的磁盘空间。可以设置文件的最大大小和滚动文件的数量，以控制文件的增长。
- 权限管理 ：确保只有授权的人员可以访问和管理审计数据。可以通过 SQL Server 的权限系统来控制对审计文件和审计规范的访问。
- 定期审查 ：定期审查审计结果，及时发现异常操作和潜在的安全问题。可以设置警报机制，当出现特定的审计事件时及时通知管理员。

12. 常见问题解答

以下是一些关于 SQL Server 审计的常见问题解答：
- 问 ：审计无法将数据记录到目标时会发生什么？
答 ：可以通过 ON_FAILURE 选项来配置处理方式。默认值是 CONTINUE ，即继续执行操作；也可以选择 SHUTDOWN ，在这种情况下，当审计无法将数据记录到目标时，SQL Server 实例将关闭。
- 问 ：如何查看审计结果？
答 ：可以使用 fn_get_audit_file 函数来查看审计文件的内容。示例代码如下：

USE master;
GO
SELECT event_time,server_principal_name, object_name, statement,*
FROM fn_get_audit_file ('C:\perm*',NULL, NULL)

• 问 ：是否可以同时使用服务器级和数据库级审计规范？
  答 ：可以。服务器级审计规范用于审计服务器级别的信息，数据库级审计规范用于审计数据库级别的信息。可以根据需要同时创建和使用这两种规范。

13. 总结

SQL Server 审计是一个强大的功能，它可以帮助我们监控和分析服务器和数据库的活动，及时发现潜在的安全问题。通过使用 T-SQL 或 GUI，我们可以方便地创建和配置审计，包括创建 SQL Server 审计、服务器审计规范和数据库审计规范，以及添加不同的操作组和操作。同时，我们还需要注意审计对性能的影响，合理管理审计文件，并定期审查审计结果。希望本文的介绍能够帮助你更好地使用 SQL Server 审计功能。

通过以上内容，我们全面了解了 SQL Server 审计的各个方面，从创建审计到配置不同类型的规范，再到测试和查看结果，以及注意事项和常见问题解答。无论是新手还是有经验的数据库管理员，都可以根据这些信息来优化和管理 SQL Server 的审计工作。