SQL Server 非对称秘钥在数据加密中的应用

最新推荐文章于 2023-06-14 21:42:59 发布

三空道人

最新推荐文章于 2023-06-14 21:42:59 发布

阅读量1.2k

点赞数

分类专栏： SQL Server SQL Server 安全管理文章标签：非对称秘钥解密加密对称秘钥

本文链接：https://blog.csdn.net/zhoujunah/article/details/106613511

版权

SQL Server 同时被 2 个专栏收录

63 篇文章 12 订阅

订阅专栏

SQL Server 安全管理

41 篇文章 3 订阅

订阅专栏

对称秘钥对数据加密解密使用了ENCRYPTBYMKEY、DECRYPTBYKEY两个函数，而非对称秘钥使用的加密解密函数分别是ENCRYPTBYASYMKEY、DECRYPTBYASYMKEY。本文将说明这两个函数如何对数据加密和解密，以及如何应用于生产实践中。

我们仍然使用《SQL Server 对称密钥在数据加密中的应用》中创建的数据库，然后创建一个新表，存储非对称密钥加密的数据。接着从文件中创建一个非对称密钥，这里因为前文已经在EncryptionTest 数据库中创建了数据库主密钥，所以不需要再创建。关于创建非对称密钥的方式会在后面的文章中给大家介绍。

USE EncryptionTest
GO
CREATE TABLE [dbo].[EncryptionDataByAsy](
      [id] [int] NULL,
      [EncryptionCol] [varbinary](128) NULL
)
CREATE ASYMMETRIC KEY AsyKeyFromFile
FROM FILE='E:\AsmDll\pri.snk'

和对称密钥的加密函数ENCRYPTBYMKEY一样，非对称密钥的加密函数ENCRYPTBYASYMKEY 也需要两个参数，第一个参数为加密数据的非对称密钥的编号，有两种方式可以获取非对称密钥的编号，一是查询动态视图 sys.asymmetric_keys ，另外一种是使用函数 ASYMKEY_ID ：

SELECT asymmetric_key_id FROM sys.asymmetric_keys
WHERE name='AsyKeyFromFile'
SELECT ASYMKEY_ID('AsyKeyFromFile')

两种方式获得的结果相同，现在来实践非对称密钥加密：

INSERT INTO EncryptionDataByAsy
VALUES(1,ENCRYPTBYASYMKEY(ASYMKEY_ID('AsyKeyFromFile'),'Jack'))

这样就简单的实现了数据的加密。

接下来，我们来解密数据，我们使用非对称密钥的解密函数DECRYPTBYASYMKEY ，此函数有三个参数，分别为非对称密钥的编号，解密字符串及非对称密钥私钥加密密码（最后一个参数是可选参数，如果私钥使用密码加密的话，则需要加密私钥的密码；如果是数据库主秘钥加密私钥的话，则不需此参数）：

SELECT CONVERT(VARCHAR(128),DECRYPTBYASYMKEY(ASYMKEY_ID('AsyKeyFromFile'),EncryptionCol))
FROM EncryptionDataByAsy

非对称密钥使用数据库主密钥加密私钥，解密起来太方便了，因为只要能查看数据库定义的用户，都能查看到非对称密钥的名称。接下来我们先改变非对称密钥的加密方式，使用密码对非对称密钥的私钥进行加密：

ALTER ASYMMETRIC KEY AsyKeyFromFile
WITH PRIVATE KEY(ENCRYPTION BY PASSWORD='!<Ilc/cby=`Qpae')

此时，我们再插入一行数据：

INSERT INTO EncryptionDataByAsy
VALUES(2,ENCRYPTBYASYMKEY(ASYMKEY_ID('AsyKeyFromFile'),'Jack'))

此时我们再使用上面的解密方式，看到结果均为NULL了：

这时，我们就需要使用解密函数的第三个参数，加密非对称密钥私钥的密钥，第三个参数的数据类型是NVARCHAR：

SELECT CONVERT(VARCHAR(128),DECRYPTBYASYMKEY(ASYMKEY_ID('AsyKeyFromFile'),EncryptionCol,N'!<Ilc/cby=`Qpae'))
FROM EncryptionDataByAsy

这样，我们要解密数据，除了需要知道是使用哪个非对称密钥加密的，还需要知道非对称密钥私钥的加密密码，相对于前一种方案，安全有了更大的保障（只要私钥加密密码不泄露）。

为应用于实践，我们同样将加密、解密过程封装为加密的存储过程：

USE EncryptionTest
GO
--加密数据的封装过程
CREATE PROC InsertEncryptDataByAsy
      @id INT,
      @EncryptionCol VARCHAR(20)
WITH ENCRYPTION AS
BEGIN
      SET NOCOUNT ON
      INSERT INTO EncryptionDataByAsy VALUES
      (@id,ENCRYPTBYASYMKEY(ASYMKEY_ID('AsyKeyFromFile'),@EncryptionCol))
END
GO
USE EncryptionTest
GO
--解密数据的封装过程
CREATE PROC SelectDecryptionDataWithAsyKey
WITH ENCRYPTION AS
BEGIN
    SET NOCOUNT ON
    SELECT
              id,
              EncryptionCol,
              CONVERT(VARCHAR(128),DECRYPTBYASYMKEY(ASYMKEY_ID('AsyKeyFromFile'),EncryptionCol,N'!<Ilc/cby=`Qpae'))
      FROM EncryptionDataByAsy
  
END

创建完成后，我们可以在对象资源管理器，EncryptionTest数据库下的可编程性，存储过程中看到这两个加密的存储过程：

这里仅仅为了说明区分加密、解密的过程，实践中不要使用这样显眼的描述，以对过程进行掩饰，提高安全性。

现在我们来测试用户使用这两个过程，首先为Jack用户赋予这两个过程的执行权限：

GRANT EXEC ON OBJECT::InsertEncryptDataByAsy TO Jack
GRANT EXEC ON OBJECT::SelectDecryptionDataWithAsyKey TO Jack

然后在Jack上下文中执行两个存储过程：

EXEC AS USER='Jack'
EXEC InsertEncryptDataByAsy 3,'Jack3'
EXEC SelectDecryptionDataWithAsyKey
REVERT

从结果来看，虽然过程成功执行，没有报错，但既没有实现对新插入的数据进行加密，也没有解密已经加密的数据，看来仅仅执行权限是不够的，下面我们给用户增加查看非对称秘钥定义的权限：

GRANT VIEW DEFINITION ON ASYMMETRIC KEY::AsyKeyFromFile TO Jack

加密已经成功了，所以执行加密过程的用户只需要执行权限加查看非对称秘钥定义的权限即可，但解密还是不行，解密需要对非对称秘钥的控制权限：

GRANT CONTROL ON ASYMMETRIC KEY::AsyKeyFromFile TO Jack

这样就可以完美的实现加密和解密了：

现在我们更换非对称秘钥，使用强名称加密的dll创建：

DROP ASYMMETRIC KEY SQLCLRTestKey
CREATE ASYMMETRIC KEY AsyKeyFromDll
FROM EXECUTABLE FILE = 'E:\AsmDll\UDF_CLR.dll'

使用新的非对称秘钥对数据进行加密，并查看结果：

INSERT INTO EncryptionDataByAsy
VALUES(6,ENCRYPTBYASYMKEY(ASYMKEY_ID('AsyKeyFromDll'),'Jack6'))
SELECT
      id,
      EncryptionCol,
      CONVERT(VARCHAR(128),DECRYPTBYASYMKEY(ASYMKEY_ID('AsyKeyFromDll'),EncryptionCol))
FROM EncryptionDataByAsy