CREATE LOGIN [AughtEight\Bob] from Windows; GO 
CREATE LOGIN [AughtEight\G NorthWest Sales] from Windows; GO 
CREATE LOGIN Carol PASSWORD='Th1sI$|\/|yP@ssw0rd'; GO 
ALTER LOGIN Carol WITH PASSWORD='newpassword', CHECK_POLICY=OFF; GO 
DROP LOGIN [AughtEight\Bob]; GO 

--使用自己的服務(wù)器名稱替代AughtEight 
USE master CREATE CREDENTIAL StreetCred WITH IDENTITY='AughtEight\CarolStreet', SECRET='P@ssw0rd'; GO 
--把Carol的SQL Server登錄名和StrretCred平局相關(guān)聯(lián) 
ALTER LOGIN Carol WITH CREDENTIAL=StreetCred; GO; 

服務(wù)器角色

Microsoft SQL Server 2008定義了8個(gè)可用于簡(jiǎn)化SQL 登錄名管理和委托管理的服務(wù)器級(jí)別角色。這些角色通常被稱為固定服務(wù)器角色，因?yàn)閷?duì)于這些角色，唯一能更改的只是成員資格。固定服務(wù)器角色可以基于角色的用途，為一個(gè)登錄名自動(dòng)支配一組通用權(quán)限。要向固定服務(wù)器角色添加一個(gè)登錄名，可使用sp_addsrvrolemember存儲(chǔ)過程。

USE master CREATE LOGIN Ted WITH PASSWORD='P@ssw0rd'; GO 
EXEC sp_addsrvrolemember 'Ted','securityadmin'; GO 
USER master EXEC sp_dropsrvrolemember 'Ted','securityadmin'; GO 

數(shù)據(jù)庫(kù)用戶

數(shù)據(jù)庫(kù)用戶是Microsoft SQL Server 2008采用的安全模型的另一個(gè)組成部分。用戶可直接或通過一個(gè)或多個(gè)數(shù)據(jù)庫(kù)角色中的成員關(guān)系訪問安全的數(shù)據(jù)庫(kù)對(duì)象。用戶也可與表、視圖和存儲(chǔ)過程之類的對(duì)象的所有權(quán)相關(guān)聯(lián)。

在創(chuàng)建登錄名時(shí)，除非它是擁有所有數(shù)據(jù)庫(kù)管理權(quán)限的固定服務(wù)器角色的一個(gè)成員，否則該登錄名在附加到服務(wù)器的各數(shù)據(jù)庫(kù)中沒有顯示權(quán)限。此時(shí)，該登錄名和來賓數(shù)據(jù)庫(kù)用戶關(guān)聯(lián)在一起，并繼承該用戶帳戶的權(quán)限。在SQL Server Management Studio管理數(shù)據(jù)庫(kù)用戶時(shí)，有幾個(gè)選項(xiàng)可供選擇。在常規(guī)屬性頁上，可以為用戶指定一個(gè)名稱，并將該用戶和一個(gè)現(xiàn)有的登錄名關(guān)聯(lián)起來。注意，用戶名和登錄名并不一定要匹配，但是為了方便管理，最好的做法是使用一致的命名約定，但這并不是強(qiáng)制要求。在常規(guī)頁上可以配置的其他選項(xiàng)包括用戶的默認(rèn)架構(gòu)、該用戶所擁有的架構(gòu)以及該用戶所屬的數(shù)據(jù)庫(kù)角色。

USE tempdb; CREATE USER Carol; GO 
USE master; CREATE LOGIN [AughtEight\Bob] FROM WINDOWS; 
USE AdventureWorks2008; 
CREATE USER BillyBob FOR LOGIN [AughtEight\Bob] WITH DEFAULT_SCHEMA=sales;  
USER AdventureWorks2008; CREATE CERTIFICATE SalesCert     
ENCRYPTION BY PASSWORD='P@ssw0rd' 
        WITH SUBJECT='Sales Schema Certificate',         
EXPIRY_DATE='12/31/2010'; GO 
CREATE USER SalesSecurity FOR CERTIFICATE SalesCert;  
USE AdventureWorks2008 ALTER USER SalesSecurity WITH NAME=SalesSchemaSecurity; GO 
USE AdventureWorks2008 ALTER USER BillyBob WITH DEFAULT_SCHEMA=Production; GO 
USE master CREATE LOGIN TempCarol WITH PASSWORD='MyPassword', CHECK_POLICY=OFF; GO 
USE tempdb ALTER USER Carol WITH Login=TempCarol; GO 
USE AdventureWorks2008 DROP USER BillyBob; GO 

