65.9K
CodeProject 正在变化。 阅读更多。
Home

使用 AES 对称密钥保护表内容

girlprogrammer
starIconstarIcon
emptyStarIcon
starIcon
emptyStarIconemptyStarIcon

2.73/5 (3投票s)

2017 年 5 月 12 日

CPOL

4分钟阅读

viewsIcon

9044

使用 AES-128 加密保护列级表数据

引言

有时，业务数据需要以人类无法读取的格式持久化到数据库中。虽然哈希也可以呈现一种场景，但简单的哈希技术可能并不总是能满足业务或域的需求。加密通过将数据转换为一串难以破译的字符来保护数据，除非知道加密算法和密码。

背景

所有创新都源于颠覆。我们遇到了一个挑战，一位客户已经拥有大量需要保护的数据，并且告知数据库将持续接收大量数据。一次的数据量可能在 100 万到 500 万条记录之间。

使用我们现有的 .NET 算法，如果我们尝试创建 .NET 库，获取、加密并将数据保存回数据库，这将是耗时耗力的，并且涉及不必要的步骤。因此，我们决定使用 SQL Server 中内置的 AES 加密。

Using the Code

当涉及到对称密钥加密时，AES 被认为是“黄金标准”。之所以称为对称，是因为加密和解密的密钥是相同的。

注意：- 我想强调的是，请不要直接在生产环境中使用此代码。本文档仅供参考。在实际操作中，您可能需要执行以下操作以满足合规性/数据安全标准： 

i) 不要将密码直接传递到存储过程中。

ii) 密码可以使用哈希算法进行哈希处理。

iii) 密钥名称和密码理想情况下不应保存在同一位置，例如在表或文件中。

在 SQL Server 中，要实现 AES 对称密钥加密，步骤如下：

  1. 使用客户端特定或根据您的选择硬编码的名称以及您选择的密码创建一个对称密钥。（@Msg 用于交叉检查状态，并非必需。存储过程可以根据您的需要进行调整）。
    CREATE PROCEDURE [dbo].[CreateNOpenSymmetricKey]
