第6章數(shù)據(jù)庫管理

第6章數(shù)據(jù)庫管理本章教學(xué)目標(biāo)了解SQLServer2012的安全機(jī)制；掌握SQLServer登錄和用戶管理；掌握SQLServer角色及權(quán)限管理；掌握SQLServer中數(shù)據(jù)庫備份和還原的方法；掌握數(shù)據(jù)庫導(dǎo)入/導(dǎo)出的方法；掌握數(shù)據(jù)庫復(fù)制的方法。本章教學(xué)任務(wù)登錄與用戶管理

角色與權(quán)限管理

數(shù)據(jù)庫備份

數(shù)據(jù)庫恢復(fù)

1234數(shù)據(jù)庫導(dǎo)入導(dǎo)出與復(fù)制

5任務(wù)6.1

登錄與用戶管理任務(wù)6.1用戶與權(quán)限管理回顧復(fù)習(xí)任務(wù)描述任務(wù)準(zhǔn)備任務(wù)實(shí)施1234課堂實(shí)踐5課堂討論6小結(jié)與作業(yè)7回顧復(fù)習(xí)1.什么是存儲(chǔ)過程？2.使用存儲(chǔ)過程有什么好處？ 1.什么是存儲(chǔ)過程？

存儲(chǔ)在SQLServer數(shù)據(jù)庫中由T-SQL語句編寫的具有特定功能的代碼段。存儲(chǔ)過程可以包含數(shù)據(jù)操縱語句、變量、邏輯控制語句等,可以帶參數(shù)，也可以返回結(jié)果。intsum(inta,intb){ints;s=a+b;returns;}存儲(chǔ)過程相當(dāng)于C語言中的函數(shù)回顧復(fù)習(xí)2.使用存儲(chǔ)過程有什么好處？執(zhí)行速度更快允許模塊化程序設(shè)計(jì)提高系統(tǒng)安全性減少網(wǎng)絡(luò)流通量任務(wù)描述

1.設(shè)置身份驗(yàn)證模式。設(shè)置SQLServer的驗(yàn)證模式，使其能夠進(jìn)行SQLServer身份驗(yàn)證。

2.登錄名創(chuàng)建。在SQLServer中創(chuàng)建新的登錄名并設(shè)置密碼。

3.添加數(shù)據(jù)庫用戶。在圖書借閱數(shù)據(jù)庫中添加新的數(shù)據(jù)庫用戶并關(guān)聯(lián)登錄名。

4.T-SQL管理登錄和用戶。使用T-SQL語句創(chuàng)建登錄賬戶和數(shù)據(jù)庫用戶。數(shù)據(jù)庫1數(shù)據(jù)庫2數(shù)據(jù)庫3表1表2表1表2表1表2增刪改查增刪改查增刪改查增刪改查增刪改查增刪改查數(shù)據(jù)庫用戶數(shù)據(jù)庫用戶數(shù)據(jù)庫用戶登錄帳號SQLserver三層安全管理任務(wù)準(zhǔn)備SQLServer2012提供了兩種身份驗(yàn)證模式：Windows身份驗(yàn)證模式:Windows身份驗(yàn)證模式是指采用數(shù)據(jù)庫服務(wù)器的內(nèi)置Windows賬戶來進(jìn)行驗(yàn)證和登錄，適合于windows平臺用戶，不需要提供密碼，和windows集成驗(yàn)證。SQLServer驗(yàn)證模式：SQLServer驗(yàn)證模式是采用數(shù)據(jù)庫內(nèi)設(shè)的登錄名來進(jìn)行登錄。適合于非windows平臺的用戶或Internet用戶，需要提供帳戶和密碼任務(wù)準(zhǔn)備SQLServer2012提供了兩種身份驗(yàn)證模式：Windows身份驗(yàn)證模式:Windows身份驗(yàn)證模式是指采用數(shù)據(jù)庫服務(wù)器的內(nèi)置Windows賬戶來進(jìn)行驗(yàn)證和登錄，適合于windows平臺用戶，不需要提供密碼，和windows集成驗(yàn)證。SQLServer驗(yàn)證模式：SQLServer驗(yàn)證模式是采用數(shù)據(jù)庫內(nèi)設(shè)的登錄名來進(jìn)行登錄。適合于非windows平臺的用戶或Internet用戶，需要提供帳戶和密碼任務(wù)準(zhǔn)備登錄名：登錄名是存放在服務(wù)器上的一個(gè)實(shí)體，使用登錄名可以進(jìn)入服務(wù)器，但是不能訪問服務(wù)器中的數(shù)據(jù)庫資源。數(shù)據(jù)庫用戶名：用戶名是一個(gè)或多個(gè)登錄名在數(shù)據(jù)庫中的映射，通過對用戶名進(jìn)行授權(quán)后，可以為登錄名提供數(shù)據(jù)庫的訪問權(quán)限。任務(wù)準(zhǔn)備（1）創(chuàng)建SQLServer登錄名基本語句格式：CREATELOGIN登錄名（2）修改登錄名。使用ALTERLOGIN語句可以修改登錄名的密碼和用戶名，基本語句格式：ALTERLOGIN登錄名WITH<修改項(xiàng)>[,..n]（3）創(chuàng)建SQLServer數(shù)據(jù)庫用戶基本語句格式：CREATEUSER數(shù)據(jù)庫用戶名[{FOR|FROM}{LOGIN登錄名}|WITHOUTLOGIN]

