第四篇 SQL Server安全許可權

標籤：

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

許可權授予主體訪問對象，以執行某些操作。SQL Server有大量你可以授予給主體的許可權，你甚至可以拒絕或回收許可權。這聽起來有點複雜，但在這一系列，你將知道SQL Server許可權是如何工作的，你可以非常精細地控制對象建立、資料訪問、以及其他類型操作在資料庫和伺服器對象上。
許可權
許可權像一個簽證允許你訪問外國，通常有一些基本條件。比如，你只有六個月的期限，你被限制在3/7的地區旅行。類似的，SQL Server許可權給主體訪問資料庫物件做一些事或執行操作。許可權可以允許主體讀取表的資料或表的某一部分，或能夠運行一段特定的代碼。甚至允許主體給其他登入名稱授予許可權。你可以給不同的主體授予數百種不同的許可權。
在授予許可權時，你要遵循最小特權原則。最小特權意味著，你給一個主體需要完成一個任務的許可權——沒有多也沒有少。堅持最小特權原則，是資料庫安全的重要步驟。如果主體在資料庫中唯一能做的事情是讀取產品資訊，這個主體就不應該能有意或無意刪除表中的內容。本質上是建立一個嚴密的容器限制主體可以做什麼。
權限類別型
SQL Server有許多種你可以授予主體控制訪問安全性實體的許可權。下面列出最常見的許可權：
控制：賦予安全性實體的所有可能的許可權，使主體成為安全性實體的虛擬擁有者。這包括將安全性實體授予許可權給其他主體。
建立：賦予建立一個特定對象的能力，這取決於它被授予的範圍。例如，CREATE DATABASE許可權允許主體在SQL Server執行個體建立新的資料庫。
更改：賦予更改安全性實體屬性的許可權，除了更改所有者。這個許可權包含了同範圍的alter、create、drop對象的許可權(比如，使用者對錶A有更改許可權，那麼它能夠add/alter/drop列、create/drop索引約束等)例如，一個資料庫層級的更改許可權，包括更改表和架構許可權。
刪除：允許一個主體刪除任何或所有儲存在表中的資料。是一個非常危險的許可權！
類比<登入>或者類比<使用者名稱>：賦予主體類比另一個登入/使用者。通常用於改變預存程序的執行內容。
插入：允許主體往表中插入新記錄
選擇：授予主體從一個特定的表讀取資料。這是使用者需要的最常見的許可權，以便他們可以在表上執行查詢。
接管所有權：Confers on a principal permission to take ownership of an object. Granting this permission does not immediately transfer ownership. Instead, it allows the principal to take ownership at some future time.
更新：允許主體更新表中的資料。
查看定義：賦予主體許可權查看安全性實體的定義。這是一個重要的許可權，因為結構資訊在資料庫攻擊中是非常有用的。如果沒有這個許可權，攻擊者探索資料庫或伺服器執行個體有價值目標的能力將嚴重受限。
建立和更改許可權可以使用ANY關鍵字：CREATE ANY <object type> and ALTER ANY <object type>.這些許可權授予建立或更改任何指定型別安全對象。例如，在資料庫層級上授予ALTER ANY SCHEMA，允許改變資料庫中任何架構的屬性。在伺服器層級上，ALTER ANY LOGIN允許主體改變伺服器上任何登入名稱。使用ANY關鍵字，可以靈活的讓主體建立或修改一整類的對象，而不是一個單一的對象。只是要注意，在建立和修改許可權之間有一些細微的差別。
當你考慮在SQL Server安全性實體的數量和一個對象可以擁有潛在權限類別型的數量時，你開始意識到有顆粒的許可權。你可以應用最小特權原則來實現任何使用者或角色的許可權集，通過給予使用者恰當的許可權來完成一項任務，而不需要將其他對象暴露。
提示：如果你使用SQL Server早期版本，你會意識到，SQL Server 2005及之後版本完全修改了可用的許可權。你不必再將一個使用者指派給一個可能有幾十個不必要的許可權的角色，從而違反了最小特權，並將資料庫暴露在有意或偶然的濫用。
這裡一個主要考慮是，授予許可不一定有效地授予執行一個操作的能力。有時還需要其他許可權，提供一個全面的安全上下文來執行敏感操作的能力。一個很常見的例子是建立表許可權。這是很有可能授予許可權，這在理論上允許一個主體在特定的資料庫下建立表。但主體還需要有能力在一個架構中建立表。如果主體沒有對任何架構的更改許可權，它將無法建立一個表。
許可權語句
即使你是通過圖形化工具分配許可權，底層還是執行TSQL許可權語句。
GRANT：授予對安全性實體或操作的許可權給主體
REVOKE：“Un-grant”許可權，取消一個早期做的grant語句。撤銷的許可權仍然可以通過在具有許可權的組或角色中繼承。當你建立新的對象時，revoke是預設的許可權狀態，所以特定許可權沒有授予，但是可以繼承。
DENY：拒絕撤銷許可權，使它不能被繼承。這是最具限制性的許可權，並且優先於所有其他許可權。DENY不適用於sysadmin角色的成員或對象的所有者。
不要低估DENY許可權的重要性。例如，假設你有一個臨時僱員，他是進來做資料輸入，你不希望他能夠編輯或刪除現有記錄。你已經分配給editor角色(公司內部人員都屬於)對某些表的足夠的許可權。你可以建立一個特殊的登入名稱，然後在適當的表上deny UPDATE和DELETE許可權。臨時僱員可以從editor角色繼承足夠的許可權輸入新記錄，但是不能修改/刪除已存在的記錄。
授予許可權
在Management Studio下授予許可權的最靈活的方法是修改資料庫使用者或角色的屬性。你還可以通過修改單個對象的屬性來授予許可權，但這種方法不靈活，並且具有更高的維護成本。
下面的練習將在AdventureWorks2012資料庫建立自訂資料庫角色。這個角色的成員，需要必要的許可權插入和更新一些HR相關的表，並且能夠執行相關的預存程序，但沒有其他的特殊許可權。
提示：一旦你為一個使用者配置許可權，然後需要為另一個使用者重複上述過程，授予和第一個使用者同樣的許可權，那麼把許可權分配給一個角色，然後簡單地將使用者添加到角色即可。
你可以利用第二篇在AdventureWorks2012資料庫下建立的使用者Topaz。如果你沒有建立使用者，執行代碼4.1建立登入名稱和使用者。

