SQL Server 数据库审核机制的实施-猿码集

1. SQL Server 数据库审核机制的概述

数据库审核机制作为数据库管理的重要一环，能够监控并记录数据库中的访问和操作行为，以便于管理员对数据库的安全状况进行评估和控制。针对 SQL Server 数据库，Microsoft 提供了多种审核机制，包括：

服务器级别审核

数据库级别审核

DDL 触发器

CLR 触发器

行级别安全性

下面将逐一介绍这几种审核机制的特点和使用方法。

2. 服务器级别审核

2.1 特点

服务器级别审核是一种非常全面的审核机制，可以监控所有针对 SQL Server 实例的操作，包括登录、数据库的创建和删除、服务器级别对象的创建和删除等。

2.2 使用方法

服务器级别审核的使用分为三个步骤：

创建审核

指定审核目标

查看审核结果

步骤一：创建审核。可以通过 SQL Server Management Studio 或者 Transact-SQL 语句来创建服务器级别审核。

USE master;
GO
CREATE SERVER AUDIT ServerAudit
TO FILE (
       FILEPATH = 'D:\Audit\',
       MAXSIZE = 0 MB,
       MAX_FILES = 2147483647,
       RESERVE_DISK_SPACE = OFF
)
WITH (
       QUEUE_DELAY = 1000,
       ON_FAILURE = CONTINUE,
       AUDIT_GUID = '633be06d-3c1a-4c7b-946b-ece2c465a607'
);
GO
ALTER SERVER AUDIT ServerAudit WITH (STATE = ON);
GO

步骤二：指定审核目标。可以对服务器实例的各种安全审计操作进行审核，例如，针对所有数据库对象进行审计：

USE master;
GO
CREATE SERVER AUDIT SPECIFICATION ServerAuditSpecification
FOR SERVER AUDIT ServerAudit
ADD (DATABASE_OBJECT_AUDIT_GROUP)
WITH (STATE = ON);
GO

步骤三：查看审核结果。可以通过 SQL Server Management Studio 或者 Transact-SQL 语句来查看服务器级别审核的结果。

SELECT * FROM sys.fn_get_audit_file('D:\Audit\ServerAudit*.sqlaudit', DEFAULT, DEFAULT);

3. 数据库级别审核

3.1 特点

数据库级别审核是一种对单个数据库进行安全审计的机制，相比服务器级别审核，可以更细粒度地控制访问和操作权限。

3.2 使用方法

数据库级别审核的使用分为三个步骤：

创建审核

指定审核目标

查看审核结果

步骤一：创建审核。可以通过 SQL Server Management Studio 或者 Transact-SQL 语句来创建数据库级别审核。

USE AdventureWorks2016CTP3;
GO
CREATE DATABASE AUDIT SPECIFICATION DatabaseAuditSpecification
FOR SERVER AUDIT ServerAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON HumanResources.Department BY dbo)
WITH (STATE = ON);
GO

步骤二：指定审核目标。可以对数据库中的各种安全审计操作进行审核，例如，针对 HR 部门表进行审计：

USE AdventureWorks2016CTP3;
GO
ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpecification
WITH (STATE = OFF);
GO
ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpecification
ADD (SELECT, INSERT, UPDATE, DELETE ON HumanResources.Department BY dbo)
WITH (STATE = ON);
GO

步骤三：查看审核结果。可以通过 SQL Server Management Studio 或者 Transact-SQL 语句来查看数据库级别审核的结果。

SELECT * FROM sys.dm_audit_actions
WHERE server_instance_name = 'localhost'
AND AUDIT_TYPE = 'DATABASE'
AND database_name = 'AdventureWorks2016CTP3'
AND audit_action_name = 'SELECT';

4. DDL 触发器

4.1 特点

DDL 触发器（Data Definition Language Triggers）用于监控数据库中的数据定义语言操作，如 CREATE、ALTER、DROP 等操作，支持的事件包括表、视图、存储过程等对象的创建和删除。

4.2 使用方法

DDL 触发器的使用分为两个步骤：

创建触发器

查看触发器执行结果

步骤一：创建触发器。可以通过 SQL Server Management Studio 或者 Transact-SQL 语句来创建 DDL 触发器。

USE AdventureWorks2016CTP3;
GO
CREATE TRIGGER DDLTrigger
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @EventData XML = EVENTDATA();
  INSERT INTO DDLLog (PostTime, DBUser, Event, TSQL)
  VALUES (GETDATE(),
          CONVERT(NVARCHAR(128), CURRENT_USER),
          @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
          @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'))
END;
GO

步骤二：查看触发器执行结果。可以通过 SQL Server Management Studio 或者 Transact-SQL 语句来查看 DDL 触发器的执行结果。

SELECT * FROM DDLLog;

5. CLR 触发器

5.1 特点

CLR 触发器（Common Language Runtime Triggers）是基于 .NET Framework 的技术，允许编写自定义的触发器，可以监控数据库中用户自定义函数、存储过程以及 CLR 程序集等对象的执行情况。

5.2 使用方法

CLR 触发器的使用分为两个步骤：

创建 CLR 程序集

创建 CLR 触发器

步骤一：创建 CLR 程序集。CLR 触发器需要使用 .NET Framework 编写，因此需要先在 SQL Server 中创建 CLR 程序集。

USE master;
GO
CREATE ASSEMBLY TriggerAssembly
FROM 'D:\CLR\TriggerAssembly.dll'
WITH PERMISSION_SET = UNSAFE;
GO

步骤二：创建 CLR 触发器。可以通过 SQL Server Management Studio 或者 Transact-SQL 语句来创建 CLR 触发器。

USE AdventureWorks2016CTP3;
GO
CREATE TRIGGER CLRTrigger
    ON  DATABASE
    FOR EXECUTE ASSEMBLY
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @EventData XML = EVENTDATA();
        INSERT INTO CLRLog (PostTime, Event, TSQL)
        VALUES (GETDATE(),
                @EventData.value('/EVENT_INSTANCE/EventType[1]', 'nvarchar(100)'),
                @EventData.value('/EVENT_INSTANCE/TSQLCommand[1]', 'nvarchar(max)'));
    END;
GO

CLR 触发器的使用要比 DDL 触发器更加复杂，需要懂得 .NET Framework 的编程和部署技术。

6. 行级别安全性

6.1 特点

行级别安全性（Row-Level Security）是 SQL Server 2016 引入的新功能，可以基于业务规则对数据库中的行数据进行访问控制和保护，保证敏感数据的安全性。

6.2 使用方法

行级别安全性的使用分为四个步骤：

创建安全策略

实现保护规则

授权用户访问

测试安全策略

具体步骤可以参考 Microsoft 的官方文档：

Row-Level Security (RLS) with SQL Server

7. 结论

SQL Server 提供多重审核机制，包括服务器级别审核、数据库级别审核、DDL 触发器、CLR 触发器和行级别安全性。管理员可以根据实际需求进行选择和使用，保证数据库的安全性和完整性。

SQL Server 数据库审核机制的实施