(
    @Key VARCHAR(MAX),
    @Pwd VARCHAR(MAX),
    @OpenConnection BIT = 1,
    @Msg VARCHAR(MAX) OUTPUT
)
AS
BEGIN
    DECLARE @KeyCreationSQL VARCHAR(MAX)
    DECLARE @OpenKeySQL VARCHAR(MAX)

    SET @KeyCreationSQL = 'IF EXISTS(SELECT 1 from sys.symmetric_keys _
    where name = ''' + @key + ''') 
                        BEGIN 
                            DROP SYMMETRIC KEY '+ @Key + ';
                        END
                        CREATE SYMMETRIC KEY ' + @Key + ' _
                        WITH ALGORITHM = AES_128 ENCRYPTION BY PASSWORD = ''' + @Pwd + ''';' 
                            

    PRINT @KeyCreationSQL
    BEGIN TRY
    
        EXEC(@KeyCreationSQL)
        SET @Msg = 'Key created'
        IF @OpenConnection = 1
        BEGIN
            SET @OpenKeySQL = 'OPEN SYMMETRIC KEY ' + @Key + _
            '  DECRYPTION BY PASSWORD = ''' + @Pwd + ''';'
            PRINT @OpenKeySQL
            EXEC(@OpenKeySQL)
            SET @Msg = 'Key created and opened'
        END
    END TRY
    BEGIN CATCH
        SELECT @Msg = ERROR_MESSAGE() 
    END CATCH
END
  2. 您还需要创建存储过程来打开和关闭密钥，以及一个专门关闭和删除密钥的存储过程。
    CREATE PROCEDURE [dbo].[OpenSymmetricKey]
(
    @Key VARCHAR(MAX),
    @Pwd VARCHAR(MAX),
    @OpenConnection BIT = 1,
    @Msg VARCHAR(MAX) OUTPUT
)
AS
BEGIN
    DECLARE @KeyCreationSQL VARCHAR(MAX)
    DECLARE @OpenKeySQL VARCHAR(MAX)

    SET @KeyCreationSQL = 'IF EXISTS(SELECT 1 from sys.symmetric_keys _
    where name = ''' + @key + ''') 
                        BEGIN 
                            OPEN SYMMETRIC KEY ' + @Key + '  _
                            DECRYPTION BY PASSWORD = ''' + @Pwd + ''';
                        END'                             

    PRINT @KeyCreationSQL
    EXEC(@KeyCreationSQL)
    SET @Msg = 'Key opened'
    
END

CREATE PROCEDURE [dbo].[CloseKey]
(
    @Key VARCHAR(MAX),
    @Msg VARCHAR(MAX) OUTPUT
)
AS
BEGIN
DECLARE @CloseKeySQL VARCHAR(MAX)
    SET @CloseKeySQL = 'CLOSE SYMMETRIC KEY ' + @Key + ';'
EXEC(@CloseKeySQL)
SET @Msg = 'Key closed'
END

CREATE PROCEDURE [dbo].[CloseAndDropKey]
(
    @Key VARCHAR(MAX),
    @Msg VARCHAR(MAX) OUTPUT
)
AS
BEGIN
DECLARE @CloseKeySQL VARCHAR(MAX)
    SET @CloseKeySQL = 'CLOSE SYMMETRIC KEY ' + @Key + _
    '; DROP SYMMETRIC KEY '+ @Key + ';'
EXEC(@CloseKeySQL)
SET @Msg = 'Key dropped'
END
  3. 一旦上述存储过程到位，就可以根据您的需求使用它们了。在这里，我们有一个名为 FileData 的表，其中有一个名为“Value”的列。此列的内容需要加密。实现此目的的步骤如下：
    1. 使用给定的名称和您选择的密码创建一个对称密钥。密码的长度和选择可以根据您的业务需求来决定。
    2. 打开密钥。
    3. 加密列值，并在需要时将其存储在同一表或其他表中。为了进行 POC（概念验证），我们将将其存储在另一个名为 CipherValueTable 的表中（CipherValue Varchar(MAX)）。
    4. 关闭密钥。
    CREATE PROCEDURE [dbo].[EncryptWithKey]
(
    @Key VARCHAR(MAX),
    @Pwd VARCHAR(MAX),
    @EncryptionStatusMsg VARCHAR(MAX) OUTPUT
)
AS
BEGIN
DECLARE @OutPut VARCHAR(MAX) 
 EXEC CreateNOpenSymmetricKey @Key, @Pwd, 1, @OutPut  OUTPUT

 IF @OutPut = 'Key created'
 BEGIN
    DECLARE @OpenKeySQL VARCHAR(MAX)
    SET @OpenKeySQL = 'OPEN SYMMETRIC KEY ' + @Key
    PRINT @OpenKeySQL
    EXEC(@OpenKeySQL)
    SET @EncryptionStatusMsg = 'Key opened'
 END
 ELSE IF @OutPut <> 'Key created and opened'
 BEGIN
    SET @EncryptionStatusMsg = 'Some issue has occurred try again later'
 END

 IF CHARINDEX(@OutPut,'Key',0)>-1
 BEGIN
    
            IF EXISTS (SELECT 1 FROM SYSOBJECTS _
            WHERE XTYPE='U' AND NAME ='CipherValueTable')
                BEGIN
                    DELETE FROM CipherValueTable
                END        

           DECLARE @startTime DATETIME , @endTime DATETIME
           SET @startTime = GETDATE()
           Print 'Encryption started here at--- ' + CAST(@startTime AS VARCHAR)

           INSERT INTO CipherValueTable (CipherValue)
           SELECT  EncryptByKey(Key_GUID(@Key), VALUE)  FROM FileData

            SET @endTime = GETDATE()

    Print 'Encryption end here at--- ' + CAST(@endTime AS VARCHAR)
    Print 'Time taken for encryption ' + _
    CAST(DATEDIFF(second,@startTime,@endTime) AS VARCHAR)

    SELECT * FROM CipherValueTable
    EXEC CloseKey @Key,@EncryptionStatusMsg
 END
END
  4. 为了解密 CipherValueTable 表中的值，需要使用相同的对称密钥和相同的密码进行解密。删除密钥并重新创建密钥和密码将无法满足需求，因为它们可能基于时间和空间约束维护唯一值（这是我的直觉，基于我收到的输出。我将对此进行更多探索）。列的内容需要解密。实现此目的的步骤如下：
    1. 使用用于加密的给定名称和密码打开对称密钥。
    2. 使用内置函数“DecryptByKey”解密值。为了进行 POC，我们将将其存储在另一个名为 ValueTable 的表中（Value Varchar(MAX)）。
    3. 关闭密钥。
    CREATE PROCEDURE [dbo].[DecryptWithKey]
(
    @Key VARCHAR(MAX),
    @Pwd VARCHAR(MAX),
    @DecryptionStatusMsg VARCHAR(MAX) OUTPUT
)
AS
BEGIN
DECLARE @OutPut VARCHAR(MAX) 
EXEC OpenSymmetricKey @Key, @Pwd, 1, @OutPut  OUTPUT
PRINT @OutPut
 IF CHARINDEX(@OutPut,'Key',0)>-1
 BEGIN
        IF EXISTS (SELECT 1 FROM SYSOBJECTS _
        WHERE XTYPE='U' AND NAME ='CipherValueTable')
        BEGIN
                IF EXISTS (SELECT 1 FROM SYSOBJECTS _
                WHERE XTYPE='U' AND NAME ='ValueTable')
                BEGIN
                    DELETE FROM ValueTable
                END

                INSERT INTO ValueTable
                SELECT CONVERT(NVARCHAR(MaX), DECRYPTBYKEY(CipherValue)) FROM CipherValueTable

                SELECT * FROM ValueTable
            EXEC CloseAndDropKey @Key,@DecryptionStatusMsg
        END
        ELSE
        BEGIN
        SET @DecryptionStatusMsg = 'Cipher table not found'
        END
 END
END
  5. 仅当您将来不再使用该密钥时，才删除它。
  6. 要调用存储过程，请使用给定的语法。（原始语句可能根据您的需求而有所不同）。
    DECLARE @EncryptionStatusMsg VARCHAR(MAX)
EXEC EncryptWithKey 'IAmSoThrilledThatICreatedMyFirstSymmetricKey' , _
'Abc@12345678', @EncryptionStatusMsg OUTPUT
PRINT @EncryptionStatusMsg

DECLARE @DecryptionStatusMsg VARCHAR(MAX)
EXEC DecryptWithKey 'IAmSoThrilledThatICreatedMyFirstSymmetricKey' , _
'Abc@12345678',  @DecryptionStatusMsg OUTPUT
PRINT @DecryptionStatusMsg

关注点

以下是我发现的一些有趣的事情：

  1. 重要：有效密码完全由 SQL Server 实例设置的密码规则定义。因此，虽然 SQL 实例可能认为 'abcdedf' 是完全有效的密码，但另一个实例可能不认为。然而，始终建议使用强密码。您也可以创建一个函数来计算给定密码的强度并进行验证。
  2. 重要：我使用了“CONVERT(NVARCHAR(MaX), DECRYPTBYKEY(CipherValue))”。这会将解密后的值转换为 NVarchar。现在，此转换或强制类型转换取决于源列。因此，如果您的源列是 NVarchar，并且您将解密后的值转换回 NVarchar，它将正常工作，但如果它是 varchar/datetime，而您将其转换为 nvarchar，则可能存在问题。因此，请根据数据类型进行转换。
  3. 上述算法速度很快。我能够在不到一分钟的时间内加密超过 50 万条记录。
  4. 解密花费的时间稍长一些，因为涉及 drop 语句、insert 语句以及随后的 select，对于相同的超过 50 万条记录，大约需要 1 分 8 秒。
  5. 与 .NET 库不同，密码长度没有前缀。
  6. SQL Server 中有许多数据安全选择，这只是冰山一角。

历史

我将根据我的改进情况持续更新。

© . All rights reserved.