第8章數據安全性管理

8.1登錄帳戶管理8.2用戶管理8.3角色管理8.4許可管理小結習題

8.1登錄帳戶管理

8.1.1SQLServer2005安全機制

SQLServer2005除繼承了SQLSewer2000的可靠性、可用性、可編程性、易用性等方面的特點外，還在安全性方面做了很大改進。

SQLServer2005新增的這些安全特性，不僅使SQLServer更加安全而且使安全性更容易理解。其中一些特性將允許開發者在以自己的權限運行時，開發自己所需要的數據庫應用程序,這就是所謂的最低權限原則。也就是說,每一個開發者都不需要以SQLServer管理員登錄帳戶sa的身份運行。其他重要的特性如下所示：

(1)?.NETFramework可執行代碼的安全性。

(2)?SQLServer用戶的密碼策略。

(3)用戶和架構的分離。

(4)授予權限。

(5)元數據的新安全性。

(6)過程化地執行上下文。

(7)證書和密鑰的支持。

(8)過程化代碼的簽名。8.1.2SQLServer2005的驗證模式

每個網絡用戶在訪問SQLServer服務器時，都必須經過兩個階段的身份驗證：第一個階段的身份驗證是驗證用戶是否具有連接服務器的資格，SQLServer使用“登錄帳戶”來標識用戶的連接資格；第二個階段的身份驗證是驗證用戶是否具有訪問和操作數據庫的資格。下面，首先討論第一階段的驗證，也就是對登錄帳戶的驗證。

SQLServer2005對登錄帳戶的驗證，有以下兩種模式：

(1)?Windows驗證模式。

(2)?SQLServer和Windows混合驗證模式。

SQLServer2005對登錄帳戶的驗證過程可以用圖8-1表示。圖8-1SQLServer2005驗證模式8.1.3配置SQLServer2005驗證模式

在SQL-MS中，單擊展開左側窗口中的服務器組，選中要進行驗證模式設置的服務器，單擊右鍵，打開快捷菜單，選擇“屬性”命令，打開“服務器屬性”對話框，選擇其中的“安全性”選項卡，如圖8-2所示。圖8-2配置驗證模式8.1.4創建登錄帳戶

登錄帳戶的信息存儲在系統數據庫master的表syslogins中，當在SQLServer2005系統中增加一個登錄帳戶時，一般要指定一個默認數據庫。為登錄帳戶指定一個默認數據庫并不是在該數據庫中創建一個用戶帳戶，而是使用數據庫中的Guest用戶帳戶訪問該數據庫。當然也可以為該登錄帳戶在數據庫中創建了一個用戶帳戶，創建的方法在后面介紹。

1.使用SQL語句

創建登錄帳戶可以通過執行系統存儲過程sp_addlogin實現。其基本語法格式為：

sp_addlogin[@loginame=]'<登錄帳戶名>'

[,[@passwd=]'<密碼>']

[,[@defdb=]'<數據庫名>']

[,[@deflanguage=]'<語言>']

實例8-1

新建登錄帳戶login1，密碼為login11，默認數據庫為student。

在查詢編輯器中輸入SQL語句并執行，如圖8-3所示。圖8-3創建登錄帳戶

2.使用SQL-MS

(1)啟動SQL-MS，展開左側窗口中指定的數據庫服務器“安全性”文件夾，單擊“登錄名”節點，右側窗口顯示已存在的登錄帳戶的信息，如圖8-4所示。

(2)選擇左側窗格中“登錄名”節點，單擊右鍵，打開快捷菜單，選擇“新建登錄名”命令，打開“登錄名-新建”對話框，如圖8-5所示。圖8-4“登錄帳戶”窗口圖8-5“登錄名-新建”對話框

(3)單擊“常規”選項頁，在“名稱”框中輸入新建登錄帳戶的名稱，也可以單擊名稱文本框后的按鈕，選擇Windows用戶作為登錄帳戶。同時，在該選項卡上還可以設置登錄帳戶的身份驗證模式、默認語言和默認數據庫。

