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;

 

版权声明：本文为blackice原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/blackice/p/5387229.html