任務(wù)實(shí)施1身份驗(yàn)證模式設(shè)置，詳見教材P194頁。任務(wù)實(shí)施2．登錄名創(chuàng)建，詳見教材P196頁。任務(wù)實(shí)施3．?dāng)?shù)據(jù)庫用戶添加，詳見教材P197頁。任務(wù)實(shí)施4.T-SQL管理登錄和用戶，詳見教材P198頁。（1）創(chuàng)建名為“NewAdmin”的登錄名，初始密碼為“66666”。CREATELOGINNewAdminWITHPASSWORD='66666'GO

將名為“NewAdmin”的登錄密碼由“666666”修改為“88888”；ALTERLOGINNewAdminWITHPASSWORD='888888'GO（2）創(chuàng)建Windows用戶的登錄名DBAdmin（對應(yīng)Windows用戶為DBAdmin）CREATELOGIN[mac-pc\DBAdmin]FROMWINDOWSGO任務(wù)實(shí)施（3）如要?jiǎng)h除登錄名“NewAdmin”.DROPLOGINNewAdminGO（4）使用T-SQL管理數(shù)據(jù)庫用戶要求創(chuàng)建與登錄名“NewAdmin”關(guān)聯(lián)的DB_CRM數(shù)據(jù)庫用戶，數(shù)據(jù)庫用戶名為“李軍”。USEDB_CRMGOCREATEUSER李軍FORLOGINNewAdminGO（5）將數(shù)據(jù)庫用戶“李軍”的名稱修改為“李強(qiáng)”USEDB_CRMGOALTERUSER李軍WITHNAME=李強(qiáng)（6）查看當(dāng)前數(shù)據(jù)庫中的數(shù)據(jù)庫用戶信息。EXECsp_helpuserGO課堂實(shí)踐與檢查（1）按照任務(wù)實(shí)施過程的要求完成各子任務(wù)并檢查實(shí)施結(jié)果。（2）在SQLServer中創(chuàng)建新的登錄名。要求：設(shè)置登錄名稱為“CRM管理員1”；設(shè)置登錄密碼為“123456”。（3）在DB_CRM客戶關(guān)系管理數(shù)據(jù)庫中添加用戶。要求：設(shè)置數(shù)據(jù)庫用戶名為“DBAdmin1”；指定該用戶的登錄賬戶為“CRM管理員1”。（4）使用T-SQL語句創(chuàng)建新的登錄名。要求：設(shè)置登錄名稱為“CRM管理員2”；設(shè)置登錄密碼為“123456”。（5）使用T-SQL修改“CRM管理員2”登錄密碼為“555666”。（6）使用T-SQL語句創(chuàng)建與登錄名“CRM管理員2”對應(yīng)的數(shù)據(jù)庫用戶“DBAdmin2”。課堂討論1.討論Windows驗(yàn)證模式和SQLServer驗(yàn)證模式有什么區(qū)別，各自的適用范圍是什么。2.總結(jié)討論數(shù)據(jù)庫用戶名和SQLServer登錄名有什么區(qū)別和聯(lián)系。3.技術(shù)基礎(chǔ)、理論基礎(chǔ)中的相關(guān)內(nèi)容小結(jié)與作業(yè)本任務(wù)主要包括以下知識：1.身份驗(yàn)證模式設(shè)置。2.登錄名創(chuàng)建。3.數(shù)據(jù)庫用戶添加。4.T-SQL管理賬戶和數(shù)據(jù)庫用戶。完成綜合訓(xùn)練數(shù)據(jù)庫相關(guān)部分完成本章練習(xí)任務(wù)6.2

