实例讲解SQL Server加密功能

sql server中加密是层级的，每一个上层为下提供保护。如图：

实例：
/**
smk(service master key)在sql server安装时生成,由windows dpapi(data protection api)提供保护
**/

/**创建数据库级别dmk(database master key),受smk保护**/
create master key encryption by password=n'passw0rd'
go

/**数据库内的加密对象受dmk保护
支持的对称加密算法:des | triple_des | triple_des_3key | rc2 | rc4 | rc4_128| desx | aes_128 | aes_192 | aes_256
非对称加密算法：rsa_512 | rsa_1024 | rsa_2048
注意避免使用rc,desx类算法,2014之后会删除此功能
**/

--1.创建非对称密钥.

create asymmetric key asyc_key_enc 
with algorithm=rsa_1024 
encryption by password=n'pass@word' 
go

--2.创建对称密钥.

create symmetric key symc_key_enc 
with algorithm=triple_des 
encryption by password=n'pass@word' 
go

--3.创建证书.证书也可被其它方式保护

create certificate cert_enc 
with subject='certificate for enc',expiry_date='20990101' 
go
 

--4.对称密钥可由以上三种方式提供加密保护

--4.1 由非对称密钥加密

create symmetric key symc_key_enc_byasyc 
with algorithm=aes_128 
encryption by asymmetric key asyc_key_enc 
go
 

--4.2 由对称密钥加密

open symmetric key symc_key_enc 
decryption by password=n'
pass@word'; 
create symmetric key symc_key_enc_bysymc 
with algorithm = des 
encryption by symmetric key symc_key_enc 
go

--4.3 由证书加密

create symmetric key symc_key_enc_bycert 
with algorithm =aes_128 
encryption by certificate cert_enc 
go

/**列级数据加密和解密.mssql提供以下4对加密/解密函数对列数据加密
encryptbycert() 和decryptbycert()—利用证书对数据进行加密和解密
encryptbyasymkey() and decryptbyasymkey()—利用非对称密钥对数据进行加密和解密
encryptbykey() and decryptbykey()—利用对称密钥对数据进行加密和解密
encryptbypassphrase() and decryptbypassphrase()—利用密码字段产生对称密钥对数据进行加密和解密
注意:被加密和解密的数据,必需是varbinary类型
**/

--以encryptbykey为例，其它的大同小异

--对***号idn进行加密和解密

create table tb(idn int,name varchar(20)); 
insert into tb 
values (123456789,'bigbrother'),(090807001,'spiderman'),(336655789,'superman') 
go

--新增列ency_idn存储加密数据,使用之前由非对称密钥加密的对称密钥symc_key_enc_byasyc来加密数据

alter table tb add ency_idn varbinary(128); 
go 
open symmetric key symc_key_enc_byasyc 
decryption by asymmetric key asyc_key_enc 
with password=n'
pass@word'; 
update tb 
set ency_idn=encryptbykey(key_guid('symc_key_enc_byasyc'),convert(varbinary,idn));--加密前要转成varbinary 
close symmetric key symc_key_enc_byasyc --显式关闭对称密钥 
go
 

--解密被加密的列数据

open symmetric key symc_key_enc_byasyc 
decryption by asymmetric key asyc_key_enc 
with password=n'
pass@word'; 
select idn,ency_idn,convert(int,decryptbykey(ency_idn))as decr_idn 
from tb; 
close symmetric key symc_key_enc_byasyc --显式关闭对称密钥 
go
1
<br>

以上就是本文的全部内容，希望对大家的学习有所帮助。