第五篇 SQL Server安全架構和安全

標籤：

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

架構本質上是一個資料庫物件，其他對象的一個容器，在複雜的資料庫中它能夠很容易的管理各組對象。架構具有重要的安全功能。在這一篇你會學習如何給主體訪問一組對象，通過給架構分配許可權而不是單個表、代碼模組和其他對象。你還會瞭解使用者架構分離的好處，以及如何提高對象安全性，如何為使用者、組使用預設架構來簡化對象訪問管理和安全性。
Schemas with Roles and Permissions
The relationship of schemas with roles and permissions is an important security concept in SQL Server.一個完全合格的資料庫物件名稱由四部分組成：

server.database.schema.object

通常使用的架構和對象名稱來引用當前資料庫下的對象。一個架構是一個對象集合，如表、代碼模組，5.1所示。這種組織圖簡化了使用者管理，特別是當你需要改變對象的所有權時。但更重要的是為了安全，它簡化了許可權管理。

圖5.1 一個包含資料庫物件的樣本架構
你可以將許可權分配給架構應用於架構下的所有對象。例如，如果你將DogSchema架構的SELECT許可權授予給一個主體，這個主體將可以查詢DogSchema架構下的所有表格。和所有使用者定義的資料庫物件一樣，架構有一個所有者可以完全控制對象。
在架構中單獨設定對象許可權通常是一個選項，但如果你已經將資料庫中的架構設計好，在某些功能類型的資料庫中，你可以在架構上設定許可權，並將其應用到對象。最棒的是，你在架構上分配的許可權會自動應用到你添加到架構中的任何對象。繼續SELECT的例子，如果一年後你添加表DogTable4到架構DogSchema，所有對架構有SELECT許可權的主體都能自動的SELECT新表。
多個使用者和角色可以具有相同的預設架構，如果主體沒有預設架構，SQL Server會嘗試在dbo架構下尋找或建立對象。
現在，你將看到如何使用架構來指派至的許可權。使用以下步驟將Purchasing架構的查詢、更新、刪除、插入許可權授予給DataEntry使用者自訂角色。在第4篇建立了DataEntry角色；如果你沒有建立，請執行代碼5.1

USE AdventureWorks2012;GOCREATE ROLE DataEntry AUTHORIZATION dbo;

代碼5.1 在AdventureWorks2012資料庫建立DataEntry角色
然後按照下面步驟在SSMS使用圖形工具來分配所需的許可權
1、物件總管->資料庫->AdventureWorks2012->安全性->角色->資料庫角色->DataEntry
2、右擊DataEntry，快顯功能表選擇屬性。在資料庫角色屬性對話方塊選擇安全性實體。如果你遵循第4篇的步驟，你應該可以看到之前為角色指派過許可權的表和預存程序
3、點擊“搜尋”按鈕開啟“添加對象”對話方塊
4、在添加對象對話方塊，選擇“特定類型的所有對象”選項，5.2所示。點擊確定，開啟“選擇物件類型”對話方塊

圖5.2 添加對象
5、在“選擇物件類型”對話方塊中，向下滾動到“架構”項目，然後選擇旁邊的複選框。對話方塊應該看起來像圖5.3。單擊“確定”以儲存選擇並關閉對話方塊

圖5.3 選擇物件類型
6、返回到資料庫角色屬性對話方塊中，向下滾動安全性實體列表然後點擊Purchasing架構。頁面的下部顯示了可用的許可權
7、顯示頁簽為Purchasing架構勾選刪除、插入、選擇、更新授予複選框。資料庫角色屬性對話方塊如5.4所示

圖5.4 設定對Purchasing架構的存取權限
到此DataEntry角色下的所有成員對AdventureWorks2012庫Purchasing架構下的所有表都有選擇、更新、刪除和插入許可權。只有當該角色的成員被拒絕任何許可權時，才會出現異常。DENY阻止了他們通過角色繼承的許可權。
當然，你也可以使用TSQL指令碼將架構上的許可權授予給角色

GRANT DELETE ON SCHEMA::Purchasing TO DataEntry;GRANT INSERT ON SCHEMA::Purchasing TO DataEntry;GRANT SELECT ON SCHEMA::Purchasing TO DataEntry;GRANT UPDATE ON SCHEMA::Purchasing TO DataEntry;GO

代碼5.2 給Purchasing架構授權
這些技術表明你可以建立不同的架構，在每個架構中放置不同的對象，然後在架構上分配許可權。這節省了單個表上分配許可權的工作。如果你授予許可權給角色，正如我們對DataEntry角色的操作，你可以高效地對許多主體分配許可權。這讓你分割資料庫，簡化你的設計並實現資料庫的安全性。
預設架構
在SQL-99規範定義，架構本質上是一個資料庫的對象容器。它可以由一個主體擁有，5.5所示(同圖5.1)。使用架構作為資料庫物件容器的好處之一是：當Carol離開公司時，不需要改變Carol擁有的數百或數千個對象的所有權，管理員只需要改變這些架構的所有權，每個可能有成千上萬的對象。這種方法是更簡潔、更容易、更安全。

