夜雨聆风
敏感数据加密和单独存储的补充说明
SELECT EncryptByPassPhrase('123456','110101199001011234') as 加密字段加密字段0x010000009687C00BBE0A97F3DA8DE68102FDDF588853FB3FC6812F6C9318EFB7EE83FD50B2CAEC985C8FBB69
对上述字段进行解密：
SELECT CONVERT(VARCHAR(50), DecryptByPassPhrase('123456',0x01000000BA39A9D74D86B306E8D42E204FF369C26EB89EC78A7F83F55B0792898BF2F94A52679E74223906B8)) as 身份证
结果是
身份证110101199001011234
我们再来看下具体的数据表，简单的数据表结构如下：
id int Uncheckedname nchar(10) Checkedtel varbinary(100) Checkedaddr varbinary(100) Checked
插入两条数据，保存时对数据进行加密：
insert intoa_test_no (name,tel,addr)values('张三',EncryptByPassPhrase('123456','138022'), EncryptByPassPhrase('123456','深圳福田区'))insert into a_test_no (name,tel,addr)values('李四',EncryptByPassPhrase('123456','138023'), EncryptByPassPhrase('123456', '深圳南山区'))
显示插入的数据：
select * from a_test_no结果如下，数据都是加密的：
id name tel addr1 张三 0x010000000F1FAC04E0FA9AE37DF8DF2D97C4AB260727E6006BADAAF7 0x010000000F369C5716FE4AFB5C3D6E072BFC648C439A326D6E4D95D5D6C50448942B2CFF2 李四 0x0100000023C75DC26E8F9B4E3D4E742F71F41A66540FAE0E98F6F6F8 0x01000000E4EA627A99FF8CF716DE5C89A5C3F5EFA145A44A112C08390AA59891B0E3D764
解密查询：
select id, name, convert(varchar(20), DecryptByPassPhrase('123456',tel)) as tel, convert(varchar(50), DecryptByPassPhrase('123456',addr)) as addr from a_test_no结果如下：
id name tel addr1 张三 138022 深圳福田区2 李四 138023 深圳南山区
这样就实现了字段的加密，在数据库的存储中是密文。
但是，这种加密方式存在以下问题:
1、查询和排序都需要通过解密的方式来处理，一旦数据表容量大，计算的消耗会非常大，可能会非常慢；批量查询、列表分页、查几十上百条每条都要逐行解密运算，CPU 直接损耗 20%~40% 很正常。全表扫描、导出报表、批量批量遍历性能掉得很明显，慢一倍都有可能。
2、数据字段的定义必须要定义为二进制方式，并且要进行一次转换，这增加了程序的复杂性，开发过程非常不直观；
3、密码在SQL中以“裸露”的方式显示；
4、加密后的数据会大2-3倍。
上面这种方式加密方式，看起来比上篇文章中那种加哈希的方式要简洁，但是排序和查询会存在很大的问题。所以，字段加密可以采用这种方式，但是加上哈希字段会大幅提升查询和排序的速度。
创建一个加密字段的数据表：
CREATE TABLE a_test1(ID INT PRIMARY KEY IDENTITY(1,1),UserName NVARCHAR(50),IDCard VARCHAR(20) COLLATE Chinese_PRC_BIN2ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_AlwaysEncrypted,ENCRYPTION_TYPE = Deterministic,ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),Phone VARCHAR(20)COLLATE Chinese_PRC_BIN2ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_AlwaysEncrypted,ENCRYPTION_TYPE = Randomized,ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),BankCard VARCHAR(30)COLLATE Chinese_PRC_BIN2ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_AlwaysEncrypted,ENCRYPTION_TYPE = Randomized,ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'));
表结构如下：
UserName nvarchar(50) CheckedIDCard varchar(20) CheckedPhone varchar(20) CheckedBankCard varchar(30) Checked
看起来跟正常表没有什么两样，其中的加密字段也不需要设置为二进制方式。
插入一条数据：
INSERT INTO a_test1 (UserName, IDCard, Phone, BankCard)VALUES ('张三', '110101199001011234', '13800138000', '6222021234567890123')
在没有加密登录的情形下的查询:
select * from a_test1结果如下:
ID UserName IDCard Phone BankCard1 张三 0x014F77A6849DFCFE0CDB95E94739735C602CBBA07140447FDD56C1EBDBC7D6C8C52BF69961C559C1AC1319E8DB9E6CEE30699F04E9A11886CB3C1EFFBB59B466D48D742B3882EF07847AAEDA31284F6598 0x01E0960A3D8121C7152F483576B9BEE5C8C99194C4A41C8ABC491CE3734916272EAEE8C0050A3C3C8FE6561CC200099647FEFCACB4581B267B35561D7B21E69D4B 0x01F069FE07F18513E49A73D5CEBDC6779F922F118A85A9BBFFF6AD5EAA302A2E1E9F4F0E151FC3CC7C92EF835F0C79CA92B4FDD52107D22DBF52DC97AB7DE40B15D74F56A194FE4B0F87EA8E1BA887E742
敏感字段的数据全部是密文。
但是如果以列加密的方式连接数据库,
再执行上面的查询语句，结果如下：
ID UserName IDCard Phone BankCard1 张三 110101199001011234138001380006222021234567890123
敏感字段就会以明文的方式显示出来。
上述加密方式意味着：
1、数据表结构不用去做改变，只需要设置加密字段就可以了；
2、即便是系统管理员，在没有密钥连接的情况下，看到的数据也是密文；
这个样的好处是，有权限的系统维护可以进行明文查看数据，而且在开发阶段完全可以不考虑字段的加密，在生产阶段一并处理就可以了。
这对数据库的开发无疑是非常友好的，为加密需要付出的工作量基本可以忽略不计了。
前提是你选择的数据库要支持该功能。
敏感字段的分开存储要求是：
敏感个人信息与一般业务数据逻辑隔离 / 存储隔离
并没有强制要求必须分库。
以下是两种存储方式的比较、差别以及适用情形：
卓建律师事务所数据合规团队是国内最早从事数据合规业务的律师事务所之一，卓建数据合规研究院是律所为数据合规成立的专门机构，数据合规团队有近20名DPO律师，在个人信息保护、数据出境和数据资产交易方面积累了大量的客户和案例。
十个GDPR处罚案例：访问权、处理目的、敏感数据、存储期限和系统安全