權(quán)限與角色管理任務(wù)6.2權(quán)限與角色管理回顧復(fù)習(xí)任務(wù)描述任務(wù)準(zhǔn)備任務(wù)實(shí)施1234課堂實(shí)踐5課堂討論6小結(jié)與作業(yè)7任務(wù)描述

1.SSMS設(shè)置用戶權(quán)限。通過SSMS實(shí)現(xiàn)對CRM客戶關(guān)系管理數(shù)據(jù)庫用戶進(jìn)行權(quán)限設(shè)置。

2.T-SQL分配權(quán)限。通過T-SQL,實(shí)現(xiàn)對CRM客戶關(guān)系管理數(shù)據(jù)庫用戶進(jìn)行權(quán)限設(shè)置。

3.角色設(shè)置。通過SSMS和T-SQL創(chuàng)建角色，實(shí)現(xiàn)把具有相同訪問權(quán)限的登錄賬戶進(jìn)行集中管理。任務(wù)準(zhǔn)備用戶權(quán)限:用戶權(quán)限是指使用和操作數(shù)據(jù)庫對象的權(quán)利，用戶權(quán)限指明了用戶可以獲得哪些數(shù)據(jù)庫對象的使用權(quán)以及用戶能夠?qū)@些對象執(zhí)行何種操作。查詢權(quán)限SELECT添加權(quán)限INSERT修改權(quán)限UPDATE刪除權(quán)限D(zhuǎn)ELETE對象權(quán)限語句權(quán)限語句權(quán)限指是否允許執(zhí)行特定的語句，如：CREATEDATABASE、CREATEDEFAULT、CREATEFUNCTION、CREATEPROCEDURE、CREATERULE、CREATETABLE、CREATEVIEW、BACKUPDATABASE、BACKUPLOG。任務(wù)準(zhǔn)備用戶角色：用戶角色是SQLServer2012用來集中管理數(shù)據(jù)庫或者服務(wù)器權(quán)限的方式。數(shù)據(jù)庫管理員通過將權(quán)限賦予角色，然后再將角色配置給數(shù)據(jù)庫用戶或者登錄賬戶，從而使這些數(shù)據(jù)庫用戶或者登錄名擁有了角色所具備的權(quán)限。教師角色學(xué)生角色管理員角色員工角色任務(wù)準(zhǔn)備數(shù)據(jù)庫角色1.固定服務(wù)器角色2.固定數(shù)據(jù)庫角色3.用戶自定義角色4.應(yīng)用程序角色數(shù)據(jù)庫權(quán)限

1.權(quán)限種類 2.權(quán)限設(shè)置下面技術(shù)知識請同學(xué)們課內(nèi)或課外進(jìn)行自主學(xué)習(xí)任務(wù)準(zhǔn)備T-SQL命令管理權(quán)限可以采用T-SQL語句來進(jìn)行權(quán)限管理。具體命令語法如下：授予權(quán)限。使用GRANT語句進(jìn)行授權(quán)活動(dòng)，其語法為：GRANT{ALL|statement[,…n]}TOsecurity_account[,…n]其中：ALL表示授予所有可以應(yīng)用的權(quán)限；Statement表示可以授予權(quán)限的命令，如：CREATEDATABASE；security_account定義授予權(quán)限的用戶。撤銷權(quán)限。使用REVOKE語句撤銷權(quán)限，其語法為：REVOKE{ALL|statement[,…n]}FROMsecurity_account[,…n]拒絕權(quán)限。在授予了用戶對象權(quán)限后，數(shù)據(jù)庫管理員可以根據(jù)實(shí)際情況在不撤銷用戶訪問權(quán)限的情況下，拒絕用戶訪問數(shù)據(jù)庫對象。拒絕對象權(quán)限的語法為：DENY{ALL|statement[,…n]}TOsecurity_account[,…n]任務(wù)實(shí)施（1）指定服務(wù)器權(quán)限，詳見教材P202頁。任務(wù)實(shí)施（2）設(shè)置數(shù)據(jù)庫權(quán)限，詳見教材P203頁。任務(wù)實(shí)施（3）設(shè)置數(shù)據(jù)庫對象權(quán)限，詳見教材P203頁。任務(wù)實(shí)施（4）設(shè)置用戶權(quán)限，詳見教材P204頁。任務(wù)實(shí)施T-SQL分配權(quán)限（1）要求：使用T-SQL語句授予用戶“李軍”對DB_CRM數(shù)據(jù)庫中TB_Salesman查詢和添加權(quán)限。

