SQL Server的安全管理课件

SQLServer的安全管理10.1身份驗證模式

SQLServer2000的安全機制是基於用戶、角色、對象和許可權的基礎上建立的。系統可以將用戶加入角色，也可以為它們指定對象許可權。每個對象都有所有者，所有權也影響到許可權。資料庫對象（表、索引、視圖、觸發器、函數或存儲過程）的用戶稱為資料庫對象的所有者。創建資料庫對象的許可權必須由資料庫所有者或系統管理員授予。但是，在授予資料庫對象這些許可權後，資料庫對象所有者就可以創建對象並授予其他用戶使用該對象的許可權。SQLServer2000安全系統的構架建立在用戶和用戶組的基礎上，也就是說Windows的用戶和用戶組可以映射到SQLServer2000中的安全帳戶，而SQLServer2000也可以獨自建立安全帳戶。對於安全帳戶SQLServer2000可以對其分配權限，也可以將其加入到角色中，從而獲得相應的許可權。如圖10-1所示。

在SQLServer2000工作時，用戶要經過兩個安全性階段：

第一階段：身份驗證。如果身份驗證成功，用戶可連接到SQLServer實例。

第二階段：授權（許可權驗證）。授權階段又分為驗證用戶連接到SQLServer實例的許可權和訪問伺服器上資料庫的許可權。為此，需授予每個資料庫中映射到用戶登錄的帳號訪問許可權。許可權驗證階段則控制用戶在SQLServer資料庫中所允許進行的活動。SQLServer指定登錄用戶和角色資料庫用戶資料庫角色Windows2000組用戶SQLServer登錄帳戶SQLServer

驗證信任聯結SQLServer驗證用戶名和口令SQLServerWindows2000或圖10-1SQLServer2000的安全架構10.1.1SQLServer的身份驗證模式

在SQLServer2000中，必須以合法的登錄身份註冊本地或遠程伺服器後，才能與伺服器建立連接並獲得對SQLServer的訪問權。系統提供了2種登錄身份驗證模式：

1．Windows身份驗證模式（Windows身份驗證）

Windows身份驗證模式使用戶得以通過WindowsNT或Windows2000用戶帳號進行登錄連接，並獲得對SQLServer的訪問許可權。

2．混合模式（Windows身份驗證和SQLServer2000身份驗證）

混合模式使用戶得以使用Windows身份驗證或SQLServer2000身份驗證的用戶帳號進行登錄連接。

在Windows身份驗證模式或混合模式下，通過Windows用戶帳號連接的用戶可以使用信任連接。10.1.2身份驗證模式的選擇

選擇身份驗證模式，可按以下步驟操作：

①在企業管理器中展開【SQLServer組】，選擇目前連接的伺服器。

②單擊【菜單】中的【屬性】命令，打開【SQLServer屬性】對話框，選擇【安全性】標籤，即可打開如圖10-2所示的對話框。

③可在【安全性】選項組中設置身份驗證模式。如圖10-2所示。圖10-2設置身份驗證模式對話框10.2安全帳戶管理

10.2.1創建安全帳戶

在SQLServer2000中，可以使用兩種方法添加登錄帳號：使用企業管理器添加登錄帳號；使用系統存儲過程添加登錄帳號。

1．系統安裝時建立的帳號

（1）sa

系統管理員（sa）是為向後相容而提供的特殊登錄帳號，擁有最高管理權限，可以執行伺服器範圍內的所有操作。默認情況下，它指派給固定伺服器角色sysadmin，並不能進行更改。雖然是內置了管理員登錄帳號，但不應例行公事地使用它。相反，應使其他的系統管理員帳號都成為sysadmin固定伺服器角色的成員，並讓他們使用自己的登錄名登錄。只有當沒有其他方法登錄到SQLServer實例（例如：當其他系統管理員不可用或忘記了密碼）時才使用sa。

（2）Builtin\administrators

本地管理員組，默認加入sysadmin角色中，因此具有管理員許可權。2．使用企業管理器添加登錄帳號

具體操作步驟如下：

在企業管理器中展開伺服器組，然後展開伺服器。

