天天看點
傳回

SQL Server安全（4/11）：許可（Permissions）

在保密你的伺服器和資料，防備目前複雜的攻擊，SQL Server有你需要的一切。但在你能有效使用這些安全功能前，你需要了解你面對的威脅和一些基本的安全概念。這篇文章提供了基礎，是以你可以對SQL Server裡的安全功能充分利用，不用在面對特定威脅，不能保護你資料的功能上浪費時間。

許可給主體通路一個對象進行在對象上或與對象的特定操作。SQL Server擁有讓你思想麻木的大量許可，你可以授予它們到主體，甚至你可以禁止或撤銷這些許可。這聽起來有點複雜，但看完這篇文章後，你會了解SQL Server許可如何工作，你如何能在資料庫和伺服器對象上進行對象建立、資料通路和其它類型的操作發揮顆粒度的控制。

許可

許可就像你可以通路外國的簽證，通常會有一些基本條件，例如你隻能呆6個月，限制你隻能在國家的七分之三區域旅遊。同樣，SQL Sever許可給主體通路到資料庫對象，在對象上或與對象做某些事情，或進行一個操作。許可會讓主體能通路表的資料或其中一部分資料，或能運作特定代碼。甚至可以能授予其它的登入的許可。按理你可以授予幾百種許可到不同的主體。

當授予許可時，你要遵循最小特權原則。最小特權意味着你給主體它需要來完成一個任務的精确許可——不多也不少。通過堅持這個最小特權原則，在安全管理資料庫裡，你已經邁出了重大一步。如果在資料庫裡，主體能做的唯一事情是讀取一些産品資訊，主體就不能——有意或無意的——删除表的内容。基本上，你要在主體能做的周圍建立一個緊密的容器。

許可類型

SQL Server有多個許可類型，你可以授予到主體來控制對安全對象的通路。這裡有個在SQL Server裡部分最常見你會遇到和使用的清單：

  • CONTROL：在安全對象上授予基本的所有可能許可，标記主體為能使安全的虛拟擁有者。這包括能在能使安全的對象上授予許可到其它主體。
  • CREATE：授予能建立特定對象的許可，取決于授予的對象範圍。例如CREATE DATABASE許可允許主體在SQL Server執行個體裡建立新的資料庫。
  • ALTER：授予安全對象的除了所有者的屬性許可。這個許可意味着在同個範圍裡修改，建立，删除對象的其它許可。例如，在資料庫上的ALTER許可包括修改表和架構的許可。
  • DELETE:允許主體删除表裡存儲的任何或所有資料。這的确是個非常危險的許可。
  • IMPERSONATE <login> or IMPERSONATE <user>：授予主體模拟另一個登入或使用者的許可。模拟經常用來對于給出的對象，給主體另一個主體的許可。這個經常用來存儲過程執行時，修改存儲過程的執行上下文。
  • INSERT：允許主體插入新行到表。
  • SELECT：授予主體從特定表讀取資料的許可。這是大多數使用者使用的常見許可，是以它們可以在表上執行查詢。
  • TAKE OWNERSHIP：授予主體占有對象所有權的許可。授予這個許可不會立即轉移所有權。而是允許主體在随後可以拿到所有權。
  • UPDATE：允許主體在表裡修改資料。
  • VIEW DEFINITION：允許主體檢視安全對象定義的許可。這是個重要的許可，因為在攻擊資料庫裡，結構資訊非常有用。沒有這個許可，攻擊者在資料庫或伺服器執行個體裡發現有趣目标的能力非常有限。

CREATE和ALTER許可有使用ANY關鍵字的各種方法：CREATE ANY <object type> 和ALTER ANY<object type>。這些許可允許建立或修改特定類型的任何安全對象。例如，在資料庫級别授予ALTER ANY SCHEMA允許主體修改資料庫裡任何架構屬性。在伺服器級别，ALTER ANY LOGIN允許主體修改在伺服器上的任何登入。使用ANY給你允許主體建立或修改對象的整個類，而不是單個特定對象。要注意CREATE和ALTER許可兩者之間的格式有一些細微差别。

當你考慮SQL Server裡的安全對象數目，對象可以擁有的潛在許可類型數目時，你已經開始考慮許可的顆粒是什麼樣的。對于任何使用者或角色，你可以應用最小特權原則來實作許可，給使用者明确的必須許可來完成工作，卻不暴露其它對象的濫用。

提示：

如果你用過SQL Server的早期版本，你會發現SQL Sever 2005和後續版本完全改造了可用許可。你不再需要配置設定使用者到擁有很多不需要許可的角色，這樣違背了最小特權原則，暴露你的資料庫為故意或無意的濫用。