(4)單擊“服務器角色”選項頁，可以設置該登錄帳戶對應的服務器角色，在“用戶映射”選項頁上可以設置該登錄帳戶的數據庫訪問許可。角色和訪問許可的相關知識將在第8.3和8.4節介紹。

(5)單擊“確定”按鈕，完成新登錄帳戶的創建。此時，在右側窗口中可以看到新創建的登錄帳戶的相關信息。8.1.5修改登錄帳戶

1.使用SQL語句

(1)修改登錄帳戶默認數據庫。修改登錄帳戶默認數據庫可以通過執行系統存儲過程sp_defaultdb實現。其基本語法格式為：

sp_defaultdb[@loginame=]‘<登錄帳戶>’,[@defdb=]‘<數據庫名>’

實例8-2

將登錄帳戶login1的默認數據庫修改為master。

在查詢編輯器中輸入SQL語句并執行，如圖8-6所示。圖8-6修改登錄帳戶默認數據庫

(2)修改登錄帳戶密碼。修改登錄帳戶密碼可以通過執行系統存儲過程sp_password實現。其基本語法格式為：

sp_password[[@old=]‘<原密碼>’,]

{[@new=]‘<新密碼>’}

[,[@loginame=]‘<登錄帳戶>’]

實例8-3

將登錄帳戶login1的密碼由login11改為abcdef。

在查詢編輯器中輸入SQL語句并執行，如圖8-7所示。圖8-7修改登錄帳戶密碼

2.使用SQL-MS

(1)啟動SQL-MS，展開左側窗口中指定的數據庫服務器“安全性”文件夾，單擊“登錄名”節點，指向右側窗口要修改的登錄帳戶，單擊右鍵，選擇“屬性”命令，打開“登錄屬性”對話框，如圖8-8所示。

(2)修改該登錄帳戶的密碼、默認庫等屬性，最后單擊“確定”按鈕，完成登錄帳戶修改。圖8-8“登錄屬性”對話框8.1.6刪除登錄帳戶

1.使用SQL語句

刪除登錄帳戶可以通過執行系統存儲過程sp_droplogin實現。其基本語法格式為：

sp_droplogin[@loginame=]‘<登錄帳戶>’

實例8-4

刪除登錄帳戶login1。

在查詢編輯器中輸入SQL語句并執行，如圖8-9所示。圖8-9刪除登錄帳戶

2.使用SQL-MS

(1)啟動SQL-MS，展開左側窗口中指定的數據庫服務器“安全性”文件夾，單擊“登錄名”節點，指向右側窗口要刪除的登錄帳戶，單擊右鍵，選擇“刪除”命令，打開“刪除對象”對話框，如圖8-10所示。

(2)單擊“確定”按鈕，完成登錄帳戶刪除。圖8-10“刪除對象”對話框

8.2用戶管理

8.2.1特殊的數據庫用戶

在SQLServer2005中包含有以下三種特殊的數據庫用戶，具有特殊的權限和作用。

(1)數據庫所有者(dbo)；

(2)數據庫對象所有者；

(3)?guest。

1.數據庫所有者

SQLServer2005數據庫的創建者即為該數據庫的所有者。此外，固定服務器角色sysadmin的所有成員被映射為每一個數據庫的dbo。dbo用戶不能從數據庫中刪除，dbo對數據庫具有所有操作權限，并且可以將所擁有的數據庫操作權限授予其他用戶。

sysadmin成員所創建的數據庫對象自動屬于dbo，而不屬于用戶自身。所以，在SQL語句中指定數據庫對象時，如果數據庫對象為sysadmin角色成員所創建，則使用dbo，否則使用創建者的用戶名稱指定數據庫對象。實例8-5

如果student數據庫有stonepf和stone兩個用戶，其中stonepf屬于sysadmin角色，而stone屬于db_owner角色。他們分別在student數據庫中建立兩個數據表Table1和Table2，指定這兩個對象時分別使用dbo和stone。

在查詢編輯器中輸入下列SQL語句并執行：

SELECT*FROMMyDB.dbo.Table1