圖5.5 Carol擁有的DogSchema架構
SQL Server允許你為使用者和組分配預設的架構。設定預設架構是一種方便，並且具有一些重要的操作好處。特別是，當命名和訪問對象時它消除了一些歧義。
使用者預設架構
SQL Server不會自動建立具有相同名稱的架構當你建立一個使用者。你必須顯式地建立一個架構，分配架構所有者，然後建立和添加對象到該架構。你可以(通常應該)為使用者指派一個預設架構，這樣所有使用者建立的對象，如果沒有顯式地分配給另一個架構，都會成為預設架構的一部分。
本篇中的代碼顯示了所有的操作，當使用者沒有一個設定預設架構時會發生什麼情況。我會解釋每一步會發生什麼，但你可能想獨自執行代碼，以更好地瞭解發生了什麼事。當然，你自己試試吧！如果你想看看發生了什麼，執行每一個代碼塊。
代碼5.3做了示範所需的一些設定。你可能已經建立了第三篇的登入名稱carol，所以如果存在登入名稱carol首先刪除它。建立DefaultSchema資料庫並設定為當前資料庫，接著建立登入名稱carol，並映射到資料庫使用者，並授予它建立表的許可權。然後，它將執行內容更改為使用者carol。

IF suser_sid(‘carol‘) IS NOT NULL DROP LOGIN carol;GOCREATE DATABASE DefaultSchema;GOUSE DefaultSchema;GOCREATE LOGIN carol WITH PASSWORD = ‘crolPWD123%%%‘;CREATE USER carol FOR LOGIN carol;GRANT CREATE TABLE TO carol;--userEXECUTE AS LOGIN = ‘carol‘;GO

代碼5.3 建立DefaultSchema資料庫並設定carol使用者
接下來的代碼試圖建立一個新表table1，如代碼5.4所示。但之前的代碼建立carol使用者時並沒有給它分配預設架構。SQL Server嘗試使用dbo架構，這是預設的回退架構。但carol沒有資料庫的所有權，所以它不能在dbo架構中建立對象。

CREATE TABLE table1 (tID int);

代碼5.4 嘗試以carol上下文建立表
因為carol沒有必要的許可權，建立表語句失敗並返回錯誤資訊。
指定的架構名稱 "dbo" 不存在，或者您沒有使用該名稱的許可權。
代碼5.5REVERT到原來的管理員登入，然後建立一個架構，並將架構所有者設為carol使用者。你會在SQL Server中看到很多AUTHORIZATION子句，因為它可以讓你在建立或更改一個對象時分配所有權。

REVERT;CREATE SCHEMA DogSchema AUTHORIZATION carol;

代碼5.5 建立DogSchema架構
再次更改執行內容為carol，然後試圖再建立表table1，但它失敗了！現在的問題是，使用者擁有一個架構並不意味著它是使用者的預設架構。一個使用者可以擁有成百上千個架構，SQL Server沒有責任挑選一個作為預設架構。最終在建立表的時候顯示的包含架構才能通過。代碼5.6中明確地在DogSchema架構建立表

EXECUTE AS LOGIN = ‘carol‘;GOCREATE TABLE DogSchema.table1 (tID int);

代碼5.6 建立帶有顯示架構的表
如果DogSchema架構存在，第二種嘗試建立表的方式，就是在建立使用者的時候分配預設架構，或者後期修改使用者預設架構。如代碼5.7所示

CREATE USER carol FOR LOGIN carol WITH DEFAULT_SCHEMA = DogSchema;-- orALTER USER carol WITH DEFAULT_SCHEMA = DogSchema;--測試可以在carol身份下執行

代碼5.7 給使用者carol設定預設架構
如果你執行ALTER USER語句為carol設定預設架構，然後你可以執行代碼5.8成功建立表而不需指定架構。建立表的語句將建立一個DogSchema.table2表，因為DogSchema是carol的預設架構。

EXECUTE AS LOGIN = ‘carol‘;GOCREATE TABLE table2 (tID int);GOSELECT * FROM table2;REVERT;

代碼5.8 建立表table2不需指定架構
另一個有趣的現象是，當你使用REVERT語句返回到自己的安全上下文，你不能執行代碼5.9。除非你設定自己的預設架構為DogSchema，否則SQL Server將尋找dbo.table2

SELECT * FROM table2;

