管理SQL Server 2008 數據庫角色

• 2020 年 4 月 2 日
• 筆記

角色是SQL Server 2008用來集中管理數據庫或者服務器的權限。數據庫管理員將操作數據庫的權限賦予角色。然後，數據庫管理員再將角色賦給數據庫用戶或者登錄賬戶，從而使數據庫用戶或者登錄賬戶擁有了相應的權限。 

1  固定服務器角色

為便於管理服務器上的權限，SQL Server提供了若干「角色」，這些角色是用於分組其他主體的安全主體。「角色」類似於Microsoft Windows操作系統中的「組」。

服務器級角色也稱為「固定服務器角色」，因為不能創建新的服務器級角色。服務器級角色的權限作用域為服務器範圍。可以向服務器級角色中添加SQL Server登錄名、Windows賬戶和Windows組。固定服務器角色的每個成員都可以向其所屬角色添加其他登錄名。

用戶可以指派給這8個服務器角色之中的任意一個角色。下面將分別介紹這8個服務器角色：

　　　　sysadmin  這個服務器角色的成員有權在SQL Server 2008中執行任何任務。不熟悉SQL Server 2008的用戶可能會意外地造成嚴重問題，所以給這個角色批派用戶時應該特別小心。通常情況下，這個角色僅適合數據庫管理員（DBA）。

　　　　securityadmin  這個服務器角色的成員將管理登錄名及其屬性。他們可以GRANT、DENY和REVOKE服務器級權限。也可以GRANT、DENY和REVOKE數據庫級權限。另外，他們可以重置SQL Server 2008登錄名的密碼。

　　　　serveradmin  這個服務器角色的成員可以更改服務器範圍的配置選項和關閉服務器。比如SQL Server 2008可以使用多大內存或者關閉服務器，這個角色可以減輕管理員的一些管理負擔。

　　　　setupadmin  這個服務器角色的成員可以添加和刪除鏈接服務器，並且也可以執行某些系統存儲過程。

　　　　processadmin  SQL Server 2008能夠多任務化，也就是說，他可以通過執行多個進程做多件事件。例如，SQL Server 2008可以生成一個進程用於向高速緩存寫數據，同時生成另一個進程用於從高速緩存中讀取數據。這個角色的成員可以結束（在SQL Server 2008中稱為刪除）進程。

　　　　diskadmin  這個服務器角色用於管理磁盤文件，比台鏡像數據庫和添加備份設備。這適合於助理DBA。

　　　　dbcreator  這個服務器角色的成員可以創建、更改、刪除和還原任何數據庫。這不僅是適合助理DBA的角色，也可能是個適合開發人員的角色。

　　　　bulkadmin  這個服務器角色的成員可以運行BULK INSERT語句。這條語句允許他們從文本文件中將數據導入到SQL Server 2008數據庫中。

在SQL Server 2008中可以使用系統存儲過程對固定服務器角色進行相應的操作，表9-3就列出了可以對服務器角色進行操作的各個存儲過程。

表3  使用服務器角色的操作

例如，想要查看所有的固定服務器角色，就可以使用系統存儲過程sp_helpsrvrole，具體的執行過程及結果如圖16所示。

16  查看固定服務器角色

下面將運用上面介紹的知識，將一些用戶指派給固定服務器角色，進而分配給他們相應的管理權限。具體步驟如下所示：

（1）打開SQL Server Management Studio，在【對象資源管理器】窗口，展開【安全性】節點，然後再展開【服務器角色】節點。

（2）雙擊sysadmin節點，打開【服務器角色屬性】節點，然後單擊【添加】按鈕，打開【選擇登錄名】窗口。

（3）單擊【瀏覽】按鈕，打開【查找對象】對話框，啟用shop_Manage選項旁邊的複選框，如圖17所示。

17  添加登錄名

（4）單擊【確定】按鈕返回到【選擇登錄名】對話框，就可以看到剛剛添加的登錄名shop_Manage，如圖18所示。

18  【選擇登錄名】對話框

（5）單擊【確定】按鈕返回【服務器角色屬性】窗口，在角色成員列表中，就可以看到服務器角色sysadmin的所有成員，其中包括剛剛添加的shop_Manage，如圖19所示。

19  【服務器角色屬性】窗口

（6）用戶可以再次通過【添加】按鈕添加新的登錄名，也可以通過【刪除】按鈕刪除某些不需要的登錄名。

（7）添加完成後，單擊【確定】按鈕關閉【服務器角色屬性】窗口。

2  固定數據庫角色

固定數據庫角色存在於每個數據庫中，在數據庫級別提供管理特權分組。管理員可將任何有效的數據庫用戶添加為固定數據庫角色成員。每個成員都獲得應用於固定數據庫角色的權限。用戶不能增加、修改和刪除固定數據庫角色。

SQL Server 2008在數據庫級設置了固定數據庫角色來提供最基本的數據庫權限的綜合管理。在數據庫創建時，系統默認創建了10個固定數據庫角色，下面將分別介紹這幾個固定數據庫角色：

　　　　db_owner  進行所有數據庫角色的活動，以及數據庫中的其他維護和配置活動。該角色的權限跨越所有其他的固定數據庫角色。

　　　　db_accessadmin  這些用戶有權通過添加或者刪除用戶來指定誰可以訪問數據庫。

　　　　db_securityadmin  這個數據庫角色的成員可以修改角色成員身份和管理權限。

　　　　db_ddladmin  這個數據庫角色的成員可以在數據庫中運行任何數據定義語言(DDL)命令。這個角色允許他們創建、修改或者刪除數據庫對象，而不必瀏覽裏面的數據。

　　　　db_backupoperator  這個數據庫角色的成員可以備份該數據庫。

　　　　db_datareader  這個數據庫角色的成員可以讀取所有用戶表中的所有數據。

