SQL2012数据库加密方法

1、非对称密钥来保护新的对称密钥

/*--测试环境Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 13:38:57 Copyright (c) Microsoft CorporationDeveloper Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)*/--新建测试登陆账号User1USE [master]GOCREATE LOGIN [User1] WITH PASSWORD=N'1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGOGO--设置示例加密数据库CREATE DATABASE EncryptionDB;GOUSE EncryptionDB;GO--向当前数据库添加用户User1CREATE USER User1 FOR LOGIN User1;go--新增测试表Customer加密信用卡类型CREATE TABLE Customer (CustId int, Name nvarchar(30), City varchar(20), CreditCardType varbinary(1000));GO-- 授予数据库用户User1查询\新增）权限GRANT SELECT,INSERT on Customer to User1;goUSE EncryptionDB;GO-- 数据库没有万能钥匙，默认情况下，所以您必须创建它,然后才可以使用它.-- 1、创建数据库主密钥CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'gz@SQL2012';-- 创建非对称密钥来保护新的对称密钥CREATE ASYMMETRIC KEY User1AsymmetricKeyAUTHORIZATION User1WITH ALGORITHM = RSA_2048-- 创建对称密钥CREATE SYMMETRIC KEY User1SymmetricKey    AUTHORIZATION User1WITH ALGORITHM = TRIPLE_DESENCRYPTION BY ASYMMETRIC KEY User1AsymmetricKey;-- 查看数据库中的对称密钥SELECT * FROM sys.symmetric_keys;--切换用户EXECUTE AS USER = 'User1';select USER_NAME()-- 插入数据，使用各种非对称密钥。-- 如果对称密钥不对时，打开没有错误，没有插入的数据。OPEN SYMMETRIC KEY User1SymmetricKeyDECRYPTION BY ASYMMETRIC KEY User1AsymmetricKey;INSERT INTO Customer VALUES (1, 'Roy Wu', 'GZ',EncryptByKey(Key_GUID('User1SymmetricKey'), 'UnionPay'));CLOSE SYMMETRIC KEY User1SymmetricKey;-- 查看使用对称密钥输入的新数据。SELECT * FROM Customer;/*CustIdNameCityCreditCardType1Roy WuGZ0x001F1EE79BE1EC4CB9F9D2118762E87F010000008266403C365BDE7D45F5821A8B10823BBCB0BD78F8EC9142D58461B936ADE8E7*/--  查看的数据。请注意您不必指定哪个对称密钥-- 要用于解密的数据，即使多个对称密钥可以打开。OPEN SYMMETRIC KEY User1SymmetricKeyDECRYPTION BY ASYMMETRIC KEY User1AsymmetricKey;SELECT CustID, Name, City,CONVERT(VARCHAR, DecryptByKey(CreditCardType)) AS CreditCardTypeFROM Customer;/*CustIDNameCityCreditCardType1Roy WuGZUnionPay*/CLOSE SYMMETRIC KEY User1SymmetricKey;--由于权限问题，需要新打开一个查询窗口-- 加密视图查看(现有密钥)--为数据库中的每个证书返回一行。SELECT * FROM sys.certificates;--为每个非对称密钥返回一行。SELECT * FROM sys.asymmetric_keys;--对于使用 CREATE SYMMETRIC KEY 语句创建的每个对称密钥，返回与其对应的一行。SELECT * FROM sys.symmetric_keys;--为数据库中的每个主体返回一行。SELECT * FROM sys.database_principals;-- 有关密钥的信息-- 为使用 CREATE SYMMETRIC KEY 语句的 ENCRYPTION BY 子句指定的每个对称密钥加密返回一行。SELECT * FROM sys.key_encryptions;-- 对于与安全对象关联的每个加密属性，返回与其对应的一行。SELECT * FROM sys.crypt_properties;-- 删除测试USE master;GODROP DATABASE EncryptionDB;DROP LOGIN User1;

2、加密详细讲解用法

