SQL2012数据库加密方法(六)
3 Roy_User2 ZG NULL
*/
-- 切换登陆用户User3
EXECUTE AS LOGIN = 'User3';
GO
SELECT CustID, Name, City,
CONVERT(VARCHAR,
DecryptByAsymKey(AsymKey_ID('User2AsymmetricKey'),
CreditCardType)) AS CreditCardType
FROM Customer2 WHERE CustID = 3;
/*
CustID Name City CreditCardType
3 Roy_User2 ZG NULL
*/
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 User1SymmetricKeyCert
AUTHORIZATION User1
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE User1Certificate;
-- 可选 IDENTITY_VALUE 生成一个 GUID 来标记数据加密密钥，让您匹配数据的关键
-- 创建使用了密码保护的TRIPLE_DES密钥并将其与User2关联
CREATE SYMMETRIC KEY User2SymmetricKeyPwd
AUTHORIZATION User2
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = 'User2SymmetricKeyPwd';
-- 创建一个密钥使用 TRIPLE_DES 密钥的非对称密钥并由 dbo 拥有
CREATE SYMMETRIC KEY GenericSymmetricKeyAsym
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY User2AsymmetricKey;
-- 创建一个密钥使用 DESX 受另一个对称密钥并由 dbo 拥有
OPEN SYMMETRIC KEY User1SymmetricKeyCert
DECRYPTION BY CERTIFICATE User1Certificate;
CREATE SYMMETRIC KEY GenericSymmetricKeySym
WITH ALGORITHM = DESX
ENCRYPTION BY SYMMETRIC KEY User1SymmetricKeyCert;
CLOSE SYMMETRIC KEY User1SymmetricKeyCert;
-- 列出数据库中的对称密钥
SELECT * FROM sys.symmetric_keys;
-- 列表(此目录视图可返回当前对话中打开的加密密钥的有关信息。)
SELECT * FROM sys.openkeys;
-- 插入数据，使用各种非对称密钥。如果不对称密钥,打开的没有错误和没有插入的数据。
OPEN SYMMETRIC KEY User1SymmetricKeyCert
DECRYPTION BY CERTIFICATE User1Certificate;
INSERT INTO Customer2 VALUES (4, 'Roy_User4', 'GZ',
EncryptByKey(Key_GUID('User1SymmetricKeyCert'), 'Amex'));
CLOSE SYMMETRIC KEY User1SymmetricKeyCert;
-- 可选的验证参数作为盐。必须使用相同的身份验证器来加密和解密。保护整个值替换攻击。
OPEN SYMMETRIC KEY User2SymmetricKeyPwd
DECRYPTION BY PASSWORD = 'User2SymmetricKeyPwd';
INSERT INTO Customer2 VALUES (5, 'Roy_User5', 'GZ',
EncryptByKey(Key_GUID('User2SymmetricKeyPwd'), 'Visa'));
CLOSE SYMMETRIC KEY User2SymmetricKeyPwd;
OPEN SYMMETRIC KEY GenericSymmetricKeyAsym
DECRYPTION BY ASYMMETRIC KEY User2AsymmetricKey;
INSERT INTO Customer2 VALUES (6, 'Roy_User6', 'GZ',
EncryptByKey(Key_GUID('GenericSymmetricKeyAsym'), 'Optima'));
CLOSE SYMMETRIC KEY GenericSymmetricKeyAsym;
OPEN SYMMETRIC KEY User1SymmetricKeyCert
DECRYPTION BY CERTIFICATE User1Certificate;
OPEN SYMMETRIC KEY GenericSymmetricKeySym
DECRYPTION 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;
/*
4 Roy_User4 GZ 0x003FDE0600B08541922D500213AE5E4501000000DE3B49C60040D6634118C754D7AC7291FC3C40C081DF90B1
5 Roy_User5 GZ 0x00ECC30427979944B6FAFAC9A00FB69D01000000893D664F157C21FF293F15C21C5696973AE687B993081CDE
6 Roy_User6 GZ 0x002B1FA92A111D4D9571BA68B05503B6010000005336FDD3DD1E33BDE404B32893A085