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

在13年，有分享了MS SQL Server的加密与解密，

https://www.cnblogs.com/insus/p/3434735.html

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

分几个示例来展示，

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

-- 加密数据        
OPEN SYMMETRIC KEY PasswordSymmetric
    DECRYPTION BY PASSWORD = 'Pass#Tutorial#word';

DECLARE @PlainText NVARCHAR(100) = N'敏感数据'; --需要加密
DECLARE @CipherText VARBINARY(256);

-- 加密
SELECT @CipherText = ENCRYPTBYKEY(KEY_GUID('PasswordSymmetric'), @PlainText);

-- 查看加密结果
SELECT @PlainText AS 明文, @CipherText AS 密文;

-- 解密
SELECT @PlainText AS 原始明文,@CipherText AS 加密数据,CONVERT(NVARCHAR(100),DECRYPTBYKEY(@CipherText)) AS 解密结果;

CLOSE SYMMETRIC KEY PasswordSymmetric;

 

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

 

-- 使用证书加密的对称密钥
OPEN SYMMETRIC KEY SymmetricByCert
    DECRYPTION BY CERTIFICATE PasswordCert;

DECLARE @CreditCardNumber NVARCHAR(20) = '6222-5335-0012-3406';
DECLARE @EncryptedCard VARBINARY(256);

-- 加密信用卡号
SELECT @EncryptedCard = ENCRYPTBYKEY(KEY_GUID('SymmetricByCert'), @CreditCardNumber);

-- 解密信用卡号
SELECT 
    @CreditCardNumber AS 原始卡号,
    @EncryptedCard AS 加密卡号,
    CONVERT(NVARCHAR(20), DECRYPTBYKEY(@EncryptedCard)) AS 解密卡号;

CLOSE SYMMETRIC KEY SymmetricByCert;

 

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

-- 先打开主对称密钥
OPEN SYMMETRIC KEY PasswordSymmetric
    DECRYPTION BY PASSWORD = 'Pass#Tutorial#word';

-- 再打开被对称密钥加密的密钥
OPEN SYMMETRIC KEY SymmetricBySy
    DECRYPTION BY SYMMETRIC KEY PasswordSymmetric;

DECLARE @Phone NVARCHAR(20) = '18900000000'
DECLARE @Email NVARCHAR(100) = 'user@example.com';
DECLARE @EncryptedPhone VARBINARY(256);
DECLARE @EncryptedEmail VARBINARY(256);

-- 加密移号码
SELECT @EncryptedPhone = ENCRYPTBYKEY(KEY_GUID('SymmetricBySy'), @Phone);
-- 加密邮箱
SELECT @EncryptedEmail = ENCRYPTBYKEY(KEY_GUID('SymmetricBySy'), @Email);

-- 解密移动号码
SELECT 
    @Phone AS 原始移动号码,
    @EncryptedPhone AS 加密移动号码,
    CONVERT(NVARCHAR(100), DECRYPTBYKEY(@EncryptedPhone)) AS 解密移动号码;

-- 解密邮箱
SELECT 
    @Email AS 原始邮箱,
    @EncryptedEmail AS 加密邮箱,
    CONVERT(NVARCHAR(100), DECRYPTBYKEY(@EncryptedEmail)) AS 解密邮箱;

CLOSE SYMMETRIC KEY SymmetricBySy;
CLOSE SYMMETRIC KEY PasswordSymmetric;

 

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

 

-- 打开被非对称密钥加密的对称密钥
OPEN SYMMETRIC KEY SymmetricByAsy
    DECRYPTION BY ASYMMETRIC KEY PasswordAsymmetric WITH PASSWORD = 'Pass#Tutorial#word';

DECLARE @MedicalInsuranceCardNumber NVARCHAR(15) = '888-545-6004';
DECLARE @EncryptedMedicalInsuranceCardNumber VARBINARY(256);

-- 加密医保卡号
SELECT @EncryptedMedicalInsuranceCardNumber = ENCRYPTBYKEY(KEY_GUID('SymmetricByAsy'), @MedicalInsuranceCardNumber);

-- 解密
SELECT 
    @MedicalInsuranceCardNumber AS 原始医保卡号,
    @EncryptedMedicalInsuranceCardNumber AS 加密医保卡号,
    CONVERT(NVARCHAR(15), DECRYPTBYKEY(@EncryptedMedicalInsuranceCardNumber)) AS 解密医保卡号;

CLOSE SYMMETRIC KEY SymmetricByAsy;

 

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