USE master;GO-- 创建3个登陆账号CREATE LOGIN User1 WITH password = '1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;CREATE LOGIN User2 WITH password = '2', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;CREATE LOGIN User3 WITH password = '3', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;GOCREATE DATABASE EncryptionDB;GOUSE EncryptionDB;GO--创建登陆账号CREATE USER User1 FOR LOGIN User1;CREATE USER User2 FOR LOGIN User2;CREATE USER User3 FOR LOGIN User3;GO-- 服务主密钥USE master;GO-- 备份服务主密钥，将存储在安全的位置,该文件被加密的使用提供的密码作为密钥BACKUP SERVICE MASTER KEY TO FILE = 'D:\Data\servicemasterkey.dat' ENCRYPTION BY PASSWORD = 'SQL@2014';--还原服务主密钥。（这是可以立即执行，因为SQL Server 会看到钥匙是相同的并不会重新对数据进行加密。）RESTORE SERVICE MASTER KEY FROM FILE = 'd:\Data\servicemasterkey.dat' DECRYPTION BY PASSWORD = 'SQL@2014';-- 此外可以使用 FORCE 选项,重新生成服务主密钥。要小心 !对用旧 SMK 加密的所有密钥进行都解密，然后对它们进行加密用新的密钥，这可需要大量的处理时间。不执行，除非你确定你想要重新生成密钥.ALTER SERVICE MASTER KEY REGENERATE;-- 如果主密钥无法再生过程将检索失败，或者是无法解密它加密的所有密钥。FORCE 选项强制再生，即使数据将会丢失。ALTER SERVICE MASTER KEY FORCE REGENERATE;-- 创建数据库主密钥USE EncryptionDB;GO-- 数据库没有一把万能钥匙，默认情况下，所以您必须创建它,你可以使用它之前：CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DB@SQL2012';-- 如果数据库掌握关键未使用服务主密钥，加密你必须在使用它之前将其打开：OPEN MASTER KEY DECRYPTION BY PASSWORD = 'DB@SQL2012';--但通常这不是必要的除非您删除的加密服务主密钥 （如果它是打开，这关闭键）：ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;-- 可以添加服务主密钥加密：ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;/*-- 这很有用，当将数据库移动到另一个服务器。删除加密,以前分离，并将其添加回新的服务器上。-- 你也可以顺便您用来创建它的密码的加密：ALTER MASTER KEY DROP ENCRYPTION BY PASSWORD = 'DB@SQL2012';-- (如果将失败的关键是开放的因为它是如果您执行了前面的代码作为它会出现在此文件中的脚本。），然后你可以将其添加回与新的或旧的密码ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'DB@SQL2012';-- 如果已经存储了密钥，要么 drop 语句将会失败。*/-- 如果您显式打开数据库主密钥，您必须先关闭它当你完成它：CLOSE MASTER KEY;-- 数据库主密钥像服务主密钥，可以转储，并从文件中加载密钥。执行此操作之前，必须打开密钥。BACKUP MASTER KEY TO FILE = 'd:\Data\EncryptionDBMasterKey.dat'ENCRYPTION BY PASSWORD = 'DB@SQL2012bak';GO--还原数据库主密钥RESTORE MASTER KEY FROM FILE = 'd:\Data\EncryptionDBMasterKey.dat'DECRYPTION BY PASSWORD = 'DB@SQL2012bak'ENCRYPTION BY PASSWORD = 'DB@SQL2012';-- 如果您正在加载的关键是在先声明不会做任何事与现有的主机密钥相同。可以使用 FORCE 选项，以及如果不需要密钥, 删除它时.--DROP MASTER KEY;-- 如果主密钥保护数据库中的任何私有密钥将失败CREATE TABLE Customer2 (CustId int, Name nvarchar(30), City varchar(20), CreditCardType varbinary(1000))GO-- 授权GRANT SELECT,INSERT on Customer2 to User1;GRANT SELECT,INSERT on Customer2 to User2;GRANT SELECT,INSERT on Customer2 to User3;GO-- 1、证书加密-- 创建与 User1，加密与数据库相关联的证书主密钥。数据库主密钥必须已经存在。请注意，你不必显式打开主密钥，因为它加密的服务主密钥。CREATE CERTIFICATE User1Certificate AUTHORIZATION User1 WITH subject = 'Certificate For User1';GO-- 授权条款是可选的。给 User1 的所有权。此外可以使用给予控制可以使用它。-- 为User2创建证书CREATE CERTIFICATE User2Certificate AUTHORIZATION User2 ENCRYPTION BY PASSWORD = 'User2Password'WITH subject = 'Certificate For User2',EXPIRY_DATE = '12/31/2020';GO--删除User2CertificateDROP CERTIFICATE User2Certificate-- 备份证书User1CertificateBACKUP CERTIFICATE User1Certificate TO FILE = 'd:\Data\myCert.dat'WITH PRIVATE KEY (FILE = 'd:\Data\myCertPrivateKey', ENCRYPTION BY PASSWORD = 'User1CertificatePassword');/*-- 您也可以从文件加载证书。通常，这将是一个证书,来自受信任的证书颁发机构。CREATE CERTIFICATE MyCertificate FROM FILE = 'd:\Data\myCert.dat'WITH PRIVATE KEY (FILE = 'd:\Data\myCertPrivateKey', DECRYPTION BY PASSWORD = 'User1CertificatePassword');-- 创建证书的声明，一次只能存在于数据库。--CREATE CERTIFICATE MyEXEcertificate FROM EXECUTABLE FILE = 'Z:\SomeFile.exe'--CREATE CERTIFICATE MyNETcertificate FROM ASSEMBLY myLoadedAssembly-- 删除证书DROP CERTIFICATE MyCertificate;*/-- 使用证书来加密数据INSERT INTO Customer2 VALUES (1, 'Roy Wu', 'GZ',EncryptByCert(Cert_ID('User1Certificate'), 'Visa'));GO-- 查看数据。请注意 enctypted 数据的长度,其中包括签名SELECT * FROM Customer2;/*CustIdNameCityCreditCardType1Roy WuGZ0xB5B4C36DD479BE908577A93D82235EE2FC23F9A5B4AF1D8FF3E91BD848AC3DD04285CCC5E93DF6E2C49840FAB74DAD913685F4B54EA89CB888B314192C9EA2643A244BD6540C18F6103258CB3BC0014BAE121572248BE725B44FB5543CEF6D732DDE950DC9435D35C4D1AD9522F20EBFAA5070C51F7BC8521FB167F68FE8D451*/-- 解密数据SELECT CustID, Name, City,CONVERT(VARCHAR, DecryptByCert(Cert_ID('User1Certificate'),CreditCardType)) AS CreditCardTypeFROM Customer2;/*CustIDNameCityCreditCardType1Roy WuGZVisa*/-- 不需要使用一个表来对数据进行加密时中，用法。DECLARE @CipherText VARBINARY(500);SET @CipherText = EncryptByCert(Cert_ID('User1Certificate'), 'Secret text');PRINT CONVERT(VARCHAR, DecryptByCert(Cert_ID('User1Certificate'),@CipherText)) ;-- 2、非对称密钥-- 用私钥受用户-创建非对称密钥对提供的密码。CREATE ASYMMETRIC KEY User1AsymmetricKeyAUTHORIZATION User1WITH ALGORITHM = RSA_2048ENCRYPTION BY PASSWORD = 'User1Password1';-- 创建另一个数据库主密钥的保护CREATE ASYMMETRIC KEY User2AsymmetricKeyAUTHORIZATION User2WITH ALGORITHM = RSA_2048;-- 创建由 dbo 拥有的另一种。CREATE ASYMMETRIC KEY DBOAsymmetricKeyWITH ALGORITHM = RSA_2048;INSERT INTO Customer2 VALUES (2, 'Roy_User1', 'ZG',EncryptByAsymKey(AsymKey_ID('User1AsymmetricKey'), 'MasterCard'));GOINSERT INTO Customer2 VALUES (3, 'Roy_User2', 'ZG',EncryptByAsymKey(AsymKey_ID('User2AsymmetricKey'), 'Discover'));GO-- 查看数据SELECT * FROM Customer2 WHERE CustID > 1;/*CustIdNameCityCreditCardType2Roy_User1ZG0x84A8C3CDE2C46E45D770CAD909A68E9B566B6D8176D4BE7C582DCD6AF9CB15C2EF0DB3F3B13323BF69B63AE8FC864FC9DA91138DDB12AEFD4680AA5791C6F745421A4011ECA5B34B4A1AA5EB61A0BBEAA918E5EAD76A2A13E63D121DCE230F9722A036503A2D781FA9DBEA6502524437E9A62DD94C1557A97704F629687B3B1B6C061A6883D6CC87DBC84C47ADCBBB4D7B3E2BA3A20C86BC28479B93981515FCBF602A360BFDF24227A1C99CC30F3D9D88C7D42E4565DF0C098307F82469E9F17942E4EE73753E132E26F64362D4228C77FF4B1F72FA522A2318AA02AB7E1E0432BFC84F01914E244664E8FDB837056ED261E74063D85F1E4AA44C65AED992143Roy_User2ZG0xED893EBAD6790543E6196E248853EEA0C600E7C49625857F20862F44FDCB8460E13A3A7575173A93250E72006EA6551C551FB92D0014F8FCD845BBABCCAE55CB344415169B96C80BAB7B127FDCD8E056DED2B26366AD4E6DA27F993C84C90EE37AC16CB95636DA776818ECE01EE531CD2A927EA8816167F52A40C0D7396CE46C0BBBD28226D543F0B55D3120B4BA693220D08065C9BBF3E8918016E0774DF32FC3044697E111EB1B3FDAF11B03F8CFFF23C53339E8D920781361B679759CA5C168E8D5DCAB94025F25162268259C18FC2FA4DC541A25757708C61928E0E535BDDF80A3068A483EC1C5F4F772E2F442672A7979481F9A60ED5DAF849CC9A07D9A*/-- CustID=2查看已解密的数据。必须通过在用于保护私钥的密码。SELECT CustID, Name, City,CONVERT(VARCHAR, DecryptByAsymKey(AsymKey_ID('User1AsymmetricKey'),CreditCardType, N'User1Password1')) AS CreditCardTypeFROM Customer2 WHERE CustID = 2;/*CustIDNameCityCreditCardType2Roy_User1ZGMasterCard*/-- CustID=3查看已解密的数据SELECT CustID, Name, City,CONVERT(VARCHAR, DecryptByAsymKey(AsymKey_ID('User2AsymmetricKey'),CreditCardType)) AS CreditCardTypeFROM Customer2 WHERE CustID = 3;-- 试图查看解密后的数据，但 custid=3 错了钥匙。返回用于加密数据的空值。SELECT CustID, Name, City,CONVERT(VARCHAR, DecryptByAsymKey(AsymKey_ID('User1AsymmetricKey'), CreditCardType)) AS CreditCardTypeFROM Customer2 WHERE CustID = 3;/*CustIDNameCityCreditCardType3Roy_User2ZGNULL*/-- 切换登陆用户User3EXECUTE AS LOGIN = 'User3';GOSELECT CustID, Name, City,CONVERT(VARCHAR, DecryptByAsymKey(AsymKey_ID('User2AsymmetricKey'),CreditCardType)) AS CreditCardTypeFROM Customer2 WHERE CustID = 3;/*CustIDNameCityCreditCardType3Roy_User2ZGNULL*/GO--将执行上下文切换回最后一个 EXECUTE AS 语句的调用方REVERT;-- 函数: ASYMKEYPROPERTY，返回非对称密钥的属性。SELECT ASYMKEYPROPERTY(AsymKey_ID('User2AsymmetricKey'), 'algorithm_desc') AS [Algorithm],ASYMKEYPROPERTY(AsymKey_ID('User2AsymmetricKey'), 'string_sid') AS StringSID,ASYMKEYPROPERTY(AsymKey_ID('User2AsymmetricKey'), 'sid') AS SID ;GO-- 算法描述只是可供扩展密钥管理密钥-- 3、对称密钥-- 创建一个密钥使用TRIPLE_DES受证书并将它与 User1 关联CREATE SYMMETRIC KEY User1SymmetricKeyCertAUTHORIZATION User1 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE User1Certificate;-- 可选 IDENTITY_VALUE 生成一个 GUID 来标记数据加密密钥，让您匹配数据的关键-- 创建使用了密码保护的TRIPLE_DES密钥并将其与User2关联CREATE SYMMETRIC KEY User2SymmetricKeyPwdAUTHORIZATION User2WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD = 'User2SymmetricKeyPwd';-- 创建一个密钥使用 TRIPLE_DES 密钥的非对称密钥并由 dbo 拥有CREATE SYMMETRIC KEY GenericSymmetricKeyAsymWITH ALGORITHM = TRIPLE_DESENCRYPTION BY ASYMMETRIC KEY User2AsymmetricKey;-- 创建一个密钥使用 DESX 受另一个对称密钥并由 dbo 拥有OPEN SYMMETRIC KEY User1SymmetricKeyCertDECRYPTION BY CERTIFICATE User1Certificate;CREATE SYMMETRIC KEY GenericSymmetricKeySymWITH ALGORITHM = DESXENCRYPTION BY SYMMETRIC KEY User1SymmetricKeyCert;CLOSE SYMMETRIC KEY User1SymmetricKeyCert;-- 列出数据库中的对称密钥SELECT * FROM sys.symmetric_keys;-- 列表(此目录视图可返回当前对话中打开的加密密钥的有关信息。)SELECT * FROM sys.openkeys;-- 插入数据，使用各种非对称密钥。如果不对称密钥,打开的没有错误和没有插入的数据。OPEN SYMMETRIC KEY User1SymmetricKeyCertDECRYPTION BY CERTIFICATE User1Certificate;INSERT INTO Customer2 VALUES (4, 'Roy_User4', 'GZ',EncryptByKey(Key_GUID('User1SymmetricKeyCert'), 'Amex'));CLOSE SYMMETRIC KEY User1SymmetricKeyCert;-- 可选的验证参数作为盐。必须使用相同的身份验证器来加密和解密。保护整个值替换攻击。OPEN SYMMETRIC KEY User2SymmetricKeyPwdDECRYPTION BY PASSWORD = 'User2SymmetricKeyPwd';INSERT INTO Customer2 VALUES (5, 'Roy_User5', 'GZ',EncryptByKey(Key_GUID('User2SymmetricKeyPwd'), 'Visa'));CLOSE SYMMETRIC KEY User2SymmetricKeyPwd;OPEN SYMMETRIC KEY GenericSymmetricKeyAsymDECRYPTION BY ASYMMETRIC KEY User2AsymmetricKey;INSERT INTO Customer2 VALUES (6, 'Roy_User6', 'GZ',EncryptByKey(Key_GUID('GenericSymmetricKeyAsym'), 'Optima'));CLOSE SYMMETRIC KEY GenericSymmetricKeyAsym;OPEN SYMMETRIC KEY User1SymmetricKeyCertDECRYPTION BY CERTIFICATE User1Certificate;OPEN SYMMETRIC KEY GenericSymmetricKeySymDECRYPTION BY SYMMETRIC KEY User1SymmetricKeyCert;INSERT INTO Customer2 VALUES (7, 'Roy_User7', 'GZ',EncryptByKey(Key_GUID('GenericSymmetricKeySym'), 'Wal-Mart'));CLOSE SYMMETRIC KEY GenericSymmetricKeySym;CLOSE SYMMETRIC KEY User1SymmetricKeyCert;-- 查看CustID>=4数扰SELECT * FROM Customer2 WHERE CustID >= 4;/*4Roy_User4GZ0x003FDE0600B08541922D500213AE5E4501000000DE3B49C60040D6634118C754D7AC7291FC3C40C081DF90B15Roy_User5GZ0x00ECC30427979944B6FAFAC9A00FB69D01000000893D664F157C21FF293F15C21C5696973AE687B993081CDE6Roy_User6GZ0x002B1FA92A111D4D9571BA68B05503B6010000005336FDD3DD1E33BDE404B32893A0850BEFD01A8ABE6527EA7Roy_User7GZ0x008772F0C5181F428D82A52CE7ED05BB010000008301BD1387E1D6D5BC80F0EE8F9868EC7C01EF8A743BCE96D0A5E8741C91866D*/-- 查看的数据。请注意，您不必指定用来解密数据，即使多个对称密钥可以打开的键。OPEN SYMMETRIC KEY User1SymmetricKeyCertDECRYPTION BY CERTIFICATE User1Certificate;SELECT CustID, Name, City,CONVERT(VARCHAR, DecryptByKey(CreditCardType)) AS CreditCardTypeFROM Customer2 WHERE CustID = 4;CLOSE SYMMETRIC KEY User1SymmetricKeyCert;/*CustIDNameCityCreditCardType4Roy_User4GZAmex*/OPEN SYMMETRIC KEY User2SymmetricKeyPwdDECRYPTION BY PASSWORD = 'User2SymmetricKeyPwd';SELECT CustID, Name, City,CONVERT(VARCHAR, DecryptByKey(CreditCardType)) AS CreditCardTypeFROM Customer2 WHERE CustID = 5;CLOSE SYMMETRIC KEY User2SymmetricKeyPwd;/*CustIDNameCityCreditCardType5Roy_User5GZVisa*/OPEN SYMMETRIC KEY GenericSymmetricKeyAsymDECRYPTION BY ASYMMETRIC KEY User2AsymmetricKey;SELECT CustID, Name, City,CONVERT(VARCHAR, DecryptByKey(CreditCardType)) AS CreditCardTypeFROM Customer2 WHERE CustID = 6;CLOSE SYMMETRIC KEY GenericSymmetricKeyAsym;/*CustIDNameCityCreditCardType6Roy_User6GZOptima*/OPEN SYMMETRIC KEY User1SymmetricKeyCertDECRYPTION BY CERTIFICATE User1Certificate;OPEN SYMMETRIC KEY GenericSymmetricKeySymDECRYPTION BY SYMMETRIC KEY User1SymmetricKeyCert;SELECT CustID, Name, City,CONVERT(VARCHAR, DecryptByKey(CreditCardType)) AS CreditCardTypeFROM Customer2 WHERE CustID = 7;CLOSE SYMMETRIC KEY GenericSymmetricKeySym;CLOSE SYMMETRIC KEY User1SymmetricKeyCert;/*CustIDNameCityCreditCardType7Roy_User7GZWal-Mart*/-- 查看表数据SELECT * FROM Customer2;/*1Roy WuGZ0xB5B4C36DD479BE908577A93D82235EE2FC23F9A5B4AF1D8FF3E91BD848AC3DD04285CCC5E93DF6E2C49840FAB74DAD913685F4B54EA89CB888B314192C9EA2643A244BD6540C18F6103258CB3BC0014BAE121572248BE725B44FB5543CEF6D732DDE950DC9435D35C4D1AD9522F20EBFAA5070C51F7BC8521FB167F68FE8D4512Roy_User1ZG0x84A8C3CDE2C46E45D770CAD909A68E9B566B6D8176D4BE7C582DCD6AF9CB15C2EF0DB3F3B13323BF69B63AE8FC864FC9DA91138DDB12AEFD4680AA5791C6F745421A4011ECA5B34B4A1AA5EB61A0BBEAA918E5EAD76A2A13E63D121DCE230F9722A036503A2D781FA9DBEA6502524437E9A62DD94C1557A97704F629687B3B1B6C061A6883D6CC87DBC84C47ADCBBB4D7B3E2BA3A20C86BC28479B93981515FCBF602A360BFDF24227A1C99CC30F3D9D88C7D42E4565DF0C098307F82469E9F17942E4EE73753E132E26F64362D4228C77FF4B1F72FA522A2318AA02AB7E1E0432BFC84F01914E244664E8FDB837056ED261E74063D85F1E4AA44C65AED992143Roy_User2ZG0xED893EBAD6790543E6196E248853EEA0C600E7C49625857F20862F44FDCB8460E13A3A7575173A93250E72006EA6551C551FB92D0014F8FCD845BBABCCAE55CB344415169B96C80BAB7B127FDCD8E056DED2B26366AD4E6DA27F993C84C90EE37AC16CB95636DA776818ECE01EE531CD2A927EA8816167F52A40C0D7396CE46C0BBBD28226D543F0B55D3120B4BA693220D08065C9BBF3E8918016E0774DF32FC3044697E111EB1B3FDAF11B03F8CFFF23C53339E8D920781361B679759CA5C168E8D5DCAB94025F25162268259C18FC2FA4DC541A25757708C61928E0E535BDDF80A3068A483EC1C5F4F772E2F442672A7979481F9A60ED5DAF849CC9A07D9A4Roy_User4GZ0x003FDE0600B08541922D500213AE5E4501000000DE3B49C60040D6634118C754D7AC7291FC3C40C081DF90B15Roy_User5GZ0x00ECC30427979944B6FAFAC9A00FB69D01000000893D664F157C21FF293F15C21C5696973AE687B993081CDE6Roy_User6GZ0x002B1FA92A111D4D9571BA68B05503B6010000005336FDD3DD1E33BDE404B32893A0850BEFD01A8ABE6527EA7Roy_User7GZ0x008772F0C5181F428D82A52CE7ED05BB010000008301BD1387E1D6D5BC80F0EE8F9868EC7C01EF8A743BCE96D0A5E8741C91866D*//*联机查看函数：EncryptByKey 用法( key_GUID , { 'cleartext' | @cleartext }        [, { add_authenticator | @add_authenticator }          , { authenticator | @authenticator } ] ) */-- 打开对称密钥有成本。所以你可以测试是否为（运行与无打开关键语句） 的关键是开放：OPEN SYMMETRIC KEY User1SymmetricKeyCertDECRYPTION BY CERTIFICATE User1Certificate;IF NOT EXISTS(SELECT 1 FROM sys.openkeys WHERE key_name = 'User1SymmetricKeyCert' AND database_name = db_Name())PRINT 'Key is not open'ELSEPRINT 'Key is open';GOCLOSE SYMMETRIC KEY User1SymmetricKeyCert;-- 如果密钥是由证书或非对称密钥的 protectedy，可以使用 DecryptByKeyAutoCert 或 DecryptByKeyAutoAsymKey 作为一个快捷方式。只是如有必要，请打开对称密钥。另外到 SQL Server 2005 后初始版本-- 证书:SELECT CustID, Name, City,CONVERT(VARCHAR, DecryptByKeyAutoCert(cert_id('User1Certificate'), NULL, CreditCardType)) AS CreditCardTypeFROM Customer2 WHERE CustID = 4;-- 非对称密钥:SELECT CustID, Name, City,CONVERT(VARCHAR, DecryptByKeyAutoAsymKey(AsymKey_ID('User2AsymmetricKey'), NULL, CreditCardType)) AS CreditCardTypeFROM Customer2 WHERE CustID = 6;--函数: SYMKEYPROPERTY-- 检查的关键属性，但只为 EKM 键SELECT SYMKEYPROPERTY(Key_ID('GenericSymmetricKeySym'), 'algorithm_desc') AS Algorithm,SYMKEYPROPERTY(Key_ID('GenericSymmetricKeySym'), 'string_sid') AS String_SID,SYMKEYPROPERTY(Key_ID('GenericSymmetricKeySym'), 'sid') AS SID;GO-- New T-SQL function: KEY_NAME-- Return name of key from GUID or cipher textSELECT KEY_NAME(Key_GUID('User1SymmetricKeyCert'));SELECT KEY_NAME(CreditCardType) FROM Customer2 WHERE CustId = 6;-- 删除测试登陆账号DROP LOGIN User1;DROP LOGIN User2;DROP LOGIN User3;USE master;GODROP DATABASE EncryptionDB;