SQL2012数据库加密方法(三)
0BEFD01A8ABE6527EA
7 Roy_User7 GZ 0x008772F0C5181F428D82A52CE7ED05BB010000008301BD1387E1D6D5BC80F0EE8F9868EC7C01EF8A743BCE96D0A5E8741C91866D
*/
-- 查看的数据。请注意，您不必指定用来解密数据，即使多个对称密钥可以打开的键。
OPEN SYMMETRIC KEY User1SymmetricKeyCert
DECRYPTION BY CERTIFICATE User1Certificate;
SELECT CustID, Name, City,
CONVERT(VARCHAR, DecryptByKey(CreditCardType)) AS CreditCardType
FROM Customer2 WHERE CustID = 4;
CLOSE SYMMETRIC KEY User1SymmetricKeyCert;
/*
CustID Name City CreditCardType
4 Roy_User4 GZ Amex
*/
OPEN SYMMETRIC KEY User2SymmetricKeyPwd
DECRYPTION BY PASSWORD = 'User2SymmetricKeyPwd';
SELECT CustID, Name, City,
CONVERT(VARCHAR, DecryptByKey(CreditCardType)) AS CreditCardType
FROM Customer2 WHERE CustID = 5;
CLOSE SYMMETRIC KEY User2SymmetricKeyPwd;
/*
CustID Name City CreditCardType
5 Roy_User5 GZ Visa
*/
OPEN SYMMETRIC KEY GenericSymmetricKeyAsym
DECRYPTION BY ASYMMETRIC KEY User2AsymmetricKey;
SELECT CustID, Name, City,
CONVERT(VARCHAR, DecryptByKey(CreditCardType)) AS CreditCardType
FROM Customer2 WHERE CustID = 6;
CLOSE SYMMETRIC KEY GenericSymmetricKeyAsym;
/*
CustID Name City CreditCardType
6 Roy_User6 GZ Optima
*/
OPEN SYMMETRIC KEY User1SymmetricKeyCert
DECRYPTION BY CERTIFICATE User1Certificate;
OPEN SYMMETRIC KEY GenericSymmetricKeySym
DECRYPTION BY SYMMETRIC KEY User1SymmetricKeyCert;
SELECT CustID, Name, City,
CONVERT(VARCHAR, DecryptByKey(CreditCardType)) AS CreditCardType
FROM Customer2 WHERE CustID = 7;
CLOSE SYMMETRIC KEY GenericSymmetricKeySym;
CLOSE SYMMETRIC KEY User1SymmetricKeyCert
;
/*
CustID Name City CreditCardType
7 Roy_User7 GZ Wal-Mart
*/
-- 查看表数据
SELECT * FROM Customer2;
/*
1 Roy Wu GZ 0xB5B4C36DD479BE908577A93D82235EE2FC23F9A5B4AF1D8FF3E91BD848AC3DD04285CCC5E93DF6E2C49840FAB74DAD913685F4B54EA89CB888B314192C9EA2643A244BD6540C18F6103258CB3BC0014BAE121572248BE725B44FB5543CEF6D732DDE950DC9435D35C4D1AD9522F20EBFAA5070C51F7BC8521FB167F68FE8D451
2 Roy_User1 ZG 0x84A8C3CDE2C46E45D770CAD909A68E9B566B6D8176D4BE7C582DCD6AF9CB15C2EF0DB3F3B13323BF69B63AE8FC864FC9DA91138DDB12AEFD4680AA5791C6F745421A4011ECA5B34B4A1AA5EB61A0BBEAA918E5EAD76A2A13E63D121DCE230F9722A036503A2D781FA9DBEA6502524437E9A62DD94C1557A97704F629687B3B1B6C061A6883D6CC87DBC84C47ADCBBB4D7B3E2BA3A20C86BC28479B93981515FCBF602A360BFDF24227A1C99CC30F3D9D88C7D42E4565DF0C098307F82469E9F17942E4EE73753E132E26F64362D4228C77FF4B1F72FA522A2318AA02AB7E1E0432BFC84F01914E244664E8FDB837056ED261E74063D85F1E4AA44C65AED99214
3 Roy_User2 ZG 0xED893EBAD6790543E6196E248853EEA0C600E7C49625857F20862F44FDCB8460E13A3A7575173A93250E72006EA6551C551FB92D0014F8FCD845BBABCCAE55CB344415169B96C80BAB7B127FDCD8E056DED2B26366AD4E6DA27F993C84C90EE37AC16CB95636DA776818ECE01EE531CD2A927EA8816167F52A40C0D7396CE46C0BBBD28226D543F0B55D3120B4BA693220D08065C9BBF3E8918016E0774DF32FC3044697E111EB1B3FDAF11B03F8CFFF23C53339E8D920781361B679759CA5C168E8D5DCAB94025F25162268259C18FC2FA4DC541A25757708C61928E0E535BDDF80A3068A483EC1C5F4F772E2F442672A7979481F9A60ED5DAF849CC9A07D9A
4 Roy_User4 GZ 0x003FDE0600B08541922D500213AE5E4501000000DE3B49C60040D6634118C754D7AC7291FC3C40C081DF90B1
5 Roy_User5 GZ 0x00ECC30427