MySQL數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用（微課版）教案 5 數(shù)據(jù)庫(kù)安全

第3章面向?qū)ο螅ㄉ希﹤湔n紙（章）課題：項(xiàng)目模塊5數(shù)據(jù)庫(kù)安全篇課時(shí)教學(xué)目的要求：了解MySQL系統(tǒng)數(shù)據(jù)庫(kù)中用戶(hù)與權(quán)限的作用掌握創(chuàng)建和刪除普通用戶(hù)及密碼管理掌握用戶(hù)權(quán)限適當(dāng)分配及角色管理了解事務(wù)的概念和4個(gè)基本特性理解事務(wù)的4種隔離級(jí)別理解鎖機(jī)制掌握MySQL的事務(wù)并發(fā)控制語(yǔ)句教學(xué)重點(diǎn)：了解MySQL系統(tǒng)數(shù)據(jù)庫(kù)中主要權(quán)限表user和db的作用掌握CREATEUSE創(chuàng)建用戶(hù)、DROPUSER刪除用戶(hù)掌握ALTERUSE設(shè)置密碼掌握GRANT、REVOKE基本語(yǔ)法為用戶(hù)、角色分配適當(dāng)?shù)臋?quán)限掌握CREATEROLE創(chuàng)建角色、DROPROLE刪除角色了解事務(wù)的概念和4個(gè)基本特性理解事務(wù)的4種隔離級(jí)別和鎖機(jī)制掌握MySQL的事務(wù)并發(fā)控制語(yǔ)句教學(xué)難點(diǎn)：熟練靈活正確使用CREATEUSE命令創(chuàng)建用戶(hù)、DROPUSER命令刪除用戶(hù)熟練靈活正確使用CREATEROLE命令創(chuàng)建角色、DROPROLE命令刪除角色熟練靈活正確使用GRANT和REVOKE命令為用戶(hù)、角色分配適當(dāng)?shù)臋?quán)限理解事務(wù)的4種隔離級(jí)別和鎖機(jī)制掌握MySQL的事務(wù)并發(fā)控制語(yǔ)句思政素養(yǎng)要點(diǎn)：養(yǎng)成嚴(yán)謹(jǐn)細(xì)致的工作態(tài)度和操作習(xí)慣；安全是基石，憂(yōu)患非憂(yōu)天，預(yù)防保安全，培養(yǎng)“勿忘數(shù)據(jù)安全、全面考慮任務(wù)、未雨綢繆、警鐘長(zhǎng)鳴”的精神；項(xiàng)目數(shù)據(jù)庫(kù)教學(xué)項(xiàng)目：高校教學(xué)質(zhì)量分析管理系統(tǒng)數(shù)據(jù)庫(kù)db_teaching實(shí)踐項(xiàng)目：怡貝銀行儲(chǔ)蓄信貸業(yè)務(wù)管理系統(tǒng)數(shù)據(jù)庫(kù)db_ebank教學(xué)方法：講解、演示、微課、課中課后實(shí)訓(xùn)實(shí)踐教具：理實(shí)一體化機(jī)房、MySQL8.0、Workbench課時(shí)安排節(jié)次內(nèi)容總課時(shí)其中講授實(shí)訓(xùn)課堂作業(yè)1任務(wù)1管理用戶(hù)和權(quán)限2任務(wù)2使用事務(wù)和鎖管理并發(fā)控制3備課紙（節(jié)）課題：任務(wù)1管理用戶(hù)和權(quán)限課時(shí)教學(xué)目的要求：了解MySQL系統(tǒng)數(shù)據(jù)庫(kù)中用戶(hù)與權(quán)限的作用掌握創(chuàng)建和刪除普通用戶(hù)及密碼管理掌握用戶(hù)權(quán)限的適當(dāng)分配掌握角色的創(chuàng)建、刪除和權(quán)限的適當(dāng)分配教學(xué)重點(diǎn)：了解MySQL系統(tǒng)數(shù)據(jù)庫(kù)中主要權(quán)限表user和db的作用掌握CREATEUSE創(chuàng)建用戶(hù)、DROPUSER刪除用戶(hù)掌握ALTERUSE設(shè)置密碼掌握GREATEROLE語(yǔ)句創(chuàng)建角色、GRANT語(yǔ)句為用戶(hù)添加角色、DROPROLE語(yǔ)句刪除角色掌握GRANT、REVOKE基本語(yǔ)法為普遍用戶(hù)、角色分配適當(dāng)?shù)臋?quán)限教學(xué)難點(diǎn)：熟練靈活正確使用CREATEUSE命令創(chuàng)建用戶(hù)、DROPUSER命令刪除用戶(hù)掌握GREATEROLE語(yǔ)句創(chuàng)建角色、GRANT語(yǔ)句為用戶(hù)添加角色、DROPROLE語(yǔ)句刪除角色熟練靈活正確使用GRANT和REVOKE命令為、角色用戶(hù)分配適當(dāng)?shù)臋?quán)限教具：理實(shí)一體化機(jī)房、MySQL8.0、MySQLWorkbench課后小結(jié)教學(xué)過(guò)程：引【學(xué)生線上微課學(xué)習(xí)，引出討論題】數(shù)據(jù)是數(shù)字經(jīng)濟(jì)社會(huì)的重要資源和核心資產(chǎn)，數(shù)據(jù)庫(kù)系統(tǒng)如何保護(hù)數(shù)據(jù)庫(kù)，有效防范信息泄露和篡改，確保數(shù)據(jù)庫(kù)資源被正確安全地訪問(wèn)或執(zhí)行？數(shù)據(jù)庫(kù)系統(tǒng)如何實(shí)現(xiàn)合法用戶(hù)在其權(quán)限范圍內(nèi)對(duì)數(shù)據(jù)庫(kù)進(jìn)行相關(guān)操作？如何實(shí)現(xiàn)用命令行和圖形化工具對(duì)MySQL用戶(hù)管理及進(jìn)行訪問(wèn)控制？導(dǎo)【教師導(dǎo)入任務(wù)情境】“高校教學(xué)質(zhì)量分析管理系統(tǒng)”中需要設(shè)置教學(xué)質(zhì)量督導(dǎo)部門(mén)及教務(wù)處管理員、教師、學(xué)生三種類(lèi)型的用戶(hù)，因每一類(lèi)型用戶(hù)操作數(shù)據(jù)庫(kù)的需求不一樣，所以要分配不同的權(quán)限。教學(xué)質(zhì)量督導(dǎo)部門(mén)及教務(wù)處管理員可以對(duì)所有表數(shù)據(jù)進(jìn)行錄入、刪除、更新、查看等所有權(quán)限，任課教師可以錄入課程評(píng)學(xué)成績(jī)，但不能修改學(xué)生表、課程表等教學(xué)基礎(chǔ)數(shù)據(jù)，學(xué)生則只可以錄入評(píng)學(xué)評(píng)教成績(jī)表中對(duì)課程任課教師的評(píng)教分?jǐn)?shù)，可以查詢(xún)自己的評(píng)學(xué)考試成績(jī)。析【教師帶動(dòng)學(xué)生】分析任務(wù)解決實(shí)施的技術(shù)要點(diǎn)，精講任務(wù)解決的要點(diǎn)了解MySQL系統(tǒng)數(shù)據(jù)庫(kù)中主要權(quán)限表user和db的作用；普遍用戶(hù)的創(chuàng)建、刪除語(yǔ)句；Root用戶(hù)修改自己的密碼和普遍用戶(hù)的密碼；普遍用戶(hù)權(quán)限授予、查看和回收。一、了解MySQL系統(tǒng)數(shù)據(jù)庫(kù)中主要權(quán)限表user和db的作用MySQL服務(wù)器通過(guò)權(quán)限表來(lái)控制用戶(hù)對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)，在安裝MySQL時(shí)，會(huì)自動(dòng)安裝名為mysql的系統(tǒng)數(shù)據(jù)庫(kù)，存放了有關(guān)于用戶(hù)賬戶(hù)和授權(quán)的權(quán)限表，分別是user表（存儲(chǔ)全局權(quán)限表）、db表（存儲(chǔ)數(shù)據(jù)庫(kù)層級(jí)權(quán)限表）、tables_priv表（存儲(chǔ)表層級(jí)權(quán)限表）、columns_priv表（存儲(chǔ)列層級(jí)權(quán)限表）、procs_priv（存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)權(quán)限表）等。MySQL服務(wù)啟動(dòng)時(shí)，會(huì)讀取mysql數(shù)據(jù)庫(kù)中的權(quán)限表，并將表中的數(shù)據(jù)加載到內(nèi)存，當(dāng)用戶(hù)進(jìn)行數(shù)據(jù)庫(kù)訪問(wèn)操作時(shí)，MySQL會(huì)根據(jù)權(quán)限表中的內(nèi)容對(duì)用戶(hù)做相應(yīng)的權(quán)限控制。mysql系統(tǒng)數(shù)據(jù)庫(kù)中的user表是權(quán)限表中最為重要的表，它記錄了允許連接到服務(wù)器的賬號(hào)信息和一些全局級(jí)的權(quán)限信息，有51個(gè)字段，這些字段可以分成4類(lèi)，分別是范圍列、權(quán)限列、安全列和資源控制列。MySQL中可通過(guò)“DESCRIBEmysql.user”語(yǔ)句查看user表的基本結(jié)構(gòu)。db表是數(shù)據(jù)庫(kù)級(jí)別授予權(quán)限的系統(tǒng)表，適用于數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)中所有對(duì)象，決定哪些用戶(hù)能從哪些主機(jī)訪問(wèn)存取哪些數(shù)據(jù)庫(kù)。MySQL中可通過(guò)“DESCRIBEmysql.db”語(yǔ)句查看db表的基本結(jié)構(gòu)。二、通過(guò)SELECT查詢(xún)語(yǔ)句查看當(dāng)前用戶(hù)SELECT查詢(xún)語(yǔ)句查看mysql.user表中當(dāng)前MySQL服務(wù)器中的用戶(hù)usemysql;/*選擇mysql數(shù)據(jù)庫(kù)*/selecthost,userfromuser;/*查看user表中有哪些用戶(hù)*/三、新建普遍用戶(hù)CREATEUSER用戶(hù)名1[IDENTIFIEDBY[PASSWORD]'密碼1'][,...];用戶(hù)名：由用戶(hù)（User）和主機(jī)名（Host）構(gòu)成，如果只指定用戶(hù)名部分，主機(jī)名部分則默認(rèn)為‘%’(即對(duì)所有的主機(jī)開(kāi)放權(quán)限)。IDENTIFIEDBY：用來(lái)設(shè)置用戶(hù)的密碼。CREATEUSER語(yǔ)句可以同時(shí)創(chuàng)建多個(gè)用戶(hù)。新用戶(hù)可以沒(méi)有初始密碼，但從數(shù)據(jù)庫(kù)安全角度考慮，不推薦使用空密碼。四、刪除普遍用戶(hù)使用DROPUSER語(yǔ)句刪除用戶(hù)DROPUSER用戶(hù)名1[,...];使用DELETE語(yǔ)句刪除用戶(hù)DELETEFROMMySQL.userWHEREhost=主機(jī)名ANDuser=用戶(hù)名;【教師舉例實(shí)操演示】對(duì)高校教學(xué)質(zhì)量分析管理系統(tǒng)數(shù)據(jù)庫(kù)db_teaching——例1：在本機(jī)localhost中新建2個(gè)用戶(hù)，用戶(hù)名分別為test1、test2，密碼分別為test1、test2。CREATEUSER'test1'@'localhost'IDENTIFIEDBY'test1',->'test2'@'localhost'IDENTIFIEDBY'test2';selecthost,userfromuser;/*查看user表中有哪些用戶(hù)*/例2：刪除本機(jī)localhost中的用戶(hù)test1。DROPUSER'test1'@'localhost';例3：使用DELETE語(yǔ)句刪除本機(jī)localhost中的用戶(hù)test2。DELETEFROMMySQL.userWHEREhost='localhost'ANDuser='test2';selecthost,userfromuser;/*查看user表中有哪些用戶(hù)*/【學(xué)生課堂實(shí)踐練習(xí)】五、root用戶(hù)修改自己的密碼root用戶(hù)擁有很高的權(quán)限，因此必須保證root用戶(hù)的密碼安全。root用戶(hù)可以通過(guò)ALTERUSER語(yǔ)句、SET語(yǔ)句等多種方式來(lái)修改密碼，使用ALTERUSER語(yǔ)句修改用戶(hù)密碼是MySQL官方推薦的方式。使用ALTERUSER命令來(lái)修改root用戶(hù)的密碼ALTERUSERUSER()IDENTIFIEDBY'新密碼';使用SET語(yǔ)句來(lái)修改root用戶(hù)的密碼SETPASSWORD='新密碼';六、root用戶(hù)修改普遍用戶(hù)的密碼使用ALTERUSER語(yǔ)句修改普通用戶(hù)的密碼ALTERUSER用戶(hù)名1[IDENTIFIEDBY[PASSWORD]'密碼1'][,...];使用SET語(yǔ)句來(lái)修改普通用戶(hù)的密碼SETPASSWORDFOR用戶(hù)名='新密碼’;【教師舉例實(shí)操演示】對(duì)高校教學(xué)質(zhì)量分析管理系統(tǒng)數(shù)據(jù)庫(kù)db_teaching——例4：使用ALTERUSER命令將root用戶(hù)的密碼修改為“hellomysql”。ALTERUSERUSER()IDENTIFIEDBY'hellomysql';例5：使用SET語(yǔ)句將root用戶(hù)的密碼修改為“root123”。SETPASSWORD='root123';例6：使用ALTERUSER命令將本機(jī)普通用戶(hù)test1的密碼修改為“hellomysql”。ALTERUSER'test1'@'localhost'IDENTIFIEDBY'hellomysql';例7：使用SET語(yǔ)句將本機(jī)普通用戶(hù)test1的密碼修改為“hellomysql”。SETPASSWORDFOR'test1'@'localhost'='test';【學(xué)生課堂實(shí)踐練習(xí)】七、授予權(quán)限用戶(hù)（全局）層級(jí)GRANTALLON*.*TO用戶(hù)名[WITHGRANTOPTION];數(shù)據(jù)庫(kù)層級(jí)GRANT權(quán)限名稱(chēng)ON數(shù)據(jù)庫(kù)名TO用戶(hù)名[WITHGRANTOPTION];數(shù)據(jù)表層級(jí)GRANT權(quán)限名稱(chēng)ON數(shù)據(jù)庫(kù)表名TO用戶(hù)名;字段層級(jí)GRANT權(quán)限名稱(chēng)（字段名稱(chēng)）ON數(shù)據(jù)庫(kù)表名TO用戶(hù)名;過(guò)程層級(jí)GRANT權(quán)限名稱(chēng)ONPROCEDURE數(shù)據(jù)庫(kù)名稱(chēng).存儲(chǔ)過(guò)程名稱(chēng)TO用戶(hù)名;GRANT權(quán)限名稱(chēng)ONFUNCTION數(shù)據(jù)庫(kù)名稱(chēng).函數(shù)名稱(chēng)TO用戶(hù)名;八、查看權(quán)限SHOWGRANTS語(yǔ)句可以顯示指定用戶(hù)的權(quán)限信息SHOWGRANTSFOR'用戶(hù)名'@'主機(jī)名';九、回收權(quán)限回收權(quán)限就是取消已經(jīng)賦予用戶(hù)的某些權(quán)限，收回用戶(hù)不必要的權(quán)限可以在一定程度上保證系統(tǒng)的安全性。MySQL中使用REVOKE語(yǔ)句取消用戶(hù)的某些權(quán)限。收回權(quán)限之后，用戶(hù)的記錄將從權(quán)限表中刪除，但是用戶(hù)記錄依然在user表中保存。REVOKE權(quán)限名稱(chēng)ON數(shù)據(jù)庫(kù)表名FROM用戶(hù)名;【教師舉例實(shí)操演示】對(duì)高校教學(xué)質(zhì)量分析管理系統(tǒng)數(shù)據(jù)庫(kù)db_teaching——例8：任課教師可以通過(guò)高校教學(xué)質(zhì)量分析管理系統(tǒng)對(duì)學(xué)生評(píng)學(xué)成績(jī)進(jìn)行錄入和查詢(xún)操作。為教師周老師創(chuàng)建一個(gè)用戶(hù)賬戶(hù)“teacher_zhou”，密碼為“test”，授予周老師對(duì)db_teaching數(shù)據(jù)庫(kù)的評(píng)學(xué)評(píng)教表tb_grade數(shù)據(jù)添加和查詢(xún)的權(quán)限。CREATEUSER'teacher_zhou'@'%'IDENTIFIEDBY'test';GRANTSELECT,INSERTONDB_Teaching.tb_gradeTOteacher_zhou;例9：查詢(xún)教師用戶(hù)teacher_zhou的權(quán)限信息。SHOWGRANTSFOR'teacher_zhou'@'%';例10：回收周老師的用戶(hù)賬戶(hù)teacher_zhou對(duì)高校教學(xué)質(zhì)量分析管理系統(tǒng)數(shù)據(jù)庫(kù)db_teaching的tb_grade表INSERT權(quán)限。REVOKEINSERTONdb_teaching.tb_gradeFROM'teacher_zhou'@'%';【學(xué)生課堂實(shí)踐練習(xí)】十、創(chuàng)建角色并分配權(quán)限當(dāng)用戶(hù)數(shù)量較多時(shí)，為了避免單獨(dú)給每一個(gè)用戶(hù)授予多個(gè)權(quán)限，可以先將權(quán)限集合放入角色中，再賦予用戶(hù)相應(yīng)的角色。創(chuàng)建角色使用GREATEROLE語(yǔ)句，其命令語(yǔ)法格式如下：CREATEROLE'角色名稱(chēng)'[@'主機(jī)名'];角色創(chuàng)建完成后，需要使用GRANT語(yǔ)句給角色授予權(quán)限，其命令語(yǔ)法格式如下：GRANT'權(quán)限名稱(chēng)'[@'主機(jī)名']ON'數(shù)據(jù)庫(kù)名稱(chēng)'TO'角色名稱(chēng)'[@'主機(jī)名'];十一、為用戶(hù)添加角色角色創(chuàng)建并授權(quán)后，要賦給用戶(hù)并處于激活狀態(tài)才能發(fā)揮作用。給用戶(hù)添加角色可使用GRANT語(yǔ)句，激活角色使用SET語(yǔ)句。GRANT角色名1[,…]TO用戶(hù)名1[,…];SETROLEDEFAULT;十二、編輯角色或權(quán)限角色授權(quán)后，可以對(duì)角色的權(quán)限進(jìn)行添加或撤銷(xiāo)。在給用戶(hù)添加角色后，也可以對(duì)用戶(hù)的角色進(jìn)行撤銷(xiāo)操作。REVOKE語(yǔ)句可以實(shí)現(xiàn)撤銷(xiāo)角色的權(quán)限，也可以撤銷(xiāo)用戶(hù)對(duì)應(yīng)的角色。撤銷(xiāo)用戶(hù)角色的命令語(yǔ)法格式如下：REVOKE角色名稱(chēng)FROM用戶(hù)名;撤銷(xiāo)角色權(quán)限的命令語(yǔ)法格式如下：REVOKE權(quán)限名稱(chēng)ON數(shù)據(jù)庫(kù)名TO用戶(hù)名;十三、刪除角色使用DROPROLE語(yǔ)句可以刪除角色，其命令語(yǔ)法格式如下：DROPROLE角色名稱(chēng);【教師舉例實(shí)操演示】對(duì)高校教學(xué)質(zhì)量分析管理系統(tǒng)數(shù)據(jù)庫(kù)db_teaching——例11：創(chuàng)建角色counsellor，授予該角色對(duì)高校教學(xué)質(zhì)量分析管理系統(tǒng)數(shù)據(jù)庫(kù)db_teaching的學(xué)生表tb_student的SELECT、INSERT、DELETE權(quán)限。CREATEROLE'counsellor'@'%';GRANTSelect,Insert,DeleteONdb_teaching.tb_studentTO'counsellor'@'%';例12：新建一個(gè)用戶(hù)counsellor_zhou，密碼為test1，將其添加到counsellor角色，并激活該角色。CREATEUSER'counsellor_zhou'@'%'IDENTIFIEDBY'test1';GRANTcounsellorto'counsellor_zhou'@'%';SETROLEDEFAULT;例13：撤銷(xiāo)用戶(hù)counsellor_zhou的counsellor角色，并撤銷(xiāo)counsellor角色的Delete權(quán)限。REVOKEcounsellorFROM'counsellor_zhou'@'%';REVOKEDeleteONdb_teaching.tb_studentFROM'counsellor';SHOWGRANTSFOR'counsellor';例14：刪除counsellor角色。DROPROLE'counsellor';【學(xué)生課堂實(shí)踐練習(xí)】探【教師指導(dǎo)，學(xué)生小組討論本節(jié)任務(wù)解決方案】“高校教學(xué)質(zhì)量分析管理系統(tǒng)”中，如何為教學(xué)質(zhì)量督導(dǎo)部門(mén)及教務(wù)處管理員、教師、學(xué)生三種類(lèi)型的用戶(hù)分配適當(dāng)訪問(wèn)數(shù)據(jù)庫(kù)權(quán)限，以保障數(shù)據(jù)庫(kù)被正確、安全、合法訪問(wèn)。解【教師指導(dǎo)，學(xué)生操作實(shí)施解決任務(wù)】命令行客戶(hù)端和MySQLWorkbench圖形化客戶(hù)端，都可以管理用戶(hù)和角色，并進(jìn)行權(quán)限分配。1.命令行客戶(hù)端=1\*GB2⑴“高校教學(xué)質(zhì)量分析管理系統(tǒng)”中需要設(shè)置教學(xué)質(zhì)量督導(dǎo)部門(mén)及教務(wù)處管理員manager、教師teacher、學(xué)生student三種類(lèi)型的角色。CREATEROLE'manager'@'%';CREATEROLE'teacher'@'%';CREATEROLE'student'@'%';=2\*GB2⑵給教學(xué)質(zhì)量督導(dǎo)部門(mén)及教務(wù)處管理員manager角色分配對(duì)對(duì)所有表數(shù)據(jù)進(jìn)行錄入、刪除、更新、查看等所有權(quán)限，給教師teacher角色分配錄入課程評(píng)學(xué)成績(jī)權(quán)限、查看所有教學(xué)基礎(chǔ)表的權(quán)限，給學(xué)生student角色分配錄入評(píng)學(xué)評(píng)教成績(jī)表中對(duì)課程任課老師的評(píng)教分?jǐn)?shù)權(quán)限和查看自己成績(jī)權(quán)限。GRANTALLONDB_Teaching.*TO'manager'@'%';GRANTSELECT,INSERTONDB_Teaching.tb_gradeTO'teacher'@'%';GRANTSELECTONDB_Teaching.tb_studentTO'teacher'@'%';GRANTSELECTONDB_Teaching.tb_courseTO'teacher'@'%';GRANTUPDATE(Teach_evalu_score)ONDB_Teaching.tb_gradeTO'student'@'%';GRANTSELECTONDB_Teaching.tb_gradeTO'student'@'%';=3\*GB2⑶創(chuàng)建教學(xué)質(zhì)量督導(dǎo)部門(mén)周老師manager_zhou及教務(wù)處王老師manager_wang用戶(hù)，設(shè)置這兩個(gè)用戶(hù)為manager角色；創(chuàng)建崔老師teacher_cui用戶(hù)，設(shè)置該用戶(hù)為teacher角色；創(chuàng)建沈同學(xué)student_shen、蔡同學(xué)student_cai用戶(hù)，設(shè)置這兩個(gè)用戶(hù)為student角色。CREATEUSER'manager_zhou'@'%','manager_wang'@'%','teacher_cui'@'%','student_shen'@'%','student_cai'@'%';GRANTmanagerto'manager_zhou'@'%','manager_wang'@'%';GRANTteacherto'teacher_cui'@'%';GRANTstudentto'student_shen'@'%','student_cai'@'%';=4\*GB2⑷激活角色，并查看manager_zhou、teacher_cui、student_shen三個(gè)用戶(hù)的權(quán)限。SETROLEDEFAULT;SHOWGRANTSFOR'manager_zhou'@'%';SHOWGRANTSFOR'teacher_cui'@'%';SHOWGRANTSFOR'student_shen'@'%';2.MySQLWorkbench圖形化客戶(hù)端通過(guò)MySQLWorkbench工具，在圖形化界面下很方便地管理用戶(hù)與權(quán)限。踐【學(xué)生綜合實(shí)踐，運(yùn)用本節(jié)學(xué)習(xí)技術(shù)完成工作任務(wù)單】1.“高校教學(xué)質(zhì)量分析管理系統(tǒng)”數(shù)據(jù)庫(kù)db_teaching“用戶(hù)和權(quán)限”實(shí)踐工作任務(wù)單2.“怡貝銀行業(yè)務(wù)管理系統(tǒng)”數(shù)據(jù)庫(kù)db_ebank“用戶(hù)和權(quán)限”實(shí)踐工作任務(wù)單