SELECT*FROMMyDB.stone.Table2

2.數據庫對象所有者

SQLServer2005數據庫中的對象包括表、索引、視圖、默認、規則、觸發器、用戶定義函數和存儲過程等，數據庫對象的創建者即成為該數據庫對象的所有者。數據庫對象所有者隱含具有該對象的所有權限，只有他顯式地向其他用戶授權后，其他用戶才能訪問該數據庫對象。

在一個數據庫中不同用戶可以創建同名的數據庫對象，所以當用戶訪問數據庫對象時，必須對數據庫對象所有者進行指定。如下面語句指定要檢索用戶stone所創建的Table1表：

SELECT*FROMstone.Table1

3.?guest用戶

guest用戶是SQLServer2005數據庫中的一個特殊用戶。當某個SQLServer2005登錄帳戶在數據庫中沒有建立對應的數據庫用戶，而又要訪問該數據庫時，SQLServer2005就查找數據庫中是否存在guest用戶。若存在該用戶，就允許該登錄帳戶使用guest用戶的權限訪問數據庫，否則將拒絕其訪問。SQLServer2005系統數據庫master和tempdb中的guest用戶帳戶不能刪除，而其他數據庫中的guest用戶均可創建或刪除。8.2.2創建數據庫用戶

1.使用SQL語句

在SQLServer2005中，為Windows用戶(組)和SQLServer2005登錄帳戶建立數據庫用戶的方法相同，可以通過執行系統存儲過程sp_grantdbaccess實現。其基本語法格式為：

sp_grantdbaccess[@loginname=]'<登錄帳戶>'

[,[@name_in_db=]'<數據庫用戶名>']

實例8-6

為sttone-i5p0laxy域中的用戶adminofdb在student數據庫中建立同名的數據庫用戶。

在查詢編輯器中輸入SQL語句并執行，如圖8-11所示。圖8-11創建數據庫用戶實例實例8-7

為SQLServer2005登錄帳戶login1在數據庫student中建立名為user1的數據庫用戶。

在查詢編輯器中輸入SQL語句并執行，如圖8-12所示。圖8-12創建數據庫用戶實例

2.使用SQL-MS

(1)啟動SQL-MS，展開左側窗口中指定的數據庫，打開“安全性”節點，單擊“用戶”節點，右側窗口顯示當前數據庫用戶的信息，如圖8-13所示。

(2)指向左側窗口中“用戶”節點，單擊右鍵，打開快捷菜單，選擇“新建用戶”命令，打開“數據庫用戶-新建”對話框，如圖8-14所示。圖8-13“數據庫用戶”窗口圖8-14“數據庫用戶-新建”對話框

(3)在“用戶名”文本框中輸入新建用戶的名稱，單擊“登錄名”文本框旁邊按鈕，打開“選擇登錄名”對話框，如圖8-15所示。圖8-15“選擇登錄名”對話框

(4)單擊“瀏覽”按鈕，打開“查找對象”對話框，如圖8-16所示。圖8-16“查找對象”對話框

(5)選擇“login1”用戶，單擊“確定”按鈕，返回“選擇登錄名”窗口，再單擊“確定”按鈕，返回“數據庫用戶-新建”對話框。

(6)同(5)，選擇默認架構為“dbo”，然后設置該用戶所屬的角色。單擊“確定”按鈕，完成新用戶的建立。

刷新“用戶”節點，就可以看到剛才創建的“login1”用戶帳戶。8.2.3刪除數據庫用戶

1.使用SQL語句

數據庫用戶可以通過執行系統存儲過程sp_revokedbaccess實現刪除。其基本語法格式為：

sp_revokedbaccess[@name_in_db=]'<數據庫用戶名>'

實例8-8

刪除student數據庫中的用戶user1。

在查詢編輯器中輸入SQL語句并執行，如圖8-17所示。圖8-17刪除數據庫用戶

2.使用SQL-MS

