第9章SQL Server安全管理

1第9章SQLServer安全管理第9章SQLServer安全管理

9.1數(shù)據(jù)庫的安全性概念9.2實現(xiàn)數(shù)據(jù)庫的安全性管理方法23

數(shù)據(jù)庫的安全性由數(shù)據(jù)庫角色來控制。當用戶成功地連接到服務器之后，會在此服務器上的數(shù)據(jù)庫角色中查找相應用戶的用戶名。如果在數(shù)據(jù)庫角色中找到了用戶名，則該用戶可以查看該數(shù)據(jù)庫的名稱和其中的數(shù)據(jù)集列表（包括虛擬的和鏈接的數(shù)據(jù)表）。但是，該用戶只能訪問那些已經(jīng)指派了數(shù)據(jù)庫角色的數(shù)據(jù)表。如果在數(shù)據(jù)庫角色中沒有找到用戶名，則該用戶不能查看或訪問服務器上的任何對象。9.1數(shù)據(jù)庫的安全性概念49.2實現(xiàn)數(shù)據(jù)庫的安全性

管理方法9.2.1SQLServer身份驗證模式9.2.2創(chuàng)建登錄賬號和用戶賬號管理9.2.3權限管理59.2實現(xiàn)數(shù)據(jù)庫的安全性

管理方法用戶具體訪問數(shù)據(jù)時，要經(jīng)過以下3個階段的處理過程。①用戶必須登錄到SQLServer的實例，進行身份鑒別，被確認合法后才能登錄到SQLServer實例。②用戶在每個要訪問的數(shù)據(jù)庫里必須有一個賬號，SQLServer實例將SQLServer登錄映射到數(shù)據(jù)庫用戶賬號上，在這個數(shù)據(jù)庫的賬號上定義數(shù)據(jù)庫的管理和數(shù)據(jù)對象訪問的安全策略。③檢查用戶是否具有訪問數(shù)據(jù)對象、執(zhí)行操作的權限，經(jīng)過語句許可權限的驗證，才能實現(xiàn)對數(shù)據(jù)的操作。69.2.1SQLServer身份驗證模式SQLServer2005提供了兩種確認用戶對數(shù)據(jù)庫引擎服務的驗證模式：Windows身份驗證模式和混合模式驗證方式（Windows身份驗證和SQLServer身份驗證）。1．Windows身份驗證當用戶通過MicrosoftWindows用戶賬戶進行連接時，SQLServer使用Windows操作系統(tǒng)中的信息驗證賬戶名和密碼。也就是說，只要用戶能夠登錄Windows操作系統(tǒng)，就能夠使用SQLServer服務器。這是默認的身份驗證模式，比混合模式更為安全。Windows身份驗證使用Kerberos安全協(xié)議，通過強密碼的復雜性驗證提供密碼策略強制實施，提供賬戶鎖定支持，并且支持密碼過期。782．混合模式身份驗證混合模式包含了Windows身份驗證和SQLServer身份驗證兩個子模式。用戶可以使用Windows身份驗證或者SQLServer身份驗證與SQLServer實例連接。在使用Windows身份驗證時，通過WindowsNT4.0或Windows用戶賬戶使用信任連接登錄SQLServer。在SQLServer身份驗證時，用戶必須提供登錄名和口令，SQLServer通過檢查是否已注冊了該SQLServer登錄賬號，以及指定的密碼是否與以前記錄的密碼匹配，自己進行身份驗證。如果SQLServer未設置登錄賬號，則身份驗證將失敗，如圖9-1所示。9圖9-1SQLServer的身份驗證10