　　　　db_datawriter  這個數據庫角色的成員可以在所有用戶表中添加、刪除或者更改數據。

　　　　db_denydatareader  這個服務器角色的成員不能讀取數據庫內用戶表中的任何數據，但可以執行架構修改（比如在表中添加列）。

　　　　db_denydatawriter  這個服務器角色的成員不能添加、修改或者刪除數據庫內用戶表中的任何數據。

　　　　public  在SQL Server 2008中每個數據庫用戶都屬於public數據庫角色。當尚未對某個用戶授予或者拒絕對安全對象的特定權限時，則該用戶將繼承授予該安全對象的public角色的權限。這個數據庫角色不能補刪除。

在SQL Server 2008中可以使用Transact-SQL語句對固定數據庫角色進行相應的操作，表9-4就列出了可以對服務器角色進行操作的系統存儲過程和命令等。

表4  數據庫角色的操作

例如，使用系統存儲過程sp_helpdbfixedrole就可以返回固定數據庫角色的列表，如圖20所示。

20 查看固定數據庫角色

提示：由於所有數據庫用戶都自動成為public數據庫角色的成員，因此給這個數據庫角色指派權限時需要謹慎。

下面通過將用戶添加到固定數據庫角色中來配置他們對數據庫擁有的權限，具體步驟如下所示：

（1）打開SQL Server Management Studio，在【對象資源管理器】窗口，展開【數據庫】節點，然後再展開數據庫【網店購物系統】節點中的【安全性】節點。

（2）接着展開【角色】節點，然後再展開【數據庫角色】節點，雙擊db_owner節點，打開【數據庫角色屬性】窗口。

（3）單擊【添加】按鈕，打開【選擇數據庫用戶或角色】對話框，然後單擊【瀏覽】按鈕打開【查找對象】對話框，選擇數據庫用戶admin，如圖21所示

21  添加數據庫用戶

（4）單擊【確定】按鈕返回【選擇數據庫用戶或角色】對話框。如圖22所示。

22  【選擇數據庫用戶或角色】對話框

（5）單擊【確定】按鈕，返回【數據庫角色屬性】窗口，在這裡可以看到當前角色擁有的架構以及該角色所有的成員，其中包括剛添加的數據庫用戶admin，如圖23所示。

23  【數據庫角色屬性】窗口

（6）添加完成後，單擊【確定】按鈕關閉【數據庫角色屬性】窗口。

3  應用程序角色

應用程序角色是一個數據庫主體，他使應用程序能夠用其自身的、類似用戶的特權來運行。使用應用程序角色，可以只允許通過特定應用程序連接的用戶訪問特定數據。與數據庫角色不同的是，應用程序角色默認情況下不包含任何成員，而且不活動。應用程序角色使用兩種身份驗證模式，可以使用sp_setapprole來激活，並且需要密碼。因為應用程序角色是數據庫級別的主體，所以他們只能通過其他數據庫中授予guest用戶賬戶的權限來訪問這些數據庫。因此，任何已禁用guest用戶賬戶的數據庫對其他數據庫中的應用程序角色都不可訪問。

創建應用程序角色的過程與創建數據庫角色的過程一樣，圖24為應用程序角色的創建窗口。

24  創建應用程序角色

應用程序角色和固定數據庫角色的區別有如下4點：

　　　　應用程序角色不包含任何成員。不能將Windows組、用戶和角色添加到應用程序角色。

　　　　當應用程序角色被激活以後，這次服務器連接將暫時失去所有應用於登錄賬戶、數據庫用戶等的權限，而只擁有與應用程序相關的權限。在斷開本次連接以後，應用程序失去作用。

　　　　默認情況下，應用程序角色非活動，需要密碼激活。

　　　　應用程序角色不使用標準權限。

4  用戶自定義角色

有時，固定數據庫角色可能不滿足需要。例如，有些用戶可能只需數據庫的「選擇」、「修改」和「執行」權限。由於固定數據庫角色之中沒有一個角色能提供這組權限，所以需要創建一個自定義的數據庫角色。

在創建數據庫角色進，先給該角色指派權限，然後將用戶指派給該角色；這樣，用戶將繼承給這個角色指派的任何權限。這不同於固定數據庫角色，因為在固定角色中不需要指派權限，只需要添加用戶。創建自定義數據庫角色的步驟如下所示：

（1）打開SQL Server Management Studio，在【對象資源管理器】窗口，展開【數據庫】|【網店購物系統】|【安全性】|【角色】節點，右擊【數據庫角色】節點從彈出菜單中選擇【新建數據庫角色】命令，打開【數據庫角色-新建】窗口。

（2）設置角色名稱為TestRole，所有者選擇dbo，單擊【添加】按鈕，選擇數據庫用戶admin，如圖25所示。

25  【數據庫角色-新建】窗口

（3）選中【安全對象】選項，打開【安全對象】選項頁面，通過單擊【搜索】按鈕，添加「商品信息」表為「安全對象」，選中【選擇】後面【授予】列的複選框，如圖26所示。

26  為角色分配權限

（4）單擊【列權限】按鈕，還可以為該數據角色配置表中每一列的具體權限，如圖27所示。

27  分配列權限

（5）具體的權限分配完成後，單擊【確定】按鈕創建這個角色，並返回到SQL Server Management Studio。

（6）關閉所有程序，並重新登錄為admin。

（7）展開【數據庫】|【網店購物系統】|【表】節點，可以看到表節點下面只顯示了擁有查看權限的【商品信息】表。

（8）由於在【列權限】窗口設置該角色的權限為：不允許查看【商品信息】表中的「商品價格」列，那麼在查詢視圖中輸入下列語句將出現錯誤，如圖29所示。

29 使用SELECT語句驗證權限