②展開【安全性】，右擊【登錄】，然後單擊【新建登錄】命令，系統打開如圖10-3所示【SQLServer登錄屬性-新建登錄】對話框。圖10-3【SQLServer登錄屬性-新建登錄】對話框①③在【身份驗證】下，單擊【Windows身份驗證】,在【名稱】框中，輸入要被授權訪問MicrosoftSQLServerTM的MicrosoftWindowsNT4.0或Windows2000帳號（以DOMAIN\User的形式）；在【身份驗證】下，單擊【SQLServer身份驗證】，在【名稱】框中，輸入登錄帳號名稱。

④設置可選項。可在【資料庫】中，單擊用戶在登錄到SQLServer實例後所連接的默認資料庫。在【語言】中，單擊顯示給用戶的資訊所用的默認語言。

【例10-1】將dss_ibm390x中的本機用戶zhangsan加入到SQLServer實例中，並建立一個SQLServer登錄帳號,帳號名稱為wang。

在企業管理器中可以按以下步驟操作：

①在企業管理器中展開伺服器組，然後展開伺服器

②展開【安全性】，右擊【登錄】，然後單擊【新建登錄】命令，打開如圖10-4所示【SQLServer登錄屬性-新建登錄】對話框。

③在【名稱】框中，輸入dss_ibm390x\zhangsan。

④在【身份驗證】下，單擊【Windows身份驗證】。

⑤填寫完成之後，單擊【確定】按鈕。

⑥重複①、②步。⑦在【名稱】框中，輸入wang。

⑧在【身份驗證】下，單擊【SQLServer身份驗證】⑨在【密碼】框中，輸入密碼，也可以密碼為空

圖10-2-2登錄帳戶資訊

⑩填寫完成之後，單擊【確定】按鈕，即可在企業管理器中看到如圖10-5所示的登錄帳號資訊。圖10-4【SQLServer登錄屬性-新建登錄】對話框圖10-5登錄帳號資訊3．使用系統存儲過程添加登錄帳號

在SQLServer2000中，也可用sp_grantlogin系統存儲過程來創建一個Windows登錄‘帳號。其語法為：

Sp_grantloginWindows帳號或組’

其中：‘Windows帳號或組’的格式為‘功能變數名稱\用戶名’。

也可用sp_addlogin命令來創建一個SQLServer登錄帳號。其語法為：

sp_addlogin[@loginame=]'login'

[,[@passwd=]'password']

[,[@defdb=]'database']

[,[@deflanguage=]'language']其中：[@loginame=]'login'：登錄的名稱。login的數據類型為sysname，沒有默認設置。[@passwd=]'password'：登錄密碼。password的數據類型為sysname，默認設置為NULL。sp_addlogin

執行後，password被加密並存儲在系統表中。[@defdb=]'database'：登錄的默認資料庫（登錄後登錄所連接到的資料庫）。database的數據類型為sysname，默認設置為master。其中：[@loginame=]'login'：登錄的名稱。login的數據類型為sysname，沒有默認設置。[@passwd=]'password'：登錄密碼。password的數據類型為sysname，默認設置為NULL。sp_addlogin

執行後，password被加密並存儲在系統表中。[@defdb=]'database'：登錄的默認資料庫（登錄後登錄所連接到的資料庫）。database的數據類型為sysname，默認設置為master。[@deflanguage=]'language'：用戶登錄到SQLServer時系統指派的默認語言。language的數據類型為sysname，默認設置為NULL。如果沒有指定language，那麼language被設置為伺服器當前的默認語言（由sp_configure配置變數defaultlanguage定義）。更改伺服器的默認語言不會更改現有登錄的默認語言。language保持與添加登錄時所使用的默認語言相同。可以使用上述系統存儲過程實現【例10-1】的功能。

在查詢分析器中執行下列存儲過程：

Sp_grantlogin‘dss_ibm390x\zhangsan’ ――添加一個Windows登錄帳號

GO

Sp_addlogin‘wang’,’’,’master’ ――添加一個SQLServer登錄帳號

GO

10.2.2管理安全帳號

①刪除SQLServer登錄帳號

Sp_drop‘login’ ――login表示登錄帳號