這裡主要考慮的是授予需要的權限不能有效的進行一個操作。有時候也需要其它權限，在能進行敏感操作周圍提供全局的安全上下文。這個最常見的例子是CREATE TABLE許可。授予這個許可很容易犯錯，理論上允許在特定資料庫裡建立一個表。但是主體也要能在其它地方建立表，尤其在架構裡。如果主體在任何架構上沒有ALTER許可，她就不能建立表。

授予許可

在SSMS裡最靈活授予許可的方法是修改資料庫使用者或角色的屬性。你也可以通過修改各個對象的屬性來授予許可，但這個方法不太靈活，需要花費太多的維護工作。

下面的練習會在AdventureWorks2012裡建立一個自定義的資料庫角色。這個角色的成員，在Human Resources部門，需要必要的許可在資料庫裡，在一些人力資源相關的表裡來輸入和更新資料，執行一些相關的存儲過程，但不能通路其它。

一旦你為使用者配置許可，然後為另一個使用者做同樣的操作，授予和第一個使用者同樣的許可，很明顯，配置設定許可到到角色，然後添加使用者到角色更容易。

你會使用在第2篇裡，在AdventureWorks2012資料庫裡建立的使用者Tudou。如果你那個時候沒有建立這個使用者，在SSMS裡執行下列代碼建立登入和使用者。

1 USE master;
 2 GO
 3 
 4 CREATE LOGIN Tudou  WITH PASSWORD = 'yBqyZIPT8}b]b[{5al0v';
 5 GO
 6 
 7 USE AdventureWorks2012;
 8 GO
 9 
10 CREATE USER TudouZ  FOR LOGIN Tudou 
11     WITH DEFAULT_SCHEMA = HumanResources;
12 GO

代碼4.1:建立一個登入并映射到資料庫使用者

在AdventureWorks2012資料庫裡，你需要一個DataEntry的使用者自定義資料庫角色。按這些步驟來建立它。

  1. 在SSMS裡，連接配接到資料庫伺服器執行個體，切換到安裝的AdventureWorks2012資料庫。在對象浏覽器裡，展開【資料庫】，展開【AdventureWorks2012】節點，然後展開【安全性】【角色】。
  2. 右擊【資料庫角色】，從彈出的菜單選擇【建立資料庫角色】打開【資料庫角色】對話框。
  3. 在【角色名稱】裡輸入DataEntry，所有者輸入dbo。
  4. 點選【添加】按鈕，添加使用者TudouZ到角色（如果不存在請在AdventureWorks2012資料庫裡建立這個使用者）。在你關掉選擇資料庫使用者或角色對話框後，【資料庫角色-建立】如插圖4.1所示。
    SQL Server安全（4/11）：許可（Permissions）
    插圖4.1:建立DataEntry資料庫角色，添加使用者TudouZ
  5. 點選【确定】儲存你的修改，建立DataEntry角色。

在這個時候DataEntry角色不允許TudouZ和其它你添加的角色做任何事情，因為你還沒設定它的任何許可。DataEntry角色成員需要能在AdventureWorks2012資料庫裡的Employee, Address,和JobCandidate表，添加和修改資料。它們也需要能執行uspUpdateEmployeeHireInfo和uspUpdateEmployeePersonaInfo存儲過程。但這些成員不能檢視存儲過程的定義。