9.2.2創(chuàng)建登錄賬號和用戶賬號管理SQLServer賬號有兩種：一種是服務器的登錄賬號，另一種是數(shù)據(jù)庫的用戶賬號。登錄賬號是指能登錄到SQLServer服務器的賬號，所有的登錄賬號信息都被存儲在系統(tǒng)表syslogins中，具有登錄賬號的用戶，也不能訪問服務器中的數(shù)據(jù)庫，要訪問服務器中的數(shù)據(jù)庫時，還必須要有用戶賬號，用戶賬號與用戶數(shù)據(jù)庫相關，數(shù)據(jù)庫中對象的全部權限和所有權由用戶賬號來控制，所有的用戶賬號都存放在系統(tǒng)表sysusers中。11使用圖形界面方式和使用SQL命令都能創(chuàng)建和修改登錄賬戶，通常使用圖形界面創(chuàng)建登錄賬戶更為方便。創(chuàng)建登錄賬戶時，需要指出該賬戶的登錄是使用Windows身份驗證還是使用SQLServer身份驗證。如果使用Windows身份驗證登錄SQLServer，則該登錄賬戶必須是Windows的系統(tǒng)賬戶。1．創(chuàng)建登錄賬號不管使用哪種驗證模式，用戶都必須具有有效的登錄賬號。12通常可以使用SQLServerManagementStudio(管理控制臺)和使用SQL語句創(chuàng)建登錄賬號。（1）使用SQLServerManagementStudio(管理控制臺)創(chuàng)建Windows身份驗證的登錄賬號①單擊“開始”菜單，選擇“程序”→“MicrosoftSQLServer2005”→“SQLServerManagementStudio”。②在管理控制臺的“對象資源管理器”中，單擊“安全性”項左側的加號，展開“安全性”，單擊“登錄名”項左側的加號，展開“登錄名”，如圖9-2所示。③右擊“登錄名”項，在彈出的菜單中選擇“新建登錄名”選項，打開“登錄名—新建”對話框，如圖9-3所示。13圖9-2“對象資源管理器”窗口14圖9-3“登錄名—新建”對話框15④在“登錄名”文本框中輸入Windows系統(tǒng)中的用戶名作為SQLServer的登錄名（test）；或者單擊文本框旁邊的“搜索”按鈕，打開“選擇用戶或組”對話框，如圖9-4所示。⑤在“選擇用戶或組”對話框中，單擊“高級”按鈕，再單擊“立即查找”按鈕，此時對話框中會顯示W(wǎng)indows系統(tǒng)中的所有賬號和組，如圖9-5所示，然后選擇用戶。最后兩次單擊“確定”按鈕，返回“登錄名—新建”對話框。注意，在這之前需要創(chuàng)建用戶，具體方法是單擊“開始”→“控制面板”→“管理工具”→“計算機管理”→“本地用戶和組”，然后右擊“用戶”，選中“新用戶”，在出現(xiàn)的“新用戶”窗口中，輸入用戶名和密碼等。16圖9-4“選擇用戶或組”對話框17圖9-5“選擇Windows用戶組”對話框18⑥在“登錄名—新建”對話框中選擇“服務器角色”頁，如圖9-6所示。在服務器角色列表框中，列出了系統(tǒng)的固定服務器角色。點選相應復選框選定相應的服務器角色成員。⑦選擇“用戶映射”頁，如圖9-7所示。對話框上半部分的列表框列出了“映射到此登錄名”的用戶，單擊左邊的復選框選定該登陸賬號可以訪問的數(shù)據(jù)庫以及該賬號在各個數(shù)據(jù)庫中對應的用戶名；下半部分的列表框列出了相應的“數(shù)據(jù)庫角色成員身份”，從中可以指定該賬號所屬的數(shù)據(jù)庫角色。19圖9-6“服務器角色”選擇頁20圖9-7“用戶映射”選擇頁21⑧選擇“安全對象”頁，如圖9-8所示。安全對象是指SQLServer數(shù)據(jù)庫引擎授權系統(tǒng)需控制對其進行訪問的資源，單擊“添加”按鈕，可完成對不同類型的安全對象進行安全授予或拒絕。⑨設置完成后，單擊“確定”即可完成服務器登陸賬號的創(chuàng)建。22圖9-8“安全對象”選擇頁23（2）使用SQLServerManagementStudio(管理控制臺)創(chuàng)建SQLServer身份驗證的登錄賬號SQLServer身份驗證的登錄賬號，是由SQLServer自身負責身份驗證的，不要求有對應的系統(tǒng)賬戶，這也是許多大型數(shù)據(jù)庫所采用的方式。①在管理控制臺的“對象資源管理器”中，單擊“安全性”項左側的加號，展開“安全性”，單擊“登錄名”項左側的加號，展開“登錄名”，可見圖9-2。24②右擊“登錄名”項，在彈出的菜單中選擇“新建登錄名”選項，打開“登錄名—新建”對話框，如圖9-9所示。選擇“常規(guī)”選項，在確認身份驗證中選中“SQLServer身份驗證”，在“登錄名”文本框中輸入新建登錄的名稱，在“密碼”文本框中輸入登錄密碼。③分別選擇“服務器角色”、“用戶映射”和“安全對象”選項，如圖9-6、圖9-7、圖9-8所示，指定服務器角色等訪問的數(shù)據(jù)庫及數(shù)據(jù)庫角色，④設置完成后，單擊“確定”即可完成服務器登陸賬號的創(chuàng)建。25圖9-9新建SQLServer身份驗證登錄對話框26（3）使用SQL語句創(chuàng)建服務器登錄賬號除了可以使用圖形界面方式創(chuàng)建登錄賬號外，還可以使用SQL命令方式創(chuàng)建SQLServer登錄賬號，即可以使用系統(tǒng)的存儲過程來創(chuàng)建使用SQLServe身份驗證的登錄賬號和Windows身份驗證的SQLServer登錄賬號?？梢允褂胹p_addlogin來創(chuàng)建使用SQLServe身份驗證的登錄賬號。具體語法如下。EXECUTEsp_addlogin'登錄名','登錄密碼','默認數(shù)據(jù)庫','默認語言'27說明：①其中，登錄名不能有反斜杠“\”，保留的登錄名（如sa）或者已經(jīng)存在的登錄名，也不能由空字符串或NULL值。②在sp_addlogin中，除登錄名外，其余參數(shù)均為可選項，如果不指定登錄密碼，則登錄密碼為NULL；如果不指定默認數(shù)據(jù)庫，則使用系統(tǒng)數(shù)據(jù)庫master；如果不指定默認語言，則使用服務器當前的默認語言。③執(zhí)行系統(tǒng)存儲過程sp_addlogin時，必須具有相應的權限，只有sysadmin和securityadmin固定服務器角色的成員才可以執(zhí)行該存儲過程。28可以使用sp_grantlogin來創(chuàng)建使用Windows身份驗證的SQLServer登錄賬號。具體語法如下：EXECUTEsp_grantlogin'登錄名'說明：①此處的登錄名是要映射的Windows系統(tǒng)賬戶名或賬戶組名，必須使用“域名\用戶”的格式。②執(zhí)行系統(tǒng)存儲過程sp_grantlogin時，必須具有相應的權限，只有sysadmin和securityadmin固定服務器角色的成員才可以執(zhí)行該存儲過程。29