USE master;GOCREATE LOGIN Topaz WITH PASSWORD = ‘yBqyZIPT8}b]b[{5al0v‘;GOUSE AdventureWorks2012;GOCREATE USER Topaz FOR LOGIN Topaz    WITH DEFAULT_SCHEMA = HumanResources;GO

View Code

代碼4.1 建立建立名並映射到資料庫使用者
在AdventureWorks2012資料庫建立一個DataEntry自訂資料庫角色，參考如下步驟：
1、SSMS串連到安裝有AdventureWorks2012資料庫的執行個體。物件總管->資料庫->AdventureWorks2012->安全性->角色->資料庫角色
2、右擊資料庫角色，快顯功能表選擇建立資料庫角色
3、角色名稱鍵入DataEntry，所有者dbo
4、點擊添加按鈕，將使用者Topaz添加到角色(如果使用者不存在請先建立)。在你關閉選擇資料庫使用者或角色對話方塊後，資料庫角色-建立對話方塊應該4.1所示

圖4.1 建立DataEntry資料庫角色並添加Topaz使用者
5、點擊確定儲存修改並建立角色
當前DataEntry角色並不允許角色中的成員能做什麼，因為你還沒有給角色指派任何許可權。DataEntry角色中的成員需要能夠往表Employee、Address、JobCandidate插入和更新資料，同時他們需要執行uspUpdateEmployeeHireInfo和uspUpdateEmployeePersonaInfo預存程序。但是他們不能查看預存程序的定義。
使用下面的步驟給DataEntry角色添加合適的許可權
1、物件總管->資料庫->AdventureWorks2012->安全性->角色->資料庫角色->DataEntry
2、右擊DataEntry，快顯功能表選擇屬性。開啟資料庫角色屬性對話方塊
3、左側選擇安全性實體，這個頁面讓你選擇角色有權操作的安全性實體，並指定對安全性實體上的許可權
4、點擊“搜尋”按鈕添加安全性實體。這將開啟“添加對象”對話方塊，該對話方塊提供了特定對象、特定類型的所有對象、屬於該架構的所有對象的選項。在本例中，因為你想為表和預存程序中添加許可權，圖4.2中保持預設選擇-特定對象，然後單擊“確定”

圖4.2 選擇添加對象
5、開啟“選擇對象”對話方塊。單擊“物件類型”按鈕開啟“選擇物件類型”對話方塊，然後從列表中選擇“預存程序”和“表”，4.3所示。請單擊“確定”以關閉該對話方塊並返回“選擇對象”對話方塊，該對話方塊現在看起來像圖4.4。你將看到在物件類型框中列出的預存程序和表

圖4.3 選擇物件類型

圖4.4 選擇對象
6、單擊“瀏覽”按鈕以查看資料庫中的預存程序和表的列表。這將開啟“尋找對象”對話方塊，向下滾動尋找並選擇對象。

圖4.5 選擇預存程序和表
7、單擊“確定”以關閉“尋找對象”對話方塊中。此時你選擇的對象以分號分隔列在選擇對象對話方塊中，4.6所示。請單擊“確定”以關閉該對話方塊並儲存更改

圖4.6 選擇對象結果
8、現在DataEntry資料庫角色屬性對話方塊列出你選擇的安全性實體，並且列出每個對象可用的許可權。DataEntry角色成員需要可以插入和更新資料到這些表，逐一選擇表，在對話方塊的下部勾選插入/更新授予列的複選框。圖4.7顯示了HumanResources.Employee表的許可權

圖4.7 在表上授予插入/更新許可權
提示：授予(Grant)允許指定的許可權，具有授予許可權(With Grant)允許使用者或角色授予其他主體的許可權。小心With Grant許可權！
9、對於每一個預存程序，授予執行許可權並拒絕查看定義許可權。圖4.8顯示了useUpdateEmployeeHireInfo預存程序的許可權

圖4.8 授予執行過程許可權、拒絕查看定義許可權
10、在“資料庫角色屬性”對話方塊中單擊“確定”以儲存你的更改並提交到資料庫。根據選定的對象和許可權的數量，這可能需要一些時間。
當然，你可以使用T-SQL代碼建立對象並分配許可權。代碼4.2建立DataEntry角色，添加Topaz使用者，然後分配和圖形介面相同的許可權。

-- Create the DataEntry role and assign Topaz to itCREATE ROLE [DataEntry] AUTHORIZATION [dbo];ALTER ROLE [DataEntry] ADD MEMBER [Topaz];-- Assign permissions to the DataEntry roleGRANT INSERT ON [HumanResources].[Employee] TO [DataEntry];GRANT UPDATE ON [HumanResources].[Employee] TO [DataEntry];GRANT INSERT ON [HumanResources].[JobCandidate] TO [DataEntry];GRANT UPDATE ON [HumanResources].[JobCandidate] TO [DataEntry];GRANT INSERT ON [Person].[Address] TO [DataEntry];GRANT UPDATE ON [Person].[Address] TO [DataEntry];GRANT EXECUTE ON [HumanResources].[uspUpdateEmployeeHireInfo] TO [DataEntry];DENY VIEW DEFINITION ON [HumanResources].[uspUpdateEmployeeHireInfo] TO [DataEntry];GRANT EXECUTE ON [HumanResources].[uspUpdateEmployeePersonalInfo] TO [DataEntry]DENY VIEW DEFINITION ON [HumanResources].[uspUpdateEmployeePersonalInfo] TO [DataEntry];GO

代碼4.2 語句建立角色並分配許可權
檢查和測試許可權
如果你想檢查DataEntry角色的許可權，你可以使用GUI工具或執行T-SQL代碼訪問資料庫物件的中繼資料。使用GUI，物件總管->資料庫->AdventureWorks2012->安全性->角色->資料庫角色->DataEntry->屬性。這將開啟和建立角色相同的"資料庫角色屬性"對話方塊，你可以使用安全性實體審查該角色的許可權。
或者你可以使用代碼4.3中的T-SQL查看DataEntry角色的許可權，利用sys.database_permissions和sys.database_principals安全目錄檢視和sys.objects目錄檢視。

SELECT DB_NAME() AS ‘Database‘, p.name, p.type_desc, dbp.state_desc,     dbp.permission_name, so.name, so.type_descFROM sys.database_permissions dbp     LEFT JOIN sys.objects so ON dbp.major_id = so.object_id     LEFT JOIN sys.database_principals p ON dbp.grantee_principal_id = p.principal_id WHERE p.name = ‘DataEntry‘ORDER BY so.name, dbp.permission_name;

代碼4.3 查看DataEntry角色的許可權
當你執行上面代碼，你將會看到結果4.9所示

圖4.9 DataEntry角色的許可權
你可以通過開啟建立查詢->更改串連用Topaz登入測試這些許可權。然後嘗試在HumanResources.Employee、HumanResources.JobCandidate、Person.Address表插入新行或更新已有資料，並執行分配許可權的預存程序。這些操作應該成功。然後嘗試插入或更新其他表中的資料，這些操作應該失敗。你應該只能夠執行已經授與權限。
你可以用代碼4.4執行相同的測試。代碼開始設定的執行內容為Topaz，DataEntry角色的成員。然後插入新行到Person.Address表。這一操作成功，因為角色允許在Person.Address表插入資料。然後代碼試圖插入新行到HumanResources.Department表，插入失敗，因為角色在此表沒有insert許可權。接下來的代碼成功執行HumanResources.uspUpdateEmployeePersonalInfo預存程序；執行dbo.uspGetManagerEmployees失敗，因為角色沒有此過程執行許可權。最後，代碼將執行內容切回到登入時的安全上下文。

EXECUTE AS USER = ‘Topaz‘;-- SucceedsINSERT INTO Person.Address    (AddressLine1, City, StateProvinceID, PostalCode)VALUES    (‘8 Hazelnut‘, ‘Irvine‘, 9, ‘92602‘);GO-- FailsINSERT INTO HumanResources.Department    (Name, GroupName)VALUES    (‘Advertising‘, ‘Sales and Marketing‘);GO-- Succeeds (doesn‘t actually change any data)DECLARE @RC INT;EXECUTE @RC = HumanResources.uspUpdateEmployeePersonalInfo    1, ‘295847284‘, ‘1963-03-02‘, ‘S‘, ‘M‘;GO-- FailsEXECUTE dbo.uspGetManagerEmployees 1;GOREVERT;

代碼4.4 測試DataEntry角色的許可權
總結
The ability to assign granular permissions on securable objects throughout SQL Server to various types of principals gives you very fine control over the security of a SQL Server instance.它讓你充分利用最小特權的安全原則，能夠限制主體執行操作和訪問資料的能力到他們需要的最低限度。

第四篇 SQL Server安全許可權