CREATE TABLE TBLUser
(
Name nvarchar(30),
Password varbinary(1000))
– 创建数据库主密钥
CREATE master key ENCRYPTION BY PASSWORD ='passW@ord'
– 创建证书
CREATE CERTIFICATE TestCert with SUBJECT ='Test Certificate'
– 创建对称密钥
CREATE SYMMETRIC KEY TestSymmetric WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE TestCert
– 使用对称秘钥加密数据
OPEN SYMMETRIC KEY TestSymmetric DECRYPTION BY CERTIFICATE TestCert;
INSERT INTO TBLUser values('张三',ENCRYPTBYKEY(Key_Guid(N'TestSymmetric'),'123456'));
CLOSE SYMMETRIC KEY TestSymmetric;
– 查看加密后的数据
SELECT * FROM TBLUser
– 解密数据
OPEN SYMMETRIC KEY TestSymmetric DECRYPTION BY CERTIFICATE TestCert;
SELECT Name,CAST(DecryptByKey(password) as varchar(100)) Password FROM TBLUser;
CLOSE SYMMETRIC KEY TestSymmetric;