SQL2012数据库加密方法(四)
285CCC5E93DF6E2C49840FAB74DAD913685F4B54EA89CB888B314192C9EA2643A244BD6540C18F6103258CB3BC0014BAE121572248BE725B44FB5543CEF6D732DDE950DC9435D35C4D1AD9522F20EBFAA5070C51F7BC8521FB167F68FE8D451
*/
-- 解密数据
SELECT CustID, Name, City,
CONVERT(VARCHAR,
DecryptByCert(Cert_ID('User1Certificate'),CreditCardType)) AS CreditCardType
FROM Customer2;
/*
CustID Name City CreditCardType
1 Roy Wu GZ Visa
*/
-- 不需要使用一个表来对数据进行加密时中，用法。
DECLARE @CipherText VARBINARY(500);
SET @CipherText = EncryptByCert(Cert_ID('User1Certificate'), 'Secret text');
PRINT CONVERT(VARCHAR, DecryptByCert(Cert_ID('User1Certificate'),@CipherText))
;
-- 2、非对称密钥
-- 用私钥受用户-创建非对称密钥对提供的密码。
CREATE ASYMMETRIC KEY User1AsymmetricKey
AUTHORIZATION User1
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'User1Password1';
-- 创建另一个数据库主密钥的保护
CREATE ASYMMETRIC KEY User2AsymmetricKey
AUTHORIZATION User2
WITH ALGORITHM = RSA_2048;
-- 创建由 dbo 拥有的另一种。
CREATE ASYMMETRIC KEY DBOAsymmetricKey
WITH ALGORITHM = RSA_2048;
INSERT INTO Customer2 VALUES (2, 'Roy_User1', 'ZG',
EncryptByAsymKey(AsymKey_ID('User1AsymmetricKey'), 'MasterCard'));
GO
INSERT INTO Customer2 VALUES (3, 'Roy_User2', 'ZG',
EncryptByAsymKey(AsymKey_ID('User2AsymmetricKey'), 'Discover'));
GO
-- 查看数据
SELECT * FROM Customer2 WHERE CustID > 1;
/*
CustId Name City CreditCardType
2 Roy_User1 ZG 0x84A8C3CDE2C46E45D770CAD909A68E9B566B6D8176D4BE7C582DCD6AF9CB15C2EF0DB3F3B13323BF69B63AE8FC864FC9DA91138DDB12AEFD4680AA5791C6F745421A4011ECA5B34B4A1AA5EB61A0BBEAA918E5EAD76A2A13E63D121DCE230F9722A036503A2D781FA9
DBEA6502524437E9A62DD94C1557A97704F629687B3B1B6C061A6883D6CC87DBC84C47ADCBBB4D7B3E2BA3A20C86BC28479B93981515FCBF602A360BFDF24227A1C99CC30F3D9D88C7D42E4565DF0C098307F82469E9F17942E4EE73753E132E26F64362D4228C77FF4B1F72FA522A2318AA02AB7E1E0432BFC84F01914E244664E8FDB837056ED261E74063D85F1E4AA44C65AED99214
3 Roy_User2 ZG 0xED893EBAD6790543E6196E248853EEA0C600E7C49625857F20862F44FDCB8460E13A3A7575173A93250E72006EA6551C551FB92D0014F8FCD845BBABCCAE55CB344415169B96C80BAB7B127FDCD8E056DED2B26366AD4E6DA27F993C84C90EE37AC16CB95636DA776818ECE01EE531CD2A927EA8816167F52A40C0D7396CE46C0BBBD28226D543F0B55D3120B4BA693220D08065C9BBF3E8918016E0774DF32FC3044697E111EB1B3FDAF11B03F8CFFF23C53339E8D920781361B679759CA5C168E8D5DCAB94025F25162268259C18FC2FA4DC541A25757708C61928E0E535BDDF80A3068A483EC1C5F4F772E2F442672A7979481F9A60ED5DAF849CC9A07D9A
*/
-- CustID=2查看已解密的数据。必须通过在用于保护私钥的密码。
SELECT CustID, Name, City,
CONVERT(VARCHAR,
DecryptByAsymKey(AsymKey_ID('User1AsymmetricKey'),
CreditCardType, N'User1Password1')) AS CreditCardType
FROM Customer2 WHERE CustID = 2;
/*
CustID Name City CreditCardType
2 Roy_User1 ZG MasterCard
*/
-- CustID=3查看已解密的数据
SELECT CustID, Name, City,
CONVERT(VARCHAR,
DecryptByAsymKey(AsymKey_ID('User2AsymmetricKey'),
CreditCardType)) AS CreditCardType
FROM Customer2 WHERE CustID = 3;
-- 试图查看解密后的数据，但 custid=3 错了钥匙。返回用于加密数据的空值。
SELECT CustID, Name, City,
CONVERT(VARCHAR,
DecryptByAsymKey(AsymKey_ID('User1AsymmetricKey'),
CreditCardType)) AS CreditCardType
FROM Customer2 WHERE CustID = 3;
/*
CustID Name City CreditCardType