(1)啟動SQL-MS，展開左側窗口中指定的數據庫，單擊“用戶”節點，指向右側窗口中要刪除的數據庫用戶，單擊右鍵，打開快捷菜單，選擇“刪除”命令，打開“刪除對象”對話框，如圖8-18所示。

(2)單擊“確定”按鈕，完成數據庫用戶刪除。圖8-18“刪除對象”對話框

8.3角色管理

8.3.1管理固定服務器角色

1.固定服務器角色

SQLServer2005在安裝過程中定義了幾個固定角色，可以向這些角色中添加用戶以獲得相關的管理權限。固定角色包括固定服務器角色和固定數據庫角色。其中，固定服務器角色是在服務器的級別上定義的。表8-1列出了固定服務器角色及其所擁有的權限。

2.向固定服務器角色中添加成員

1)使用SQL語句

向固定服務器角色中添加成員，可以通過執行系統存儲過程sp_addsrvrolemember實現。其基本語法格式為：

sp_addsrvrolemember[@loginame=]‘<登錄帳戶名>’，[@rolename=]‘<角色名>’

其中，“角色名”為固定服務器角色。

實例8-9

在student數據庫中，把登錄帳戶login1添加到固定服務器角色sysadmin中。

在查詢編輯器中輸入SQL語句并執行，如圖8-19所示。圖8-19添加固定服務器角色成員

2)使用SQL-MS

(1)啟動SQL-MS，展開左側窗口中指定數據庫服務器“安全性”文件夾，單擊“服務器角色”節點，則右側窗口顯示出固定服務器角色信息，如圖8-20所示。

(2)指向右側窗口中要添加成員的固定服務器角色，單擊右鍵，打開快捷菜單，選擇“屬性”命令，打開“服務器角色屬性”對話框，如圖8-21所示。圖8-20“查看服務器角色”窗口圖8-21“服務器角色屬性”對話框

(3)單擊“添加”按鈕，打開“選擇登錄名”對話框，如圖8-22所示。圖8-22“選擇登錄名”對話框

(4)單擊“瀏覽”按鈕，打開“查找對象”對話框，如圖8-23所示。

(5)選擇要添加的登錄帳戶，單擊“確定”按鈕，返回“選擇登錄名”對話框，再單擊“確定”按鈕，返回“服務器角色屬性”窗口，如圖8-21所示。

(6)單擊“確定”按鈕，完成服務器角色添加。圖8-23“查找對象”對話框

3.從固定服務器角色中刪除成員

1)使用SQL語句

刪除固定服務器角色的成員，可以通過執行系統存儲過程sp_dropsrvrolemember實現。其語句基本語法格式為：

sp_dropsrvrolemember[@loginame=]‘<登錄帳戶名>’，[@rolename=]‘<角色名>’

實例8-10

把登錄帳戶login1從固定服務器角色sysadmin中刪除。

在查詢編輯器中輸入SQL語句并執行，如圖8-24所示。圖8-24刪除固定服務器角色成員

2)使用SQL-MS

(1)啟動SQL-MS，展開左側窗口中指定數據庫服務器“安全性”文件夾，單擊“服務器角色”節點，指向右側窗口要刪除成員的固定服務器角色，單擊右鍵，打開快捷菜單，選擇“屬性”命令，彈出“服務器角色屬性”對話框，參見圖8-21。

(2)單擊“刪除”按鈕，再單擊“確定”按鈕，完成成員刪除。8.3.2管理數據庫角色

數據庫角色是在數據庫級別上定義的，存在于每個數據庫中，分為固定數據庫角色和用戶自定義數據庫角色。

1.固定數據庫角色

SQLServer2005在安裝過程中定義了幾個固定數據庫角色，固定數據庫角色及其所擁有的權限如表8-2所示。

2.建立用戶自定義數據庫角色

1)使用SQL語句

創建用戶自定義數據庫角色可以通過執行系統存儲過程sp_addrole實現。?其基本語法格式為：

sp_addrole[@rolename=]‘<角色名>’[，[@ownername=]‘<所有者>’]

其中，“角色名”為新角色的名稱。“所有者”為新角色的所有者，必須是當前數據庫中的某個用戶或角色，默認值為dbo。