②禁止一個Windiws帳號訪問SQLServer實例

Sp_denylogin‘login’ ――login表示Windows用戶帳號，形式為Domain\User

③刪除一個Windiws登錄帳號

Sp_revokelogin‘login’ ――login表示Windows用戶帳號，形式為Domain\User10.2.3創建資料庫用戶帳號

有了登錄帳號之後，可以訪問SQLServer，但還不能訪問資料庫。要讓用戶能夠訪問資料庫，還需要在指定資料庫中生成資料庫用戶帳號。在SQLServer2000中，可以使用兩種方法將登錄帳號生成為指定資料庫的用戶帳號。它們是使用企業管理器新建資料庫用戶；使用系統存儲過程新建資料庫用戶。

1．缺省資料庫用戶帳號

（1）dbo

dbo是可以在資料庫範圍內執行一切操作的最高權利擁有者。系統將固定伺服器角色sysadmin的任何成員都映射到每個資料庫內稱為dbo的這個特殊用戶上。另外，由固定伺服器角色sysadmin的任何成員創建的任何對象都自動屬於dbo,並且dbo用戶無法刪除

（2）guest

Guest用戶帳號允許任何已經登錄到SQLServer伺服器的用戶都可以訪問資料庫。但必須滿足以下兩個條件：①登錄有訪問SQLServer實例的許可權，但沒有通過自己的用戶帳號訪問資料庫的許可權；②資料庫中含有guest用戶帳號。10.2.4創建資料庫用戶帳號

使用企業管理器創建資料庫用戶帳號

可按以下步驟進行操作：

①在企業管理器中展開伺服器組，然後展開伺服器。

②展開指定資料庫，右擊【用戶】，在彈出的快捷菜單中單擊【新建資料庫用戶(U)…】命令,打開如圖10-6所示的【資料庫用戶屬性-新建用戶】對話框。

③在【登錄名】框中，選擇一個登錄帳號。

④在【用戶名】框中，輸入用戶名稱。需要說明的是，一個登錄帳號可以在不同的資料庫中擁有不同的用戶名稱。

⑤在【資料庫角色成員】欄中，可以選擇該資料庫用戶將加入到哪一個資料庫角色中，但所有的用戶都屬於public角色。

⑥填寫完畢之後，單擊【確定】按鈕。圖10-7為數據庫XSCJ新建用戶登錄帳號圖10-6【資料庫用戶屬性-新建用戶】對話框【例10-2】為數據庫XSCJ新建用戶登錄帳號為：dss_ibm390x\zhangsan，在資料庫XSCJ中的用戶名為zhangsan。

在企業管理器中可以按以下步驟操作：

①在企業管理器中展開伺服器組，然後展開伺服器，在展開資料庫。

②展開指定資料庫XSCJ，右擊【用戶】，然後單擊【新建資料庫用戶】命令，打開【資料庫用戶屬性-新建用戶】對話框，如圖10-7所示。

③在【登錄名】框中，選擇一個登錄帳號(dss_ibm390x\zhangsan)。

④在用戶名框中，輸入用戶名稱zhangsan。

⑤在資料庫角色成員欄中，可以選擇zhangsan為數據庫XSCJ的擁有者，既在db_owner上打√。⑥單擊【確定】按鈕，設置結果如圖10-8所示圖10-8為數據庫XSCJ新建用戶登錄帳號2．使用系統存儲過程創建資料庫用戶帳號

在SQLServer2000中，也可用sp_grantdbaccess命令來新建資料庫用戶帳號。其語法為：

sp_grantdbaccess[@loginame=]'login'

[,[@name_in_db=]'name_in_db'[OUTPUT]]

其中：

[@loginame=]'login'：當前資料庫中新安全帳號的登錄名稱。WindowsNT組和用戶必須用WindowsNT功能變數名稱限定，格式為"域\用戶"，例如LONDON\Joeb。登錄不能使用資料庫中已有的帳號作為別名。

[@name_in_db=]'name_in_db'[OUTPUT]：資料庫中用戶帳號的名稱。name_in_db是sysname

