sqlserver对数据进行加密、解密

– 创建测试表

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;

在这里插入图片描述

注:转载于https://jingyan.baidu.com/article/4dc4084852fceec8d946f1ee.html
如有侵权,请联系我立即删除(微信:hdyi1997)

展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读