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個可用于簡化SQL 登錄名管理和委托管理的服務(wù)器級別角色。這些角色通常被稱為固定服務(wù)器角色，因為對于這些角色，唯一能更改的只是成員資格。固定服務(wù)器角色可以基于角色的用途，為一個登錄名自動支配一組通用權(quán)限。要向固定服務(wù)器角色添加一個登錄名，可使用sp_addsrvrolemember存儲過程。

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ù)庫用戶

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

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

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ù)庫角色

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

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 

另外一種可以用來幫助保護數(shù)據(jù)庫環(huán)境的角色是應(yīng)用程序角色。應(yīng)用程序角色和標(biāo)準(zhǔn)的角色類型截然不同，他們沒有成員，可以而且應(yīng)該被設(shè)置為使用密碼進行身份驗證。當(dāng)運行一個特定的應(yīng)用程序的所有用戶必須采用同樣的數(shù)據(jù)訪問時，通常使用應(yīng)用程序角色。應(yīng)用程序可以不需要提示用用提供用戶名和密碼就實例化應(yīng)用程序角色，從而避免了依賴于個人用戶是否擁有適當(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í)行的所有活動都將在該應(yīng)用程序角色下操作。當(dāng)關(guān)閉連接時，應(yīng)用程序角色會話也終止。通過ALTER APPLICATION ROLE語句，可以更改應(yīng)用程序角色的名稱、密碼和默認架構(gòu)。

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

權(quán)限

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

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

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

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

--將創(chuàng)建數(shù)據(jù)庫的權(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ù)庫 
USE master DENY CREATE ANY DATABASE TO Ted; GO 

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

端點是服務(wù)器級別的對象，在授予、撤銷和拒絕時，它使用的語法和服務(wù)器權(quán)限有一些不同。下面的例子創(chuàng)建了一個名為ServiceBroker的端點，它將被用于一個Service Broker應(yīng)用程序，然后將該端點的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ù)庫的作用域權(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使用的加密層級結(jié)構(gòu)，頂層是Windows層，其中包括Windows Data Protection API (DPAPI)。DPAPI負責(zé)使用本地機器密鑰加密服務(wù)的服務(wù)主密鑰。服務(wù)主密鑰是SQL環(huán)境中加密鏈的頂層。首次創(chuàng)建低級別的密鑰時，系統(tǒng)會自動生成服務(wù)主密鑰。

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

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

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

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

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

SELECT NAME, [is_master_key_encrypted_by_server] FROM sys.databases 

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

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

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ù)庫主密鑰的方法如下：

--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 

可擴展的密鑰管理

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

為了使用EKM，必須首先在服務(wù)器上啟用它。它默認是關(guān)閉的，但可通過sp_configure命令打開。由于啟用EKM是高級功能，因此必須制定shwo advanced配置，下面的實例展示了如何為服務(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è)備上存儲加密密鑰。不管何時使用存儲在這些設(shè)備上的密鑰加密數(shù)據(jù)，要解密數(shù)據(jù)，必須連接上這些設(shè)備。這顆防止未授權(quán)的用戶將數(shù)據(jù)庫文件復(fù)制并放至欺騙性服務(wù)器，從而訪問所有秘密數(shù)據(jù)。

對稱密鑰

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

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

　　下面的例子創(chuàng)建了一個名為SalesKey1的新的對稱密鑰，該密鑰使用的是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語句刪除對稱密鑰。在這個例子里，使用之前在數(shù)據(jù)庫用戶中創(chuàng)建的SalesCert證書來加密對稱密鑰，然后刪除前例中的密碼加密

--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 

非對稱密鑰

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

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

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

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

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

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

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

證書

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

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

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

一旦證書不需要，可以使用DROP CERTIFICATE語句刪除。如果證書仍然和其他對象關(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)加密，因為解密大量會被一再查詢的數(shù)據(jù)的過程通常會得不償失。加密數(shù)據(jù)之前，必須打開將執(zhí)行加密過程的密鑰。數(shù)據(jù)通常手對稱密鑰保護，而對稱密鑰又受到非對稱密鑰對保護。如果對稱密鑰手密碼保護，那么對對稱密鑰和密碼有ALTER 權(quán)限的用戶都可以打開和關(guān)閉對稱密鑰。如果對稱密鑰由一個非對稱密鑰或證書保護，用戶還需要擁有對非對稱密鑰或證書上的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語句中至此那個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的另一項新工能是透明數(shù)據(jù)加密（TDE,Transparent Data Encryption）。TDE被設(shè)計為針對啟用了TDE的數(shù)據(jù)庫或事務(wù)日志文件，使用數(shù)據(jù)庫加密密鑰（DEK,Database Encryption Key）執(zhí)行實時IO加密。TDE的好處是它保護處于休眠狀態(tài)的所有數(shù)據(jù)。這意味著當(dāng)前未讀入內(nèi)存的數(shù)據(jù)都是用DEK保護。不過，當(dāng)查詢運行時，從查詢檢索的數(shù)據(jù)將在被讀入內(nèi)存時解密。與使用對稱和非對稱密鑰解密單個表或列中的數(shù)據(jù)不同，在讀或?qū)懯躎DE保護的數(shù)據(jù)庫中的表時，不必調(diào)用解密函數(shù)。

設(shè)置TDE比其他加密方法要復(fù)雜些，因為在啟用它之前有一些條件必須滿足：首先，master數(shù)據(jù)庫中必須有一個數(shù)據(jù)庫主密鑰；其次，必須在master數(shù)據(jù)庫中創(chuàng)建或安裝一個可用于加密DEK的證書，或者可以使用EKM提供程序的非對稱密鑰；然后，需要在將加密的數(shù)據(jù)庫中創(chuàng)建DEK，最后，在數(shù)據(jù)庫中啟用加密。

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ù)字簽名提供身份驗證和不可否認性。同城，公鑰私鑰對用于對消息進行數(shù)字簽名。下面是數(shù)字簽名如何和電子郵件消息一起工作的例子。

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

下面的代碼創(chuàng)建了一個名為Sales.DisplaySomeVendors的簡單存儲過程。然后可以使用前面的SalesCert證書給該存儲過程添加一個簽名。需要解密該西藥來對該存儲過程進行數(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 

最佳實踐

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

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