代碼5.9 在carol安全上下文之外運行會出錯
你需要顯式地使用架構來識別你想從中讀取資料的表，如代碼5.10。本代碼成功，並返回DogSchema.table2中的內容。

SELECT * FROM DogSchema.table2;

代碼5.10 指定架構執行SELECT語句
SQL Server使用者和架構分離可以嚴格控制你的資料庫和應用程式的安全結構。這使得它更容易管理一個資料庫和SQL Server。你不需要為每一個對象設定成dbo使用者所有者，這在2005之前版本很常見。
組預設架構
使用者的預設架構，在SQL Server 2005引進，解決了查詢、建立對象以及其他動作在正確的架構下使用正確的對象。但這些預設架構的存在一個問題，你可以很容易的在資料庫中建立大量的架構。Windows組的預設架構，在SQL Server 2012引進，解決了這些問題。
使用下面的步驟來探索使用者預設架構的潛在問題。步驟假定本地Windows有一個DBAs組，並且ClearFile使用者是DBAs組中的成員。你需要更改範例程式碼中的機器名稱。最後，DefaultSchema資料庫應該已經存在。
1、修改當前資料庫為DefaultSchema，代碼5.11首先建立一個Windows組的登入名稱，然後建立DataAdmins使用者映射到此登入名稱，接著建立DBAs角色並添加DataAdmins使用者到角色中。

USE DefaultSchemaGOCREATE LOGIN [USER-67NP5R8LGK\DBAs] FROM WINDOWS;CREATE USER DataAdmins FROM LOGIN [USER-67NP5R8LGK\DBAs];CREATE ROLE DBAs;ALTER ROLE DBAs ADD MEMBER DataAdmins;

代碼5.11 建立登入名稱、使用者、角色
2、授予對DogSchema架構的建立表和控制許可權給DBAs角色

GRANT CREATE TABLE TO DBAs;GRANT CONTROL ON SCHEMA::DogSchema TO DBAs;

代碼5.12 授予許可權給DBAs角色
3、用ClearFile身份運行另一個SSMS。在Windows啟動菜單按下SHIFT鍵，並且右擊SSMS。在彈出的菜單中選擇以其他使用者身份運行，鍵入ClearFile的使用者名稱和密碼
4、在串連到伺服器對話方塊使用Windows身分識別驗證，使用者名稱為ClearFile，5.6所示。點擊串連，就以ClearFile身份運行SSMS

圖5.6 以ClearFile身份登入SSMS
5、在剛開啟的SSMS建立查詢，可用資料庫切換到DefaultSchema資料庫
6、執行代碼5.13，成功建立了表table1，但它的架構是什麼呢？

CREATE TABLE table1 (tID int)

代碼5.13 使用ClearFile建立表table1
7、物件總管下DefaultSchema資料庫展開表、使用者、架構。5.7所示，前面語句建立的表叫USER-67NP5R8LGK\ClearFile.table1,資料庫使用者USER-67NP5R8LGK\ClearFile，架構USER-67NP5R8LGK\ClearFile

圖5.7 沒有預設架構時建立表的結果
8、回到你以管理員登入的SSMS，在物件總管下按順序刪除剛才建立的表、架構和使用者
9、還是在原SSMS，執行代碼5.14給使用者DataAdmins設定預設架構DogSchema

ALTER USER DataAdmins WITH DEFAULT_SCHEMA = DogSchema;

代碼5.14 為DataAdmins使用者認置預設架構
10、返回到ClearFile登入的SSMS，執行代碼5.15建立新表table3。這次代碼建立的表叫做DogSchema.table3，並且沒有添加ClearFile資料庫使用者，也沒有添加ClearFile架構

CREATE TABLE table3 (tID int)

代碼5.15 建立表table3
你也可以在資料庫使用者對話方塊設定預設架構，5.8所示

圖5.8 使用資料庫使用者對話方塊設定預設架構
SQL Server 2012增加了給組設定預設架構的功能，類似於給使用者設定預設架構解決問題，使安全管理更容易。對於同樣的原因，你建立沒有任何許可權的使用者，然後將它們添加到需要的許可權組中，你可以為組指定預設架構，而不是每個使用者。與使用者一樣，你可以通過create user或者alter user指定預設架構。
總結
架構在SQL Server管理資料庫對象上是一個很好的特徵，還提供了重要的安全功能。通過在一個架構上設定許可權，而不是它包含的單個對象，你可以更輕鬆地管理資料庫的許可權。這是特別重要的，當你有很多主體需要授予許可權時。
一定要給使用者和組分配預設架構，這樣就可以避免意外的對象建立，同時簡化代碼和資料庫維護。隨著對組設定預設架構的能力，微軟充實了架構安全效益。

第五篇 SQL Server安全架構和安全