固定數(shù)據(jù)庫(kù)角色

每個(gè)SQL Server數(shù)據(jù)庫(kù)都有一系列固定數(shù)據(jù)庫(kù)角色，可用于在必要時(shí)把權(quán)限委托給用戶。和固定服務(wù)器角色一樣，對(duì)于固定數(shù)據(jù)庫(kù)角色來說，唯一可以改變的就是成員資格。用戶定義的數(shù)據(jù)庫(kù)角色對(duì)管理權(quán)限和對(duì)數(shù)據(jù)庫(kù)中的資源的訪問提供了更多的控制。在使用基于角色的安全模型時(shí)，可能京城發(fā)現(xiàn)內(nèi)置主體（比如Windows中的組或SQL中的角色）提供了過得uod訪問權(quán)限，或者沒有提供足夠的權(quán)限。在這種情況下，可以創(chuàng)建用戶定義的角色，控制整個(gè)一組用戶對(duì)安全對(duì)象的訪問。

USE AdventureWorks2008 CREATE ROLE SalesStaff AUTHORIZATION Carol; GO 
USE AdventureWorks2008 ALTER ROLE SalesStaff WITH NAME=SalesStaffRole; GO 
USE AdventureWorks2008 DROP ROLE SalesStaffRole; GO 
USE AdventureWorks2008 EXEC sp_addrolemember 'db_datareader','Carol'; GO 
USE AdventureWorks2008 EXEC sp_droprolemember 'db_datareader','Carol'; GO 

另外一種可以用來幫助保護(hù)數(shù)據(jù)庫(kù)環(huán)境的角色是應(yīng)用程序角色。應(yīng)用程序角色和標(biāo)準(zhǔn)的角色類型截然不同，他們沒有成員，可以而且應(yīng)該被設(shè)置為使用密碼進(jìn)行身份驗(yàn)證。當(dāng)運(yùn)行一個(gè)特定的應(yīng)用程序的所有用戶必須采用同樣的數(shù)據(jù)訪問時(shí)，通常使用應(yīng)用程序角色。應(yīng)用程序可以不需要提示用用提供用戶名和密碼就實(shí)例化應(yīng)用程序角色，從而避免了依賴于個(gè)人用戶是否擁有適當(dāng)?shù)脑L問權(quán)限讓應(yīng)用程序正常工作。

USE AdventureWorks2008 CREATE APPLICATION ROLE SalesApp WITH PASSWORD='P@www1rd',DEFAULT_SCHEMA=Sales; GO 
USE AdventueWorks2008 GO 
DECLARE @cookie varbinary(8000); 
EXEC sp_setapprole 'SalesApp','P@ssw0rd',@fCreateCookie=true,@cookie=@cookie OUTPUT; GO 
SELECT USER_NAME(); 

一旦執(zhí)行了上述腳本，該連接執(zhí)行的所有活動(dòng)都將在該應(yīng)用程序角色下操作。當(dāng)關(guān)閉連接時(shí)，應(yīng)用程序角色會(huì)話也終止。通過ALTER APPLICATION ROLE語句，可以更改應(yīng)用程序角色的名稱、密碼和默認(rèn)架構(gòu)。

USE AdventureWorks2008 ALTER APPLICATION ROLE SalesApp WITH NAME=OrderEntry,PASSWORD='newP@ss0rd'; GO 
USE AdventureWorks2008 DROP APPLICATION ROLE OrderEntry; GO 

權(quán)限

在對(duì)象或資源上定義權(quán)限的語句會(huì)定義一個(gè)權(quán)限狀態(tài)、一個(gè)操作、獎(jiǎng)項(xiàng)哪個(gè)對(duì)象應(yīng)用權(quán)限和操作，以及權(quán)限和動(dòng)作將應(yīng)用于哪個(gè)安全主體。首先，需要知道基本上有三種權(quán)限狀態(tài)：GRANT,GRANT_W_GRANT和DENY。用于控制權(quán)限狀態(tài)的三個(gè)命令為GRANT,REVOKE和DENY。

