第三篇 SQL Server安全性主體和安全性實體

標籤：

本篇文章是SQL Server安全系列的第三篇，詳細內容請參考原文。

一般來說，你通過給主體指派至的許可權來實現SQL Server上的使用者與對象的安全。在這一系列，你會學習在SQL Server執行個體中通過許可權授權來執行操作及訪問安全性實體。在SQL Server中重要的主體是角色，你會學習角色可以讓安全管理比使用單獨使用者更容易。你也會學習SQL Server的安全性實體。
授權
Authentication is only part of accessing all of the goodies in a database server。認證有點像是有一個護照證明你是誰，但沒有簽證。你需要一個簽證才能訪問其他國家。在這一篇你會學習授權以及它是如何作為簽證訪問資料庫物件。
主體是一個使用者或進程可以訪問一個或多個SQL Server/資料庫的安全性實體。一個安全性實體是一個受保護的資源，只有某些人或過程可以查看或更改，比如一個表中的資料。一個許可權使主體獲得訪問一種特定型別安全對象。
繼續護照的比喻，主體是護照的持有人，安全性實體是主體想訪問的國家，許可權是簽證可以跨越國界並享有訪問。
主體
主體，在安全上下文是任何使用者、組(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登入
->資料庫使用者映射到非對稱金鑰
->Public角色
重要的是要瞭解這個層次，因為一個主體的範圍決定了授予它的許可權的範圍。例如，資料庫使用者
只有在授予許可權的資料庫中有許可權。一個SQL Server層級的主體可以在SQL Server上有許可權，而Windows層級的主體有許可權超出SQL Server的限制，在Windows的本地執行個體和整個網路。
一個主體可以是一個登入(或使用者)或是角色。在SQL Server角色的作用類似於Windows組。角色中的成員繼承分配給角色的許可權。角色使安全管理更容易，因為你不需要為單獨的使用者管理複雜的許可權。SQL Server支援以下類型的角色：
->固定伺服器角色：SQL Server內建角色執行伺服器級任務
->使用者定義的伺服器角色：你建立的自訂伺服器角色，分配伺服器級許可權，並指定登入名稱，以便登入名稱繼承伺服器對象的許可權
->固定資料庫角色：內建角色執行資料庫任務和分配基本許可權
->使用者定義的資料庫角色：你建立的自訂資料庫角色，分配許可權，然後添加使用者，以便使用者繼承資料庫物件的許可權
你可以將使用者指派給多個角色。角色也可以嵌套，但是如果你的嵌套太複雜，你會受到效能損失，並且它可能會成為維護和診斷的噩夢。
固定伺服器角色
固定伺服器角色是SQL Server內建的角色，你不能以任何方式改變他們，你只可以給他們添加登入名稱。它們存在於伺服器層級僅用於執行管理工作。SQL Server中固定伺服器角色如下(括弧中是實際角色名稱)：
->系統管理員(sysadmin)：在SQL Server執行個體執行任何活動。這個角色包含了所有的其他角色，一旦使用者是sysadmin的成員，他們不需要任何其他的角色。sysadmin的成員可以做任何事情，所以很有必要限制成員使用者，只給那些需要並且可以信任的使用者訪問
->大容量插入管理員(bulkadmin)：執行BULK INSERT語句讓資料快速匯入到資料庫中
->建立資料庫(dbcreator)：建立和更改資料庫
->磁碟管理員(diskadmin)：管理儲存資料庫的磁碟檔案
->進程管理員(processadmin)：管理在SQL Server上啟動並執行進程
->伺服器管理員(serveradmin)：設定管理員範圍的設定。儘管與系統管理員的名稱相似，serveradmin是一個非常不同的和非常有限的角色
->設定管理員(setupadmin)：安裝複製和管理擴充程式
->安全性系統管理員(securityadmin)：管理伺服器的登入名稱
固定伺服器角色提供的靈活性和安全性允許你將伺服器的任務劃分成部分。換句話說，如果他們只需要建立資料庫，你就不必讓他們成為系統管理員的成員。讓他們成為dbcreator成員，就有他們需要的所有許可權。
你可以使用Management Studio或T-SQL來指定一個登入名稱到一個固定伺服器角色。要使用Management Studio，執行以下步驟：
1、展開，SSMS>物件總管>安全性>登入名稱
2、按右鍵Topaz登入名稱並從快顯功能表中選擇“屬性”
3、在“登入屬性”對話方塊中，選擇“伺服器角色”。這將列出所有可用的伺服器角色。注意Topaz，像所有的登入名稱一樣，已經是public角色的成員
4、指定登入到dbcreator和diskadmin角色。圖3.1顯示Topaz登入對話方塊
圖3.1 分配Topaz登入名稱到dbcreator和diskadmin固定伺服器角色
5、單擊“確定”以儲存更改
或者，你也可以通過物件總管>安全性>伺服器角色，來添加登入名稱到對應的角色。下面添加Topaz到securityadmin伺服器角色：
1、展開，物件總管>安全性>伺服器角色
2、按右鍵securityadmin伺服器角色並選擇屬性，這將開啟伺服器角色屬性視窗
3、單擊對話方塊的右下方“添加”按鈕，開啟“選擇伺服器登入名稱”對話方塊。你可以鍵入Topaz，單擊檢查名稱，或單擊“瀏覽”按鈕來獲得一個登入名稱列表。一旦你鍵入Topaz，對話方塊3.2所示

圖3.2 選擇Topaz添加到伺服器角色
4、單擊確定添加Topaz到伺服器角色。伺服器角色屬性對話方塊看起來像圖3.3
圖3.3 添加Topaz到securityadmin伺服器角色
5、單擊“確定”以儲存更改
另一種添加一個登入名稱到伺服器角色的方式是T-SQL，利用sp_addsrvrolemember系統預存程序。下面的代碼添加到現有的已有的登入名稱Topaz到sysadmin角色：

EXEC sp_addsrvrolemember ‘Topaz‘, ‘sysadmin‘;

代碼3.1 添加登入名稱到伺服器角色
你可以通過運行sp_helpsrvrole和sp_helpsrvrolemember預存程序找到有關固定伺服器角色資訊。如果你給sp_helpsrvrole傳入一個有效伺服器角色名稱，它將顯示該角色的描述；否則顯示所有伺服器角色。圖3.4顯示了這兩個過程的使用方式及返回效果。
圖3.4 使用系統預存程序擷取伺服器角色資訊
使用者定義的伺服器角色
SQL Server 2012中一個期待已久的安全功能是使用者定義的伺服器角色。SQL Server很早就有資料庫層級的使用者定義的資料庫角色，但你終於可以得到顆粒與伺服器層級許可權的自訂伺服器角色。
在SQL Server的舊版本，授予使用者的某些許可權只能通過分配他們到內建固定伺服器角色，這樣通常有太大的許可權。讓每個使用者成為系統管理員是一個可怕的但常見的做法，一個特別的問題，因為你不能限制系統管理員什麼。This violates the principal of least privilege in a big way, but was often a practical necessity。SQL Server 2005之後，使這一切更精細，讓你指定任何特定的伺服器層級的使用者權限，但是缺少將許可權放到一個伺服器角色裡。
SQL Server 2012解決了這個問題，支援使用者定義的伺服器角色。使用CREATE SERVER ROLE建立一個新的伺服器角色：

CREATE SERVER ROLE LimitedDBA;

代碼3.2 建立伺服器角色
你可以授予和拒絕任何想要的伺服器層級許可權。下面的代碼授予CONTROL SERVER許可權給新的角色，然後拒絕部分許可權來縮小的伺服器角色的成員的許可權。這是一個非常靈活的方式授予使用者特定許可權。

USE master;GO--Grant the role virtual sysadmin permissionsGRANT CONTROL SERVER TO LimitedDBA;--And take some permissions awayDENY ALTER ANY LOGIN TO LimitedDBA;DENY ALTER ANY SERVER AUDIT TO LimitedDBA;DENY ALTER ANY SERVER ROLE TO LimitedDBA;DENY CREATE SERVER ROLE TO LimitedDBA;--Covered by ALTER ANY SERVER ROLEDENY UNSAFE ASSEMBLY TO LimitedDBA;

代碼3.3 給伺服器角色授予/拒絕許可權
為了測試角色，代碼3.4建立一個映射到Windows組的登入名稱，並添加新的登入名稱到LimitedDBA角色。

--Create a login for DBAs Windows groupCREATE LOGIN [Marathon\DBAs] FROM WINDOWS;--Add to the server roleALTER SERVER ROLE LimitedDBA ADD MEMBER [Marathon\DBAs];

代碼3.4 建立登入名稱並添加到伺服器角色
代碼3.5中建立一個SQL Server身分識別驗證的登入名稱carol，沒有分配任何SQL Server執行個體的許可權。然後，嘗試在carol安全上下文執行各種操作，這些操作需要伺服器層級的許可權：建立另一個登入名稱、查看系統資訊，並建立另一個伺服器角色。所有這些操作都失敗了，你可以在圖3.5中看到，因為carol主體沒有任何許可權來執行這些操作。

--Create carol loginCREATE LOGIN carol WITH PASSWORD = ‘crolPWD123%%%‘;EXECUTE AS LOGIN = ‘carol‘;--Verify user contextPRINT suser_sname();--Can Carol alter logins?CREATE LOGIN donkiely WITH PASSWORD = ‘[email protected]#fW^MNyA‘;--No--Other server-level permissions?SELECT * FROM sys.dm_exec_cached_plans;--No,requires VIEW USER STATECREATE SERVER ROLE CarolRole;--NoREVERT;

代碼3.5 建立登入並測試它是否有特殊許可權

圖3.5 執行失敗因為carol沒有許可權
接下來的代碼添加carol到LimitedDBA使用者定義伺服器角色，並再次嘗試執行相同的操作。3.6所示，現在carol可以擷取系統資訊(查詢操作)，因為角色授予了CONTROL SERVER許可權。但carol仍然無法建立登入名稱或伺服器角色，因為LimitedDBA角色顯示拒絕了這些許可權。

ALTER SERVER ROLE LimitedDBA ADD MEMBER carol;-- Now does Carol have permissions?EXECUTE AS LOGIN = ‘carol‘;CREATE LOGIN donkiely WITH PASSWORD = ‘[email protected]#fW^MNyA‘;-- Still not possibleSELECT * FROM sys.dm_exec_cached_plans;-- Yes, CONTROL SERVER covers VIEW USER STATECREATE SERVER ROLE CarolRole;-- Not possibleREVERT;

代碼3.6 再次嘗試carol是否有特殊許可權
圖3.6 LimitedDBA角色成員執行結果

為了查看你可以授予和拒絕的伺服器層級許可權給伺服器角色，執行以下代碼。圖3.7顯示了結果。

SELECT * FROM sys.fn_builtin_permissions(‘SERVER‘)  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代表資料定義語言 (Data Definition Language)，使資料庫結構變化的一組T-SQL命令)
->db_denydatareader:無法查看資料庫中的任何資料
->db_denydatawriter:無法更改資料庫中的任何資料
->db_owner:可以執行所有的資料庫角色以及維護和配置活動。這個角色包含了所有其他的角色，所以它基本上是這個資料庫的管理員
->db_securityadmin:可以在資料庫中管理角色成員、語句和對象許可權
固定資料庫角色可以簡化資料庫中的許可權分配。例如，假設你希望只允許使用者備份某個特定的資料庫。你不希望使用者能夠讀取資料，只要備份。你能很容易完成這個讓使用者成為db_backupoperator和db_denydatareader角色的成員。使用sp_helprole和sp_helprolemember系統預存程序來查看有關資料庫角色資訊。
The Public Role and Guest User
有一些特別的主體需要提醒。你可能不會以任何有意義的方式來使用這些主體，但它們確實會影響安全性，所以你需要知道他們是什麼。
Public角色是一種特殊的伺服器角色，不能被刪除。每個資料庫使用者都屬於這個Public角色，所以你不需要為它分配使用者、組或角色。每一個SQL Server資料庫中包含的Public角色，包括master、msdb、tempdb和model。但是，你可以授予或限制Public角色的許可權，根據你的安全需要決定。重要的事情要記住的是，你授予給Public角色的許可權，適用於所有的資料庫使用者。
Guest使用者在每個資料庫中都存在，包括系統資料庫。作為使用者，它繼承了Public角色的許可權。它是當一個登入名稱沒有在資料庫中映射使用者時使用的。預設情況下，Guest使用者沒有許可權，但你可以授予訪問資料庫物件的許可權，並在資料庫中執行操作。正如你所預期的，這是一個非常危險的事情，在一個良好設計的資料庫伺服器是很少需要的，你應該避免分配許可權給Guest使用者。雖然你不能刪除Guest使用者，你應該在使用者資料庫中禁用它。