實例8-11

在student數據庫中添加角色role1。

在查詢編輯器中輸入SQL語句并執行，如圖8-25所示。圖8-25添加角色

2)使用SQL-MS

(1)啟動SQL-MS，展開左側窗口中指定數據庫，展開“安全性”節點，打開“角色”節點，單擊“數據庫角色”節點，在右側窗口顯示出固定數據庫角色信息，如圖8-26所示。

(2)指向左側窗口“數據庫角色”節點，單擊右鍵，打開快捷菜單，選擇“新建數據庫角色”命令，打開“數據庫角色-新建”對話框，如圖8-27所示。圖8-26“查看固定數據庫角色”窗口圖8-27“數據庫角色-新建”對話框

(3)在“角色名稱”框中輸入新建角色的名稱，單擊所有者輸入框后的按鈕，選擇角色的所有者，此處為“db_owner”，可以設置此角色擁有的架構，如果需要，單擊“添加”按鈕，將適當的用戶添加至新建的數據庫角色中。

(4)單擊“確定”按鈕，完成定義用戶自定義數據庫角色。

3.刪除用戶自定義數據庫角色

1)使用SQL語句

刪除用戶自定義數據庫角色可以通過執行系統存儲過程sp_droprole實現。其基本語法格式為：

sp_droprole[@rolename=]‘<角色名>’

實例8-12

在student數據庫中刪除角色role1。

在查詢編輯器中輸入SQL語句并執行，如圖8-28所示。圖8-28刪除用戶自定義數據庫角色

2)使用SQL-MS

(1)啟動SQL-MS，展開左側窗口中指定數據庫，展開“安全性”節點，打開“角色”節點，單擊“數據庫角色”節點，指向右側窗口中要刪除的角色，單擊右鍵，打開快捷菜單，選擇“刪除”命令，打開“刪除對象”對話框，如圖8-18所示。

(2)單擊“確定”按鈕，完成刪除用戶自定義數據庫角色。

4.向數據庫角色中添加成員

1)使用SQL語句

向數據庫角色(包括固定數據庫角色和用戶自定義數據庫角色)中添加成員，可以通過執行系統存儲過程sp_addrolemember實現。其基本語法格式為：

sp_addrolemember[@rolename=]‘<角色名>’，[@membername=]‘<用戶名>’

實例8-13

在數據庫student中，把用戶user1添加到角色role1中。

在查詢編輯器中輸入SQL語句并執行，如圖8-29所示。圖8-29添加數據庫角色成員

2)使用SQL-MS

(1)啟動SQL-MS，展開左側窗口中指定數據庫，展開“安全性”節點，打開“角色”節點，單擊“數據庫角色”節點，指向右側窗口中要添加成員的數據庫角色，單擊右鍵，打開快捷菜單，選擇“屬性”命令，打開“數據庫角色屬性”對話框，如圖8-30所示。圖8-30“數據庫角色屬性”對話框

(2)單擊“添加”按鈕，打開“選擇數據庫用戶或角色”對話框，如圖8-31所示。圖8-31“選擇數據庫用戶或角色”對話框

(3)選擇“瀏覽”按鈕，打開“查找對象”對話框。

(4)選擇要添加的成員，單擊“確定”按鈕，返回“選擇數據庫用戶或角色”對話框，再單擊“確定”按鈕，返回“數據庫角色屬性”對話框，單擊“確定”按鈕，完成添加數據庫角色成員。

5.從數據庫角色中刪除成員

1)使用SQL語句

從數據庫角色(包括固定數據庫角色和用戶自定義數據庫角色)中刪除成員，可以通過執行系統存儲過程sp_droprolemember實現。其基本語法格式為：

sp_droprolemember[@rolename=]‘<角色名>’，

[@membername=]‘<用戶名>’

實例8-14

把用戶user1從角色role1中刪除。

在查詢編輯器中輸入SQL語句并執行，如圖8-32所示。圖8-32刪除數據庫角色成員

2)使用SQL-MS

