MSSQL数据加密解密

敏感数据，如密码，卡号一般不能使用正常数值来存储，否则会有安全隐患。以往的加密解密都有前端应用程序来辅助完成。

2005年开始数据库也提供数据加密解密：1，短语加密,2，凭证方式,3，对称密钥,4，非对称密钥

1，利用密码短语加密（EncryptByPassPhrase进行加密，DecryptByPassPhrase进行解密）
drop table t0;
create table t0(
    id int identity(1,1),
    name nvarchar(225),
    password varbinary(8000)
);
--短语加密：适合一般的数据加密。参数的数据行不可变动，否则解密失败。
insert t0(name,password)
select '张三',encryptbypassphrase(N'password@1',N'123456')--password@1是密码用来加密
--对数据加密后，无法直接看到原始内容
select *from t0;

--解密
select id,name,
    convert(nvarchar(max),decryptbypassphrase(N'password@1',password))
from t0;

drop table t0;
2，利用凭证的方式加密数据（EncrypByCert进行加密和DecryptByCert函数进行解密）
drop table t1;
create table t1(
    id int identity(1,1),
    name nvarchar(225),
    password varbinary(8000)
);
--建立凭证一，该证书使用数据库主密钥来加密
create certificate cert_1 --证书名
with subject = N'该证书使用数据库主密钥来加密',--证书描述
start_date = '2023/01/01', --证书生效日期
expiry_date = '2025/01/01' --证书到期日期
--建立凭证二，该证书使用密码来加密
create certificate cert_2 
encryption by password='12345678' --凭证密码
with subject = N'该证书使用凭证密码来加密',
start_date='2023/01/01',
expiry_date = '2025/01/01'

--证书建好后，可以用证书对数据加密。如对表t1做insert时，使用EncryptByCert加密数据
insert t1(name,password)
select '张三',encryptbycert(cert_id(N'cert_1'),N'123456');--使用证书1加密
insert t1(name,password)
select '李四',encryptbycert(cert_id(N'cert_2'),N'456789');--使用证书2加密
--对数据加密后，无法直接看到原始内容
select *from t1;

--需要对内容解密才能看到原始内容，解密使用decryptByCert和convert函数
select id, name,
    convert(nvarchar(50), decryptByCert(cert_id(N'cert_1'),password)) as cert_1, --使用证书1解密
    convert(nvarchar(50), decryptByCert(cert_id(N'cert_2'),password)) as cert_2, --使用证书2解密：使用证书2解密必须指定第三个参数DecryptByCert,否则返回null
    convert(nvarchar(50), decryptByCert(cert_id(N'cert_2'),password,N'12345678')) as cert_2 --使用证书2解密
from t1;

--删除证书
drop certificate cert_1;
drop certificate cert_2;

3，利用对称密钥加密数据（EncrypByKey进行加密和DecryptByKey函数进行解密）
drop table t2;
create table t2(
    id int identity(1,1),
    name nvarchar(225),
    password varbinary(8000)
);
--创建对称密钥（比较适合大数据量，过程耗用资源较少）
create symmetric key Symkey_1
with algorithm = AES_256 --使用des加密算法
encryption by password='password@1'; --密码

--加密前，需要先打开open symmetric key。否则所产生的数据都是null值。
open symmetric key Symkey_1 decryption by password='password@1';
--对称密钥加密数据
--如对表t2做insert时，使用EncryptByKey加密数据
insert t2(name,password)
select '张三',encryptbykey(key_guid(N'Symkey_1'),N'123456')
insert t2(name,password)
select '李四',encryptbykey(key_guid(N'Symkey_1'),N'abcdefg')
--对数据加密后，无法直接看到原始内容
select *from t2;

--解密过程同样需要open symmetric key 
open symmetric key Symkey_1 decryption by password = 'password@1';
--解密使用DecryptByKey和convert函数
select id, name,
    convert(nvarchar(max), decryptByKey(password)) as AsymKey_1
from t2;

 

4，利用非对称密钥加密数据（EncrypByAsymKey进行加密和DecryptByAsymKey函数进行解密）
drop table t3;
create table t3(
    id int identity(1,1),
    name nvarchar(225),
    password varbinary(8000)
);
--创建非对称密钥（更高安全级别的加解密数据，耗用更多资源）
create asymmetric key AsymKey_1 
with algorithm = rsa_2048
encryption by password = 'password@1';

--非对称密钥使用两种不同的密钥，所以加密不需要输入密码验证，但解密需要。
--如对表t3做insert时，使用EncryptByAsymKey加密数据
insert t3(name,password)
select '张三',encryptbyasymkey(asymkey_id(N'AsymKey_1'),N'123456')
insert t3(name,password)
select '李四',encryptbyasymkey(asymkey_id(N'AsymKey_1'),N'abcdefg')
--对数据加密后，无法直接看到原始内容
select *from t3;

--解密使用decryptByAsymKey和convert函数
select id, name,
    convert(nvarchar(max), decryptByAsymKey(asymkey_id(N'AsymKey_1'),password,N'password@1')) as AsymKey_1
from t3;