類型的OUTPUT變數，默認值為NULL。如果沒有指定，則使用login。如果將其指定為NULL值的OUTPUT變數，則設置@name_in_db為login。當前資料庫不必存在name_in_db。【例10-3】為數據庫XSCJ新建用戶帳號，該用戶帳號的登錄帳號為wang，在資料庫XSCJ中的用戶名為wang。在查詢分析器中運行如下命令：Sp_grantdbaccess‘wang’,’wang’GO運行結果如圖10-9所示。圖10-9使用系統存儲過程創建資料庫用戶帳號10.2.5管理資料庫用戶帳號

1．刪除資料庫用戶

Sp_revokedbaccess'name' ――name表示資料庫用戶名

2．報告當前資料庫的用戶資訊

Sp_helpuser[name] ――name表示資料庫用戶名10.3角色管理

角色是一個強大的工具，可以將用戶集中到一個單元中，然後對該單元應用許可權。對一個角色授予、拒絕或廢除許可權也適用於該角色的任何成員。可以建立一個角色來代表單位中一類工作人員所執行的工作，然後給這個角色授予適當的許可權。當工作人員開始工作時，只須將他們添加為該角色成員，當他們離開工作時，將他們從該角色中刪除，而不必在每個人接受或離開工作時，反復授予、拒絕和廢除其許可權。許可權在用戶成為角色成員時自動生效。10.3.1固定伺服器角色

將用戶添加到表10-1所列角色中，這些用戶就獲得管理SQLServer的管理權限。

表10-1固定伺服器角色固定伺服器角色許可權描述Sysadmin可以在SQLServer中執行任何活動。Serveradmin可以設置伺服器範圍的配置選項，關閉伺服器。Securityadmin可以管理登錄，還可以讀取錯誤日誌和更改密碼。Setupadmin可以管理鏈接伺服器和啟動過程。Processadmin可以管理在SQLServer中運行的進程。Dbcreator可以創建、更改和刪除資料庫。Diskadmin可以管理磁片檔。bulkadmin可以執行BULKINSERT語句。上述角色是在安裝SQLServer時自動創建的，並且系統已經給這些角色分配權限。用戶不能修改其許可權，也不能再定義固定伺服器角色。

10.3.2固定資料庫角色

每個資料庫都有一系列固定資料庫角色。雖然每個資料庫中都存在名稱相同的角色，但各個角色的作用域只限於特定的資料庫內。

將用戶添加到表10-2所列角色中，這些用戶就獲得這個資料庫的管理權限。固定資料庫角色許可權描述Db_owner在資料庫中有全部許可權。Db_accessadmin可以添加或刪除用戶IDDb_securityadmin可以管理全部許可權、對象所有權、角色和角色成員的資格db_ddladmin可以發出ALLDDL，但不能發出GRANT、REVOKE或DENY語句。db_backupoperator可以發出DBCC、CHECKPOINT和BACKUP語句。db_datareader可以選擇資料庫內任何用戶表中的所有數據。db_datawriter可以更改資料庫內任何用戶表中的所有數據。db_denydatareader不能選擇資料庫內任何用戶表中的任何數據。db_denydatawriter不能更改資料庫內任何用戶表中的任何數據。表10-2固定資料庫角色10.3.3創建資料庫角色

1．創建自定義資料庫角色

當一組用戶需要在SQLServer中執行一組指定的活動時，可以創建SQLServer資料庫角色。用戶添加到角色中就繼承角色的許可權，這樣可以更加方便管理。

方法一、使用企業管理器創建SQLServer自定義資料庫角色

具體操作步驟為：

①展開伺服器組，然後展開伺服器。

②展開【資料庫】檔夾，然後展開指定的資料庫

③右擊【角色】，在彈出的快捷菜單中單擊【新建資料庫角色】命令，打開【資料庫角色屬性-新建角色】對話框，如圖10-10所示。

④在【名稱】框中輸入新角色的名稱。

⑤單擊【添加】按鈕，將成員添加到【標準角色】列表中，然後選擇要添加的一個或多個用戶。

⑥設置完畢後，點擊【確定】。設置結果如圖10-10所示。圖10-10【資料庫角色屬性-新建角色】對話框方法二、使用系統存儲過程創建自定義資料庫角色並將用戶添加到角色中

