實例講授SQL Server加密功效

實例講授SQL Server加密功效。本站提示廣大學習愛好者：（實例講授SQL Server加密功效）文章只能為提供參考，不一定能成為您想要的結果。以下是實例講授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>

以上就是本文的全體內容，願望對年夜家的進修有所贊助。