【例9.1】創(chuàng)建一個名為stu01,使用SQLServer身份驗證的登錄賬號，密碼為stu04，默認數(shù)據(jù)庫為TSGL，默認語言不變。程序代碼如下：Executesp_addlogin'stu01','stu01','TSGL‘【例9.2】創(chuàng)建一個名為stu02,使用Windows身份驗證的

SQLServer登錄賬號。程序代碼如下：Executesp_addlogin'LIN1\stu01'302．用戶賬號管理 在數(shù)據(jù)庫中，一個用戶或工作組取得合法的登錄賬號，只表明該賬號通過了Windows驗證或者SQLServer驗證，不能表明它可以對數(shù)據(jù)庫的對象進行某些操作，只有當它同時擁有了用戶賬號后，才可以訪問數(shù)據(jù)庫。數(shù)據(jù)庫的安全性主要是靠管理數(shù)據(jù)庫用戶賬號來控制的。（1）使用SQLServerManagementStudio(管理控制臺)添加數(shù)據(jù)庫用戶①在SQLServerManagementStudio(管理控制臺)的“對象資源管理器”窗口中，依次展開“數(shù)據(jù)庫”、“用戶數(shù)據(jù)庫（本圖例中為TSGL）”、“安全性”、“用戶”。31②右擊“用戶”，如圖9-10所示,從彈出的快捷菜單中選擇“新建數(shù)據(jù)庫用戶”命令，則會出現(xiàn)“數(shù)據(jù)庫用戶—新建”對話框，如圖9-11所示。在“用戶名”文本框中輸入用戶名；單擊“登錄名”右側的按鈕，選擇登錄賬號，或者直接輸入SQLServer的登錄名作為需要授權訪問數(shù)據(jù)庫SQLServer登錄賬號。③在“此用戶擁有的架構”列表框中選擇擁有的架構。在“數(shù)據(jù)庫角色成員身份”列表框中選擇新建用戶應該屬于的數(shù)據(jù)庫成員角色，如選擇db_datareader授權給指定的用戶test。④設置完成后，單擊“確定”按鈕，即可為數(shù)據(jù)庫創(chuàng)建一個新的用戶賬號。32圖9-10“新建用戶”對話框33圖9-11“數(shù)據(jù)庫用戶—新建”對話框34（2）使用SQL語句創(chuàng)建數(shù)據(jù)庫的用戶賬號使用存儲過程sp_grantdbaccess可以為一個登錄賬戶在當前數(shù)據(jù)庫中映射一個或多個數(shù)據(jù)庫用戶，使它具有默認的數(shù)據(jù)庫角色public，具體語法格式如下。EXECUTEsp_grantdbaccess'登錄名','用戶名'說明①登錄名可以是Windows身份驗證的登錄名，也可以是SQLServer身份驗證的登錄名。②用戶名是該數(shù)據(jù)庫中使用的，如沒有指定，則直接使用登錄名③使用該存儲過程，只能向當前數(shù)據(jù)庫中添加用戶登錄賬號的用戶名，而不能添加sa的用戶名。35【例9.3】使用SQL語句，為Windows身份驗證的登錄賬號LIN1\test和SQLServer身份驗證的登錄賬號stu01，在數(shù)據(jù)庫TSGL中分別建立用戶名test和stu01的數(shù)據(jù)庫用戶。程序代碼如下：USETSGLGOEXECUTEp_grantdbaccess'LIN1\test','test'EXECUTEsp_grantdbaccess'stu01'GO363．管理登錄賬號和用戶賬號 （1）查看服務器的登錄賬號①使用SQLServerManagementStudio(管理控制臺)查看登錄賬號進入SQLServerManagementStudio(管理控制臺)，在對象資源管理器中，展開“服務器”→“安全性”→“登錄名”，即可查看SQLServer系統(tǒng)創(chuàng)建的默認登錄賬號及已經(jīng)建立的登錄賬號。②使用SQL語句查看登錄賬號使用sp_helplogins可以查看登錄賬號，其語法格式為：EXECsp_helplogins37（2）查看數(shù)據(jù)庫的用戶賬號①使用SQLServerManagementStudio(管理控制臺)查看數(shù)據(jù)庫的用戶賬號進入SQLServerManagementStudio(管理控制臺)，在對象資源管理器中，展開“服務器”→“數(shù)據(jù)庫”→“用戶數(shù)據(jù)庫（如TSGL）”→“安全性”→“用戶”，即可查看當前數(shù)據(jù)庫中所有的用戶賬號。②使用SQL語句查看數(shù)據(jù)庫的用戶賬號使用sp_helpuser可以查看用戶賬號，其語法格式為：EXECsp_helpuser38【例9.4】列出當前TSGL數(shù)據(jù)庫中所有的用戶賬號程序代碼如下：USETSGLGOEXECsp_helpuser39（3）修改登錄賬號①使用SQLServerManagementStudio(管理控制臺)修改登錄賬號進入SQLServerManagementStudio(管理控制臺)，在對象資源管理器中，展開“服務器”→“安全性”→“登錄名”，右擊某一登錄名（如test），在快捷菜單中選擇“屬性”，打開“登錄屬性”對話框，按照前述創(chuàng)建登錄賬號的方法，參見圖9-2～圖9-9，即可修改SQLServer系統(tǒng)已經(jīng)建立的登錄賬號。②使用SQL語句修改登錄賬號使用sp_password改變登錄賬號的密碼，其語法格式如下:EXEXsp_password'原密碼','新密碼','登錄名'使用sp_addsrvrolemember將登錄賬號加入到服務器角色中，其語法格式如下。EXEXsp_addsrvrolemember'登錄名','服務器角色名稱'4041【例9.5】將test登錄賬號的密碼由原來的123456改為654321程序代碼如下：EXEXsp_password'123456','654321','test'【例9.6】將登錄賬號test加入到dbcreator服務器角色中。程序代碼如下：EXEXsp_addsrvrolemember'test','dbcreator'42（4）刪除登錄賬號①使用SQLServerManagementStudio(管理控制臺)刪除登錄賬號進入SQLServerManagementStudio(管理控制臺)，在對象資源管理器中，展開“服務器”→“安全性”→“登錄名”，右擊欲刪除的登錄賬號（如test），在彈出的快捷菜單中單擊“刪除”，打開“刪除對象”對話框，單擊“確定”按鈕，即可完成刪除，如圖9-12所示。②使用SQL語句刪除登錄賬號使用sp_droplogin可以刪除一個SQLServer身份驗證的登錄賬號，其語法格式如下。EXEXsp_droplogin'登錄名'使用sp_revokelogin可以刪除一個Windows身份驗證的登錄賬號，其語法格式如下。EXEXsp_revokelogin'登錄名'43圖9-12“數(shù)據(jù)庫用戶—新建”對話框44【例9.7】使用SQL語句刪除Windows身份驗證的登錄賬號LIN1\test和SQLServer身份驗證的登錄賬號stu01.程序代碼如下：EXEXsp_droplogin'test'EXEXsp_revokelogin'LIN1\stu01'45（5）刪除用戶賬號①使用SQLServerManagementStudio(管理控制臺)刪除登錄賬號進入SQLServerManagementStudio(管理控制臺)，在對象資源管理器中，展開“服務器”→“數(shù)據(jù)庫”→“用戶數(shù)據(jù)庫（如TSGL）”→“安全性”→“用戶”，右擊欲刪除的用戶賬號，在彈出的快捷菜單中選擇“刪除”，打開“刪除對象”對話框，單擊“確定”按鈕，即可完成刪除操作。②使用SQL語句刪除用戶賬號使用sp_revokedbaccess可以刪除數(shù)據(jù)庫中的用戶賬號，其語法格式如下。EXEXsp_revokedbaccess'用戶名'46【例9.8】使用SQL語句刪除數(shù)據(jù)庫用戶stu01程序代碼如下：USETSGLGOEXEXsp_revokedbaccess'stu01'GO479.2.3權限管理1．權限的分類SQLServer中權限可以分為對象權限、語句權限和默認權限三種類型。（1）對象權限對象權限可表示對特定的數(shù)據(jù)庫對象（如表、視圖、存儲過程）的操作權限，它決定了能對表、視圖等數(shù)據(jù)庫對象執(zhí)行哪些操作。如果用戶想要對某一對象進行操作，其必須具有相應的操作權限。表和視圖權限用來控制用戶在表和視圖上執(zhí)行SELECT、INSERT、UPDATE、DELETE語句的能力，字段權限用來控制用戶在單個字段上執(zhí)行SELET、UPDATE和REFERENCES操作的能力。存儲過程權限用來控制用戶執(zhí)行EXEUTE語句的能力。48（2）語句權限語句權限可表示對數(shù)據(jù)庫的操作權限，即創(chuàng)建數(shù)據(jù)庫或者創(chuàng)建數(shù)據(jù)庫中的其它內(nèi)容所需要的權限類型稱為語句權限，這些語句通暢是一些具有管理性的操作，如創(chuàng)建數(shù)據(jù)庫、表和存儲過程等，這種語句雖然仍包含有操作的對象，但這些對象在執(zhí)行該語句之前并不存在于數(shù)據(jù)庫中。因此，語句權限針對的是某個SQL語句，而不是數(shù)據(jù)庫中已經(jīng)創(chuàng)建的特定的數(shù)據(jù)庫對象。（3）默認權限默認權限是指系統(tǒng)安裝以后用戶和角色不必授權就有的權限，這種角色包括固定服務器角色和固定數(shù)據(jù)庫角色，用戶包括數(shù)據(jù)庫對象所有者，只有固定角色或者數(shù)據(jù)庫對象所有者的成員才能執(zhí)行相應操作。492.用戶權限管理用戶權限的管理包括權限的授權、撤銷和拒絕。（1）使用SQLServerManagementStudio(管理控制臺)管理權限。SQLServer可以通過兩種途徑實現(xiàn)對用戶權限的設定：面向單一用戶的權限設置和面向數(shù)據(jù)庫對象的權限設置①面向單一用戶的權限設置按前述方法打開SQLServerManagementStudio(管理控制臺)，在對象資源管理器中，展開“服務器”→“數(shù)據(jù)庫”→“用戶數(shù)據(jù)庫（如TSGL）”→“安全性”→“用戶”，在用戶列表中，右擊要進行設置的用戶賬號，從彈出的快捷菜單中選擇“屬性”，打開“用戶數(shù)據(jù)庫”對話框，在“選擇頁”中選擇“安全對象”，如圖9-13所示。50圖9-13“數(shù)據(jù)庫用戶”對話框51在“數(shù)據(jù)庫用戶”對話框中單擊“添加”按鈕，則彈出“添加對象”對話框，如圖9-14所示。選擇“特定對象”，單擊“確定”按鈕，則會出現(xiàn)“選擇對象”對話框，如圖9-15所示。單擊“對象類型”按鈕后，出現(xiàn)“選擇對象類型”對話框，如圖9-16所示，選擇想要查找的對象類型，單擊“確定”按鈕，返回“選擇對象”對話框，在“選擇對象”對話框中，單擊瀏覽“按鈕，出現(xiàn)“查找對象”對話框，如圖9-17所示，選擇要相應對象（如TSGL），單擊“確定”按鈕，返回“選擇對象”對話框，單擊“確定”按鈕。出現(xiàn)“數(shù)據(jù)庫用戶”對話框，如圖9-18所示。在“顯示權限”列表框中，選擇相應的權限，單擊“確定”按鈕，即可完成相應權限設置。52圖9-14“添加對象”對話框53圖9-15“選擇對象”對話框54圖9-16“選擇對象類型”對話框55圖9-17“查找對象”對話框56圖9-18“數(shù)據(jù)庫用戶”對話框57②面向數(shù)據(jù)庫對象的權限設置打開SQLServerManagementStudio(管理控制臺)，在對象資源管理器中，展開“服務器”→“數(shù)據(jù)庫”→”“用戶數(shù)據(jù)庫（如TSGL）”，選擇需要設置的數(shù)據(jù)庫對象（如表、視圖、存儲過程等），右擊該對象（如dbo.讀者），從彈出的快捷菜單中選擇“屬性”選項，打開“表屬性”對話框，如圖9-19所示。單擊“選擇頁”下的“權限”，單擊“添加”按鈕，出現(xiàn)“選擇用戶或角色”對話框，如圖9-20所示，單擊“瀏覽”按鈕，出現(xiàn)“查找對象對話框”，如圖9-21所示，選擇用戶或數(shù)據(jù)庫角色后，單擊“確定”按鈕，返回到“表屬性”對話框。這時，可以在“顯式權限”將各類權限設置為“授予”、“具有授予權限”、“允許”、“拒絕”。設置完成后，單擊“確定”按鈕即可完成設置。58圖9-19“表屬性”對話框59圖9-20“選擇用戶或角色”對話框60圖9-21“查找對象”對話框61（2）使用SQL語