Sql Server数据库实现表中字段的列加密

目录

• 1、问题描述
• 2、加密方法介绍
• 2.1、证书加密（推荐）
• 2.2、非对称密钥加解密
• 2.3、对称密钥加解密
• 3、总结
• 4、参考资料

use mydb;

--查看数据库中的证书
select * from sys.certificates;

--创建数据库主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='123@#456';

--创建证书
CREATE CERTIFICATE MyCert
with SUBJECT = 'My Test Certificate'
GO

--创建证书并授权给指定用户(zhangsan)
CREATE CERTIFICATE MyCertToZhangSan AUTHORIZATION zhangsan 
with SUBJECT = 'My Test Certificate To ZhangSan'
GO

--创建测试表TUser_test_Cert，要对pwd字段进行加密因此其字段类型设为varbinary
--varbinary表示可变长度二进制数据，但是在sqlserver表存放是以十六进制存的。
create table TUser_test_Cert(username varchar(50),pwd varbinary(2000));

--使用MyCert证书加密pwd字段
insert into TUser_test_Cert(username,pwd) values('liming',
        ENCRYPTBYCERT(
        CERT_ID('MyCert')
        ,'112233'
    )
);

select * from TUser_test_Cert;

--使用MyCert证书解密pwd字段
select username,CONVERT(
varchar(100),
DecryptByCert
    (
    CERT_ID('MyCert'),pwd
    )
) as pwd from TUser_test_Cert;

--创建一个角色zhangsan_role
create role zhangsan_role;
--给zhangsan_role这个角色分配TUser_test_Cert表的增删改查权限
grant select,update,insert,delete on TUser_test_Cert to zhangsan_role;

--使用MyCertToZhangSan证书加密pwd字段
insert into TUser_test_Cert(username,pwd) values('zhangsan',
        ENCRYPTBYCERT(
        CERT_ID('MyCertToZhangSan')
        ,'666333'
    )
);

select * from TUser_test_Cert;

--使用MyCert证书解密pwd字段
select username,CONVERT(
varchar(100),
DecryptByCert
    (
    CERT_ID('MyCertToZhangSan'),pwd
    )
) as pwd from TUser_test_Cert;

----利用非对称密钥对数据进行加密和解密---

--查看数据库中的非对称密钥
select * from sys.asymmetric_keys;

--创建非对称密钥
CREATE ASYMMETRIC KEY MyTestAsymmetric
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD = '%^&123%456';
GO

insert into TUser_test_Cert(username,pwd) values('ssan',
        EncryptByAsymKey(
        ASYMKEY_ID('MyTestAsymmetric')
        ,'112233'
        )
);

select * from TUser_test_Cert;

select username,CONVERT(
varchar(100),
DecryptByAsymKey
    (
    ASYMKEY_ID('MyTestAsymmetric'),pwd,N'%^&123%456'
    )
) as pwd from TUser_test_Cert;

----利用对称密钥对数据进行加密和解密----

--查看数据库中的对称密钥
select * from sys.symmetric_keys;

--创建对称密钥
CREATE SYMMETRIC KEY MyTestSymmetric
    WITH ALGORITHM = AES_256
    ENCRYPTION BY PASSWORD = '123456@aes';
GO
--打开密钥并进行加密/解密（只有对称加密需打开密钥！！！）
open symmetric key MyTestSymmetric decryption by password='123456@aes';

insert into TUser_test_Cert(username,pwd) values('qianqian',
        EncryptByKey(Key_GUID('MyTestSymmetric'), 'qw123sa')
);

select * from TUser_test_Cert;

select username,CONVERT(
varchar(100),
DECRYPTBYKEY(pwd) 
) as pwd from TUser_test_Cert