如何在表的某些列中加密/解密数据以及插入新记录时也应该得到加密

Question

我知道这样插入新记录

INSERT INTO dbo.Customer_data (Customer_id, Customer_Name, Credit_card_number)
VALUES (25665, 'mssqltips4', EncryptByKey( Key_GUID('SymmetricKey1'), CONVERT(varchar,'4545-58478-1245') ) ); 

，但我想用普通的插入语句插入一个新记录，该记录应加密。 例如：

INSERT INTO dbo.Customer_data (Customer_id, Customer_Name, Credit_card_number)
VALUES (25665, 'mssqltips4','4545-58478-1245') ) );       

Answer 1

几个月前我也有类似的情况。包含个人数据的表需要将某些列加密，但是该表用于旧版应用程序，并具有许多参考。

所以，我可以创建一个单独的表来保存加密数据：

CREATE TABLE [dbo].[Customer_data_encrypted]
(
    [customer_id] PRIMARY KEY -- you can create foreign key to the original one, too
   ,[name] VARBANRY(..)
   ,[cretit_card_numbe] VARBINARY(..)
);

然后在原始表上创建INSTEAD OF INSERT UPDATE DELETE触发器。触发器中的逻辑很简单：

1. 在删除上，从两个表中删除
2. 在"更新/插入"上 - 加密数据并在新表中插入；在原始表中使用某种掩码（例如***或43-****-****-****）

然后，执行初始迁移以将数据从原始表移动到新表，然后掩盖它。

执行上述步骤很不错，因为：

1. 原始表的每个插入/更新继续工作
2. 您可以使用EXECUTE AS OWNER创建触发器，以便可以访问对称密钥并直接在T-SQL语句中执行更改，而无需打开证书或无法访问它们的用户
3. 在所有读取引用中，您都将获得蒙版数据，因此您不必担心申请
4. 拥有触发器使您能够轻松创建和更改信息

这取决于您的环境和业务需求，因为对于其中一个表，我将加密值存储为新列而不是单独的表。因此，选择更适合您的东西。