備課紙（節(jié)）課題：任務(wù)2使用事務(wù)和鎖管理并發(fā)控制課時(shí)教學(xué)目的要求：了解事務(wù)的概念及其ACID特性理解事務(wù)的4種隔離級(jí)別理解鎖機(jī)制掌握MySQL的事務(wù)并發(fā)控制語(yǔ)句教學(xué)重點(diǎn)：了解事務(wù)的概念及其ACID特性理解事務(wù)的4種隔離級(jí)別理解鎖機(jī)制掌握MySQL的事務(wù)并發(fā)控制語(yǔ)句教學(xué)難點(diǎn)：了解事務(wù)的隔離級(jí)別理解鎖機(jī)制掌握MySQL的事務(wù)并發(fā)控制語(yǔ)句教具：理實(shí)一體化機(jī)房、MySQL8.0課后小結(jié)教學(xué)過(guò)程：引【學(xué)生線上微課學(xué)習(xí)，引出討論題】：幾位任課教師用戶(hù)同時(shí)修改學(xué)生評(píng)學(xué)成績(jī)時(shí)，怎么保證所有數(shù)據(jù)操作命令執(zhí)行的同步性、數(shù)據(jù)變更的一致性？導(dǎo)【教師導(dǎo)入任務(wù)情境】：G-EDU格諾博公司在開(kāi)發(fā)和設(shè)置“高校教學(xué)質(zhì)量管理分析系統(tǒng)”時(shí)，針對(duì)教學(xué)質(zhì)量督導(dǎo)部門(mén)人員、教師、學(xué)生的不同角色用戶(hù)，在進(jìn)行數(shù)據(jù)操作的過(guò)程中，會(huì)遇到需要通過(guò)一組SQL語(yǔ)句來(lái)完成的情況，必須保證這組所有數(shù)據(jù)操作命令執(zhí)行的同步性、數(shù)據(jù)變更的一致性。當(dāng)一位教師用戶(hù)在錄入或修改學(xué)生評(píng)學(xué)成績(jī)時(shí)，其他教師用戶(hù)應(yīng)該不得同時(shí)對(duì)這個(gè)學(xué)生評(píng)學(xué)成績(jī)也進(jìn)行錄入或修改，要避免因此會(huì)導(dǎo)致的數(shù)據(jù)不一致；缺考或作弊的學(xué)生是不能進(jìn)行對(duì)教師評(píng)教打分的，這樣的操作應(yīng)同步有取消撤銷(xiāo)機(jī)制來(lái)保障。所以G-EDU格諾博公司通過(guò)MySQL提供的事務(wù)機(jī)制和鎖機(jī)制的策略，來(lái)進(jìn)行數(shù)據(jù)的并發(fā)控制。析【教師帶動(dòng)學(xué)生】分析任務(wù)解決實(shí)施的技術(shù)要點(diǎn)，精講：任務(wù)解決的要點(diǎn)事務(wù)的概念及其ACID特性事務(wù)的4種隔離級(jí)別鎖機(jī)制MySQL的事務(wù)并發(fā)控制語(yǔ)句一、事務(wù)的概念及其ACID特性事務(wù)處理在數(shù)據(jù)庫(kù)開(kāi)發(fā)過(guò)程中有著非常重要的作用，它可以保證在同一個(gè)事務(wù)中的操作具有同步性。在MySQL中，事務(wù)就是針對(duì)數(shù)據(jù)庫(kù)的一組操作，它可以由一條或多條SQL語(yǔ)句組成，且每條SQL語(yǔ)句是相互依賴(lài)的。只要在程序執(zhí)行過(guò)程中有一條SQL語(yǔ)句執(zhí)行失敗或發(fā)生錯(cuò)誤，則其他語(yǔ)句都不會(huì)執(zhí)行。也就是說(shuō)，事務(wù)的執(zhí)行要么成功，要么返回到事務(wù)開(kāi)始前的狀態(tài)，這就保證了同一事務(wù)操作的同步性和數(shù)據(jù)的完整性。MySQL中的事務(wù)具有原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）和持久性（Durability）這4個(gè)特性，通常簡(jiǎn)稱(chēng)為ACID。1.A原子性原子性是指一個(gè)事務(wù)必須被視為一個(gè)不可分割的最小工作單元。只有事務(wù)中所有的數(shù)據(jù)庫(kù)操作都執(zhí)行成功，才能整個(gè)事務(wù)執(zhí)行成功。事務(wù)中如果有任何一個(gè)SQL語(yǔ)句執(zhí)行失敗，已經(jīng)執(zhí)行成功的SQL語(yǔ)句也必須撤銷(xiāo)，數(shù)據(jù)庫(kù)的狀態(tài)退回到執(zhí)行事務(wù)前的狀態(tài)。2.C一致性一致性是指在事務(wù)處理時(shí)，無(wú)論執(zhí)行成功還是失敗，都要保證數(shù)據(jù)庫(kù)系統(tǒng)處于一致的狀態(tài)，保證數(shù)據(jù)庫(kù)系統(tǒng)不會(huì)返回到一個(gè)未處理的事務(wù)中。MySQL的一致性主要由日志機(jī)制實(shí)現(xiàn)，通過(guò)日志記錄數(shù)據(jù)庫(kù)的所有變化，為事務(wù)恢復(fù)提供了跟蹤記錄。3.I隔離性隔離性是指當(dāng)一個(gè)事務(wù)在執(zhí)行時(shí)，不會(huì)受到其他事物的影響。保證了未完成事務(wù)的所有操作與數(shù)據(jù)庫(kù)系統(tǒng)的隔離，直到事務(wù)完成為止，才能看到事務(wù)執(zhí)行結(jié)果。隔離性相關(guān)的技術(shù)有并發(fā)控制、可串行化、鎖等，當(dāng)多個(gè)用戶(hù)并發(fā)訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，數(shù)據(jù)庫(kù)為每一個(gè)用戶(hù)開(kāi)啟的事務(wù)，不能被其他事務(wù)的操作數(shù)據(jù)所干擾，多個(gè)并發(fā)事務(wù)之間要相互隔離。4.D持久性持久性是指事務(wù)一旦提交，其對(duì)數(shù)據(jù)庫(kù)的修改就是永久性的，即使系統(tǒng)重啟或者出現(xiàn)系統(tǒng)故障，數(shù)據(jù)仍可恢復(fù)。二、事務(wù)的4種隔離級(jí)別由于數(shù)據(jù)庫(kù)是一個(gè)多用戶(hù)的共享資源，MySQL允許多線程并發(fā)訪問(wèn)，因此用戶(hù)可以通過(guò)不同的線程執(zhí)行不同的事務(wù)。為了保證這些事務(wù)之間不受影響，對(duì)事務(wù)設(shè)置隔離級(jí)別是十分必要的。SQL標(biāo)準(zhǔn)定義了4種隔離級(jí)別，指定了事務(wù)中哪些數(shù)據(jù)改變?cè)谄渌聞?wù)可見(jiàn)、哪些數(shù)據(jù)改變?cè)谄渌聞?wù)不可見(jiàn)。低級(jí)別的隔離級(jí)別可以支持更高的并發(fā)處理，同時(shí)占用的系統(tǒng)資源更少。MySQL中事務(wù)隔離級(jí)別有READ-UNCOMMITTED(讀未提交)、READ-COMMITTED(不可重復(fù)讀)、REPEATABLE-READ(可重復(fù)讀)和SERIALIZABLE(可串行化)4種。下面針對(duì)每種隔離級(jí)別的特點(diǎn)、帶來(lái)的問(wèn)題以及解決方案進(jìn)行詳細(xì)講解。1.READ-UNCOMMITTED(讀未提交)READ-UNCOMMITTED是事務(wù)中最低的級(jí)別，該級(jí)別下的事務(wù)可以讀取到另一個(gè)事務(wù)中未提交的數(shù)據(jù)，也被稱(chēng)為臟讀（DirtyRead）,這是相當(dāng)危險(xiǎn)的。由于該級(jí)別較低，在實(shí)際開(kāi)發(fā)中避免不了任何情況，所以一般很少使用。2.READ-COMMITTED(不可重復(fù)讀)大多數(shù)的數(shù)據(jù)庫(kù)管理系統(tǒng)是默認(rèn)隔離級(jí)別為READ-COMMITTED，該級(jí)別下的事務(wù)只能讀取其他事務(wù)已經(jīng)提交的內(nèi)容，可以避免臟讀，但不能避免重復(fù)和幻讀的情況。重復(fù)讀就是在事務(wù)內(nèi)重復(fù)讀取了別的線程已經(jīng)提交的數(shù)據(jù)，但兩次讀取的結(jié)果不一致，原因是查詢(xún)的過(guò)程中其他事務(wù)做了更新的操作?；米x是指在一個(gè)事務(wù)內(nèi)兩次查詢(xún)中數(shù)據(jù)條數(shù)不一致，原因是查詢(xún)的過(guò)程中其他的事務(wù)做了添加操作。這兩種情況并不算錯(cuò)誤，但有些情況是不符合實(shí)際需求的。3.REPEATABLE-READ(可重復(fù)讀)REPEATABLE-READ是MySQL默認(rèn)的隔離級(jí)別，它可以避免臟讀，不可重復(fù)讀的問(wèn)題，確保同一事務(wù)的多個(gè)實(shí)例在并發(fā)讀取數(shù)據(jù)時(shí)，會(huì)看到同樣的數(shù)據(jù)行。但理論上該級(jí)別會(huì)出現(xiàn)幻讀的情況，不過(guò)MySQL的存儲(chǔ)引擎通過(guò)多版本并發(fā)控制機(jī)制解決了這個(gè)問(wèn)題，因此該級(jí)別是可以避免幻讀的。4.SERIALIZABLE(可串行化)SERIALIZABLE是事務(wù)的最高隔離級(jí)別，它會(huì)強(qiáng)制對(duì)事務(wù)進(jìn)行排序，使之不會(huì)發(fā)生沖突，從而解決了臟讀、幻讀、重復(fù)讀的問(wèn)題。實(shí)際上就是在每個(gè)讀的數(shù)據(jù)行上加鎖。這個(gè)級(jí)別可能導(dǎo)致大量的超時(shí)現(xiàn)象和鎖競(jìng)爭(zhēng)，實(shí)際應(yīng)用中很少使用。三、鎖機(jī)制為解決數(shù)據(jù)庫(kù)并發(fā)控制問(wèn)題，MySQL中使用鎖機(jī)制，防止其他用戶(hù)修改另外一個(gè)未完成的事務(wù)中的數(shù)據(jù)，從而實(shí)現(xiàn)事務(wù)的并發(fā)操作，避免造成數(shù)據(jù)的不一致，與此同時(shí)，為實(shí)現(xiàn)MySQL的各種隔離級(jí)別，鎖機(jī)制也為其提供了安全保障。MySQL8默認(rèn)的InnoDB存儲(chǔ)引擎支持行級(jí)鎖(row-levellocking)，即以記錄為單位進(jìn)行加鎖，也支持表級(jí)鎖（table-levellocking），即以表為單位進(jìn)行加鎖。默認(rèn)的情況下是采用行級(jí)鎖。鎖粒度越小，并發(fā)訪問(wèn)性能就越高。越適合做并發(fā)更新操作；鎖粒度越大，并發(fā)訪問(wèn)性能就越低，越適合做并發(fā)查詢(xún)操作。不過(guò)，鎖粒度越小，完成某個(gè)功能時(shí)所需要的加鎖、解鎖次數(shù)就會(huì)越多，反而會(huì)消耗較多服務(wù)器資源，也容易發(fā)生死鎖問(wèn)題。1.行級(jí)鎖定行級(jí)鎖最大的特點(diǎn)是鎖定對(duì)象的顆粒度很小，發(fā)生鎖定資源爭(zhēng)用的概率也很小，能夠給予應(yīng)用程序盡可能大的并發(fā)處理能力，從而提高一些需要高并發(fā)應(yīng)用系統(tǒng)的整體性能。InnoDB存儲(chǔ)引擎使用行級(jí)鎖機(jī)制，實(shí)現(xiàn)了兩種類(lèi)型的行級(jí)鎖，包括共享鎖和排他鎖。而在鎖定機(jī)制的實(shí)現(xiàn)過(guò)程中，為了讓行級(jí)鎖定和表級(jí)鎖定共存，InnoDB還使用了兩種內(nèi)部使用的意向鎖，也就是意向共享鎖和意向排他鎖。各種鎖的含義如下：共享鎖（S）：允許一個(gè)事務(wù)讀一行數(shù)據(jù)時(shí)阻止其他的事務(wù)讀取相同數(shù)據(jù)的排他鎖。排他鎖（X）：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)的共享鎖和排他鎖。意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行加行共享鎖。事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的意向共享鎖。意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加行排他鎖。事務(wù)在給一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表意向排他鎖。2.表級(jí)鎖定與行級(jí)鎖不同的是，表級(jí)鎖的鎖定機(jī)制的顆粒度最大，該鎖定機(jī)制的最大特點(diǎn)是系統(tǒng)開(kāi)銷(xiāo)比較小，由于實(shí)現(xiàn)邏輯非常簡(jiǎn)單，因此帶來(lái)的系統(tǒng)負(fù)面影響最小。由于表級(jí)鎖一次性將整個(gè)表鎖定，因此可以很好的避免死鎖的問(wèn)題。表級(jí)鎖定機(jī)制也存在一定缺陷，由于表級(jí)鎖的鎖定機(jī)制顆粒很大，所以發(fā)生鎖沖突的概率最高，并發(fā)度最低。MySQL數(shù)據(jù)庫(kù)的表級(jí)鎖定主要分為兩種類(lèi)型，一種是讀鎖定，另一種是寫(xiě)鎖定。MySQL數(shù)據(jù)庫(kù)提供了以下4種隊(duì)列來(lái)維護(hù)這兩種鎖定，間接地說(shuō)明了數(shù)據(jù)庫(kù)表級(jí)鎖定的4種狀態(tài)。Currentreadlockqueue(lock->read)Paddingreadlockqueue(lock->readwait)Currentwritelockqueue(lock->write)Paddingwritelockqueue(lock->writewait)四、MySQL的事務(wù)并發(fā)控制語(yǔ)句在MySQL中，默認(rèn)用戶(hù)執(zhí)行的每一條SQL語(yǔ)句都會(huì)被當(dāng)成單獨(dú)的事務(wù)自動(dòng)提交。如果要將一組SQL語(yǔ)句作為一個(gè)事務(wù)，則需要使用MySQL事務(wù)控制語(yǔ)句，要先顯式地開(kāi)啟一個(gè)事務(wù)，并手動(dòng)提交該事務(wù)。1.開(kāi)始事務(wù)BEGIN|STARTTRANSACTION;2.提交事務(wù)COMMIT;3.回滾（撤銷(xiāo)）事務(wù)ROLLBACK;4.事務(wù)保存點(diǎn)（1）創(chuàng)建保存點(diǎn)SAVEPOINT保存點(diǎn)名;（2）回滾到保存點(diǎn)ROLLBACKTO保存點(diǎn)名;（3）刪除保存點(diǎn)RELEASESAVEPOINT保存點(diǎn)名;5.鎖機(jī)制控制語(yǔ)句如果一個(gè)事務(wù)請(qǐng)求的鎖模式與當(dāng)前的鎖模式兼容，InnoDB就將請(qǐng)求的鎖授予該事務(wù)；如果兩者不兼容，那么該事務(wù)要等待鎖釋放。意向鎖是InnoDB存儲(chǔ)引擎自動(dòng)加的。對(duì)于SELECT語(yǔ)句，InnoDB不會(huì)加任何鎖；對(duì)于INSERT、UPDATE、DELETE語(yǔ)句，InnoDB會(huì)自動(dòng)給涉及的數(shù)據(jù)加排他鎖。（1）添加行級(jí)共享鎖SELET*FROM數(shù)據(jù)庫(kù)表名WHERE...LOCKINSHAREMODE;（2）添加行級(jí)排他鎖SELET*FROM數(shù)據(jù)庫(kù)名WHERE...FORUPDATE;探【學(xué)生小組討論本節(jié)任務(wù)解決方案】：在對(duì)G-EDU格諾博公司在開(kāi)發(fā)的“高校教學(xué)質(zhì)量管理分析系統(tǒng)”進(jìn)行數(shù)據(jù)訪問(wèn)時(shí)，哪些用戶(hù)的哪些操作需要采取事務(wù)并發(fā)控制保證操作的同步性、數(shù)據(jù)的完整性與一致性。解【學(xué)生操作實(shí)施解決任務(wù)】命令行客戶(hù)端，通過(guò)事務(wù)實(shí)現(xiàn)一個(gè)教師角色teacher的用戶(hù)在添加、修改學(xué)生評(píng)學(xué)成績(jī)記錄數(shù)據(jù)時(shí)，其他用戶(hù)不得對(duì)這些學(xué)生的評(píng)學(xué)成績(jī)也進(jìn)行添加或修改，避免可能導(dǎo)致的數(shù)據(jù)不一致。（1）教師角色teacher的用戶(hù)張老師在“高校教學(xué)質(zhì)量分析管理系統(tǒng)”通過(guò)事務(wù)提交方式，給評(píng)學(xué)評(píng)教成績(jī)表增加學(xué)生成績(jī)記錄。BEGIN;useDB_Teaching;INSERTINTOTB_Grade(ID,Stu_No,Course_No,Teacher_No,Teach_evalu_score,Score,Mark,Test_Type,Test_Term,missed_classes)VALUES(156,'202004013002','900013','000002',80.0,85.0,NULL,'考試','2021-2022學(xué)年一',1);INSERTINTOTB_Grade(ID,Stu_No,Course_No,Teacher_No,Teach_evalu_score,Score,Mark,Test_Type,Test_Term,missed_classes)VALUES(157,'201902016202','900013','000002',95.0,71.0,NULL,'考試','2021-2022學(xué)年一',1);INSERTINTOTB_Grade(ID,Stu_No,Course_No,Teacher_No,Teach_evalu_score,Score,Mark,Test_Type,Test_Term,missed_classes)VALUES(158,'201902016203','900013','000002',86.0,67.0,NULL,'考試','2021-2022學(xué)年一',1);INSERTINTOTB_Grade(ID,Stu_No,Course_No,Teacher_No,Teach_evalu_score,Score,Mark,Test_Type,Test_Term,missed_classes)VALUES(159,'201902016204','900013','000002',90.0,60.0,NULL,'考試','2021-2022學(xué)年一',1);INSERTINTOTB_Grade(ID,Stu_No,Course_No,Teacher_No,Teach_evalu_score,Score,Mark,Test_Type,Test_Term,missed_classes)VALUES(160,'202002016205','900013','000002',93.0,45.0,NULL,'考試','2021-2022學(xué)年二',1);COMMIT;（2）教師角色teacher的用戶(hù)張老師在“高校教學(xué)質(zhì)量分析管理系統(tǒng)”，將評(píng)學(xué)評(píng)教成績(jī)表中ID編號(hào)為“156”的學(xué)生成績(jī)修改為“90”，事務(wù)開(kāi)啟鎖機(jī)制，進(jìn)行并發(fā)控制，