SQL Server 2008使用一個(gè)分層的安全模型，允許指定可在服務(wù)器、數(shù)據(jù)庫(kù)、架構(gòu)或者對(duì)象級(jí)別授予的權(quán)限。也可以在表和視圖內(nèi)為選定列指派權(quán)限。在保護(hù)數(shù)據(jù)庫(kù)服務(wù)器時(shí)，應(yīng)使用兩個(gè)關(guān)鍵策略：在授權(quán)時(shí)應(yīng)當(dāng)采用的第一個(gè)策略為”最小特權(quán)原則“，這一策略規(guī)定只向用戶提供適當(dāng)?shù)臋?quán)限進(jìn)行操作。通過對(duì)數(shù)據(jù)庫(kù)環(huán)境做出這樣嚴(yán)格的限制，可以提供一個(gè)能最小化服務(wù)器的受攻擊面，同時(shí)又維護(hù)運(yùn)行功能的解決方案。第二個(gè)策略是深度防御。好的安全實(shí)現(xiàn)方案應(yīng)在數(shù)據(jù)庫(kù)的所有層上提供安全性。這可能包括針對(duì)客戶機(jī)和服務(wù)器之間的通信采用IPSec或SSL，在身份驗(yàn)證服務(wù)器上使用強(qiáng)密碼加密以及在表或視圖中配置列級(jí)權(quán)限。

服務(wù)器權(quán)限

可以通過制定權(quán)限以及將被指派權(quán)限的登錄名來管理服務(wù)器控制權(quán)限，如：

--將創(chuàng)建數(shù)據(jù)庫(kù)的權(quán)限授予登錄名Ted 
USE master GRANT CREATE ANY DATABASE TO Ted; GO 
--Ted有權(quán)更改該登錄名及允許其他人更改登錄名 
USE master GRANT ALTER ANY LOGIN TO Ted WITH GRANT OPTION; GO 
--刪除Ted的更改登錄名的能力 
USE master REVOKE ALTER ANY LOGIN TO Ted CASCADE; GO 
--禁止Ted創(chuàng)建新數(shù)據(jù)庫(kù) 
USE master DENY CREATE ANY DATABASE TO Ted; GO 

但是，對(duì)于一個(gè)登錄名或用戶時(shí)否能夠執(zhí)行某一特定的操作而言，DENY和EVOKE并不總是終極答案，如果某登錄名是sysadmin固定服務(wù)器角色的一個(gè)成員，該登錄名就可以完全控制SQL Server及其資源，而且組織此登錄名訪問服務(wù)器上的對(duì)象沒有太大意義。

端點(diǎn)是服務(wù)器級(jí)別的對(duì)象，在授予、撤銷和拒絕時(shí)，它使用的語法和服務(wù)器權(quán)限有一些不同。下面的例子創(chuàng)建了一個(gè)名為ServiceBroker的端點(diǎn)，它將被用于一個(gè)Service Broker應(yīng)用程序，然后將該端點(diǎn)的ALTER權(quán)限授予Ted：

CREATE ENDPOINT ServiceBroker STATE=STARTED AS TCP (LISTENER_PORT=5162) FOR SERVICE_BROKER (AUTHENTICATION=WINDOWS); GO 
USER master GRANT ALTER ON ENDPOINT :: ServiceBroker TO Ted; GO 
USE master GRANT ALTER ON LOGIN::Ted TO Carol WITH GRANT OPTION; GO 

數(shù)據(jù)庫(kù)的作用域權(quán)限

USE AdventureWorks2008 CREATE USER Alice FOR LOGIN [AughtEight\Alice] 
WITH DEFAULT_SCHEMA=SALES; GO 
GRANT CONTROL ON SCHEMA::Sales TO Alice 

架構(gòu)作用域權(quán)限

Use AdventureWorks2008 GRANT SELECT, UPDATE ON Person.Person to Alice GO 

SQL Server加密

關(guān)于SQL Server 2008使用的加密層級(jí)結(jié)構(gòu)，頂層是Windows層，其中包括Windows Data Protection API (DPAPI)。DPAPI負(fù)責(zé)使用本地機(jī)器密鑰加密服務(wù)的服務(wù)主密鑰。服務(wù)主密鑰是SQL環(huán)境中加密鏈的頂層。首次創(chuàng)建低級(jí)別的密鑰時(shí)，系統(tǒng)會(huì)自動(dòng)生成服務(wù)主密鑰。

