SQL2012数据库加密方法(五)
979944B6FAFAC9A00FB69D01000000893D664F157C21FF293F15C21C5696973AE687B993081CDE
6 Roy_User6 GZ 0x002B1FA92A111D4D9571BA68B05503B6010000005336FDD3DD1E33BDE404B32893A0850BEFD01A8ABE6527EA
7 Roy_User7 GZ 0x008772F0C5181F428D82A52CE7ED05BB010000008301BD1387E1D6D5BC80F0EE8F9868EC7C01EF8A743BCE96D0A5E8741C91866D
*/
/*联机查看函数：EncryptByKey 用法( key_GUID , { 'cleartext' | @cleartext }
[, { add_authenticator | @add_authenticator }
, { authenticator | @authenticator } ] )
*/
-- 打开对称密钥有成本。所以你可以测试是否为（运行与无打开关键语句） 的关键是开放：
OPEN SYMMETRIC KEY User1SymmetricKeyCert
DECRYPTION 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'
ELSE
PRINT 'Key is open';
GO
CLOSE SYMMETRIC KEY User1SymmetricKeyCert;
-- 如果密钥是由证书或非对称密钥的 protectedy，可以使用 DecryptByKeyAutoCert 或 DecryptByKeyAutoAsymKey 作为一个快捷方式。只是如有必要，请打开对称密钥。另外到 SQL Server 2005 后初始版本
-- 证书:
SELECT CustID, Name, City,
CONVERT(VARCHAR, DecryptByKeyAutoCert(cert_id('User1Certificate'), NULL, CreditCardType)) AS CreditCardType
FROM Customer2 WHERE CustID = 4;
-- 非对称密钥:
SELECT CustID, Name, City,
CONVERT(VARCHAR, DecryptByKeyAutoAsymKey(AsymKey_ID('User2AsymmetricKey'), NULL, CreditCardType)) AS CreditCardType
FROM 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 text
SELECT 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;
GO
DROP DATABASE EncryptionDB;