如何在 SQL Server 中实现行级和列级安全性

SQL Server 包含一系列安全功能。其中最有价值的两个是列级安全性和行级安全性，它们使您能够精细地授予用户对表中行和列的访问权限。本文介绍如何在您的环境中实现这些功能。

担保物、委托人和权限

在深入研究行和列安全性之前，我们需要回顾一些 SQL Server 安全基础知识。基本上，数据库安全涉及允许某人访问数据库中存储的数据并对其执行操作。 “某人”是主体，他们被允许采取的操作是他们的权限，并且执行操作的数据是安全的 - 安全对象的示例包括数据库、表、模式、行和列。

让我们看一下可应用于安全对象的权限：

• SELECT — 委托人需要此权限才能从表中获取或读取数据。
• INSERT — 委托人需要此权限才能向表中添加新行。
• 更新 - 委托人需要此权限才能修改表中的数据。
• DELETE — 委托人需要此权限才能从表中删除行。
• 引用 — 如果委托人对表没有其他权限，则他们需要此权限才能创建表的外键。

SQL Server 列级安全性

SQL Server中有两种保护列的方法：列级权限和列级加密。对于管理员来说，最棒的是这两种技术可以结合起来。

列级权限

列级权限为数据库中的数据提供更细粒度的安全性。您不需要为每一列执行单独的 GRANT 或 DENY 语句；只需在查询中将它们全部命名即可：

GRANT SELECT ON data1.table (column1, column2) TO user1;

GO

DENY SELECT ON data1.table (column3) TO user1;

GO

如果您在表级别对用户的列执行 DENY 语句，然后对同一列执行 GRANT 语句，则 DENY 权限将被删除，并且用户可以访问该列。同样，如果执行GRANT，然后执行DENY，则DENY权限将生效。

列级加密

列级加密是对列中数据的直接加密。它需要更改代码，因为它只能在对数据库进行编程时应用。这是一种加密数据的精细方式，因此只能加密特定列或部分列。您甚至可以对不同的列使用不同的加密密钥。列级加密需要二进制列来存储加密数据。而且，它把密钥存储在服务器上，因此SQL数据库需要信任存储密钥的服务器。

列级加密使用对称密钥来加密数据，因为这有助于保持生产力，并且每个对称密钥都受到非对称密钥的保护。您可以对对称密钥使用不同的算法，但 AES 是最佳选择。此外，对称密钥还可以使用密码或主密钥进行保护。

为了保证密钥的安全，您可以使用 SQL 权限来限制只有受信任的用户帐户才能访问它们。另一个重要注意事项是加密数据不可索引；如果你想索引它，你需要先解密它。

要实现列级加密，我们需要采取以下步骤：

1. 首先，我们创建数据库主密钥来加密对称密钥。这可以使用CREATE MASTER KEY命令和ENCRYPTION BY PASSWORD参数来完成，该参数定义了加密密钥的密码：

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$w0rd'

GO

1. 然后我们必须创建一个对称密钥，但为了确保它的安全，我们应该制作一个数字签名的证书。要创建证书，请使用CREATE CERTIFICATE命令，该命令将受到我们的主密钥的保护。然后我们可以使用CREATE SYMMETRIC KEY命令和AES_256加密算法创建对称密钥。

CREATE CERTIFICATE SelfSignedCertificate WITH SUBJECT = 'Column Encryption';

GO

CREATE SYMMETRIC KEY SQLSymmetricKey

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE SelfSignedCertificate;

GO

1. 接下来，我们需要向表中添加一列来存储加密数据。

USE DB;

GO

ALTER TABLE Testtable

ADD EncryptedColumn varbinary(MAX )NULL

GO

1. 为了加密表数据，我们需要打开对称密钥并更新表。

OPEN SYMMETRIC KEY SQLSymmetricKey

DECRYPTION BY CERTIFICATE SelfSignedCertificate;

1. 一旦对称密钥打开，我们就需要更新表。

UPDATE Testtable

SET [EncryptedColumn] = EncryptByKey(Key_GUID('SQLSymmetricKey'), ColumnWithSensitiveData);

GO

select * from Testtable

1. 加密列已创建；它包含 columnwithsensitivedata 的加密数据 为了实现我们的 SQL Server 安全目标，我们应该使用 DROP 命令删除现有的纯文本 columnwithsensitivedata 列，并仅保留加密列。

SQL Server 行级安全性

行级安全性（RLS）允许开发人员逐行控制用户对信息的访问权限；每个用户只能看到他们有权访问的行。此安全功能具有高级别的权限集，因为它绕过现有的 SQL Server 安全访问列表。它由三部分组成：

• 谓词函数 — 这是一个架构函数，用于控制执行查询的用户帐户是否有权访问该行。

• 安全谓词 - 安全谓词将谓词函数绑定到表。有两种类型的安全谓词：

  • 过滤谓词 — 这限制了行上的读取操作 (SELECT)。此过滤是静默完成的，没有任何事件日志。
• 块谓词 — 这限制了行上的写入操作 (INSERT)。 BLOCK 谓词有四种类型：AFTER INSERT、BEFORE UPDATE、AFTER UPDATE 和 BEFORE DELETE 操作。

逻辑很简单：谓词函数根据某些参数定义是允许还是阻止用户可以访问的行，然后将该函数应用于具有安全策略的表并变为活动状态。让我们仔细看看这个过程：

1. 首先，我们需要创建一个过滤谓词。对于此示例，如果用户与该行的 privilegeuser 列匹配或者用户名为 ADMIN，我们将允许该用户访问该行。

CREATE FUNCTION dbo.NewPredicateFunction

( @privilegeuser AS SYSNAME )

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN SELECT 1 AS AccessRight

 WHERE @privilegeuser = USER_NAME() OR USER_NAME() = 'ADMIN'

GO

1. 接下来，我们通过执行以下脚本来创建安全策略，该脚本将我们刚刚创建的谓词函数添加为测试表上的过滤谓词。

CREATE SECURITY POLICY PrivacyPolicy

ADD FILTER PREDICATE

dbo.NewPredicateFunction(privilegeuser) ON dbo.testtable

WITH (STATE = ON)

就这样。我们的安全策略在 SQL 数据库中处于活动状态，并且将允许用户仅访问其行，同时允许 ADMIN 帐户访问所有行。