GRANTSELECT,INSERTONTB_SalesmanTO李軍（2）要求：使用T-SQL語句授予用戶“李軍”在DB_CRM數(shù)據(jù)庫中有創(chuàng)建表和視圖的權(quán)限。

GRANTCREATETABLE,CREATEVIEWTO李軍（3）要求：使用T-SQL語句禁止用戶“李軍”對DB_CRM數(shù)據(jù)庫中TB_Salesman表更新和刪除權(quán)限。

DENYDELETE,UPDATEONTB_SalesmanTO李軍

(4)使用T-SQL語句撤銷權(quán)限①要求：使用T-SQL語句撤銷用戶“李軍”對DB_CRM數(shù)據(jù)庫中TB_Salesman添加權(quán)限。

REVOKEINSERTONTB_SalesmanFROM李軍

②要求：使用T-SQL語句撤銷用戶“李軍”在DB_CRM數(shù)據(jù)庫中有創(chuàng)建表的權(quán)限。

REVOKECREATETABLEFROM李軍任務(wù)實(shí)施角色設(shè)置，詳見教材P205頁。任務(wù)實(shí)施角色設(shè)置，詳見教材P205頁。課堂實(shí)踐1）按照任務(wù)實(shí)施過程的要求完成各子任務(wù)并檢查實(shí)施結(jié)果。（2）對用戶“DBAdmin1”進(jìn)行權(quán)限設(shè)置。要求：使其在DB_CRM中有創(chuàng)建表和視圖的權(quán)限；使其對產(chǎn)品表和訂購表的訪問權(quán)限為只讀。（3）使用T-SQL語句對用戶“DBAdmin2”進(jìn)行權(quán)限，授權(quán)它創(chuàng)建視圖的權(quán)限，對客戶信用評價(jià)檔案表只有只讀權(quán)限，對客戶反饋信息表有查詢、更新和添加的權(quán)限，拒絕對該表刪除的權(quán)限。（4）使用T-SQL語句撤銷用戶“DBAdmin2”對客戶反饋信息表更新和添加的權(quán)限。（5）創(chuàng)建“銷售管理員角色’，擁有對商品表、客戶表、訂購表所有操作的權(quán)限，并且把DBAdmin1用戶歸類為該角色成員。課堂討論1.數(shù)據(jù)庫角色的作用是什么，有什么好處？2.什么是授權(quán)的主體？3.技術(shù)基礎(chǔ)、理論基礎(chǔ)中的相關(guān)內(nèi)容小結(jié)與作業(yè)本任務(wù)主要包括以下知識：1.SSMS設(shè)置用戶權(quán)限2.T-SQL分配權(quán)限3.角色設(shè)置完成綜合訓(xùn)練數(shù)據(jù)庫相關(guān)部分完成本章練習(xí)任務(wù)6.3

數(shù)據(jù)庫備份任務(wù)6.3數(shù)據(jù)庫備份回顧復(fù)習(xí)任務(wù)描述任務(wù)準(zhǔn)備任務(wù)實(shí)施1234課堂實(shí)踐5課堂討論6小結(jié)與作業(yè)7任務(wù)描述

1數(shù)據(jù)庫完整備份。對CRM客戶關(guān)系管理數(shù)據(jù)庫進(jìn)行完整備份。