服務(wù)主密鑰的下面是數(shù)據(jù)庫(kù)主密鑰Database Master Key。數(shù)據(jù)庫(kù)主密鑰可以保護(hù)數(shù)據(jù)庫(kù)中所有證書的私鑰和非對(duì)稱密鑰的私鑰。它是采用3DES加密算法和密碼加密的對(duì)稱密鑰。該密鑰的副本使用服務(wù)主密鑰加密，并存儲(chǔ)在主數(shù)據(jù)庫(kù)和應(yīng)用它的數(shù)據(jù)庫(kù)中。如果數(shù)據(jù)庫(kù)被轉(zhuǎn)移到另一個(gè)服務(wù)器上，可以使用OPEN MASTER KEY語句并提供加密密碼來解密數(shù)據(jù)庫(kù)主密鑰。

在數(shù)據(jù)庫(kù)作用域中，還有可用于加密數(shù)據(jù)庫(kù)的對(duì)稱密鑰和非對(duì)稱密鑰，以及可以用于數(shù)字簽名和實(shí)現(xiàn)不可否認(rèn)性的證書。

應(yīng)該首先創(chuàng)建數(shù)據(jù)庫(kù)主密鑰，記住數(shù)據(jù)庫(kù)主密鑰是對(duì)稱密鑰，用來加密數(shù)據(jù)庫(kù)中的所有私有密鑰數(shù)據(jù)。如果使用非對(duì)稱密鑰或證書，這是非常有用的，因?yàn)閯?chuàng)建它們時(shí)不需要提供密碼或其它保護(hù)與雙方關(guān)聯(lián)的私鑰的機(jī)制。要為AdventureWorks2008數(shù)據(jù)庫(kù)創(chuàng)建一個(gè)新的主密鑰，可以執(zhí)行下面的命令：

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

　　創(chuàng)建主密鑰需要數(shù)據(jù)庫(kù)的CONTROL權(quán)限，如果已經(jīng)創(chuàng)建了一個(gè)主密鑰，那么要想創(chuàng)建新的主密鑰，必須刪除現(xiàn)有的主密鑰。如果現(xiàn)有的主密鑰已被用來加密數(shù)據(jù)庫(kù)中的一個(gè)私鑰，那么就不能刪除它了。一旦創(chuàng)建了主密鑰，就可以查詢sys.database目錄視圖，通過is_master_key_encrypted_by_server列查看該數(shù)據(jù)庫(kù)主密鑰是否已經(jīng)使用服務(wù)主密鑰加密。該列使用一個(gè)布爾值表明數(shù)據(jù)庫(kù)主密鑰是否使用服務(wù)主密鑰加密。如果數(shù)據(jù)庫(kù)主密鑰是在另一個(gè)服務(wù)器上創(chuàng)建的，該值就為0

SELECT NAME, [is_master_key_encrypted_by_server] FROM sys.databases 

　　在繼續(xù)討論使用其他密鑰之前，讓我們看一下如何備份服務(wù)主密鑰和數(shù)據(jù)庫(kù)主密鑰。如果必須進(jìn)行災(zāi)難恢復(fù)，且需要恢復(fù)使用這些密鑰之一加密的數(shù)據(jù)，那么這將是相當(dāng)有用的。梁宗密鑰的語法是相似的，但需要額外的一部來備份加密的數(shù)據(jù)庫(kù)主密鑰。

首先看服務(wù)主密鑰，在BACKUP SERVICE MASTER KEY語句中使用一個(gè)文件路徑，可以是一個(gè)本地或UNC路徑，以及一個(gè)滿足密碼復(fù)雜度要求的密碼，備份文件時(shí)使用密碼可以防止他人將您的主密鑰還原到另一臺(tái)服務(wù)器上，然后解密您的數(shù)據(jù)庫(kù)主密鑰

BACKUP SERVICE MASTER KEY TO FILE='C:\KyeBackups\ServiceMasterKey' ENCRYPTION BY PASSWORD='c@MplexP@ssw0rd';  --如果需要還原該服務(wù)主密鑰，可使用 
RESTORE SERVICE MASTER KEY FROM FILE='C:\KyeBackups\ServiceMasterKey' DECRYPTION BY PASSWORD='c@MplexP@ss0rd'; 

備份和還原數(shù)據(jù)庫(kù)主密鑰的方法如下：

--Backup the database master key 
USE AdventureWorks2008; OPEN MASTER KEY DECRYPTION BY PASSWORD='P@ssw0rd'  
BACKUP MASTER KEY TO FILE='C:\KyeBackups\AWorksMasterKey' ENCRYPTION BY PASSWORD='dn9e8h93ndwjKJD'; GO 
 