使用下列步驟來添加和拒絕合适的許可到DataEntry角色。

  1. 在SSMS的對象浏覽器裡，展開 AdventureWorks2012資料庫的【安全性】【角色】【資料庫角色】節點。
  2. 右擊【DataEntry】節點，從彈出菜單裡選擇【屬性】。這會打開【資料庫角色屬性】對話框。
  3. 在對話框的左邊清單裡選擇【安全對象】頁。這頁會讓你選擇這個角色已經授予的許可的安全對象，指定你想要授予角色的具體許可。
  4. 點選【搜尋】來添加安全對象。這會打開【添加對象】對話框，提供選擇特定對象的選項，特定對象，特定類型的所有對象和屬于該架構的所有的所有對象。在這個練習裡，因為你想為表和存儲過程同時添加許可，保持預設【特定對象】選擇如插圖4.2所示，點選确定。
    SQL Server安全（4/11）：許可（Permissions）
    插圖4.2：選擇要授予許可的對象類型
  5. 【選擇對象】對話框打開了。點選【對象類型】按鈕來打開【選擇對象類型】對話框，從清單裡選擇【存儲過程】和【表】，如插圖4.3所示。點選【确定】關閉對話框，回到【選擇對象】對話框，如插圖4.4所示。在對象類型裡你可以看到存儲過程和表。
    SQL Server安全（4/11）：許可（Permissions）
    插圖4.3：需要授予許可的對象類型
    SQL Server安全（4/11）：許可（Permissions）
    插圖4.4：選擇存儲過程和表後的【選擇對象】對話框
  6. 點選【浏覽】按鈕來檢視資料庫裡的存儲過程和表清單。這會打開【查找對象】對話框，劃下找到并選擇這些對象：點選【确定】關閉【查找對象】對話框。注意你選擇的對象現在列在【選擇對象】對話框裡，在分号隔開的清單裡，如插圖4.6所示。點選【确定】關閉這個對話框來儲存你的修改。
    • HumanResources.Employee 表
    • HumanResources.JobCandidate 表
    • HumanResources.uspUpdateEmployeeHireInfo 存儲過程
    • HumanResources.uspUpdateEmployeePersonalInfo 存儲過程

    • Person.Address 表

      當你完成表和存儲過程選擇時，對話框應該如插圖4.5所示。（為了顯示所有選擇項目，我把對話框的高度調高了）。

      SQL Server安全（4/11）：許可（Permissions）
      插圖4.5：選擇配置設定權限的存儲過程和表
  7. SQL Server安全（4/11）：許可（Permissions）
    插圖4.6：用來配置設定許可的選擇對象結果
  8. 選擇對于DataEntry資料庫橘色屬性對話框列出了你選擇的安全對象和每個可用許可。DataEntry角色成員需要能這些表裡插入和修改資料，是以一次選擇一個表，在對話框的底部，為這些在許可選擇授予列點選複選框。對于HumanResources.Employee表，選擇的情況如插圖4.7所示。
    SQL Server安全（4/11）：許可（Permissions）

    插圖4.7：在表上選擇插入和更新許可

    不要和【具有授予權限】弄混。【授予】允許特定許可，【具有授予權限】允許使用者或角色授予許可到其它主體。小心錯發了【具有授予權限】許可。

  9. 對于每個存儲過程，授予【執行】許可和拒絕【檢視定義】許可。插圖4.8顯示了對于useUpdateEmployeeHireInfo存儲過程的選擇情況。
    SQL Server安全（4/11）：許可（Permissions）
    插圖4.8：對于useUpdateEmployeeHireInfo存儲過程授予【執行】，拒絕【檢視定義】許可。
  10. 在【資料庫角色屬性】對話框裡點選【确定】儲存你的修改，送出它們到資料庫。取決于對象個數和你選擇的許可，這會花上一會。

當然，你同樣可以使用T-SQL代碼來建立對象和配置設定許可。代碼4.2建立了DataEntry角色，配置設定使用者TudouZ給它，授予和拒絕了你使用對話框一樣的許可。

1 -- Create the DataEntry role and assign Topaz to it
 2 CREATE ROLE [DataEntry] AUTHORIZATION [dbo];
 3 ALTER ROLE [DataEntry] ADD MEMBER [TudouZ];
 4 
 5 -- Assign permissions to the DataEntry role
 6 GRANT INSERT ON [HumanResources].[Employee] TO [DataEntry];
 7 GRANT UPDATE ON [HumanResources].[Employee] TO [DataEntry];
 8 GRANT INSERT ON [HumanResources].[JobCandidate] TO [DataEntry];
 9 GRANT UPDATE ON [HumanResources].[JobCandidate] TO [DataEntry];
10 GRANT INSERT ON [Person].[Address] TO [DataEntry];
11 GRANT UPDATE ON [Person].[Address] TO [DataEntry];
12 GRANT EXECUTE ON [HumanResources].[uspUpdateEmployeeHireInfo] TO [DataEntry];
13 DENY VIEW DEFINITION ON [HumanResources].[uspUpdateEmployeeHireInfo] TO [DataEntry];
14 GRANT EXECUTE ON [HumanResources].[uspUpdateEmployeePersonalInfo] TO [DataEntry]
15 DENY VIEW DEFINITION ON [HumanResources].[uspUpdateEmployeePersonalInfo] TO [DataEntry];
16 GO

代碼4.2：配置設定新的角色和配置設定許可的代碼

檢查和測試許可

