資料庫的安全管理

1. 第 6 章 資料庫的安全管理

2. 本章重點 • 6-1 資料庫的使用者 • 6-2 資料庫的角色 • 6-3 設定存取權限 • 6-4 權限設定的考量 • 6-5 將資料庫加密

3. 6-1 資料庫的使用者 • 在前一章提過, SQL Server 是利用使用者(User) 物件, 讓登入帳戶取得資料庫的使用權, 例如若我們想要存取 master 資料庫的話, 先決條件就是我們所用的登入在 master 資料庫中有對應的使用者。 • 前一章在介紹新增登入時, 曾稍微提到設定資料庫使用者的方法, 本節將延續這方面的主題, 但改從 『在資料庫中設定使用者』 為出發點, 並進一步說明使用者、結構描述與存取權限的設定方式。

4. 特殊的資料庫使用者 • 根據前一章所述, 資料庫使用者是用來賦予登入帳戶存取資料庫權利, 所以一般是在有需要時才會去建立使用者物件, 不過有 2 個特別的使用者是在建立新資料庫時就會產生的, 那就是 dbo 及 guest (如果您的 model 資料庫中還有其它自訂的使用者, 它們也會出現在新的資料庫中)。 • dbo 是資料庫擁有者 (database owner) 的意思, 建立資料庫的人理應就是資料庫擁有者, 因此任何人在建立資料庫時, 他就自然地成為該資料庫的 dbo 了 (當然得先有建立資料庫的權限才行)。

5. 特殊的資料庫使用者 • 除了 dbo 之外, 還有一個特別的使用者, 那就是 guest, 它和 Windows Server 2003/2008 中的 guest 帳戶相似, 都是提供『未經授權』的使用者某種程度的存取權限, 也就是說如果資料庫中有個 guest 的使用者, 則所有登入 SQL Server 的人, 都可存取這個資料庫。 • 雖然我們曾說過, 必須是資料庫的使用者才有存取資料庫的權利, 不過當資料庫中存有 guest 使用者時則是個例外。

6. 特殊的資料庫使用者 • 由於 model 資料庫中就有 guest 使用者, 所以每個資料庫預設都會有 guest 使用者。 • 但基於安全性的考量, guest 預設是被設為已停用的狀態, 因此無需擔心任何人可透過 guest 存取所有的資料庫的問題。 • 但基於 SQL Server 本身運作的考量, master 及 tempdb 資料庫的 guest 使用者則是被啟用的, 且無法將之設為停用。 • 為配合 sys.* 、INFORMATION_SCHEMA.* 這兩組系統檢視表, 各資料庫中預設也會有 sys、INFORMATION_SCHEMA 這兩個使用者, 建議不要更動這 2 個內建的使用者。

7. 特殊的資料庫使用者 • 如果您想啟用特定資料庫的 guest 使用者, 可使用 GRANT CONNECT 語法, 例如以下就是將 AdventureWorks 資料庫中的 guest 設為可使用的語法：

8. 特殊的資料庫使用者 • 雖然透過 guest 可讓大家都能存取到資料庫, 但基於資料安全的考量, 通常我們都需對不同的人設定不同的資料存取權限, 此時仍需透過資料庫使用者並搭配資料庫角色來做更具彈性的安全管理, 以下先介紹如何在 Management Studio 中建立新的資料庫使用者。

9. 建立與管理使用者 • 要在資料庫中建立新的使用者, 請在 Management Studio 中展開該資料庫, 並在安全性上按右鈕執行『新增/使用者』命令, 就會出現如下的交談窗。

10. 建立與管理使用者

11. 建立與管理使用者 • 在交談窗最上方的使用者名稱欄要輸入的就是新建使用者的名稱, 通常會將使用者名稱設成與登入名稱相同；在登入名稱欄則要輸入此使用者要對應到哪一個登入。 • 請注意, 若要輸入 Windows 驗證的登入, 則要以『網域或電腦名稱\帳戶名稱』的格式輸入。 • 交談窗中間則是與結構描述相關的設定, 首先是預設結構描述, 此欄若不輸入, 則此使用者的預設結構描述為 dbo。 • 在中間的清單則會列出資料庫中現存的所有結構描述, 我們可在此勾選要讓此新建使用者擁有的結構描述。