--Restore the database master key 
USE AdventureWorks2008; RESTORE MASTER KEY FROM FILE='C:\KyeBackups\ServiceMasterKey' 
DECRYPTION BY PASSWORD='dn9e8h93ndwjKJD' ENCRYPTION BY PASSWORD='P@ss0rd' 
GO 

可擴(kuò)展的密鑰管理

SQL Server 2008中一項(xiàng)最重要的新功能是可擴(kuò)展的密鑰管理EKM,Extensible Key Management，它使用Microsoft Cryptographic API,MSCAPI在SQL Server 2008環(huán)境的外部生成和存儲(chǔ)用于數(shù)據(jù)和密鑰加密的加密密鑰。這通常通過使用HSM,Harware Security Model，硬件安全模塊實(shí)現(xiàn)，HSM供應(yīng)商可創(chuàng)建一個(gè)與MSCAPI連接的提供程序，提供一部分HSM功能給SQL Server 2008和其他利用MSCAPI的應(yīng)用程序，遺憾的是，由于MSCAPI用作HSM與SQL Server之間的中間層，它無法將HSM的所有功能提供給SQL Server。

為了使用EKM，必須首先在服務(wù)器上啟用它。它默認(rèn)是關(guān)閉的，但可通過sp_configure命令打開。由于啟用EKM是高級(jí)功能，因此必須制定shwo advanced配置，下面的實(shí)例展示了如何為服務(wù)器打開EKM：

sp_configure 'show advanced',1; GO 
RECONFIGURE 
GO sp_configure 'EKM provider enabled',1; GO 
RECONFIGURE 
GO 

　　在啟用了EKM后，現(xiàn)在可以在HSM模塊、智能卡或USB設(shè)備上存儲(chǔ)加密密鑰。不管何時(shí)使用存儲(chǔ)在這些設(shè)備上的密鑰加密數(shù)據(jù)，要解密數(shù)據(jù)，必須連接上這些設(shè)備。這顆防止未授權(quán)的用戶將數(shù)據(jù)庫(kù)文件復(fù)制并放至欺騙性服務(wù)器，從而訪問所有秘密數(shù)據(jù)。

對(duì)稱密鑰

如前所述，對(duì)稱密鑰提供了一個(gè)用于加密大量數(shù)據(jù)的有效模型。使用同樣的密鑰來加密和解密可以把資源開銷降低到最低，生成對(duì)稱密鑰的語法：

CREATE SYMMETRIC KEY name [AUTHORIZATION owner] [FROM PROVIDER] providername WITH options ENCRYPTION BY mechanism 

　　下面的例子創(chuàng)建了一個(gè)名為SalesKey1的新的對(duì)稱密鑰，該密鑰使用的是192為的3DES（3KEY）算法：

USE AdventureWorks2008 GO 
--Create Symmetric Key 
CREATE SYMMETRIC KEY SalesKey1     
WITH ALGORITHM=TRIPLE_DES_3KEY,     
KEY_SOURCE='The quick brown fox jumped over the lazy dog',     
IDENTITY_VALUE='FoxAndHound'     
ENCRYPTION BY PASSWORD='9348hsxasnA@B'; GO 

可以使用ALTER SYMMETRIC KEY語句添加或刪除用于加密密鑰的方法，也可以使用DROP SYMMETRIC KEY語句刪除對(duì)稱密鑰。在這個(gè)例子里，使用之前在數(shù)據(jù)庫(kù)用戶中創(chuàng)建的SalesCert證書來加密對(duì)稱密鑰，然后刪除前例中的密碼加密

--Open the symmetric key 
OPEN SYMMETRIC KEY SalesKey1 DECRYPTION BY PASSWORD='9348hsxasnA@B' 
--Add encryption using the certificate created earlier 
ALTER SYMMETRIC KEY SalesKey1 ADD ENCRYPTION BY CERTIFICATE SalesCert --Remove the password encryption 
LATER SYMMETRIC KEY SalesKey1 DROP ENCRYPTION BY PASSWORD='9348hsxasnA@B' 
--Close the symmetric key 
CLOSE SYMMETRIC KEY SalesKey1 

非對(duì)稱密鑰

CREATE ASYMMETRIC KEY name [AUTHORIZATION owner] [FROM key_source] WITH ALGORITHM=algrithm [ENCRYPTION BY PASSWORD='password'] 