如果你想檢查下DataEntry角色擁有的許可，你可以使用SSMS裡的圖形工具或執行通路資料庫對象中繼資料的T-SQL代碼。使用SSMS，在對象浏覽器裡展開AdventureWorks2012資料庫，展開【安全性】【角色】【資料庫角色】。找到DataEntry角色（沒看到的話重新整理下），右擊角色，從彈出菜單裡選擇【屬性】。這個打開和用來建立角色一樣的【資料庫角色屬性】對話框，你可以使用【安全對象】頁來檢視角色的許可。

或者你可以使用代碼4.3的代碼來檢視DataEntry角色的許可，使用sys.database_permissions，sys.database_principals安全類别視圖和sys.objects類别視圖。

1 SELECT DB_NAME() AS 'Database', p.name, p.type_desc, dbp.state_desc, 
2     dbp.permission_name, so.name, so.type_desc
3 FROM sys.database_permissions dbp 
4     LEFT JOIN sys.objects so ON dbp.major_id = so.object_id 
5     LEFT JOIN sys.database_principals p ON dbp.grantee_principal_id = p.principal_id 
6 WHERE p.name = 'DataEntry'
7 ORDER BY so.name, dbp.permission_name;

代碼4.3：對DataEntry角色檢視許可中繼資料的代碼

當你運作這個代碼，你會看到如插圖4.9所示的結果。

SQL Server安全（4/11）：許可（Permissions）

插圖4.9：對于DataEntry角色的許可中繼資料

你可以打開新的SSMS執行個體裡的圖形界面測試這些許可，以TudouZ登入（如果你使用這篇或第2篇的代碼，密碼是

yBqyZIPT8}b]b[{5al0v

)）。然後嘗試在HumanResources.Employee, HumanResources.JobCandidate, 或Person.Address表裡插入新的記錄或更新現有的記錄，運作下你配置設定執行許可的2個存儲過程之一。這些操作應該會成功。然後在别的表嘗試插入或更新資料，這些操作會失敗。你應該隻能進行你授權的操作。

當然你也可以在T-SQL代碼裡進行同樣的測試，使用如清單4.4的代碼。這個代碼首先設定運作的上下文為TudouZ，它是DataEntry角色的成員。然後它在Person.Address表裡插入新行。這個操作會成功，因為DataEntry有INSERT許可。然後代碼嘗試在HumanResources.Department表裡插入新行，這個操作會失敗，因為DataEntry在那個表上沒有INSERT許可。接下來這個代碼成功執行了HumanResources.uspUpdateEmployeePersonalInfo存儲過程，因為在那個存儲過程上DataEntry有執行許可。執行dbo.uspGetManagerEmployees失敗，因為DataEntry沒有運作那個代碼的許可。最後代碼傳回執行的上下文為你登入SSMS資料庫的樣子。

1 EXECUTE AS USER = 'TudouZ';
 2 
 3 -- Succeeds
 4 INSERT INTO Person.Address
 5     (AddressLine1, City, StateProvinceID, PostalCode)
 6 VALUES
 7     ('8 Hazelnut', 'Irvine', 9, '92602');
 8 GO
 9 
10 -- Fails
11 INSERT INTO HumanResources.Department
12     (Name, GroupName)
13 VALUES
14     ('Advertising', 'Sales and Marketing');
15 GO
16 
17 -- Succeeds (doesn't actually change any data)
18 DECLARE @RC INT;
19 EXECUTE @RC = HumanResources.uspUpdateEmployeePersonalInfo 
20    1, '295847284', '1963-03-02', 'S', 'M';
21 GO
22 
23 -- Fails
24 EXECUTE dbo.uspGetManagerEmployees 1;
25 GO
26 
27 REVERT;

代碼4.4：測試DataEntry角色許可的代碼

小結

在整個SQL Server不同類别的主體、安全對象上能配置設定顆粒的許可的能力，會讓你很好的控制SQL Server的安全。它讓你充分利用最小特權的安全原則，限制主體的許可進行操作，最小化的通路你的資料，能完成它們的工作，剛剛好。

原文連結：

http://www.sqlservercentral.com/articles/Stairway+Series/117128/

注：此文章為

WoodyTu

學習MS SQL技術，收集整理相關文檔撰寫，歡迎轉載，請在文章頁面明顯位置給出此文連結！

若您覺得這篇文章還不錯請點選下右下角的推薦，有了您的支援才能激發作者更大的寫作熱情，非常感謝！

delete SQL Server安全 sql server 2012 VIEW DEFINITION update TAKE OWNERSHIP select insert user&amp or IMPERSONATE &amp gt login&amp LT amp IMPERSONATE &amp alter create control 許可
上一篇: SQL Server安全（5/11）：架構與安全（Schemas and Security）
下一篇: SQL Server 2016的資料庫範圍内的配置

繼續閱讀