在保密你的伺服器和數據，防備當前複雜的攻擊，SQL Server有你需要的一切。但在你能有效使用這些安全功能前，你需要理解你面對的威脅和一些基本的安全概念。這篇文章提供了基礎，因此你可以對SQL Server里的安全功能充分利用，不用在面對特定威脅，不能保護你數據的功能上浪費時間。通常來說，你通過

在保密你的伺服器和數據，防備當前複雜的攻擊，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固定伺服器角色
點擊【確定】來保存修改。

或者，你可以使用對象瀏覽器里，伺服器節點下的【伺服器角色】節點來添加登錄到角色。添加Tudou到securityadmin伺服器角色。

在對象瀏覽器下的【安全性】節點，展開【伺服器角色】節點。
在對象瀏覽器里右擊【securityadmin】伺服器角色，選擇【屬性】。這會打開伺服器角色屬性對話框。
在對話框的右邊點擊【添加】按鈕，它打開選擇登錄對話框。你可以輸入Tudou，點擊【檢查名稱】，或者點擊【瀏覽】按鈕來獲得登錄列表。一旦你輸入Tudou，對話框如插圖3.2所示。

插圖3.2：選擇Tudou來添加到伺服器角色
點擊【確定】來添加Tudou到伺服器角色。伺服器角色屬性對話框如插圖3.3所示。

插圖3.3：添加Tudou到securityadmin伺服器角色
點擊【確定】保存修改。

另一個添加登錄到伺服器角色是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/

SQL Server安全（3/11）：主體和安全對象（Principals and Securables）