SQLServer 資料加密解密（一），sqlserver加密解密

SQLServer 資料加密解密（一），sqlserver加密解密

都是基本樣本，更多參考官方文檔：

1. Transact-SQL 函數
2. 資料庫密鑰
3. 認證
4. 非對稱金鑰
5. 對稱金鑰

--drop table EnryptTestcreate table EnryptTest(id int not null primary key,EnryptData nvarchar(20),)insert into EnryptTestvalues(1,N'888888'),(2,N'888888'),(3,N'123456'),(4,N'A');select * from EnryptTest;

【Transact-SQL 函數加密】

/***********************************【Transact-SQL 函數加密】********************************/--使用 TRIPLE DES 演算法（128 密鑰位長度）的複雜密碼加密資料。--添加測試列alter table EnryptTest add PassPhrase varbinary(256)alter table EnryptTest add PassPhrase2 varbinary(256)--用於驗證器驗證--加密(EncryptByPassPhrase)--https://technet.microsoft.com/zh-cn/library/ms190357%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396update EnryptTest set PassPhrase = EncryptByPassPhrase('Hello.kk',EnryptData)goupdate EnryptTest set PassPhrase2 = EncryptByPassPhrase(  'Hello.kk'--用於產生對稱金鑰的複雜密碼, EnryptData--要加密的明文, 1--指示是否將驗證器與明文一起加密。如果將添加驗證器，則為 1, convert(varbinary,id)--用於派生驗證器的資料(如 主鍵))go--解密(DecryptByPassPhrase)--https://technet.microsoft.com/zh-cn/library/ms188910%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396select convert(nvarchar,DecryptByPassphrase('Hello.kk',PassPhrase)) from EnryptTest;goselect convert(nvarchar,DecryptByPassphrase(  'Hello.kk'--產生解密密鑰的複雜密碼, PassPhrase2--要解密的加密文本varbinary , 1--添加驗證器, convert(varbinary,id)))--驗證器為主鍵from EnryptTest;go--附：未用驗證器的，資料並不安全--如：把所有id的密碼都改為與A一樣，其他密碼的解密與A一樣，別人就有可能登入其他帳號update EnryptTest set PassPhrase = (select PassPhrase from EnryptTest where id=4)goselect id,EnryptData,convert(nvarchar,DecryptByPassphrase('Hello.kk',PassPhrase)) from EnryptTest;--刪除測試列alter table EnryptTest drop column PassPhrase alter table EnryptTest drop column PassPhrase2go

【資料庫主要金鑰】

/***************************************【資料庫主要金鑰】***********************************/select * from sys.key_encryptionsselect * from sys.crypt_properties--建立資料庫主要金鑰--https://technet.microsoft.com/zh-cn/library/ms174382(v=sql.105).aspxcreate master key encryption by password = N'Hello@MyMasterKey' --必須符合Windows密碼原則要求go--開啟當前資料庫的資料庫主要金鑰--https://technet.microsoft.com/zh-cn/library/ms186336(v=sql.105).aspxopen master key decryption by password = N'Hello@MyMasterKey'go--更改資料庫主要金鑰的屬性--https://technet.microsoft.com/zh-cn/library/ms186937(v=sql.105).aspxalter master key regenerate with encryption by password = N'Hello@MyMasterKey'alter master key add encryption by password = N'Hello@kk'alter master key drop encryption by password = N'Hello@kk'alter master key add encryption by service master keyalter master key drop encryption by service master key--匯出資料庫主要金鑰--https://technet.microsoft.com/zh-cn/library/ms174387(v=sql.105).aspxbackup master key to file = N'D:\XXDB_MasterKey' encryption by password = N'Hello@MyMasterKey'go--從備份檔案中匯入資料庫主要金鑰--https://technet.microsoft.com/zh-cn/library/ms186336(v=sql.105).aspxrestore master key from file = N'D:\XXDB_MasterKey'    decryption by password = N'Hello@MyMasterKey'    encryption by password = N'Hello@MyMasterKey' --New Passwordgo--從當前資料庫中刪除主要金鑰--https://technet.microsoft.com/zh-cn/library/ms180071(v=sql.105).aspxdrop master keygo

【認證】

