SQL Server 2005加密體系

SQL Server 2005中引入了一套完整的加密方法，具體的術語呢就偷個懶不寫了，大家可以看BOL么。

大致的結構呢就是在安裝sQL Server 2005的時候利用SQL Server服務賬號生成一個服務主密鑰Service Master Key，然后數據庫的管理員可以在數據庫上創建Database Master Key，當然也可以不創建，同時數據庫管理員可以為Database User創建證書、對稱密鑰或者非對稱密鑰。這三種對象都可以用于加密用戶數據，但一般推薦利用證書簽署代碼，利用證書或者非對稱密鑰加密對稱密鑰，利用對稱密鑰加密用戶數據。

以下還是給一段代碼，因為考慮到我跟王輝兄弟當時在成都賓館里研究這個東西就是苦于找不到完整的范例代碼，后來還是GTEC的徐強大拿給了個Link，然后我們又東拼西湊才出了一段代碼，不敢獨享，拿來與大家分享。

先給利用證書簽署代碼的范例，這段代碼的好處是不用給Database User大的權限，就可以讓用戶修改部分數據，這也是SQL Server 2005中權限粒度化的一種表現：

--------------------------------------------------------------------------------

--創建實驗用數據庫USE masterIF EXISTS(SELECT [name] FROM sys.databases WHERE [name] = 'Sales')DROP DATABASE SalesCREATE DATABASE SalesIF EXISTS(SELECT principal_id FROM sys.server_principals WHERE [name] = 'ryan' AND [type] = 'S')DROP LOGIN ryanCREATE lOGIN ryan WITH PASSWORD = 'P@ssw0rd'IF EXISTS(SELECT principal_id FROM sys.server_principals WHERE [name] = 'teddy' AND [type] = 'S')DROP LOGIN teddyCREATE lOGIN teddy WITH PASSWORD = 'P@ssw0rd'

--創建用戶ryan，并創建數據庫主密鑰USE SalesIF EXISTS(SELECT * FROM sys.database_principals WHERE [name] = 'ryan' AND [type] = 'S')DROP User ryanCREATE USER ryan FOR LOGIN ryan WITH DEFAULT_SCHEMA = dboGO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'GO

--創建證書IF EXISTS(SELECT [name] FROM sys.certificates WHERE [name] = 'CERT_MAINTAIN')DROP CERTIFICATE CERT_MAINTAINCREATE CERTIFICATE CERT_MAINTAINWITH SUBJECT = 'Certificate For Database Maintainance',START_DATE = '01/01/2006',EXPIRY_DATE = '12/31/2015'GO

SELECT * FROM sys.certificates

--利用證書創建數據庫用戶，并授予該用戶管理數據庫用戶的權限CREATE USER USER_MAINTAIN FOR CERTIFICATE CERT_MAINTAINGRANT ALTER ANY USER TO USER_MAINTAIN

--創建存儲過程IF EXISTS(SELECT [name] FROM sys.procedures WHERE [name] = 'usp_AddUser')DROP PROCEDURE dbo.usp_AddUserGOCREATE PROCEDURE dbo.usp_AddUser@UserName varchar(50)ASIF EXISTS(SELECT * FROM sys.database_principals WHERE [name] = @UserName AND [type] = 'S')EXEC ('DROP USER ' + @UserName)

EXEC ('CREATE USER ' + @UserName)GO

--完成準備工作，開始測試加密GRANT EXEC ON dbo.usp_AddUser TO ryanEXECUTE AS LOGIN = 'ryan'BEGIN TRYEXEC dbo.usp_AddUser 'teddy'END TRYBEGIN CATCHSELECT ERROR_MESSAGE() AS 'Error Msg'END CATCHREVERT

--利用證書簽署存儲過程代碼ADD SIGNATURE TO dbo.usp_AddUser BY CERTIFICATE CERT_MAINTAINALTER CERTIFICATE CERT_MAINTAIN REMOVE PRIVATE KEY

--在此嘗試執行腳本EXECUTE AS LOGIN = 'ryan'EXEC dbo.usp_AddUser 'teddy'REVERT

緊接上一篇，再給一段用密鑰加密數據的范例，這段代碼比較簡單，大黃不準像蹂躪大余一樣說我灌水！?。?/p>

--------------------------------------------------------------------------------

--創建實驗用數據庫USE masterIF EXISTS(SELECT [name] FROM sys.databases WHERE [name] = 'Sales')DROP DATABASE SalesCREATE DATABASE SalesIF EXISTS(SELECT principal_id FROM sys.server_principals WHERE [name] = 'ryan' AND [type] = 'S')DROP LOGIN ryanCREATE lOGIN ryan WITH PASSWORD = 'P@ssw0rd'IF EXISTS(SELECT principal_id FROM sys.server_principals WHERE [name] = 'teddy' AND [type] = 'S')DROP LOGIN teddyCREATE lOGIN teddy WITH PASSWORD = 'P@ssw0rd'GO

--創建用戶ryan，并創建數據庫主密鑰USE SalesIF EXISTS(SELECT * FROM sys.database_principals WHERE [name] = 'ryan' AND [type] = 'S')DROP User ryanCREATE USER ryan FOR LOGIN ryan WITH DEFAULT_SCHEMA = dboGOIF EXISTS(SELECT * FROM sys.database_principals WHERE [name] = 'teddy' AND [type] = 'S')DROP User teddyCREATE USER teddy FOR LOGIN teddy WITH DEFAULT_SCHEMA = dboGOCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'GO--使用服務主密鑰加密數據庫主密鑰，--在此刪除，因為發現數據庫主密鑰創建時默認及利用服務主密鑰加密--利用服務主密鑰加密的數據庫主密鑰稱為自動密鑰管理--可以利用以下查詢語句是否啟用數據庫主密鑰的自動密鑰管理SELECT [name], is_master_key_encrypted_by_server FROM sys.databases WHERE [name] = 'Sales'--以下語句用于啟用數據庫主密鑰的自動管理--ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEYGO

--為ryan創建證書IF EXISTS(SELECT [name] FROM sys.certificates WHERE [name] = 'CERT_RYAN')DROP CERTIFICATE CERT_RYANCREATE CERTIFICATE CERT_RYAN AUTHORIZATION ryan--ENCRYPTION BY PASSWORD = 'P@ssw0rd' --建議不要使用密碼，因為經過測試，是用密碼的證書是利用密碼保護， --而非數據庫主密鑰，可用以下語句測試證書的加密方法 --SELECT [name], pvt_key_encryption_type_desc FROM sys.certificates --WHERE [name] = 'CERT_DB'WITH SUBJECT = 'Certificate For Database',START_DATE = '01/01/2006',EXPIRY_DATE = '12/31/2015'GO--為teddy創建證書IF EXISTS(SELECT [name] FROM sys.certificates WHERE [name] = 'CERT_TEDDY')DROP CERTIFICATE CERT_TEDDYCREATE CERTIFICATE CERT_TEDDY AUTHORIZATION teddy--ENCRYPTION BY PASSWORD = 'P@ssw0rd' --建議不要使用密碼，因為經過測試，是用密碼的證書是利用密碼保護， --而非數據庫主密鑰，可用以下語句測試證書的加密方法 --SELECT [name], pvt_key_encryption_type_desc FROM sys.certificates --WHERE [name] = 'CERT_DB'WITH SUBJECT = 'Certificate For Database',START_DATE = '01/01/2006',EXPIRY_DATE = '12/31/2015'GO

SELECT * FROM sys.certificates

--為ryan和teddy分別創建利用證書保護的對稱密碼CREATE SYMMETRIC KEY Key_SYM_RYAN AUTHORIZATION ryanWITH ALGORITHM = TRIPLE_DESENCRYPTION BY CERTIFICATE CERT_RYANGOCREATE SYMMETRIC KEY Key_SYM_TEDDY AUTHORIZATION teddyWITH ALGORITHM = TRIPLE_DESENCRYPTION BY CERTIFICATE CERT_TEDDYGO

--創建測試用表IF EXISTS(SELECT [name] FROM sys.tables WHERE [name] = 'encryption')DROP TABLE encryptionCREATE TABLE dbo.encryption (PT nchar(10),;;;;--Plain TextET varbinary(128),; --Encrypted Text)GOGRANT SELECT, INSERT ON encryption TO ryanGRANT SELECT, INSERT ON encryption TO teddy

--完成準備工作，開始測試加密EXECUTE AS LOGIN = 'ryan'OPEN SYMMETRIC KEY Key_SYM_RYAN DECRYPTION BY CERTIFICATE CERT_RYANINSERT INTO encryption VALUES (N'RYAN',EncryptByKey(Key_GUID('Key_SYM_RYAN'), N'RYAN'))CLOSE ALL SYMMETRIC KEYSREVERT

EXECUTE AS LOGIN = 'teddy'OPEN SYMMETRIC KEY Key_SYM_TEDDY DECRYPTION BY CERTIFICATE CERT_TEDDYINSERT INTO encryption VALUES (N'TEDDY',EncryptByKey(Key_GUID('Key_SYM_TEDDY'), N'TEDDY'))CLOSE ALL SYMMETRIC KEYSREVERT

--測試數據已經被加密SELECT * FROM encryption

--解密數據EXECUTE AS LOGIN = 'ryan'OPEN SYMMETRIC KEY Key_SYM_RYAN DECRYPTION BY CERTIFICATE CERT_RYANSELECT PT, CONVERT(nchar,DecryptByKey(ET)) AS ET FROM encryptionCLOSE ALL SYMMETRIC KEYSREVERTEXECUTE AS LOGIN = 'teddy'OPEN SYMMETRIC KEY Key_SYM_TEDDY DECRYPTION BY CERTIFICATE CERT_TEDDYSELECT PT, CONVERT(nchar,DecryptByKey(ET)) AS ET FROM encryptionCLOSE ALL SYMMETRIC KEYSREVERT

sp; --而非數據庫主密鑰，可用以下語句測試證書的加密方法 --SELECT [name], pvt_key_encryption_type_desc FROM sys.certificates --WHERE [name] = 'CERT_DB'WITH SUBJECT = 'Certificate For Database',START_DATE = '01/01/2006',EXPIRY_DATE = '12/31/2015'GO

SELECT * FROM sys.certificates

--為ryan和teddy分別創建利用證書保護的對稱密碼CREATE SYMMETRIC KEY Key_SYM_RYAN AUTHORIZATION ryanWITH ALGORITHM = TRIPLE_DESENCRYPTION BY CERTIFICATE CERT_RYANGOCREATE SYMMETRIC KEY Key_SYM_TEDDY AUTHORIZATION teddyWITH ALGORITHM = TRIPLE_DESENCRYPTION BY CERTIFICATE CERT_TEDDYGO

--創建測試用表IF EXISTS(SELECT [name] FROM sys.tables WHERE [name] = 'encryption')DROP TABLE encryptionCREATE TABLE dbo.encryption (