　　在創(chuàng)建非對(duì)稱密鑰時(shí)，可以指定密鑰對(duì)的所有者和密鑰源（可以是一個(gè)強(qiáng)名稱文件、一個(gè)程序集或一個(gè)可執(zhí)行的程序集文件）另外，也可以使用一個(gè)決定私鑰使用的位數(shù)的算法，選擇密鑰成俗是512，1024還是2048位，還可以使用ENCRYPTION BY PASSWORD選項(xiàng)來加密私鑰。如果沒有指定密碼，數(shù)據(jù)庫(kù)主密鑰將加密私鑰

USE AdventureWorks2008 CREATE ASYMMETRIC KEY HumanResources WITH ALGORITHM=RSA_2048; GO 

　　可以使用ALTER ASYMMETRIC KEY語句更改密鑰對(duì)的屬性，可以使用REMOVE PRIVATE KEY選項(xiàng)將私鑰從數(shù)據(jù)庫(kù)中刪除（確保已經(jīng)先備份過私鑰），或者可以更改保護(hù)私鑰的方式。例如，可以更改用來加密私鑰的密碼，然后把保護(hù)方式從密碼改為數(shù)據(jù)庫(kù)主密鑰，反之亦然。下面的代碼，通過密碼加密在前面的例子中創(chuàng)建的HumanResouces密鑰對(duì)中的私鑰：

USE AdventureWorks2008 ALTER ASYMMETRIC KEY HumanResources WITH PRIVATE KEY (ENCRYPTION BY PASSWORD='P@ssw0rd') 

　　下面的例子中，通過先解密，然后用一個(gè)新的密碼重新加密的方式更改用于加密私鑰的密碼

USE AdventureWorks2008 ALTER ASYMMETRIC KEY HumanResources 
WITH PRIVATE KEY(DECRYPTION BY PASSWORD='P@ssw0rd',ENCRYPTION BY PASSWORD='48UFDSJehf@*hda'); GO 

證書

在加密方面，公鑰證書和非對(duì)稱密鑰的操作方式一樣，不過，密鑰對(duì)被綁定到證書。公鑰包含在省屬詳細(xì)信息中，而私鑰必須被安全地歸檔。與證書關(guān)聯(lián)的私鑰必須用密碼、數(shù)據(jù)庫(kù)主密鑰或另外一個(gè)加密密鑰保護(hù)。在加密數(shù)據(jù)時(shí)，最好的做法是使用一個(gè)對(duì)稱密鑰加密數(shù)據(jù)，然后使用公鑰加密該對(duì)稱密鑰。

當(dāng)創(chuàng)建一個(gè)自簽名證書時(shí)，可以使用CREATE CERTIFICATE語句?？梢赃x擇一個(gè)強(qiáng)密碼或者數(shù)據(jù)庫(kù)主密鑰加密私鑰。或者也可以使用CREATE CERTIFICATE語句從一個(gè)文件導(dǎo)入證書或私鑰。另外，可以基于已簽名的程序集創(chuàng)建證書。

在證書創(chuàng)建完成之后，可以使用ALTER CERTIFICATE語句修改證書?？梢詧?zhí)行的更改包括改變保護(hù)四方的方式或從SQL Server中刪除私鑰。只有證書被用于驗(yàn)證數(shù)字簽名時(shí)才需要杉樹私鑰。如果公鑰被用來加密數(shù)據(jù)或?qū)ΨQ密鑰，那私鑰應(yīng)當(dāng)可以解密。在創(chuàng)建證書時(shí)最好使用BACKUP CERTIFICATE語句備份證書和關(guān)聯(lián)的私鑰。您可以只備份證書而不歸檔私鑰，使用公鑰來驗(yàn)證或加密只能用私鑰解密的信息。

一旦證書不需要，可以使用DROP CERTIFICATE語句刪除。如果證書仍然和其他對(duì)象關(guān)聯(lián)，則無法刪除。

--Create the Personnel Data Certificate 
USE AdventureWorks2008; CREATE CERTIFICATE PersonnelDataCert ENCRYPTION BY PASSWORD='HRcertific@te' 
WITH SUBJECT='Personnel Data Encryption Certificate' EXPIRY_DATE='12/31/2011'; GO 
 
--Backup the certificate and private key to the file system 
Use AdventureWorks2008 BACKUP CERTIFICATE PersonnelDataCert TO FILE='c:\certs\Personnel.cert'  
WITH PRIVATE KEY (DECRYPTION BY PASSWORD='HRcertific@te', FILE='c:\certs\Personnelkey.pvk', ENCRYPTION BY PASSWORD='@notherPassword'); GO 
 
