在Sql Server中使用证书加密数据

IF NOT EXISTS 
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY 
PASSWORD = 'QWE23987zxJKL569&agf1$94467GRkjg5k3fd137r))#1946kcj$n44nhdlj';


--创建证书
CREATE CERTIFICATE ForEncryptCertDemo WITH SUBJECT = 'For Encrypt/Decrypt';


--创建密钥
CREATE SYMMETRIC KEY SymKeyDemo WITH ALGORITHM = AES_256,
KEY_SOURCE = 'Encryption',
IDENTITY_VALUE = 'Encryption'
ENCRYPTION BY CERTIFICATE Demo;


--增加加密字段

alter table Admin add EncPasswd varbinary(256);

OPEN SYMMETRIC KEY SymKeyDemo DECRYPTION BY CERTIFICATE ForEncryptCertDemo;
UPDATE Admin SET EncPasswd = ENCRYPTBYKEY(KEY_GUID('SymKeyDemo'),convert(varchar,password));
CLOSE SYMMETRIC KEY SymKeyDemo;

 

--允许用户使用

GRANT VIEW DEFINITION ON SYMMETRIC KEY::symkeydemo to test_user;
GRANT CONTROL ON CERTIFICATE::forencryptcertdemo to test_user;