(1)啟動SQL-MS，展開左側窗口中指定數據庫，展開“安全性”節點，打開“角色”節點，單擊“數據庫角色”節點，指向右側窗口中要添加成員的數據庫角色，單擊右鍵，打開快捷菜單，選擇“屬性”命令，打開“數據庫角色屬性”對話框，單擊選擇要刪除的成員，如圖8-33所示。

(2)單擊“刪除”按鈕，再單擊“確定”按鈕，完成刪除成員。圖8-33“數據庫角色屬性”窗口

8.4許可管理

數據庫對象創建后，只有擁有者可以訪問該數據庫對象，任何其他用戶想訪問該對象必須獲得擁有者的授權，也就是許可。許可分為以下兩種類型。

(1)語句許可：數據庫擁有者可以授予執行某些SQL命令的許可。這種許可在SQLServer2005中稱作語句許可。如擁有者可給將執行CREATETABLE或CREATEVIEWS等語句的許可授予其他用戶，這些語句本來只有特定用戶(如dbo)可以使用。

(2)對象許可：擁有者可以將數據庫對象的許可授予指定的數據庫用戶，這種許可被稱作對象許可。如對表這種數據對象，擁有者可以把對該表執行INSERT、DELETE、UPDATE、SELECT和REFERENCE等的許可授予其他用戶。8.4.1使用SQL-MS

1．管理語句許可

(1)啟動SQL-MS，指向左側窗口指定數據庫節點，單擊右鍵，打開快捷菜單，選擇“屬性”命令，打開“數據庫屬性”窗口，選擇“權限”選項頁，如圖8-34所示。

(2)選中用戶，“顯式權限”中每個語句對應的復選框，可以設置該用戶語句許可。

(3)單擊“確定”按鈕，完成語句許可設置。圖8-34“數據庫屬性”窗口“權限”選項頁

2.管理對象許可

(1)啟動SQL-MS，展開左側窗口指定數據庫，指向右側窗口中指定表，單擊右鍵，打開快捷菜單，選擇“屬性”命令，打開“表屬性”對話框，選擇“權限”選項頁，如圖8-35所示。

(2)單擊用戶每個語句下面的復選框，設置該用戶語句許可。“√”表示授予該用戶對該表的該語句許可；空白表示未設置許可。單擊“確定”按鈕，完成對象許可設置。圖8-35“對象屬性”對話框8.4.2使用SQL語句

1.授予許可

授予語句許可語句的基本語法格式為：

GRANT{ALL|<語句>[，...n]}

TO<安全帳戶>[，...n]

授予對象許可語句的基本語法格式為：

GRANT

{ALL|<對象權限>[，...n]}

{[(<列名>[，...n])]ON{<表名>|<視圖名>}

|?ON{<表名>|<視圖名>}[(<列名>[，...n])]

|?ON{<存儲過程名>}

|?ON{<用戶自定義函數名>}

}

TO<安全帳戶>[，...n]

[WITHGRANTOPTION][AS{group|role}]實例8-15

把student數據庫中的表s的SELECT操作的許可授予public角色，然后再把有關它的INSERT、UPDATE、DELETE操作的許可授予帳戶user1。

在查詢編輯器中輸入SQL語句并執行，如圖8-36所示。

實例8-16

把在student數據庫上創建表和視圖的命令授予用戶user1。

在查詢編輯器中輸入SQL語句并執行，如圖8-37所示。圖8-36授予對象許可圖8-37授予語句許可

2.撤銷許可

撤銷許可語句的基本語法格式為：

REVOKE{ALL|<語句>[，...n]}

FROM<安全帳戶>[，...n]

撤銷對象許可語句的基本語法格式為：

REVOKE[GRANTOPTIONFOR]

{ALL|<對象許可>[，...n]}

{[(<列名>[，...n])]ON{<表名>|<視圖名>}

|ON{<表名>|<視圖名>}[(<列名>[，...n])]

|ON{<存儲過程名>}

|ON{<用戶自定義函數名>}

}

{TO|FROM}