--Import the certificate and private key into the TempDB database 
USE tempdb CREATE CERTIFICATE PersonnelDataCert FROM FILE='c"\certs\Personnel.cer' 
WITH PRIVATE KEY (FILE='c:\certs\Personnelkey.pvk', DECRYPTION BY PASSWORD='@notherPassword', ENCRYPTION BY PASSWORD='TempDBKey1'); GO 
 
 
--更改用于加密私鑰的密碼 
USE tempdb ALTER CERTIFICATE PersonnelDataCert 
WITH PRIVATE KEY (ENCRYPTION BY PASSWORD='P@ssw0rd789', DECRYPTION BY PASSWORD='TempDBKey1')  
USE AdventureWorks2008 ALTER CERTIFICATE PersonnelDataCert REMOVE PRIVATE KEY 
GO 
 
USE tempdb DROP CERTIFICATE PersonnelDataCert; GO 

加密數(shù)據(jù)

并不是每一種數(shù)據(jù)類型都可以使用EncryptByKey函數(shù)加密。有效的數(shù)據(jù)類型是nvarchar、char、wchar、varchar和nchar。表或視圖中常備查詢的列不應(yīng)加密，因?yàn)榻饷艽罅繒?huì)被一再查詢的數(shù)據(jù)的過程通常會(huì)得不償失。加密數(shù)據(jù)之前，必須打開將執(zhí)行加密過程的密鑰。數(shù)據(jù)通常手對(duì)稱密鑰保護(hù)，而對(duì)稱密鑰又受到非對(duì)稱密鑰對(duì)保護(hù)。如果對(duì)稱密鑰手密碼保護(hù)，那么對(duì)對(duì)稱密鑰和密碼有ALTER 權(quán)限的用戶都可以打開和關(guān)閉對(duì)稱密鑰。如果對(duì)稱密鑰由一個(gè)非對(duì)稱密鑰或證書保護(hù)，用戶還需要擁有對(duì)非對(duì)稱密鑰或證書上的CONTROL權(quán)限

ALTER TABLE Sales.CreditCard ADD EncryptedCardNumber varbinary(128); GO 
 
OPEN SYMMETRIC KEY SalesKey1 DECRYPTION BY CERTIFICATE SalesCert WITH PASSWORD='P@ssw0rd' 
 
UPDATE Sales.CreditCard SET EncryptedCardNumber=EncryptByKey(Key_GUID('SalesKey1'),CardNumber); GO  CLSE SYMMETRIC KEY SalesKey1; GO 
 
ALTER TABLE Sales.CreditCard ADD DecryptedCardNumber NVARCHAR(25); GO 
 
OPEN SYMMETRIC KEY SalesKey1 DECRYPTION BY CERTIFICATE SalesCert WITH PASSWORD='P@ssw0rd'; GO 
 
UPDATE Sales.CreditCard SET DecryptedCardNumber=DecryptByKey(EncryptedCardNumber); GO 
 
CLOSE SYMMETRIC KEY SalesKey1; GO 
 
Select TOP(10) CreditCardID, CardNumber AS Original, EncryptedCardNumber AS Encrypted, DecryptedCardnumber AS Decrypted FROM Sales.CreditCard; GO 

不過，可以在SELECT語句中至此那個(gè)DecryptByKey函數(shù)來查看為加密的數(shù)據(jù)

OPEN SYMMETRIC KEY SalesKey1 DECRYPTION BY CERTIFICATE SalesCert WITH PASSWORD='P@ssw0rd'; GO 
 
SELECT CreditCardID, CardNumber, EncryptedCardNumber AS 'Encrypted Card Number', CONVERT(nvarchar, DecryptByKey(EncryptedCardNumber)) 
AS 'Decrypted Card Number' FROM Sales.CreditCard; GO 
 
CLOSE SYMMETRIC KEY SalesKey1; 

透明數(shù)據(jù)加密

