第1个回答 推荐于2016-08-06
/*
1.利用对称密钥
搭配EncryptByKey进行数据jiami
使用DecryptByKey函数进行jiemi
这种方式比较适合大数据量
*/
CREATE SYMMETRIC KEY ZXC WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = 'ZXC123456'
OPEN SYMMETRIC KEY ZXC DECRYPTION BY PASSWORD = 'ZXC123456'
--jiami
SELECT Encryptbykey(Key_guid('ZXC'), 'YHDH_C')
--jiemi
SELECT CONVERT(VARCHAR(10), Decryptbykey(0x001B05DF93A12F4A941E64A06B9AEE94010000007582B2C772953F1A6ABC7298E126B76A22A6815115466653D11D6D1F23641DA6))
CLOSE SYMMETRIC KEY ZXC;
DROP SYMMETRIC KEY ZXC;
/*
2.利用非对称密钥
搭配EncryptByAsymKey进行数据jiami
使用DecryptByAsymKey函数进行jiemi
用于更高安全级别的加jiemi数据
*/
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XIAOMA'
CREATE ASYMMETRIC KEY ABC WITH ALGORITHM=RSA_2048 ENCRYPTION BY PASSWORD = 'ZXC123456'
ALTER ASYMMETRIC KEY ABC WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'ZXC123456')
--jiami
Select Encryptbyasymkey(Asymkey_id ('ABC'), 'ZXC123456')
SELECT CONVERT(VARCHAR(100), Decryptbyasymkey (Asymkey_id('ABC'),0xDA7081BE942896AE961589B5BC8ABDA88BF28DBECD4AB0F9EF1C1666591511BE4D2659EE01B8686002946A8EF7074B14CFFBFF042F21D4F86878EAD8B24E1CF29031D89315D20F70F456A69D74491652522035A0B394E412B0AF52198E665C3E163765A2095D654E6922E0F94E0FAD83C6C322E56C910DBEACD3FF98C912F153B4C701431A74E6A10EAF342491345B0D1D47C49B8895EB02E0152EABFC19A3FA4BD857F228B7082F0195DFEB82E734E0B0722798D2382D1F2FEC8203CCA302B8E80D06913A168C50011FD0A526A1088C55EA905AFB97AC414CBDACD4A28BCC4CD3CBDC8140851057A3E6882F17DA914ECFFC01A196B1F7E7B7AC3E945E106D82 ))
DROP ASYMMETRIC KEY ABC;
DROP MASTER KEY
/*
3.利用凭证的方式
搭配EncryptByCert进行jiami
DecryptByCert函数进行jiemi
比较类似非对称密钥
*/
CREATE CERTIFICATE ZXC
ENCRYPTION BY PASSWORD = 'xiaoxiao'
WITH SUBJECT = 'DB_Encrypt_Demo Database Encryption Certificate',
START_DATE = '2015-10-1'
--jiami
Select Encryptbycert(Cert_id('ZXC'), '123456')
--jiemi
SELECT convert(Varchar(10), Decryptbycert(Cert_id('ZXC'), 0xB4E223CC08A73DE5AFE81A6FF14E76503CF55066EE8186F0781942E54B4FA97D3B49D9A16A102398311F9B259D3E2F23F0B35D3720F4AAB08357850D96A7E43494A306D227A29812BA62A33B39AC600D6585EA6B03F5F97A012C5A0BE7BC52FF60519421BD8D09311F10CC55EEA166FDBFAD2A5D9E2D3043A215AE27A918E35B,N'xiaoxiao'))
DROP CERTIFICATE ZXC;
/*
4.利用mima短语方式
搭配EncryptBypassPhrase进行jiami
使用DecryptByPassPhrase函数来jiemi
比较适合一般的数据加jiemi
*/
--jiami
Select Encryptbypassphrase('xiaoma', '123456')
--jiemi
SELECT CONVERT(VARCHAR(100), Decryptbypassphrase('xiaoma',0x01000000435DFA417C8E1A1417D901357378A1864F721EAA53DCD800 ))本回答被提问者和网友采纳