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

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

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

先給利用證書簽署代碼的范例，這段代碼的好處是不用給Database User大的權(quán)限，就可以讓用戶修改部分?jǐn)?shù)據(jù)，這也是SQL Server 2005中權(quán)限粒度化的一種表現(xiàn)：

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

--創(chuàng)建實驗用數(shù)據(jù)庫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'

--創(chuàng)建用戶ryan，并創(chuàng)建數(shù)據(jù)庫主密鑰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

--創(chuàng)建證書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

--利用證書創(chuàng)建數(shù)據(jù)庫用戶，并授予該用戶管理數(shù)據(jù)庫用戶的權(quán)限CREATE USER USER_MAINTAIN FOR CERTIFICATE CERT_MAINTAINGRANT ALTER ANY USER TO USER_MAINTAIN

--創(chuàng)建存儲過程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

--完成準(zhǔn)備工作，開始測試加密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

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

緊接上一篇，再給一段用密鑰加密數(shù)據(jù)的范例，這段代碼比較簡單，大黃不準(zhǔn)像蹂躪大余一樣說我灌水！！！

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

--創(chuàng)建實驗用數(shù)據(jù)庫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

--創(chuàng)建用戶ryan，并創(chuàng)建數(shù)據(jù)庫主密鑰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--使用服務(wù)主密鑰加密數(shù)據(jù)庫主密鑰，--在此刪除，因為發(fā)現(xiàn)數(shù)據(jù)庫主密鑰創(chuàng)建時默認(rèn)及利用服務(wù)主密鑰加密--利用服務(wù)主密鑰加密的數(shù)據(jù)庫主密鑰稱為自動密鑰管理--可以利用以下查詢語句是否啟用數(shù)據(jù)庫主密鑰的自動密鑰管理SELECT [name], is_master_key_encrypted_by_server FROM sys.databases WHERE [name] = 'Sales'--以下語句用于啟用數(shù)據(jù)庫主密鑰的自動管理--ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEYGO

--為ryan創(chuàng)建證書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' --建議不要使用密碼，因為經(jīng)過測試，是用密碼的證書是利用密碼保護(hù)， --而非數(shù)據(jù)庫主密鑰，可用以下語句測試證書的加密方法 --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創(chuàng)建證書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' --建議不要使用密碼，因為經(jīng)過測試，是用密碼的證書是利用密碼保護(hù)， --而非數(shù)據(jù)庫主密鑰，可用以下語句測試證書的加密方法 --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分別創(chuàng)建利用證書保護(hù)的對稱密碼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

--創(chuàng)建測試用表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

--完成準(zhǔn)備工作，開始測試加密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

--測試數(shù)據(jù)已經(jīng)被加密SELECT * FROM encryption

--解密數(shù)據(jù)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; --而非數(shù)據(jù)庫主密鑰，可用以下語句測試證書的加密方法 --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分別創(chuàng)建利用證書保護(hù)的對稱密碼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

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