12. 建立與管理使用者 • 在交談窗下方的則是設定此使用者所屬的角色, 您可視需要選取一或多個角色, 例如若要讓此使用者可查詢所有的資料表, 可勾選 db_databasereader 這個角色, 至於各角色的功用留待在下一節再做說明。 • 若您未在此欄選擇要讓使用者加入的角色, 預設所有的使用者都會屬於 public 角色 (未列於交談窗中), 也就是說使用資料庫的每個人至少都會是 public 角色的成員。 • 登入名稱、使用者名稱、結構描述、和角色這三項都設定好了, 就可按確定鈕建立此使用者。

13. 建立與管理使用者 • 您可在資料庫的安全性/使用者項目下看到您所建立的使用者：

14. 建立與管理使用者 • 只要依如上的步驟, 就能在各資料庫中為必要的登入帳號建立使用者物件, 讓使用者能存取資料庫的內容。 • 提醒各位, 在建立新使用者物件時, 若未更改存取權限設定, 預設每個人都是 public 角色, 此角色預設並無資料的存取權限, 不過我們可自行修改 public 的設定, 讓它具有必要的基本權限。 • 關於如何設定角色的存取權限, 請參見稍後的說明。

15. 群組使用者 • 大家應該還記得在建立 Windows 驗證的登入帳戶時, 可使用群組帳戶。 • 若使用這個群組帳戶的登入帳戶來建立資料庫使用者, 就代表該群組中的成員都可存取該資料庫, 利用這種方法, 管理者就可在很短的時間內為眾多使用者建立必要的資料庫存取權限設定了。

16. 修改與刪除使用者 • 若日後要修改使用者的設定, 可在 Management Studio 中展開資料庫下的使用者項目, 然後雙按使用者名稱, 就會開啟如前的交談窗, 讓我們修改該使用者的各項設定。

17. 修改與刪除使用者

18. 修改與刪除使用者 • 若要刪除使用者, 只需在 Management Studio 中選取使用者後, 再按 [Delete] 鍵即可, 但要注意刪除使用者和刪除登入的限制類似, 凡是在該資料庫中擁有任何物件的使用者都不能被刪除。

19. 使用 T-SQL 語法及系統預存程序 • 我們也可用 T-SQL 語法及系統預存程序進行使用者的相關設定。要建立使用者, 可使用 CREATE USER 敘述： • 若省略 FOR LOGIN (或 FROM LOGIN) 子句時, 則是建立對應到同名登入的使用者。

20. 使用 T-SQL 語法及系統預存程序 • 若要指定 Windows 驗證的登入, 仍是要用『網域或電腦名稱\帳戶名稱』的格式；WITH DEFAULT_SCHEMA 子句則可設定預設結構描述。 • 例如以下就是為 Windows 驗證的登入 Flag\Ken 在 AdventureWorks2008 資料庫建立對應的同名使用者, 並將其預設結構描述設為 Sales：

21. 使用 T-SQL 語法及系統預存程序 • 若要刪除使用者則可用 DROP USER 敘述： • 另外還有個可用來顯示使用者資訊的預存程序： • 如果未指定使用者名稱, 則會顯示資料庫中所有使用者的資訊, 如下圖所示。

22. 使用 T-SQL 語法及系統預存程序 • sp_helpuser 不止可用來查詢使用者資訊, 如果以角色名稱為參數, 也可查詢目前屬於該角色的使用者, 此部份請參見下一節的說明。

23. 6-2 資料庫的角色 • 當我們的登入帳戶在資料庫中有對應的使用者物件, 就表示我們具備存取該資料庫的基本資格, 但可做什麼樣的存取則需透過存取權限的設定來決定。 • 前一章說過, 我們可對每位使用者做個別的存取權限設定, 但使用角色來設定是比較方便的作法, 所以本節先來介紹使用系統內建角色來設定存取權限的方法, 下一節再來詳細介紹個別存取權限的設定。

24. 角色的種類 • 我們已提過資料庫角色相當於擁有特定存取權限的群組, 凡是可扮演該角色的資料庫使用者, 就具有和該角色相同的存取權限。在資料庫中的角色有下列幾種： • 內建角色：這些角色和伺服器角色一樣, 都是系統內建的, 也就是在建立資料庫時, 就會有這些角色, 我們無法將它們刪除, 也不能更改其存取權限設定 (只有pubic 角色可修改)。內建角色有十個, 分別簡介如下： • db_accessadmin：可建立和管理資料庫使用者。 • db_backupoperator：可備份資料庫。