/*****************************************【認證】*************************************/--認證和非對稱金鑰使用資料庫級的內部公開金鑰加密資料，並且使用資料庫級內部私密金鑰解密資料--當使用資料庫主要金鑰對私密金鑰進行加密時，不需要 ENCRYPTION BY PASSWORD 選項。私密金鑰使用資料庫主要金鑰進行加密--（有點難理解，最後給出例子）select * from sys.key_encryptionsselect * from sys.crypt_propertiesselect * from sys.certificatesselect * from EnryptTest--添加測試列alter table EnryptTest add CertificateCol varbinary(max)go--建立認證--https://technet.microsoft.com/zh-cn/library/ms187798%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396create certificate Mycertificateencryption by password = N'Hello@Mycertificate'--加密密碼with subject = N'EnryptData certificate',--認證描述  start_date = N'20150401',--認證生效日 expiry_date = N'20160401';--認證到期日  go--使用認證的公開金鑰加密資料--https://msdn.microsoft.com/zh-cn/library/ms188061(v=sql.105).aspxupdate EnryptTest set CertificateCol = EncryptByCert(CERT_ID('Mycertificate'),CONVERT(VARCHAR(MAX),EnryptData))go--用認證的私密金鑰解密資料--https://msdn.microsoft.com/zh-cn/library/ms178601(v=sql.105).aspxselect *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol,N'Hello@Mycertificate'))from EnryptTest;go--修改私密金鑰密碼 --https://msdn.microsoft.com/zh-cn/library/ms189511(v=sql.105).aspxalter certificate mycertificate with private key (decryption by password = N'Hello@Mycertificate', encryption by password = N'Hello@Mycertificate')go--從認證中刪除私密金鑰 alter certificate mycertificate remove private keygo--備份認證--https://msdn.microsoft.com/zh-cn/library/ms178578(v=sql.105).aspxbackup certificate mycertificate to file = N'D:\mycertificate.cer' --用於加密的認證備份路徑with private key ( file = N'D:\mycertificate_saleskey.pvk' , --用於解密認證私密金鑰檔案路徑 decryption by password = N'Hello@Mycertificate' ,--對私密金鑰進行解密的密碼encryption by password = N'Hello@Mycertificate' );--對私密金鑰進行加密的密碼go--建立/還原認證create certificate mycertificate from file = N'D:\mycertificate.cer' with private key (    file = N'D:\mycertificate_saleskey.pvk',     decryption by password = 'Hello@Mycertificate');go--刪除對稱金鑰--https://msdn.microsoft.com/zh-cn/library/ms182698(v=sql.105).aspxdrop certificate  Mycertificate;go--刪除測試列alter table EnryptTest drop column CertificateCol;go

【非對稱金鑰】

/***************************************【非對稱金鑰】*************************************/--預設情況下，私密金鑰受資料庫主要金鑰保護select * from sys.key_encryptionsselect * from sys.crypt_propertiesselect * from sys.certificatesselect * from sys.asymmetric_keysselect * from sys.openkeysselect * from EnryptTest--添加測試列alter table EnryptTest add AsymmetricCol varbinary(max)go--建立非對稱金鑰--https://msdn.microsoft.com/zh-cn/library/ms174430(v=sql.105).aspxcreate asymmetric key MyAsymmetric with algorithm=rsa_512 encryption by password='Hello@MyAsymmetric';go  --加密（EncryptByAsymKey）--https://msdn.microsoft.com/ZH-CN/library/ms186950(v=sql.105).aspxupdate EnryptTest set AsymmetricCol = EncryptByAsymKey(asymkey_id ('MyAsymmetric'),convert(varchar(max ),EnryptData))  go--解密（DecryptByAsymKey）--https://msdn.microsoft.com/ZH-CN/library/ms189507(v=sql.105).aspxselect *,convert(varchar(max),DecryptByAsymKey(asymkey_id('MyAsymmetric'),AsymmetricCol,N'Hello@MyAsymmetric'))from EnryptTestgo--更改非對稱金鑰屬性--https://msdn.microsoft.com/zh-cn/library/ms187311(v=sql.105).aspx--更改私密金鑰密碼alter asymmetric key MyAsymmetric     with private key (    decryption by password = N'Hello@MyAsymmetric',--原私密金鑰密碼    encryption by password = N'Hello@MyAsymmetric');--新私密金鑰密碼go--刪除私密金鑰,只保留公開金鑰--如果將非對稱金鑰映射到 EKM 裝置上的可擴充密鑰管理 (EKM) 密鑰並且未指定 REMOVE PROVIDER KEY 選項，--則會從資料庫中刪除該密鑰，但不會從裝置上刪除它。這時會發出一條警告。alter asymmetric key MyAsymmetric remove private key;go--刪除非對稱金鑰--https://msdn.microsoft.com/ZH-CN/library/ms188389(v=sql.105).aspxdrop symmetric key MyAsymmetric ;go--刪除測試列alter table EnryptTest drop column AsymmetricColgo

【對稱金鑰】