在查詢分析器中執行下列命令即可：

USE資料庫名

－選擇要創建角色的資料庫

sp_addrole‘role’

－在當前資料庫創建新的角色，role為角色名

2．創建應用程式角色

由於某種需要，可能希望限制用戶只能通過特定應用程式來訪問數據或防止用戶直接訪問數據。限制用戶的這種訪問方式將禁止用戶使用應用程式連接到SQLServer實例並執行編寫品質差的查詢，以免對整個伺服器的性能造成負面影響。應用角色不包含成員，默認情況下，應用程式角色是非活動的，需要用密碼啟動，當一個應用程式角色被該應用程式啟動以用於連接時，連接會在連接期間永久地失去資料庫中所有用來登錄的許可權。

使用sp_addapprole’role','password'命令即可創建一個應用程式角色。

使用sp_setapprole'role','password'命令即可啟動應用程式角色。

'role'為應用程式角色名,'password'為啟動口令。

也可以使用企業管理器創建應用程式角色，如圖10-10所示。10.3.4管理資料庫角色

1．將用戶加入到固定伺服器角色中

sp_addsrvrolemember‘用戶名稱’,‘角色名稱’

2．將用戶從固定伺服器角色中移去

sp_dropsrvrolemember‘用戶名稱’,‘角色名稱’

3．將用戶加入到資料庫角色中

sp_addrolemember‘角色名稱’,‘用戶名稱’

4．將用戶從資料庫角色中移去

sp_droprolemember‘角色名稱’,‘用戶名稱’

5．查詢角色資訊

sp_helprole‘角色名稱’ --列出角色的名稱、識別碼

sp_helprolemember‘角色名稱’ --列出該角色所有成員及各成員的對象識別碼

6．刪除角色

sp_droprole'角色名稱'

注意：內建角色無法刪除，當角色中仍有成員時也無法刪除。10.4.1許可權概述在SQLServer中，並不是所有的用戶（login）都可以訪問特定的資料庫，能訪問某個資料庫的用戶也不一定有全部訪問許可權。為了進行這種限制，我們需要使用權限進行控制。管理權限包括：處理數據和執行過程（對象許可權）創建資料庫或資料庫中專案（語句許可權）利用授予預定義角色的許可權（暗示性許可權）

2.許可許可權包括：GRANT：授予用戶有訪問許可權REVOKE：撤銷已經授予、或撤銷已經拒絕的許可權DENY：拒絕用戶有訪問許可權三者之間的關係如圖10-11所示。GRANT:

CanPerformActionREVOKE:NeutralDENY:

CannotPerformAction圖10-11許可許可權之間的關係10.4.2對象許可權1．對象許可權類別處理數據或執行過程時需要的許可權稱為對象許可權。SELECT、INSERT、UPDATE和DELETE語句許可權，它們可以應用到整個表或視圖中。SELECT和UPDATE語句許可權，它們可以有選擇性地應用到表或視圖中單個列上。SELECT許可權，它們可以應用到用戶定義函數。INSERT和DELETE語句許可權，它們會影響整行，因此只可以應用到表或視圖中，而不能應用到單個列上。EXEXUTE語句許可權，它們可以影響存儲過程和函數。2．分配權限

【例10-4】給public角色授予SELECT許可權。然後，將特定的許可權授予用戶wang、zhangsan、tom。於是這些用戶有了對“班級表”的所有權限。

在查詢分析器中執行以下命令即可：

GRANTSELECTON班級表TOpublic

GRANTINSERT,UPDATE,DELETEON班級表TOwang,zhangsan,tom

上述過程既是分配對象許可權的過程。

撤銷許可權只要將GRANT換為REVOKE，拒絕許可權只要將GRANT換為DENY即可。10.4.3語句許可權

創建資料庫或資料庫中的項（如表或存儲過程）所涉及的活動要求另一類稱為語句許可權的許可權。例如，如果用戶必須能夠在資料庫中創建表，則應該向該用戶授予CREATETABLE語句許可權。語句許可權適用於語句自身，而不適用於資料庫中定義的特定對象。

1．語句許可權有：

BACKUPDATABASE