25. 角色的種類 • db_datareader：可讀取全部的資料庫物件, 也就是可對物件做 SELECT 動作。 • db_datawriter：可寫入所有的資料庫物件, 也就是可對物件做 DELETE、INSERT 、和 UPDATE 動作。 • db_ddladmin：可使用 DDL (Data Definition Language) 語言, 也就是可建立物件、修改資料庫物件的設計, 但不含 GRANT、DENY、和 REVOKE 敘述的使用權限。 • db_denydatareader：禁止讀取資料庫物件, 也就是不能對物件做 SELECT 動作。

26. 角色的種類 • db_denydatawriter：禁止寫入資料庫物件, 也就是不能對物件做 DELETE、INSERT 、或 UPDATE 動作。 • db_owner：資料庫擁有者, 資料庫使用者 dbo 就是此角色的成員, 可對資料庫做所有的動作。 • db_securityadmin：可新增、移除資料庫角色和管理角色中的成員, 也可做各類存取權限的設定 。 • public：這個角色比較特別, 前面說過, 每位使用者都會屬於這個角色, 此角色預設並無任何資料存取權限, 必須由我們自行設定之。在所有的內建角色中, 只有 public 可以讓我們自訂角色的存取權限。

27. 角色的種類 • 自訂角色：雖然我們不能自行建立新的伺服器角色, 不過卻可以自行建立新的資料庫角色, 稍後我們就會介紹如何建立資料庫角色。 • 應用程式角色：其實這一項算是自訂角色中的一類, 只不過這種角色是專供應用程式用的, 而不是給『人』用的。在某些環境下, 我們會讓權限不夠的使用者, 可執行某特殊的應用程式來讀取 SQL Server 中的資料, 為了讓這些人能順利讀到資料, 我們就可設定有足夠權限的應用程式角色給應用程式使用, 一般使用者不能成為應用程式角色的成員。這種讓使用者只能透過特定的程式來存取資料庫的方式, 也可算是一種安全管理方法。

28. 角色的種類 • 當使用者成為某角色的成員後, 使用者就具有該角色的權限, 但在 SQL Server 中, 不只是資料庫使用者能做為角色的成員, 其實非系統內建的角色也可以是角色的成員。 • 例如若我們建立了一個名為 myrol e 的自訂角色, 我們可將它加到 db_datareader 和 db_datawriter 之中, 使 myrole 角色具有資料庫讀取及寫入的存取權限, 當然 myrole 的成員也就自動取得相同的權限。

29. 角色的種類 • 不過將角色加到其它角色時有兩種限制, 首先就是系統內建角色是不能作為其它角色的成員, 因為如此就違背了內建角色的存取權限不能更改的限制, 不過自訂角色倒是可作為內建角色的成員。 • 其次是不能有互相隸屬的關係, 例如甲角色已經是乙角色的成員, 我們就不能再將乙角色設為甲角色的成員。

30. 檢視及新增角色 • 在一般情況下, 使用內建的角色大概就足夠了, 只有需要設定特殊的存取權限組合時, 才需建立自訂角色。 • 因此在討論建立自訂角色之前, 我們先來看如何檢視各角色目前的設定, 以及將使用者加入角色中。 • 在 Management Studio 中展開資料庫下的安全性/角色/資料庫角色項目, 即可看到此資料庫所有的角色物件, 雙按任一角色即可開啟其內容交談窗。

31. 檢視及新增角色

32. 檢視及新增角色

33. 檢視及新增角色 • 當選取 public 或已建立好的自訂角色時, 交談窗中還會有另一個安全性實體的頁面可設定角色的存取權限, 此部分我們稍後再介紹。 • 如果要新增角色, 可在 Management Studio 中, 於角色項目上按滑鼠右鈕開啟快顯功能表, 然後執行『新增/新增資料庫角色』或『新增/新增應用程式角色』命令, 就會開啟和上面一樣的交談窗, 只是各欄位都是空白的。

34. 檢視及新增角色

35. 檢視及新增角色 • 在組織中建立角色時, 通常可考慮根據組織中所有成員的職級結構, 來建立對應的角色。 • 例如在橫向的組織方面, 可建立屬於會計部、人事部等各部門的角色, 在垂直方向上, 則可建立經理級、主任級、一般職員級等等的角色, 然後再為這些不同的角色設定適當的存取權限。 • 詳細的存取權設定, 會在下一節介紹, 以下先說明如何用 T-SQL 語法及系統預存程序來做角色的管理。

