MS SQL Server 数据加密与解密实例

在13年，有分享了MS SQL Server的加密与解密，
https://www.cnblogs.com/insus/p/3434735.html

看了，只知在数据库中创建了主密钥、证书、非对称密钥和多个对称密钥。但没有实例，还清楚怎样使用它们。

分几个示例来展示，

#1，使用对称密钥(PasswordSymmetric)加密和解密

1. -- 加密数据        
2. OPEN SYMMETRIC KEY PasswordSymmetric
3.     DECRYPTION BY PASSWORD = 'Pass#Tutorial#word';
5. DECLARE @PlainText NVARCHAR(100) = N'敏感数据'; --需要加密
6. DECLARE @CipherText VARBINARY(256);
8. -- 加密
9. SELECT @CipherText = ENCRYPTBYKEY(KEY_GUID('PasswordSymmetric'), @PlainText);
11. -- 查看加密结果
12. SELECT @PlainText AS 明文, @CipherText AS 密文;
14. -- 解密
15. SELECT @PlainText AS 原始明文,@CipherText AS 加密数据,CONVERT(NVARCHAR(100),DECRYPTBYKEY(@CipherText)) AS 解密结果;
17. CLOSE SYMMETRIC KEY PasswordSymmetric;

复制代码

View Code 
#2，使用由证书加密的对称密钥(SymmetricByCert)加密和解密

 

1. -- 使用证书加密的对称密钥
2. OPEN SYMMETRIC KEY SymmetricByCert
3.     DECRYPTION BY CERTIFICATE PasswordCert;
5. DECLARE @CreditCardNumber NVARCHAR(20) = '6222-5335-0012-3406';
6. DECLARE @EncryptedCard VARBINARY(256);
8. -- 加密信用卡号
9. SELECT @EncryptedCard = ENCRYPTBYKEY(KEY_GUID('SymmetricByCert'), @CreditCardNumber);
11. -- 解密信用卡号
12. SELECT
13.     @CreditCardNumber AS 原始卡号,
14.     @EncryptedCard AS 加密卡号,
15.     CONVERT(NVARCHAR(20), DECRYPTBYKEY(@EncryptedCard)) AS 解密卡号;
17. CLOSE SYMMETRIC KEY SymmetricByCert;

复制代码

View Code 
#3，使用由对称密钥加密的对称密钥(SymmetricBySy)加密和解密

1. -- 先打开主对称密钥
2. OPEN SYMMETRIC KEY PasswordSymmetric
3.     DECRYPTION BY PASSWORD = 'Pass#Tutorial#word';
5. -- 再打开被对称密钥加密的密钥
6. OPEN SYMMETRIC KEY SymmetricBySy
7.     DECRYPTION BY SYMMETRIC KEY PasswordSymmetric;
9. DECLARE @Phone NVARCHAR(20) = '18900000000'
10. DECLARE @Email NVARCHAR(100) = 'user@example.com';
11. DECLARE @EncryptedPhone VARBINARY(256);
12. DECLARE @EncryptedEmail VARBINARY(256);
14. -- 加密移号码
15. SELECT @EncryptedPhone = ENCRYPTBYKEY(KEY_GUID('SymmetricBySy'), @Phone);
16. -- 加密邮箱
17. SELECT @EncryptedEmail = ENCRYPTBYKEY(KEY_GUID('SymmetricBySy'), @Email);
19. -- 解密移动号码
20. SELECT
21.     @Phone AS 原始移动号码,
22.     @EncryptedPhone AS 加密移动号码,
23.     CONVERT(NVARCHAR(100), DECRYPTBYKEY(@EncryptedPhone)) AS 解密移动号码;
25. -- 解密邮箱
26. SELECT
27.     @Email AS 原始邮箱,
28.     @EncryptedEmail AS 加密邮箱,
29.     CONVERT(NVARCHAR(100), DECRYPTBYKEY(@EncryptedEmail)) AS 解密邮箱;
31. CLOSE SYMMETRIC KEY SymmetricBySy;
32. CLOSE SYMMETRIC KEY PasswordSymmetric;

复制代码

View Code 
#4，使用由非对称密钥加密的对称密钥(SymmetricByAsy)加密和解密

 

1. -- 打开被非对称密钥加密的对称密钥
2. OPEN SYMMETRIC KEY SymmetricByAsy
3.     DECRYPTION BY ASYMMETRIC KEY PasswordAsymmetric WITH PASSWORD = 'Pass#Tutorial#word';
5. DECLARE @MedicalInsuranceCardNumber NVARCHAR(15) = '888-545-6004';
6. DECLARE @EncryptedMedicalInsuranceCardNumber VARBINARY(256);
8. -- 加密医保卡号
9. SELECT @EncryptedMedicalInsuranceCardNumber = ENCRYPTBYKEY(KEY_GUID('SymmetricByAsy'), @MedicalInsuranceCardNumber);
11. -- 解密
12. SELECT
13.     @MedicalInsuranceCardNumber AS 原始医保卡号,
14.     @EncryptedMedicalInsuranceCardNumber AS 加密医保卡号,
15.     CONVERT(NVARCHAR(15), DECRYPTBYKEY(@EncryptedMedicalInsuranceCardNumber)) AS 解密医保卡号;
17. CLOSE SYMMETRIC KEY SymmetricByAsy;

复制代码

View Code 
更详细，可参考MSDN......
 

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！