SQL Server 2008的另一項(xiàng)新工能是透明數(shù)據(jù)加密（TDE,Transparent Data Encryption）。TDE被設(shè)計(jì)為針對(duì)啟用了TDE的數(shù)據(jù)庫(kù)或事務(wù)日志文件，使用數(shù)據(jù)庫(kù)加密密鑰（DEK,Database Encryption Key）執(zhí)行實(shí)時(shí)IO加密。TDE的好處是它保護(hù)處于休眠狀態(tài)的所有數(shù)據(jù)。這意味著當(dāng)前未讀入內(nèi)存的數(shù)據(jù)都是用DEK保護(hù)。不過，當(dāng)查詢運(yùn)行時(shí)，從查詢檢索的數(shù)據(jù)將在被讀入內(nèi)存時(shí)解密。與使用對(duì)稱和非對(duì)稱密鑰解密單個(gè)表或列中的數(shù)據(jù)不同，在讀或?qū)懯躎DE保護(hù)的數(shù)據(jù)庫(kù)中的表時(shí)，不必調(diào)用解密函數(shù)。

設(shè)置TDE比其他加密方法要復(fù)雜些，因?yàn)樵趩⒂盟坝幸恍l件必須滿足：首先，master數(shù)據(jù)庫(kù)中必須有一個(gè)數(shù)據(jù)庫(kù)主密鑰；其次，必須在master數(shù)據(jù)庫(kù)中創(chuàng)建或安裝一個(gè)可用于加密DEK的證書，或者可以使用EKM提供程序的非對(duì)稱密鑰；然后，需要在將加密的數(shù)據(jù)庫(kù)中創(chuàng)建DEK，最后，在數(shù)據(jù)庫(kù)中啟用加密。

USE master CREATE MASTER KEY ENCRYPTION BY PASSWORD='MyStrongP@ssw0rd'; GO 
CREATE CERTIFICATE AughtEightTDE WITH SUBJECT='TDE Certificate for the AUGHTEIGHT Server'; GO 
USE AdventureWorks2008 CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM=TRIPLE_DES_3KEY ENCRYPTION BY SERVER CERTIFICATE AughtEightTDE; GO 
ALTER DATABASE AdventureWorks2008 SET ENCRYPTION ON; GO 

數(shù)字簽名

數(shù)字簽名提供身份驗(yàn)證和不可否認(rèn)性。同城，公鑰私鑰對(duì)用于對(duì)消息進(jìn)行數(shù)字簽名。下面是數(shù)字簽名如何和電子郵件消息一起工作的例子。

Bob給Alice發(fā)送了一條信息，而他的郵件客戶端被配置為自動(dòng)為所有發(fā)出的消息添加他的數(shù)字簽名。在這種情況下，當(dāng)消息準(zhǔn)備好發(fā)送時(shí)，系統(tǒng)會(huì)生成一個(gè)密鑰，然后傳遞給一個(gè)哈希算法，將數(shù)據(jù)單向轉(zhuǎn)換為一個(gè)哈希值。哈希值附加在消息上，而用于生成哈希值的密鑰由Bob的私鑰加密。該消息發(fā)送給了Alice，她接受明文形式的消息，以及該消息的哈希值版本。Alice具有訪問Bob的公鑰的權(quán)限，使用該公鑰解密用來生成哈希值的密鑰。于是該密鑰被傳遞給哈希算法，生成一個(gè)新的哈希。如果新的哈希與原來的隨消息一起發(fā)送的哈希匹配，Alice可以確信該消息在發(fā)送過程中沒有被更改。如果哈希值不匹配，那么說明該消息在發(fā)送之后已經(jīng)被更改，不應(yīng)被信任。

下面的代碼創(chuàng)建了一個(gè)名為Sales.DisplaySomeVendors的簡(jiǎn)單存儲(chǔ)過程。然后可以使用前面的SalesCert證書給該存儲(chǔ)過程添加一個(gè)簽名。需要解密該西藥來對(duì)該存儲(chǔ)過程進(jìn)行數(shù)字簽名：

CREATE PROCEDURE Sales.DisplaySomeVendors AS SELECT TOP (20) * FROM Purchasing.Vendor; GO 
USE AdventureWorks2008; ADD SIGNATURE TO Sales.DisplaySomeVendors BY CERTIFICATE SalesCert WITH PASSWORD='P@ssw0rd'; GO 

最佳實(shí)踐

1. 點(diǎn)評(píng)：巍然聳立的SQL Server 2012
2. 全面掌握SQL Server Profiler系列1:原理與相關(guān)概念
3. 微軟SQL Server 2012助速8酒店洞察擴(kuò)張先機(jī)
4. SQL Server 2008 R2故障轉(zhuǎn)移群集環(huán)境準(zhǔn)備
5. SQL Server: 局部變量是如何影響查詢性能的

【責(zé)任編輯：彭凡 TEL：（010）68476606】