36. 使用 T-SQL 語法及系統預存程序 • 在 T-SQL 語法及系統預存程序中也有許多是與角色相關的, 分別介紹如下。 • 建立角色 • 設定角色成員 • 查詢角色資訊 • 移除角色

37. 建立角色 • 要在目前資料庫中建立新角色可使用 CREATE ROLE 敘述： • 若未指定擁有者時, 預設的擁有者為 dbo。 • 要建立應用程式角色時, 則需使用 CREATE APPLICATION ROLE 敘述：

38. 設定角色成員 • 要將使用者加入角色之中, 可使用系統預存程序 sp_addrolemember ： • 如前所述, 除了資料庫使用者外, 角色本身也可成為另一個角色的成員, 下列就是將自訂的 myrole 角色設為 db_accessadmin 的成員：

39. 設定角色成員 • sp_addrolemember 有個特別之處, 就是 @membername 參數也可指定為 Windows 驗證的登入名稱, 此時若該登入在目前資料庫尚無對應的使用者, 系統將會自動為該登入建立對應的使用者, 同時將該使用者加入指定的角色中。

40. 查詢角色資訊 • 若要查看角色的資料, 可使用 sp_helprole 和 sp_helprolemember： • sp_helprole 會列出角色的名稱、識別碼及是否為應用程式角色。 • 而sp_helprolemember 則會列出該角色所有的成員 (使用者名稱) 及各成員的物件識別碼。

41. 查詢角色資訊 • 另外還有個 sp_helpdbfixedrole 系統預存程序, 其用法和前兩者類似, 不過它是用來列出內建角色的名稱和描述。 • 這三個系統預存程序在不加參數時, 都會列出所有的角色、角色的所有成員、和所有的內建角色, 例如。

42. 查詢角色資訊

43. 查詢角色資訊 • 雖然是列出所有的內建角色, 但卻不會列出 public。同理, 以 'public' 為參數執行 sp_helprolemember 時, 也不會列出 public 的成員, 因為預設所有使用者都是public 角色的成員。 • 以下就是查詢 db_datawriter 成員的情形：

44. 查詢角色資訊 • 上一節提到過, sp_helpuser 除了可用來查看使用者資訊外, 也可用來查看角色資訊, 其輸出結果同樣是角色成員的清單, 但列出的欄位則和sp_helprolemember 有所不同：

45. 移除角色 • 若要將角色移除的話, 可使用下列的預存程序： • 相信這兩個敘述的用法應不用再加以說明了, 不過要再提醒大家的是, 內建角色是無法移除的；而當自訂角色中仍有成員, 也無法將該角色移除, 此時 sp_droprole會列出其所有成員的名稱。

46. 移除角色 • 要移除角色成員, 可用 sp_droprolemember ： • 再次提醒大家, 我們無法將任何使用者自 public 角色中移除。

47. 6-3 設定存取權限 • 到目前為止, 我們已經介紹了登入帳戶、使用者、和角色的設定方式, 但我們還未探究到 SQL Server 安全管理中的最底層, 也就是個別物件的存取權限設定。 • 在介紹各種存取權限設定方式之前, 我們先來看 SQL Server 有哪些不同的存取權限可供使用。

48. 存取權限的種類 • 如前一章所述, 我們可對資料庫中的使用者及自訂角色, 設定其對資料庫及結構描述層次安全性實體的存取權限。依安全性實體的種類不同, 可設定的權限種類也稍有不同。 • 其中幾乎所有安全性實體都可設定的權限有 ALTER 及 CONTROL 兩種：前者即為變更該物件 (例如變更檢視表的定義) 的權限, 但不能變更擁有權；CONTROL 意指控制權, 相當於所有的權限, 舉例來說, 資料庫擁有者即對資料庫有CONTROL 的權限。 • 各主要安全性實體可設定的其它權限簡列如下表。

49. 存取權限的種類

50. 權限的階層關係 • 前一章提過, 安全性實體的存取權限有其階層性的關係, 亦即, 當使用者擁有較上層級的安全性實體之存取權限時, 自然也擁有該實體範圍內的其它安全性實體的所有權限。 • 舉例來說, 當我們將 AdventureWorks 資料庫的 SELECT 權限授給使用者 Emily 時, 她即具有資料庫中所有資料表、檢視表、同義字等物件的 SELECT 權限；若只授與 Sales 這個結構描述的權限, 則可對 Sales 結構描述中的資料表及檢視表做 SELECT 動作。