BACKUPLOG

CREATEDATABASE

CREATEDEFAULT

CREATEFUNCTION

CREATEPROCEDURE

CREATERULE

CREATETABLE

CREATEVIEW2．分配權限

【例10-5】使用戶wang和dss_ibm390x\zhangsan做建立資料庫和建立表的工作。

在查詢分析器中執行以下命令即可：

GRANTCREATEDATABASE,CREATETABLETOwang,[dss_ibm390x\zhangsan]

上述過程既是分配語句許可權的過程。

撤銷許可權只要將GRANT換為REVOKE，拒絕許可權只要將GRANT換為DENY即可。

10.4.4使用企業管理器授予、撤銷和拒絕許可權分配

具體操作步驟如下：

1．從對象列表設置對象許可權

①展開企業管理器，然後展開【資料庫】，指定資料庫。

②在指定資料庫的對象列表（表、視圖、存儲過程等）中，右擊某對象，並從彈出的快捷菜單中選擇【屬性】命令，打開【對象屬性】對話框。③單擊【許可權】按鈕，打開【對象屬性】對話框，如圖10-12所示。

④【對象屬性】對話框顯示了所有的用戶和角色，可以使用它來修改這些用戶和角色的許可權。

⑤在□上打√為授予許可權，打〤為拒絕，無為撤銷。

⑥對話框底部的【列】按鈕可以打開【列許可權】對話框，利用【列許可權】對話框可以為單個列設置select和update許可權，如圖10-13所示

⑦點擊【確定】按鈕，許可權分配完畢。圖10-12【對象屬性】對話框圖10-13【列許可權】對話框2．從用戶列表設置對象許可權

①展開企業管理，然後展開【資料庫】，指定資料庫。

②在指定資料庫的用戶列表中，選擇一個用戶點擊滑鼠右鍵，並從彈出的快捷菜單中選擇【屬性】，打開【資料庫用戶屬性】對話框，如圖10-14所示。圖10-14【資料庫用戶屬性】對話框圖10-15【資料庫用戶屬性】的【許可權】對話框③在屬性框中可以將用戶加入到資料庫角色中，點擊【許可權】按鈕將會打開【許可權】選項卡，如圖10-15所示。在此可以為用戶設置單個許可權。

④點擊【確定】，許可權設置完畢。

3．從角色列表設置對象許可權

①展開企業管理，然後展開【資料庫】，指定資料庫

②在指定資料庫的角色列表中，選擇一個角色點擊滑鼠右鍵，並從彈出的快捷方式菜單中選擇【屬性】，打開【資料庫角色屬性】對話框，如圖10-16所示。圖10-16【資料庫角色屬性】對話框③在屬性框中可以將用戶或其他角色加入到指定的角色，也可以將它們從角色中刪除。通過點擊【許可權】按鈕將會打開【許可權】選項卡，如圖10-17所示。在此可以為角色設置對象許可權。

④點擊【確定】，許可權設置完畢。圖10-17【資料庫角色屬性】的【許可權】對話框本章小結

在本章中同學們學習了SQLServers的安全機制和安全管理的基礎知識，並重點學習了登錄帳號、用戶帳號的創建和管理、許可權分配和靈活使用角色實現許可權管理的技術。本章應重點掌握如何根據安全規劃，創建登錄帳號和用戶帳號，並對其進行合理的許可權分配和有效管理。思考與練習

一、選擇題

1、下列語句中不是語句許可權的是（）。

A、CREATEDATABASEB、CREATETABLEC、INSERTD、BACKUPLOG

2、可以在SQLServer中執行任何任務的角色是（）。

A、db_ownerB、sysadminC、serveradminD、setupadmin

3、允許沒有用戶帳號的登錄，且能訪問資料庫的用戶帳號是（）。

A、saB、administratorC、guestD、dbo

4、（）是授予用戶有訪問許可權。

A、CREATEB、DENYC、GRANTD、REVOKE

5、查看角色資訊的存儲過程是（）。

A、sp_helpB、sp_helpuserC、sp_helproleD、sp_helplogin

6、新建資料庫用戶的系統存儲過程是（）。

A、sp_revokedbacce