USE Northwind;GOREVOKE CONNECT FROM guest;GO

代碼3.8 通過回收串連許可權禁用Guest使用者
注意：不要禁用系統資料庫中的Guest使用者，這可能會導致你不想處理的問題！這些資料庫需要Guest使用者的各種功能。
dbo使用者和架構
dbo是一個特殊的使用者帳戶在每個資料庫映射為sysadmin固定伺服器角色。這意味著如果你是sysadmin角色的成員，你建立的任何資料庫物件，對象的所有者會是dbo，而不是你。你不能刪除dbo使用者，它的映射只有sysadmin，而不是資料庫擁有者(db_owner)。
每個資料庫有dbo架構，並且是dbo使用者的預設架構。因此，當你以sysadmin訪問一個資料庫，並建立一個對象，而不指定架構，它的兩部分名稱將是dbo.objectname。其他使用者訪問資料時如果沒有指定架構名稱，dbo架構就是它們的第二個預設架構。如果使用者Joe試圖訪問表sales，SQL Server將首先檢查是否有Joe預設架構的sales表，如果沒有，它會檢查是否有dbo.sales。只有在兩種架構下都沒有找到sales表，才會返回錯誤資訊。最佳實務是為每個對象指定一個架構名稱。
使用者定義資料庫角色
資料庫角色並不局限於預定義的角色，你可以建立自己的角色。使用者可以定義類型的資料庫角色:
->標準角色：使用此角色來簡化使用者組的許可權。你可以嵌套固定資料庫角色或其他使用者定義的角色，並將使用者指派給角色，在這種情況下，他們繼承了角色的許可權。
->應用程式角色：應用程式使用這個角色允許應用程式或串連登入到資料庫，並通過提供角色名稱和密碼來啟用應用程式角色。你不能像其他角色添加使用者的方式將使用者添加到應用程式角色，而且一旦啟用，應用程式許可權的生命週期存在於串連期間。使用者任何單獨的許可權可能置疑，直到檢查應用程式許可權。
你可以將使用者定義的資料庫角色添加到固定資料庫角色，類似於將使用者添加到固定資料庫角色，通過固定資料庫角色的屬性視窗。
安全性實體
一個安全性實體是一個你可以存取控制的受保護資源。通常它是一個物理的東西，或者至少是一個可以物理化的數字對象。但安全性實體也可以是一個操作，可以對資料庫或資料庫執行個體做出某些改變。例如，一個管理員可以授予一個主體擁有某個對象。授予此許可權並不能立即更改該對象的所有者，它只是給主體在未來的時間裡有能力去做。
圖3.8顯示了在SQL Server執行個體的大部分安全性實體。伺服器層級的安全性實體具有最廣泛的範圍，including permissions that affect a principal’s ability to make changes to all databases。資料庫層級範圍包括一個特定的資料庫中的所有對象，比如那些用於系統管理使用者以及建立加密金鑰的對象。架構範圍包括架構內的所有對象，基本上是資料庫的資料結構，包括表及其資料。資料庫可以包含多種架構，並且每種架構都包含一個完整的資料庫物件集合的子集。架構功能的強大之處，你可以指定和拒絕架構許可權，並且這些許可權適用於架構包含的所有對象。
圖3.8 SQL Server中的安全性實體
注意：授予伺服器層級的許可權通常會影響到小/低範圍的許可權。例如，授予資料庫級許可權可能意味著主體對對象在一個或全部資料庫結構描述中具有隱含許可權。
總結
這一篇學習了授權、主體和安全性實體。在下一篇你將瞭解許可權，給主體授予或拒絕對對象做某事的能力。通過理解，你就可以有效利用SQL Server的認證和授權粒度嚴格控制資料庫資源，同時允許授權使用者和進程完成他們的工作。

第三篇 SQL Server安全性主體和安全性實體