2數(shù)據(jù)庫差異備份。對CRM客戶關(guān)系管理數(shù)據(jù)庫進(jìn)行差異備份。

3數(shù)據(jù)庫事務(wù)日志備份。對CRM客戶關(guān)系管理數(shù)據(jù)庫進(jìn)行事務(wù)日志備份。

4T-SQL實(shí)現(xiàn)備份。使用T-SQL語句備份CRM客戶關(guān)系管理數(shù)據(jù)庫。任務(wù)準(zhǔn)備數(shù)據(jù)庫備份：數(shù)據(jù)庫備份是指通過一定的手段來制作數(shù)據(jù)庫結(jié)構(gòu)、對象以及數(shù)據(jù)的拷貝，以便在數(shù)據(jù)庫發(fā)生損壞時(shí)能夠?qū)?shù)據(jù)庫進(jìn)行修復(fù)，以保證相關(guān)系統(tǒng)的正常運(yùn)行。SQLServer2012提供了高性能的備份和恢復(fù)功能，包括有：完整數(shù)據(jù)庫備份、差異數(shù)據(jù)庫備份、事務(wù)日志備份以及文件組備份。任務(wù)準(zhǔn)備使用T-SQL備份數(shù)據(jù)庫的基本語句格式如下：

BACKUPDATABASE數(shù)據(jù)庫名TO備份設(shè)備[,…n]使用T-SQL備份數(shù)據(jù)庫到一個(gè)備份文件中

BACKUPDATABASE數(shù)據(jù)庫名TODISK=’備份文件路徑（包括.BAK后綴名）’備份一個(gè)事務(wù)日志的基本語句格式如下：

BACKUPLOG數(shù)據(jù)庫名TO備份設(shè)備[,…n]任務(wù)實(shí)施1．?dāng)?shù)據(jù)庫完整備份，詳見教材P212頁任務(wù)實(shí)施2.差異備份，詳見教材P214頁任務(wù)實(shí)施3.事務(wù)日志備份，詳見教材P214頁任務(wù)實(shí)施4.T-SQL實(shí)現(xiàn)備份要求把DB_CRM數(shù)據(jù)庫完整備份到D盤的DBbackup的文件夾下，保存的備份文件名為BK_CRM_New.bak。T-SQL命令如下：BACKUPDATABASEDB_CRMTODISK='D:\DBbackup\BK_CRM_New.bak'課堂實(shí)踐（1）按照任務(wù)實(shí)施過程的要求完成各子任務(wù)并檢查實(shí)施結(jié)果。（2）創(chuàng)建邏輯名稱為DBbak01的備份設(shè)備，對應(yīng)的物理文件存放在系統(tǒng)默認(rèn)路徑中。（3）對DB_CRM數(shù)據(jù)庫進(jìn)行一次完整備份，備份到備份設(shè)備DBbak01中。（4）創(chuàng)建邏輯名稱為DBbak02的備份設(shè)備，對應(yīng)的物理文件存放在c:\bak路徑中。（5）對DB_CRM數(shù)據(jù)庫進(jìn)行一次事務(wù)日志備份，備份到備份設(shè)備DBbak02中。（6）使用T-SQL語句對DB_CRM進(jìn)行一次完整備份，備份到c:\bak，備份文件名為DBbak03.bak課堂討論1.討論進(jìn)行完整備份和差異備份各自的特點(diǎn)和適用的場合。2.總結(jié)討論需要采用怎樣的備份措施才能盡可能維護(hù)數(shù)據(jù)庫的完整性。3.技術(shù)基礎(chǔ)、理論基礎(chǔ)中的相關(guān)內(nèi)容小結(jié)與作業(yè)本任務(wù)主要包括以下知識：1.數(shù)據(jù)庫完整備份2.差異備份3.事務(wù)日志備份4.T-SQL實(shí)現(xiàn)備份完成綜合訓(xùn)練數(shù)據(jù)庫相關(guān)部分完成本章練習(xí)任務(wù)6.4

