6.4 服務器和數據庫角色
在7.0版之前，SQL Server有過組的概念——這是用戶權限的分組，你只需簡單地把用戶分配到組中，就能一次指派所有這些權限。這里的組與Windows中的組起作用的方式有很大不同，用戶能夠屬于多個Windows組，因此，可以根據需要混合搭配它們。在SQL Server 6.5（和更早的版本）中，每一個數據庫里，一個用戶只允許屬于一個組。
SQL Server 7.0之前版本的這種方式產生的后遺癥是，SQL Server組屬于以下3類之一：
l 經常根據用戶級別的許可權限對它們進行修改；
l 它們只是主要的組的微小變形；
l 它們擁有多于所需的訪問權限（以便使DBA的工作更為輕松）。
基本上，它們雖然很有必要，但同時也是一個很大的麻煩。
伴隨7.0版的出現，在這方面發生了一些很大的變化。現在，用戶屬于一個角色，而非一個組。在最一般的意義上，角色與組是相同的事物。
角色是一組訪問權限的集合，通過簡單地把用戶分配到那個角色中，就能將這一組訪問權限一起指派給用戶。
在這里，相似之處逐漸消失。使用角色時，用戶能夠一次屬于多個角色。由于能夠把訪問權限組織到更小的和更合理的組中，然后把它們混合搭配為最適合用戶的規則，這簡直令人難以置信的便利。
角色分為兩類：
l 服務器角色；
l 數據庫角色。
很快，我們還將看到第三種稱為角色的事物——應用程序角色，盡管我希望微軟選用另外的名字。這是一種特殊的方式，用來把用戶化名到不同的許可權限組中。應用程序角色不是分配用戶的，它是一種讓應用程序擁有的權限集不同于來自用戶的權限集的方法。由于這個原因，我通常不認為應用程序角色是真正意義上的“角色”。
服務器角色限制在那些當發布SQL Server時就已經建立于其中的角色，并且，它在這里主要是為了進行系統的維護以及授予完成非數據庫特有的事情的能力，如創建登錄賬戶和創建鏈接服務器。
與服務器角色很類似，這里有一定數目的內置（或“固定”）數據庫角色，不過，你也可以定義自己的數據庫角色，以滿足你獨特的需求。數據庫角色用來進行設置，以及在一個給定的數據庫中分組特定的用戶權限。
接下來，我們分別來看這兩種類型的角色。
6.4.1 服務器角色
所有的服務器角色都是“固定的”角色，并且，從一開始就存在于那里——自安裝完SQL Server的那一刻起，你將擁有的所有服務器角色就已經存在了。
|
角 色
|
特 性
|
|
sysadmin
|
該角色能夠執行SQL Server上的任何操作。本質上，任何具有這種角色成員身份的人都是那個服務器上的sa。這種服務器角色的創建為微軟提供了某一天去除sa登錄的能力——實際上，聯機叢書把sa稱作本質上為遺留物的東西
值得注意的是，在SQL Server上，Windows的Administrators組被自動映射到sysadmin角色中。這意味著服務器的Administrators組中的任何成員同時也具有對SQL數據的sa級別的訪問權限。如果需要，你可以從sysadmin角色中刪除Windows的administrators組，以提高安全性、防范漏洞
|
|
serveradmin
|
該角色能設置服務器范圍的配置選項或關閉服務器。盡管它在范圍上相當有限，但是，由該角色的成員所控制的功能對于服務器的性能會產生非常重大的影響
|
|
setupadmin
|
該角色僅限于管理鏈接服務器和啟動過程
|
|
securityadmin
|
對于專門創建出來用于管理登錄名、讀取錯誤日志和創建數據庫許可權限的登錄名來說，該角色非常便利。在很多方面，該角色是典型的系統操作員角色——它能夠處理多數的日常事務，但是，卻不具備一個真正無所不能的超級用戶所擁有的那種全局訪問
|
|
processadmin
|
能夠管理SQL Server中運行的進程——必要的話，該角色能夠終止長時間運行的進程
|
|
dbcreator
|
該角色僅限于創建和更改數據庫
|
|
diskadmin
|
管理磁盤文件（指派給了什么文件組、附加和分離數據庫，等等）
|
|
bulkadmin
|
該角色有些怪異。它被明確創建出來，用于執行BULK INSERT語句的權限，否則的話，只能由具有sysadmin權限的人來執行BULK INSERT語句。坦白地說，我不明白為什么該語句不能像其他事情那樣通過GRANT命令來授予權限，但它的確沒有。要記住，即使把一個用戶加入到了bulkadmin組中，也只是給了他們訪問那個語句的權限，對于運行該語句的表，并沒有授予用戶訪問那個表的權限。這意味著不僅需要把用戶添加到bulkadmin中，而且，對于想要用戶能在其上執行BULK INSERT的表，還要授予（GRANT）用戶INSERT許可權限。此外，對于將在BULK INSERT語句中引用的所有表，還要確保用戶擁有正確的到那些表的SELECT訪問權限
|
對于在服務器上承擔管理角色任務的單個用戶，你可以對其混合搭配這些角色。一般來說，我懷疑只有最大型的數據庫才會使用比sysadmin和securityadmin更多的角色，然而，有它們在旁邊還是很便利的。
在本章的前面，我曾就全能用戶會帶來的麻煩進行過抨擊。當新的sysadmin角色添加到7.0版時，我完全是欣喜若狂的，或許，得知此事你不會感到驚奇。sysadmin角色的存在表明，在不斷發展的基礎上，不再需要讓所有人都有sa登錄賬戶——只要讓需要擁有那種訪問級別的用戶成為sysadmin角色的成員，這樣他們就不再需要以sa登錄。
6.4.2 數據庫角色
數據庫角色限制在單個數據庫的范圍之內——用戶屬于一個數據庫中的db_datareader角色并不意味著他屬于另一個數據庫中的那個角色。數據庫角色分為兩個子類：固定數據庫角色和用戶定義數據庫角色。
1．固定數據庫角色
就如同存在若干個固定服務器角色一樣，這里也有許多的固定數據庫角色。他們中的一些有預先定義好的專門的用途，這是不能使用常規的語句復制出來的（即是說，你無法創建擁有同樣功能的用戶定義數據庫角色）。然而，大多數角色的存在是為了處理更一般的情形，并讓你做起事情來更加容易。
|
角 色
|
特 性
|
|
db_owner
|
該角色表現得就好像它是所有其他數據庫角色中的成員一樣。使用這一角色能夠造就這樣的情形：多個用戶可以完成相同的功能和任務，就好像他們是數據庫的所有者一樣
|
|
db_accessadmin
|
實現類似于securityadmin服務器角色所實現功能的一部分，只不過這一角色僅局限于指派它并創建用戶的單個數據庫中（不是單個的權限）。它不能創建新的SQL Server登錄賬戶，但是，該角色中的成員能夠把Windows用戶和組以及現有的SQL Server登錄賬戶加入到數據庫中
|
|
db_datareader
|
能夠在數據庫中所有的用戶表上執行SELECT語句
|
|
db_datawriter
|
能夠在數據庫中所有的用戶表上執行INSERT、UPDATE和DELETE語句
|
|
db_ddladmin
|
能夠在數據庫中添加、修改或刪除對象
|
|
db_securityadmin
|
securityadmin服務器角色的數據庫級別的等價物。這一數據庫角色不能在數據庫中創建新的用戶，但是，能夠管理角色和數據庫角色的成員，并能在數據庫中管理語句和對象的許可權限
|
|
db_backupoperator
|
備份數據庫（打賭你不會想到那樣一個角色！）
|
|
db_denydatareader
|
提供一種等同于在數據庫中所有表和視圖上DENY SELECT的效果
|
|
db_denydatawriter
|
類似于db_denydatareader，只不過這里影響的是INSERT、UPDATE和DELETE語句
|
與使用固定服務器角色很類似，除非是在最大型的數據庫中，否則，你可能不會使用到所有這些角色。在這些固定數據庫角色中，一些是無法用你自己的數據庫角色來替換的，而另一些，只不過在處理那些似乎經常出現的簡單粗糙的情形時非常便利而已。
2．用戶定義數據庫角色
實際上，可供使用的固定角色只是為了幫助你開始入手。安全性真正的中流砥柱是用戶定義數據庫角色的創建和分配。對于這些角色來說，由你來決定它們將包含什么許可權限。
使用用戶定義角色時，可以像針對單獨的用戶那樣，用完全相同的方式進行GRANT、DENY和REVOKE。關于使用角色，好的事情是，用戶往往歸入訪問需要的范疇——通過使用角色，你能夠在一個地方做改動，并將改動散播給所有類似的用戶（至少被指派到那個角色的用戶）。
● 創建用戶定義角色
我們使用sp_addrole系統存儲過程來創建我們自己的角色。其語法非常簡單：
sp_addrole [@rolename =] <'角色名'>
[,[@ownername =] <'所有者'>]
role name只不過是想要用來稱呼那個角色的名稱。常見的命名模式的例子包括：以部門來命名（Accounting、Sales、Marketing等），或者以具體的工作來命名（CustomerService、Salesperson、President等）。使用這樣的角色的確能夠讓向系統中添加新用戶的工作變得容易。如果會計部門新近雇用了某人，你只需把他（或她）添加到Accounting角色中（或者，如果更加精確，甚至可以是AccountsPayable角色），然后，就可以丟開這件事了——無需研究“這個人應當具有什么權限呢？”
此處的owner與系統中所有其他對象上的owner是相同的事物。默認是數據庫的所有者，并且，我強烈建議讓它保持那樣（換句話說，只需忽略這個可選參數即可）。
接下來，創建我們自己的角色：
當執行上面的語句時，將返回給你一個友好的消息，告訴你新的角色已經加入。
現在，我們需要為這個角色實際指派一些權限，以這種方式為這個角色增加一些價值。要完成這一任務，只需像本章前面對實際的用戶所做的那樣，使用GRANT、DENY或REVOKE語句：
現在，所有屬于我們角色的人都擁有了到Territories表的SELECT訪問權限（除非在他們的安全性信息中的其他地方有DENY）。
此刻，已經準備好添加用戶了。
● 向角色中添加用戶
有了所有這些角色固然不錯，但是，如果沒有把任何人指派給他們，則角色將沒什么用處。向角色中添加用戶非常簡單，就是使用系統存儲過程sp_addrolemember并提供數據庫名和登錄ID：
sp_addrolemember [@rolename =] <角色名>,
[@membername =] <登錄ID>
關于該存儲過程的參數，一切都是非常一目了然的，因此，我們直接進入一個例子。
先從證實TestAccount不具有到Territories表的訪問權限開始：
果不其然，我們被拒絕了（眼下尚沒有訪問的權限）：
現在，把我們的Windows用戶TestAccount添加到OurTestRole角色中：
同樣，我們收到一條確認消息，告知事情正確完成了：
此時，到了再次嘗試并運行SELECT語句的時候了——這一次順利得多（會得到大約53個返回行）。
● 從角色中刪除用戶
有起必有落，添加到角色中的用戶勢必也將從角色中刪除。
從角色中刪除用戶的操作與把用戶添加到角色中的操作幾乎一樣，只不過這里使用的是一個名為sp_droprolemember的不同的存儲過程，使用的形式如下：
sp_droprolemember [@rolename =] <角色名>,
[@membername =] <安全賬號>
接下來，返回到我們的例子，并從OurTestRole數據庫角色中刪除TestAccount：
你會收到另一個確認消息，告知一切順利。現在，再試試我們的SELECT語句：
果然，我們又一次收到了說明我們沒有訪問權限的錯誤消息。
可以用這種方式向任何角色中添加用戶以及從任何角色中刪除用戶——角色是用戶定義角色還是固定角色并不重要，是服務器角色還是數據庫角色也沒什么關系。無論在什么情況下，它們的操作幾乎完全一樣。
還要注意的是，所有這些工作都可以在Management Studio中進行。要更改與角色相關聯的權限，只需單擊數據庫結點的角色成員，然后使用復選框指派權限即可。當想要向角色中添加用戶時，只需去到用戶的屬性對話框中，選擇服務器或數據庫角色選項卡，然后，在所有想要用戶擁有其角色成員身份的角色上打上勾號。
● 刪除角色
刪除角色與添加角色一樣容易。其語法很簡單：
EXEC sp_droprole <'角色名'>
執行后，角色就被刪除了。