在保密你的伺服器和資料，防備目前複雜的攻擊，SQL Server有你需要的一切。但在你能有效使用這些安全功能前，你需要了解你面對的威脅和一些基本的安全概念。這篇文章提供了基礎，是以你可以對SQL Server裡的安全功能充分利用，不用在面對特定威脅，不能保護你資料的功能上浪費時間。
通常來說，你通過在對象上配置設定許可到主體來實作SQL Server裡的使用者和對象安全。但什麼是SQL Server主體？它上面獲得哪些許可？在這篇文章裡，你會學到各種主體，可以通過許可授權進行SQL Server執行個體裡進行操作和通路的安全對象。SQL Server裡重要的主體是角色，你會學到相比使用使用者這類主體，角色如何讓安全管理更加容易。在這篇文章裡你還會學到SQL Server裡的安全對象，為學習許可打下基礎。
授權（Authorization）
在第2篇裡談到的授權，是通路資料庫伺服器裡所有好東西的一部分。驗證就像有護照證明你是誰但是沒有簽證——你需要簽證來進入和逗留國家。在這篇文章裡你會學到授權，它如何扮演簽證提供對資料庫對象通路。
主體（Principal）是可以通路SQL Server或它的資料庫裡的一個或多個安全對象的使用者或線程。安全對象（Securables）是個保護的資源，是一些隻能特定人或線程才可以通路或修改的，例如表裡的資料。許可（permission）讓主體獲得特定安全主體的通路。
繼續和護照類比，主體是護照持有者，裡面有所有人的照片。安全對象是主體想要通路的國家，許可是穿越國家邊界并享受通路的簽證。
主體（Principals）
主體，在安全上下文裡，是任何使用者（人類），使用者組（在SQL Server裡稱為角色），或程序裡運作的代碼，它們可以清酒對安全對象的通路且被授予或禁止通路。所有的Windows和SQL Server登入都是主體，和映射到資料庫裡的使用者一樣。下面清單展示了SQL Server裡較重要主體的大多數層次，從SQL Server執行個體權限生成的伺服器級别主體，到資料庫級别的主體：
Windows級别主體：
- Windows域登入
- Windows組
- Windows本地登入
SQL Server級别主體：
- SQL Server登入
- SQL Server映射到證書登入
- SQL Server映射到Windows登入的登入
- SQL Server映射到非對稱匙的登入
資料庫級别主體:
- 應用程式角色
- 資料庫角色
- 資料庫使用者
- 資料庫使用者映射到證書
- 資料庫使用者映射到Windows登入
- 資料庫使用者映射到非對稱匙
- 公共角色
了解這個層次非常重要，因為主體的範圍基本上決定了可以授予它的許可範圍。例如，資料庫使用者隻能在資料庫上下文裡授予許可。資料庫級别的主體可以有伺服器的許可，Windows級别的主體可以有在SQL Server範圍外，Windows本地示例裡和網絡上的許可，
注意剛才的清單裡，一個主體除了其它方面之外，既是角色也是登入（或使用者）。SQL Server裡的角色類似于Windows組。在角色裡擁有成員資格的使用者繼承配置設定到角色的許可。角色讓安全管理更加簡單，因為你不需要為各個使用者管理複雜的一系列許可。SQL Server支援下列角色：
- 固定伺服器角色（Fixed server roles）：進行伺服器級别任務的SQL Server内建角色。
- 使用者自定義伺服器角色（User-defined server roles）：你建立的，配置設定伺服器級别許可，配置設定登入的自定義伺服器角色，是以它們在伺服器對象上繼承許可。
- 固定資料庫角色（Fixed database roles）：進行資料庫任務的内建角色，用于配置設定基本許可。
- 使用者自定義資料庫角色（User-defined database roles）：你建立的，配置設定許可，然後添加使用者到它裡面的自定義資料庫角色，是以使用者在資料庫對象上繼承許可。
你可以配置設定使用者到多個角色。角色也可以嵌套，但不要忘乎是以——如果你嵌套的架構太複雜，你會遭受性能損失，讓維護和故障排除變成噩夢。
固定伺服器角色
在SQL Server裡固定伺服器角色是内建的角色，你不能修改它們——你隻能添加登入到它們。它們隻在伺服器級别存在用來進行管理任務。SQL Server裡固定的伺服器角色列在這裡，附帶上它們實際的角色名：
- 系統管理者（sysadmin）：在SQL Server執行個體裡進行任何活動。這個角色包含了所有其它角色——一旦使用者是sysadmin成員，它們不需要其它任何角色。sysadmin成員可以做任何它們想做的事，是以限制為隻需要它的，且是可以信任的成員來擁有無限制的通路，加入這個角色是個好主意。
- 大容量插入管理者（bulkadmin）：執行BULK INSERT語句來将資料快速插入資料庫。
- 資料庫建立者（dbcreator）：建立和修改資料庫。
- 磁盤管理者（diskadmin）：管理存儲資料庫的各個磁盤檔案。
- 程序管理者（processadmin）：管理在SQL Server裡運作的程序。
- 伺服器管理者（serveradmin）：配置伺服器範圍的配置。盡管名字和系統管理者類似，serveradmin完全不同，是限制更多的角色。
- 設定管理者（setupadmin）：安裝複制和管理擴充過程。
- 安全管理者（securityadmin）：管理對于伺服器的登入。
固定伺服器角色通過允許你将伺服器任務拆分來提供靈活性和安全性。換句話說，如果他隻需要建立資料庫，你不必讓某人成為系統管理者。讓他成為資料庫建立者成員即可，它們已經擁有所有需要的許可。
你可以使用SSMS或T-SQL來配置設定登入到固定伺服器角色。使用SSMS，按如下步驟：
提示：
來自這個系列第2篇代碼建立的Tudou登入。如果你沒有建立那個登入，請随意運作那個代碼來建立它，或者使用第2篇裡讨論的技術來建立你自己的登入。如果你稍後做，調整需要的步驟來使用那個登入。
- 在SSMS裡展開【對象浏覽器】的【安全性】部分來顯示登入清單。
- 右擊Tudou登入，從彈出的菜單選擇【屬性】。
- 在【登入屬性】對話框裡，選擇【伺服器角色】頁。這裡列出了可以選擇的所有可用伺服器角色，單選複選框來增加角色到登入。像所有登入，Tudou，已經是公共（public）角色的成員。
- 配置設定資料庫建立者（dbcreator）和磁盤管理者（diskadmin）到登入。對于Tudou登入如插圖3.1所示。 插圖3.1：配置設定Tudou登入到dbcreator和diskadmin固定伺服器角色
SQL Server安全（3/11）：主體和安全對象（Principals and Securables） - 點選【确定】來儲存修改。
或者，你可以使用對象浏覽器裡，伺服器節點下的【伺服器角色】節點來添加登入到角色。添加Tudou到securityadmin伺服器角色。
- 在對象浏覽器下的【安全性】節點，展開【伺服器角色】節點。
- 在對象浏覽器裡右擊【securityadmin】伺服器角色，選擇【屬性】。這會打開伺服器角色屬性對話框。
- 在對話框的右邊點選【添加】按鈕，它打開選擇登入對話框。你可以輸入Tudou，點選【檢查名稱】，或者點選【浏覽】按鈕來獲得登入清單。一旦你輸入Tudou，對話框如插圖3.2所示。 插圖3.2：選擇Tudou來添加到伺服器角色
SQL Server安全（3/11）：主體和安全對象（Principals and Securables） - 點選【确定】來添加Tudou到伺服器角色。伺服器角色屬性對話框如插圖3.3所示。 插圖3.3：添加Tudou到securityadmin伺服器角色
SQL Server安全（3/11）：主體和安全對象（Principals and Securables） - 點選【确定】儲存修改。
另一個添加登入到伺服器角色是T-SQL，使用sp_addsrvrolemember系統存儲過程。下列代碼添加現存的Tudou登入到sysadmin角色：
1 EXEC sp_addsrvrolemember 'Tudou', 'sysadmin'; 代碼3.1：添加登入到伺服器角色的代碼
你可以通過運作2個存儲過程sp_helpsrvrole 和sp_helpsrvrolemember來獲得固定伺服器角色的資訊。如果你傳入一個有效的伺服器角色名稱到sp_helpsrvrole ，它會顯示那個角色的介紹；否則顯示所有伺服器角色。插圖3.4顯示在SSMS裡2個系統存儲過程的執行，顯示了securityadmin的介紹和它的目前成員。
1 -- Get a list of all server roles
2 EXEC sp_helpsrvrole;
3
4 -- Get the description of a single server role
5 EXEC sp_helpsrvrole securityadmin
6
7 -- Get list of members of the securityadmin role
8 EXEC sp_helpsrvrolemember securityadmin 插圖3.4：使用系統存儲過程獲得伺服器角色資訊
使用者自定義伺服器角色
在SQL Server 2012裡期待已久的安全功能是使用者自定義伺服器角色。對于資料庫級别的許可（在這篇文章裡你馬上就會學到），SQL Server擁有一直靈活的使用者自定義資料庫角色，但使用自定義伺服器角色，最終你可以獲得和伺服器級别一樣顆粒的許可。
在早期的SQL Server版本裡，唯一授予一些許可到使用者是配置設定它們到呢間的固定伺服器角色，這會有太多的許可。讓每個人都是sysadmin太可怕，卻是習慣做法，問題的關鍵是你不能阻止sysadmin任何事情。這個嚴重違法了最小權限原則，但經常實際上需要。SQL Sever 2005和後續版本讓這些變得更加明細，讓你可以配置設定特定的伺服器級别許可到使用者，但不能配置設定組到伺服器許可。
SQL Server 2012使用對自定義伺服器角色來解決這個問題。建立新的伺服器角色非常簡單，使用CREATE SERVER ROLE語句：
CREATE SERVER ROLE LimitedDBA; 代碼3.2：建立新的伺服器角色代碼
接下來你可以授予或拒絕你想要的任何伺服器級别許可。下列代碼授予
CONTROL SERVER
許可到建立的角色——授予了類似sysadmin權限——然後拒絕了一些許可來縮小伺服器角色的成員權限。這是非常靈活的方式來授予特定許可組成員的使用者。
1 USE master;
2 GO
3
4 -- Grant the role virtual sysadmin permissions
5 GRANT CONTROL SERVER TO LimitedDBA;
6
7 -- And take some permissions away
8 DENY ALTER ANY LOGIN TO LimitedDBA;
9 DENY ALTER ANY SERVER AUDIT TO LimitedDBA;
10 DENY ALTER ANY SERVER ROLE TO LimitedDBA;
11 DENY CREATE SERVER ROLE TO LimitedDBA; -- Covered by ALTER ANY SERVER ROLE
12 DENY UNSAFE ASSEMBLY TO LimitedDBA; 代碼3.3：添加或拒絕許可到伺服器角色的代碼
測試角色，代碼3.4建立一個登入關聯到Windows組，DBAs，在名為PC201602202041的機器上，添加新的登入到LimitedDBA角色。
在運作這個代碼前，DBAs組應該在Windows的本地執行個體上，你可以通過控制台裡的【計算機管理】，展開【系統工具】和【本地使用者群組】節點，添加它到【組】節點，還有修改PC201602202041為你的本地計算機名。
1 -- Create a login for DBAs Windows group
2 CREATE LOGIN [PC201602202041\DBAs] FROM WINDOWS;
3
4 -- Add to the server role
5 ALTER SERVER ROLE LimitedDBA ADD MEMBER [PC201602202041\DBAs]; 代碼3.4：建立登入添加它到伺服器角色的代碼
代碼3.5然後建立一個SQL Sever登入carol，在SQL Server執行個體裡沒有任何權限。然後這個代碼在carol的需要伺服器級别許可的上下文裡嘗試各種操作：建立另一個登入，檢視系統資訊，建立另一個伺服器角色。如你在插圖3.5裡看到的，所有這些操作都失敗了，因為carol主體沒有任何權限進行這些操作。
1 -- Create carol login
2 CREATE LOGIN carol WITH PASSWORD = 'crolPWD123%%%';
3
4 EXECUTE AS LOGIN = 'carol';
5 -- Verify user context
6 PRINT suser_sname();
7 -- Can Carol alter logins?
8 CREATE LOGIN donkiely WITH PASSWORD = 'G@Sm3aIKU3HA#fW^MNyA'; -- No
9 -- Other server-level permissions?
10 SELECT * FROM sys.dm_exec_cached_plans; -- No, requires VIEW USER STATE
11 CREATE SERVER ROLE CarolRole; -- No
12 REVERT; 代碼3.5：建立登入和測試它是否有特定許可的代碼
提示：這個代碼不檢測在這個SQL Server執行個體裡是否有carol登入存在。如果有的話，CREATE LOGIN語句會失敗。在這個情況下，跳過那個語句。
代碼3.5：操作失敗，因為carol沒有任何權限
接下來的代碼添加carol到建立的LimitedDBA使用者自定義伺服器角色，再一次嘗試同樣的操作。如你在插圖3.6裡所見，這次carol能獲得系統資訊（SELECT操作），因為那個許可是通過CONTROL SERVER許可授予的。但是carol還是不能建立登入或伺服器角色，因為那些許可在LimitedDBA角色裡拒絕了。
1 ALTER SERVER ROLE LimitedDBA ADD MEMBER carol;
2
3 -- Now does Carol have permissions?
4 EXECUTE AS LOGIN = 'carol';
5 CREATE LOGIN donkiely WITH PASSWORD = 'G@Sm3aIKU3HA#fW^MNyA'; -- Still not possible
6 SELECT * FROM sys.dm_exec_cached_plans; -- Yes, CONTROL SERVER covers VIEW USER STATE
7 CREATE SERVER ROLE CarolRole; -- Not possible
8 REVERT; 代碼3.6：再次測試伺服器角色成員是否有特定許可。
插圖3.6：通過LimitedDBA隻有部分權限的伺服器級别操作結果
為了檢視你授予和覺得的伺服器角色的所有可用伺服器級别許可，執行下列代碼。插圖3.7顯示了結果。
1 SELECT * FROM sys.fn_builtin_permissions('SERVER')
2 ORDER BY permission_name; 代碼3.7：檢視所有可用伺服器級别許可的代碼
插圖3.7：伺服器級别許可的部分清單
你可用建立使用者自定義伺服器角色來授予使用者群組它們需要剛好能用來完成它們工作的特定的一系列許可。這比SQL Server的早期版本更加靈活，使用SQL Server 2012讓安全管理更加簡單，更容易的管理意味着更安全的伺服器。
固定資料庫角色
固定資料庫角色存在于資料庫級别，不是伺服器級别，隻在資料庫裡控制許可。每個資料庫都有它自己的固定資料庫角色集合，是以你可以在你的每個資料庫獨立配置角色。固定資料庫角色和固定伺服器角色一樣，它們不能被删除，修改，或修改，但你可以添加資料庫使用者和使用者自定義角色作為成員。固定資料庫角色是：
- db_accessadmin：可以在資料庫裡添加和删除Windows登入群組，SQL Server登入。
- db_backupoperator：可以備份資料庫。
- db_datareader：可以在資料庫裡從使用者表裡檢視任何資料。
- db_datawriter：可以在資料庫裡的使用者表裡添加，修改或删除資料。
- db_ddladmin：可以在資料庫裡添加，修改或删除對象。（DDL是資料定義語言（Definition Language）的簡稱，對資料庫做出結構化修改的T-SQL指令集）
- db_denydatareader：在資料庫裡不能檢視任何資料。
- db_denydatawriter：在資料庫裡不能修改任何資料。
- db_owner：可以進行資料庫角色的任何活動，包括維護和配置活動。這個角色包含素有其它角色，對于這個資料庫，這個是作為管理者的基礎。
- db_securityadmin：可以在資料庫裡管理角色成員資格和聲明，還有對象許可。
在資料庫裡，固定資料庫角色可以簡單的配置設定許可。例如，假設你想一個使用者對通路的資料庫隻能備份。你不想使用者能讀取資料——隻有備份。你可以通過讓使用者是db_backupoperator和db_denydatareader角色的成員來實作。使用sp_helprole和sp_helprolemember系統存儲過程來檢視資料庫角色的資訊。
公用角色（Public Role）和來賓使用者（Guest User）
有兩個特定主體需要提下。你不見得在任何有意義的方式裡使用這些主體，但它們卻影響安全，是以你要知道它們是什麼。
公用角色是不能删除的特殊伺服器角色。每個資料庫使用者屬于這個公用角色，是以你不需要配置設定使用者，組或角色給它。每個SQL Server資料庫包含這個公用角色，包括master，msdb，tempdb和model。但是，你可以授予或限制公用角色的許可依你安全需要。對于公用角色你要記在心上的是你授予的許可會應用到所有資料庫使用者。
通常你想限制到公用角色的許可，因為在安全資料庫裡授予的許可很少到每個人。
在每個資料庫都存在來賓使用者，包括像mater和model這樣的系統資料庫。作為使用者，它從公用角色裡繼承許可，在特定資料庫裡，當伺服器登入沒有映射到使用者時，它發揮作用。預設情況下，來賓使用者沒有許可，但你可以在資料庫裡授予通路資料庫對象和進行操作的許可。你會料到，這是一件非常危險的事，對于資料庫伺服器，在精心設計的安全架構裡，幾乎沒有必要，你應該避免配置設定許可給這個使用者。盡管你不能删除這個使用者，你可以通過撤銷它的CONNECT許可在使用者資料庫裡停用它，使用代碼3.8。
1 USE Northwind;
2 GO
3
4 REVOKE CONNECT FROM guest;
5 GO 代碼3.8：在使用者資料庫裡通過撤銷它的CONNECT許可來停用來賓使用者的代碼。
不要在系統資料庫裡停用來賓使用者，這會帶來你不想處理的問題！這些資料庫需要來賓使用者做各種各樣的功能。
dbo使用者和架構
在每個資料庫裡dbo是個特殊的使用者賬号，它映射到sysadmin固定伺服器角色。這就是說，如果你是sysadmin角色的成員，你在任何資料裡建立了一個對象，那麼那個對象的擁有者是dbo，不是你。你不能删除dbo使用者，它隻映射到sysadmin，不是資料庫擁有者（db_owner）。這是令人迷惑的，因為dbo使用者真的和db_owner角色毫無關系。
每個資料庫也有屬于dbo使用者的dbo架構，這是dbo使用者的預設架構。是以，當你作為sysadmin通路資料庫時，不指定任何架建構立一個對象，它的兩部分名稱會是dbo.對象名稱。當任何其他使用者通路資料時，如果沒有指定架構名稱的話，dbo架構也是預設的次要架構。如果用使用者joe嘗試通路名為sales的表，SQL Server首先會檢查對于joe使用者，在預設架構裡是否有sales表，如果沒有的話，它會檢查在dbo架構裡是否有sales表。僅當2個架構裡都沒有sales表存在，才會有生成找不到對象的錯誤。是以對于每個通路的對象，最好的做法是指定它的架構名。
使用者定義資料庫角色
資料庫角色不限于預定義的角色——你可以建立你自己的角色。一個使用者可以建立2類資料庫角色：
- 标準角色（Standard Role）：使用這個角色可以建東配置設定到使用者組的許可。你可以嵌套固定資料庫角色或其他使用者自定義角色，配置設定使用者到角色，在這個情況下，它們從角色裡繼承許可。
- 應用程式角色（Application Role）：應用程式使用這個角色來運作應用程式或連接配接，通過提供角色名和密碼來登入到資料庫，并激活應用程式角色。你不能對其它角色的方式添加使用者到應用程式角色，一旦激活，應用程式角色的許可應用到連接配接的持續時間。任何個人權限的使用者會被挂起，隻會應用程式角色的許可會被檢查。
你可以用添加使用者到固定資料庫角色的方式，添加使用者定義角色到固定資料庫角色：通過固定資料庫角色的屬性對話框。
可安全對象（Securable Objects）
安全對象是你可以控制通路的保護資源。通常它是實體上的東西，或者它至少實體上可以是數字對象的東西！但可安全的（securable）可以是一個行為，能對資料庫或SQL Server執行個體做出修改的能力。例如，管理者可以授予主體獲得對象擁有權的能力。授予這個許可不能立即改變對象的所有權；它隻是主體以後可以做個的能力。
插圖3.8顯示了在SQL Server執行個體裡的大多數的可安全對象。伺服器級别擁有最廣的範圍，圍繞了整個SQL Server，包括可以影響主體對資料庫做出改變的能力許可。資料庫範圍圍繞了特定資料庫裡的所有對象，例如用來管理使用者和建立加密匙。架構範圍包括架構裡的所有對象——資料庫的基本資料結構，包括表和它們的資料。一個資料庫可以包含很多架構，每個可以包含資料庫對象完整集合的子集。架構強大的地方是你可以在架構上配置設定和拒絕許可，這些許可會應用到架構裡包含的所有對象。
插圖3.8：SQL Server裡的可安全對象。箭頭顯示的是在對象層次裡一個範圍如何包含一個小的範圍
在伺服器級别授予許可意味這也授權更小範圍的許可，了解這個非常重要。例如，授予伺服器級别許可會意味着在一個或所有資料庫的架構裡的主體都有這個許可。
小結
在這篇文章裡，你學到了授權的第一部分，在SQL Server執行個體和它的資料庫裡的主體和安全對象。在下篇文章裡，你會學到許可，當在安全對象上授予主體時，給予或拿走主體能在對象上做一些事情的能力。有了這個了解，你能在SQL Server裡用好驗證和許可的顆粒性，當允許許可的使用者或程序完成它們的工作時，保持整個資料庫财産的嚴格管控。
原文連結：
http://www.sqlservercentral.com/articles/stairway/113155/
注：此文章為
WoodyTu學習MS SQL技術，收集整理相關文檔撰寫，歡迎轉載，請在文章頁面明顯位置給出此文連結！
若您覺得這篇文章還不錯請點選下右下角的推薦，有了您的支援才能激發作者更大的寫作熱情，非常感謝！