數(shù)據(jù)庫備份任務(wù)6.4數(shù)據(jù)庫還原回顧復(fù)習(xí)任務(wù)描述任務(wù)準(zhǔn)備任務(wù)實(shí)施1234課堂實(shí)踐5課堂討論6小結(jié)與作業(yè)7任務(wù)描述1數(shù)據(jù)庫完全還原。使用數(shù)據(jù)庫備份對CRM客戶關(guān)系管理數(shù)據(jù)庫進(jìn)行完全恢復(fù)。2數(shù)據(jù)庫時(shí)點(diǎn)還原。使用數(shù)據(jù)庫備份把CRM客戶關(guān)系管理數(shù)據(jù)庫恢復(fù)到某一時(shí)間點(diǎn)狀態(tài)。3T-SQL還原數(shù)據(jù)庫。使用T-SQL語句實(shí)現(xiàn)CRM客戶關(guān)系管理數(shù)據(jù)庫還原。任務(wù)準(zhǔn)備數(shù)據(jù)庫還原：數(shù)據(jù)庫恢復(fù)就是指使用已有的數(shù)據(jù)庫備份來重建數(shù)據(jù)庫。SQLServer2012包括了3種恢復(fù)模型，它們分別是：簡單恢復(fù)模型、完全恢復(fù)模型以及大容量日志記錄恢復(fù)模型。不同的恢復(fù)模型在備份、恢復(fù)的方式和性能方面都存在差異。任務(wù)準(zhǔn)備使用T-SQL命令還原數(shù)據(jù)庫的基本語句格式如下：RESTOREDATABASE數(shù)據(jù)庫名FROM備份設(shè)備[,…n]任務(wù)實(shí)施數(shù)據(jù)完整還原，詳見教材P220頁任務(wù)實(shí)施常見問題任務(wù)實(shí)施數(shù)據(jù)時(shí)點(diǎn)還原，詳見教材P221頁任務(wù)實(shí)施使用T-SQL語句把D盤DBbackup文件下的BK_CRM.bak文件還原為數(shù)據(jù)庫“DB_CRM_New3”。

RESTOREDATABASEDB_CRM_New3FROMDISK='D:\DBbackup\BK_CRM.bak'

課堂實(shí)踐（1）按照任務(wù)實(shí)施過程的要求完成各子任務(wù)并檢查實(shí)施結(jié)果。（2）先刪除DB_CRM數(shù)據(jù)庫中的業(yè)務(wù)員任務(wù)計(jì)劃表TB_Task，然后利用任務(wù)3的備份（DBbak01）恢復(fù)數(shù)據(jù)庫到完整備份狀態(tài)。（3）使用T-SQL語句，利用任務(wù)3的備份（DBbak02）實(shí)現(xiàn)數(shù)據(jù)庫的事務(wù)日志恢復(fù)。課堂討論1.討論SQLServer2012中有幾種備份和恢復(fù)模式？2.技術(shù)基礎(chǔ)、理論基礎(chǔ)中的相關(guān)內(nèi)容小結(jié)與作業(yè)本任務(wù)主要包括以下知識：1.數(shù)據(jù)庫完全還原2.數(shù)據(jù)庫時(shí)點(diǎn)還原3.T-SQL還原數(shù)據(jù)庫完成綜合訓(xùn)練數(shù)據(jù)庫相關(guān)部分完成本章練習(xí)任務(wù)6.5

數(shù)據(jù)庫導(dǎo)入/導(dǎo)出與復(fù)制任務(wù)6.5數(shù)據(jù)導(dǎo)入/導(dǎo)出與復(fù)制回顧復(fù)習(xí)任務(wù)描述任務(wù)準(zhǔn)備任務(wù)實(shí)施1234課堂實(shí)踐5課堂討論6小結(jié)與作業(yè)7任務(wù)描述

1數(shù)據(jù)導(dǎo)出到Excel。將DB_CRM數(shù)據(jù)庫中客戶表數(shù)據(jù)的導(dǎo)出到“CRMExecl.xls”Excel文件。

2Excel數(shù)據(jù)導(dǎo)入?！癈RMExecl.xls”Excel文件數(shù)據(jù)導(dǎo)入到DB_CRM_New4數(shù)據(jù)庫。

3數(shù)據(jù)庫復(fù)制。將數(shù)據(jù)庫DB_CRM復(fù)制為DB_CRM_New5。任務(wù)準(zhǔn)備數(shù)據(jù)庫轉(zhuǎn)換：數(shù)據(jù)庫轉(zhuǎn)