SQL Server 加密功能实例

亦凉 2022-01-13 06:29 233阅读 0赞

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

![spacer.gif][][![wKioL1W583OjD3bXAAGJ5Fpwxlo470.jpg][]][wKioL1W583OjD3bXAAGJ5Fpwxlo470.jpg]

实例：

/\*\*  
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

转载于:https://blog.51cto.com/joetang/1680238

[spacer.gif]: 
[wKioL1W583OjD3bXAAGJ5Fpwxlo470.jpg]: /images/20220113/d516833769af4d98a6a111a58c6ab9ce.png