/***************************************【對稱金鑰】*************************************/--也稱為單祕密金鑰加密,採用單鑰密碼系統的加密方法，同一個密鑰可以同時用作資訊的加密和解密.--非對稱金鑰消耗多些系統效能，一般使用對稱金鑰密碼編譯資料，使用非對稱金鑰保護對稱金鑰select * from sys.key_encryptionsselect * from sys.crypt_propertiesselect * from sys.certificatesselect * from sys.asymmetric_keysselect * from sys.openkeysselect * from sys.symmetric_keysselect * from EnryptTest--添加測試列alter table EnryptTest add SymmetricCol varbinary(max)go--建立對稱金鑰--https://msdn.microsoft.com/zh-cn/library/ms188357(v=sql.105).aspxcreate symmetric key MySymmetric--以密碼加密的對稱金鑰with algorithm=aes_128 encryption by password='Hello@MySymmetric';gocreate symmetric key MySymmetric--以非對稱金鑰密碼編譯的對稱金鑰with algorithm=aes_128 encryption by asymmetric key MyAsymmetricgo--開啟對稱金鑰(開啟才能有效使用加密解密函數)--https://msdn.microsoft.com/zh-cn/library/ms190499(v=sql.105).aspxopen symmetric key MySymmetric decryption by password='Hello@MySymmetric';goopen symmetric key MySymmetric decryption by asymmetric key MyAsymmetric with password='Hello@MyAsymmetric';go--加密資料--https://technet.microsoft.com/zh-cn/library/ms174361%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396update EnryptTest set SymmetricCol = encryptbykey(key_guid('MySymmetric'),convert(varchar(max),EnryptData))go--解密資料--https://technet.microsoft.com/zh-cn/library/ms181860(v=sql.105).aspxselect *,convert(varchar(max ) ,convert (varchar(max ),decryptbykey(SymmetricCol)))  from EnryptTestgo--關閉對稱金鑰，或關閉在當前會話中開啟的所有對稱金鑰--https://msdn.microsoft.com/zh-cn/library/ms177938%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396--close all symmetric keys; close symmetric key MySymmetric;go--alter symmetric 添加或刪除新的加密方式(如添加多多個密碼，任何一個密碼都可用)--https://msdn.microsoft.com/zh-cn/library/ms189440(v=sql.105).aspxopen symmetric key MySymmetric decryption by password='Hello@MySymmetric';alter symmetric key MySymmetric add encryption by password = 'Hello@kk' --New another Passwordclose symmetric key MySymmetric;open symmetric key MySymmetric decryption by password='Hello@kk'; --Use New Passwordselect convert(varchar(max ) ,convert (varchar(max ),decryptbykey(SymmetricCol))) from EnryptTestalter symmetric key MySymmetric drop encryption by password = 'Hello@kk'--Drop the new Passwordclose symmetric key MySymmetric;go--刪除對稱金鑰--https://msdn.microsoft.com/zh-cn/library/ms182698(v=sql.105).aspxdrop symmetric key MySymmetric;go--刪除測試列alter table EnryptTest drop column SymmetricColgo

【樣本】

--測試資料/*drop certificate  Mycertificate;godrop master keygodrop table EnryptTestgo*/create table EnryptTest(id int not null primary key,EnryptData nvarchar(20),)goinsert into EnryptTestvalues(1,N'888888'),(2,N'888888'),(3,N'123456'),(4,N'A');goselect * from EnryptTest;alter table EnryptTest add CertificateCol varbinary(max)go--建立主要金鑰create master key encryption by password = N'Hello@MyMasterKey'go/*key_idthumbprintcrypt_typecrypt_type_desccrypt_property-----------------------------------------------------------------1010x01ESKMENCRYPTION BY MASTER KEY0x7A2FEDA8139F1DE8F3377424C120DBDB8E1F7EAAEC1BBD73E72AC04F5CEECBFAC15FC7E130CA1756281EA0D8E6997F44101NULLESKPENCRYPTION BY PASSWORD0x61D8F28F12CE4A1247F91E0ED828F2E937206E5D69B0754EE76756567AB428CBD5B54B76BCD1FC15E5E12202DBA4E187*/--建立認證，因為預設使用主要金鑰加密，此處不要求輸入密碼create certificate Mycertificatewith subject = N'EnryptData certificate',start_date = N'20150401',expiry_date = N'20160401';go--加密解密都自動使用服務主要金鑰加密了。即使使用“close master key ”也不起作用update EnryptTest set CertificateCol = EncryptByCert(CERT_ID('Mycertificate'),CONVERT(VARCHAR(MAX),EnryptData))goselect *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol)) from EnryptTest;go--現在刪除“服務主要金鑰”alter master key drop encryption by service master keygo--再查詢資料，沒有解密出來。不自動使用主要金鑰加密解密了select *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol)) from EnryptTest;go--這時需要顯式開啟主要金鑰,使用主要金鑰密碼加密解密open master key decryption by password = N'Hello@MyMasterKey'go--再查詢資料，解密出來了。select *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol)) from EnryptTest;go--最後關閉主要金鑰close master key go--查看主要金鑰，少了"ENCRYPTION BY MASTER KEY",沒有了主要金鑰進行加密,而是使用密碼進行加密select * from sys.key_encryptions/*key_idthumbprintcrypt_typecrypt_type_desccrypt_property-----------------------------------------------------------------101NULLESKPENCRYPTION BY PASSWORD0xFB7D746C3CF0471147ECD710B1173B69A966421089FBB813BCF108E1ED90574F5C5F0998BA44F48869E05E9093BC59E6*/--刪除測試資料drop certificate  Mycertificate;godrop master keygodrop table EnryptTestgo參考:服務主要金鑰:https://msdn.microsoft.com/zh-cn/library/ms189060(v=sql.90).aspx

插圖2張：