第14章MySQL數(shù)據(jù)庫(kù)安全管理

第14章MySQL數(shù)據(jù)庫(kù)安全管理主要內(nèi)容14.1數(shù)據(jù)庫(kù)安全管理概述14.2用戶管理14.3權(quán)限管理14.4角色管理14.5本章小結(jié)14.1數(shù)據(jù)庫(kù)安全管理概述數(shù)據(jù)庫(kù)的安全性是指有效保護(hù)數(shù)據(jù)庫(kù)，防止不合法地使用造成數(shù)據(jù)泄露、更改或者毀壞，其本質(zhì)是要保護(hù)數(shù)據(jù)的安全性、完整性和一致性。MySQL是一個(gè)多用戶的數(shù)據(jù)庫(kù)管理系統(tǒng)，它提供了功能強(qiáng)大的訪問(wèn)控制系統(tǒng)，以此來(lái)確保MySQL服務(wù)器的安全訪問(wèn)，即它可以為不同用戶指定不同的訪問(wèn)權(quán)限，從而進(jìn)一步減少數(shù)據(jù)泄露或被損壞的風(fēng)險(xiǎn)。數(shù)據(jù)庫(kù)安全管理最重要的是要確保有資格的用戶訪問(wèn)數(shù)據(jù)庫(kù)的權(quán)限，同時(shí)令所有未授權(quán)的用戶無(wú)法獲取數(shù)據(jù)。14.2用戶管理14.2.1查看用戶信息使用user表查看用戶信息MySQL的用戶管理包括管理用戶的賬號(hào)、權(quán)限和角色等。MySQL用戶可以分為兩種：root用戶和普通用戶。root用戶是超級(jí)管理員，擁有所有權(quán)限，普通用戶只擁有被授予的各種權(quán)限。MySQL的用戶賬號(hào)及相關(guān)信息都存儲(chǔ)在mysql數(shù)據(jù)庫(kù)的user表中，我們可以用SELECT語(yǔ)句去user表中查看所有的用戶信息?！纠?4.1】查看MySQL中所有用戶。在MySQL命令行客戶端輸入命令：SELECTuser,hostFROMmysql.user;14.2用戶管理14.2.1查看用戶信息使用user表查看用戶信息user表是MySQL中非常重要的一個(gè)權(quán)限表，以下是它的四類(lèi)字段：范圍列：包括user字段和host字段。權(quán)限列：決定了用戶的權(quán)限，表示了在全局范圍內(nèi)允許對(duì)數(shù)據(jù)和數(shù)據(jù)庫(kù)進(jìn)行的操作。安全列：都是和加密、標(biāo)識(shí)用戶、授權(quán)插件相關(guān)的字段。資源控制列：用來(lái)限制用戶使用的資源。14.2用戶管理14.2.1查看用戶信息使用函數(shù)查看當(dāng)前用戶第11章我們介紹過(guò)部分系統(tǒng)函數(shù)，可以使用這些函數(shù)來(lái)查看當(dāng)前登錄用戶的名稱。【例14.2】查看當(dāng)前用戶。在MySQL命令行客戶端輸入命令：SELECTUSER();SELECTCURRENT_USER();

14.2用戶管理14.2.1查看用戶信息使用圖形化工具查看用戶打開(kāi)Workbench工具，連接到MySQL服務(wù)器。選中“Administration”標(biāo)簽，單擊“MANAGEMENT”下的“UsersandPrivileges”選項(xiàng)，出現(xiàn)界面如圖所示。

14.2用戶管理14.2.2登錄和退出MySQL服務(wù)器啟動(dòng)MySQL服務(wù)后，可以在DOS窗口通過(guò)mysql命令登錄到MySQL服務(wù)器，基本語(yǔ)法格式如下：mysql-hhostname|hostIP-Pport-uusername-p-DDatabaseName-e“SQL語(yǔ)句”其余參數(shù)可以通過(guò)mysql--help或者mysql-?語(yǔ)句來(lái)查詢退出登錄直接輸入QUIT或者EXIT命令即可【例4.3】登錄MySQL服務(wù)器中的jwgl數(shù)據(jù)庫(kù)。在MySQL命令行客戶端輸入命令：mysql-uroot-pjwgl回車(chē)后在下一行出現(xiàn)“Enterpassword：”時(shí)輸入密碼即可。

14.2用戶管理14.2.3創(chuàng)建用戶使用命令創(chuàng)建用戶可以使用CREATEUSER語(yǔ)句來(lái)創(chuàng)建用戶，語(yǔ)法格式：CREATEUSER[IFNOTEXISTS]user[IDENTIFIEDBY'password'][,user[IDENTIFIEDBY'password']]…[DEFAULTROLErole[,role]]...[WITHresource_option[resource_option]...][password_option|lock_option]...[COMMENT'comment_string']

14.2用戶管理14.2.3創(chuàng)建用戶使用命令創(chuàng)建用戶【例14.4】創(chuàng)建新用戶u1，主機(jī)名為localhost，密碼為“123456”。在MySQL命令行客戶端輸入命令：CREATEUSERu1@localhostIDENTIFIEDBY'123456';

新創(chuàng)建的用戶擁有的權(quán)限很少，它們可以登錄到MySQL服務(wù)器，只允許進(jìn)行不需要權(quán)限的操作。14.2用戶管理14.2.3創(chuàng)建用戶使用圖形化工具創(chuàng)建用戶打開(kāi)Workbench工具，連接到MySQL服務(wù)器。進(jìn)入如圖14.2所示界面，單擊左下角的“AddAccount”按鈕，進(jìn)入如圖14.4所示的創(chuàng)建用戶界面。將新用戶信息填入后，單擊“Apply”，新用戶u2已創(chuàng)建完成，此時(shí)在左側(cè)“User”列表中就出現(xiàn)了新用戶u2。14.2用戶管理14.2.4修改用戶使用RENAMEUSER語(yǔ)句RENAMEUSER語(yǔ)句可以修改一個(gè)或多個(gè)已經(jīng)存在的用戶賬號(hào)名稱，語(yǔ)法格式：RENAMEUSERold_userTOnew_user[,old_userTOnew_user]...【例14.5】將用戶u2名稱修改為user2。在MySQL命令行客戶端輸入命令：RENAMEUSERu2@localhosttouser2@localhost;14.2用戶管理14.2.4修改用戶使用ALTERUSER語(yǔ)句使用ALTERUSER語(yǔ)句可以修改MySQL用戶，它可以為現(xiàn)有用戶修改身份驗(yàn)證、角色、資源限制、密碼管理、鎖定或解鎖賬戶等。語(yǔ)法格式：ALTERUSER[IFEXISTS]user[auth_option][,user[auth_option]]...[DEFAULTROLErole][WITHresource_option[resource_option]...][password_option|lock_option]...[COMMENT'comment_string']14.2用戶管理14.2.4修改用戶使用ALTERUSER語(yǔ)句【例14.6】修改root用戶的密碼。在MySQL命令行客戶端輸入命令：ALTERUSERroot@localhostIDENTIFIEDBY'123';執(zhí)行結(jié)果如圖所示。14.2用戶管理14.2.4修改用戶使用ALTERUSER語(yǔ)句【例14.8】將u1用戶每小時(shí)查詢、修改的最大次數(shù)分別設(shè)置為100、50。在MySQL命令行客戶端輸入命令：ALTERUSERu1@localhostWITHMAX_QUERIES_PER_HOUR100MAX_UPDATES_PER_HOUR50;【例14.9】將u1用戶密碼設(shè)置為立即過(guò)期。在MySQL命令行客戶端輸入命令：ALTERUSERu1@localhostPASSWORDEXPIRE;此時(shí)退出root用戶，用u1用戶連接服務(wù)器，查看其密碼是否過(guò)期。EXIT;mysql-uu1-p執(zhí)行結(jié)果如圖所示。14.2用戶管理14.2.4修改用戶使用SET語(yǔ)句使用SET語(yǔ)句來(lái)修改用戶密碼，常用語(yǔ)法格式:SETPASSWORD[FORuser]='ew_password’說(shuō)明：如果沒(méi)有可選的FOR子句，則是當(dāng)前用戶修改自身的密碼；如果有FOR子句，則表示當(dāng)前用戶在修改其他用戶的密碼。【例14.10】在當(dāng)前的root賬戶下修改用戶u1的密碼。在MySQL命令行客戶端輸入命令：SETPASSWORDFORu1@localhost='123456';14.2用戶管理14.2.4修改用戶使用mysqladmin語(yǔ)句使用mysqladmin語(yǔ)句也可以修改密碼，但需要注意，該命令必須在CMD窗口使用，語(yǔ)法格式:mysqladmin-uusername-pPASSWORD"new_password"說(shuō)明：這里的PASSWORD是關(guān)鍵字，而不是指舊密碼。舊密碼是在按下回車(chē)鍵后根據(jù)提示輸入，并且新密碼須用雙引號(hào)括起來(lái)，不能用單引號(hào)?！纠?4.11】修改root用戶的密碼，將其改為“123456”。在CMD窗口輸入命令：mysqladmin-uroot-pPASSWORD"123456"14.2用戶管理14.2.4修改用戶使用圖形化工具修改用戶打開(kāi)Workbench工具，連接到MySQL服務(wù)器。打開(kāi)圖14.5所示界面，選中想要修改的用戶如u1，如圖所示。14.2用戶管理14.2.4修改用戶使用圖形化工具修改用戶在該界面中可以修改密碼、用戶名稱等，或者單擊“AccountLimits”選項(xiàng)，出現(xiàn)如圖所示界面。所有想要修改的選項(xiàng)設(shè)置完成后，單擊“Apply”即可完成用戶的修改操作。14.2用戶管理14.2.5刪除用戶使用DROPUSER語(yǔ)句使用DROPUSER語(yǔ)句刪除用戶是MySQL官方推薦的方法，語(yǔ)法格式：DROPUSER[IFEXISTS]user[,user]...【例14.12】刪除user2用戶。在MySQL命令行客戶端輸入命令：DROPUSERuser2@localhost;所有想要修改的選項(xiàng)設(shè)置完成后，單擊“Apply”即可完成用戶的修改操作。14.2用戶管理14.2.5刪除用戶使用DELETE語(yǔ)句【例14.13】刪除user2用戶。

在MySQL命令行客戶端輸入命令：DELETEFROMmysql.userWHEREuser='user2'ANDhost='localhost’;注意：MySQL雖然沒(méi)有禁止使用DELETE語(yǔ)句刪除用戶，但給出了風(fēng)險(xiǎn)自負(fù)的提示。14.2用戶管理14.2.5刪除用戶使用圖形化工具刪除用戶打開(kāi)Workbench工具，連接到MySQL服務(wù)器。進(jìn)入如圖14.10所示界面，選中想要?jiǎng)h除的用戶，如user2，單擊下方的“Delete”按鈕，此時(shí)彈出如圖14.13所示界面。在該界面中單擊“Delete”，即可完成刪除user2用戶的操作。14.3權(quán)限管理權(quán)限管理主要是對(duì)登錄到MySQL的用戶進(jìn)行權(quán)限驗(yàn)證。用戶的權(quán)限信息存儲(chǔ)在mysql數(shù)據(jù)庫(kù)中的user表、db表、host表、tables_priv表、columns_priv表和proc_priv表中。在MySQL啟動(dòng)時(shí)，服務(wù)器將這些數(shù)據(jù)庫(kù)表中的權(quán)限信息內(nèi)容讀入內(nèi)存。MySQL提供了GRANT和REVOKE命令來(lái)管理訪問(wèn)權(quán)限。14.3權(quán)限管理14.3.1授予權(quán)限常用管理權(quán)限14.3權(quán)限管理14.3.1授予權(quán)限使用命令行授予用戶權(quán)限語(yǔ)法格式：GRANTpriv_type[(column_list)]ONdatabase.tableTOuser[,user]…[WITHGRANTOPTION]其中：ONdatabase.table：ON后面跟的是該權(quán)限的適用對(duì)象，有幾種情況：全局權(quán)限，適用于一個(gè)給定服務(wù)器中的所有數(shù)據(jù)庫(kù)，可以用“*.*”表示。數(shù)據(jù)庫(kù)權(quán)限，適用于一個(gè)給定數(shù)據(jù)庫(kù)中的所有目標(biāo)，可以用“database.*”表示。表權(quán)限，適用于一個(gè)具體表中的所有列，可以用“database.table”表示。列權(quán)限，適用于表中的具體列，此時(shí)在權(quán)限后面把具體列表示出來(lái)即可。14.3權(quán)限管理14.3.1授予權(quán)限使用命令行授予用戶權(quán)限【例14.14】將student表中sno字段、sname字段的查詢權(quán)限授予用戶u1。在MySQL命令行客戶端輸入命令：GRANTSELECT(sno,sname)ONjwgl.studentTOu1@localhost;QUIT;mysql-uu1-pjwglSELECTsno,snameFROMstudentWHEREsno='20190101001';SELECTsno,sname,mnoFROMstudentWHEREsno='20190101001';14.3權(quán)限管理14.3.1授予權(quán)限使用圖形化工具授予用戶權(quán)限打開(kāi)Workbench工具，連接到MySQL服務(wù)器。在圖14.10中，單擊“SchemaPrivileges”標(biāo)簽，出現(xiàn)如圖所示界面。14.3權(quán)限管理14.3.1授予權(quán)限使用圖形化工具授予用戶權(quán)限之前例題給u1用戶授予了部分權(quán)限，此時(shí)我們單擊u1用戶在jwgl數(shù)據(jù)庫(kù)中擁有的這些權(quán)限，出現(xiàn)如圖所示界面。

14.3權(quán)限管理14.3.1授予權(quán)限使用圖形化工具授予用戶權(quán)限如果想授予u1用戶在jwgl數(shù)據(jù)庫(kù)上的權(quán)限，那么直接在上圖中將想要授予的權(quán)限前面的復(fù)選框選中。假如選中CREATE和ALTER兩個(gè)權(quán)限，單擊“Apply”，此時(shí)給用戶u1授予jwgl數(shù)據(jù)庫(kù)上的權(quán)限完成，得到如圖所示界面。如果在第（2）步，發(fā)現(xiàn)u1用戶目前沒(méi)有任何權(quán)限，或者想給用戶u1授予除jwgl之外的其他數(shù)據(jù)庫(kù)權(quán)限，則從第（2）步直接跳到第（5）步執(zhí)行。此時(shí)單擊圖14.16中的按鈕“AddEntry…”，進(jìn)入到選擇數(shù)據(jù)庫(kù)界面，如圖所示。

14.3權(quán)限管理14.3.1授予權(quán)限使用圖形化工具授予用戶權(quán)限選好之后單擊“OK”按鈕，進(jìn)入到選擇具體權(quán)限設(shè)置界面，此時(shí)出現(xiàn)一條在mysql數(shù)據(jù)庫(kù)上none權(quán)限的記錄，如圖所示。選中這行記錄，然后會(huì)出現(xiàn)圖14.16所示界面，接下來(lái)跳回第（3）步和第（4）步繼續(xù)執(zhí)行，即可完成授予用戶u1在mysql數(shù)據(jù)庫(kù)上的權(quán)限，如圖所示。14.3權(quán)限管理14.3.2查看用戶權(quán)限使用命令查看用戶權(quán)限mysql數(shù)據(jù)庫(kù)下的user表中存儲(chǔ)著用戶的基本權(quán)限，所以可以使用SELECT語(yǔ)句去user表中查詢各用戶的權(quán)限。SELECT*FROMmysql.user\G也可以使用SHOWGRANTSFOR語(yǔ)句來(lái)查詢用戶的權(quán)限?！纠?4.16】創(chuàng)建一個(gè)新用戶u3，使用SHOWGRANTFOR語(yǔ)句分別查看用戶u1和u3的權(quán)限。在MySQL命令行客戶端輸入命令：CREATEUSERu3identifiedby'123';SHOWGRANTSFORu3;SHOWGRANTSFORu1@localhost;14.3權(quán)限管理14.3.3撤銷(xiāo)權(quán)限使用命令撤銷(xiāo)用戶權(quán)限撤銷(xiāo)權(quán)限就是收回授予給用戶的某些權(quán)限，我們可以使用REVOKE語(yǔ)句撤銷(xiāo)用戶的權(quán)限，語(yǔ)法格式：REVOKEpriv_type[(column_list)]…ONdatabase.tableFROMuser[,user]…【例14.17】撤銷(xiāo)用戶u1查詢student表中sno字段和sname字段的權(quán)限，撤銷(xiāo)用戶u2的所有權(quán)限。在MySQL命令行客戶端輸入命令：REVOKESELECT(sno,sname)ONjwgl.studentFROMu1@localhost;REVOKEALL,GRANTOPTIONFROMu2@localhost;14.3權(quán)限管理14.3.3撤銷(xiāo)權(quán)限使用圖形化工具撤銷(xiāo)用戶權(quán)限打開(kāi)Workbench工具，連接到MySQL服務(wù)器。進(jìn)入如圖14.16所示界面，把想要撤銷(xiāo)的權(quán)限前面的復(fù)選框?qū)慈サ簦纯沙蜂N(xiāo)該用戶權(quán)限。如果想撤銷(xiāo)所有權(quán)限，單擊“RevokeAllPrivileges”按鈕即可完成14.4角色管理MySQL8.0中增加了部分功能，在用戶權(quán)限方面增加了角色的管理，而角色實(shí)質(zhì)上就是權(quán)限的集合。既然已經(jīng)設(shè)置了權(quán)限，為什么還要設(shè)置角色呢？在給用戶授權(quán)時(shí)，現(xiàn)實(shí)中用戶數(shù)量較多，如果單獨(dú)給每一個(gè)用戶授予多個(gè)權(quán)限，語(yǔ)句重復(fù)度高，代碼執(zhí)行效率低。為了避免這種情況，我們可以先將要授權(quán)的權(quán)限放入角色中，然后再輕松地把角色授予相應(yīng)的用戶。14.4角色管理14.4.1創(chuàng)建角色可以使用CREATEROLE語(yǔ)句創(chuàng)建角色，語(yǔ)法格式：CREATEROLE[IFNOTEXISTS]role[,role]...【例14.18】創(chuàng)建角色x1和x2。

在MySQL命令行客戶端輸入命令：CREATEROLEx1,x2@localhost;14.4角色管理14.4.2角色授權(quán)與撤銷(xiāo)權(quán)限創(chuàng)建成功之后的角色是空的權(quán)限集合，我們需要使用GRANT語(yǔ)句給角色授權(quán)。給角色授權(quán)的語(yǔ)法格式和14.3.1小節(jié)中給用戶授權(quán)的語(yǔ)法格式相似，區(qū)別是把TO后面的用戶換成了角色。當(dāng)然我們也可以從角色中把某些權(quán)限撤回，使用的仍然是REVOKE語(yǔ)句，語(yǔ)法格式和14.3.3小節(jié)中的撤銷(xiāo)用戶權(quán)限語(yǔ)法格式相同，只是把FROM后面從用戶換成了角色。【例14.19】給角色x1授權(quán)和撤銷(xiāo)權(quán)限。在MySQL命令行客戶端輸入命令：GRANTSELECT(sno,sname)ONjwgl.studentTOx1;GRANTDROP,CREATE,CREATEROUTINEONjwgl.*tox1;REVOKECREATEROUTINEONjwgl.*FROMx1;14.4角色管理14.4.3授予用戶角色與撤銷(xiāo)角色角色創(chuàng)建并授權(quán)成功后，需要將角色授予用戶才能發(fā)揮其作用。授予用戶角色的語(yǔ)法格式與14.3.1小節(jié)中給用戶授權(quán)的語(yǔ)法格式相似。區(qū)別有兩點(diǎn)：一是把GRANT后面的權(quán)限換成角色，二是去掉了ONdatabase.table子句。我們可以通過(guò)這一點(diǎn)來(lái)區(qū)分到底是給用戶授予權(quán)限還是給用戶授予角色：有ON子句的是給用戶授予權(quán)限，沒(méi)有ON子句的則是給用戶授予角色。當(dāng)用戶不需要某些操作權(quán)限時(shí)，出于數(shù)據(jù)安全性的考慮，我們可以撤銷(xiāo)權(quán)限或角色。撤銷(xiāo)角色使用REVOKE語(yǔ)句，語(yǔ)法格式：REVOKEroleFROMuser14.4角色管理14.4.3授予用戶角色與撤銷(xiāo)角色

【例14.20】將角色x1授予用戶u1和u2，然后從用戶u2撤銷(xiāo)角色x1。在MySQL命令行客戶端輸入命令：GRANTx1tou1@localhost,u2@localhost;REVOKEx1FROMu2@localhost;由于語(yǔ)法不同，因此不能在同一語(yǔ)句中對(duì)用戶混合分配權(quán)限和角色，需要分開(kāi)使用GRANT語(yǔ)句。14.4角色管理14.4.3授予用戶角色與撤銷(xiāo)角色創(chuàng)建好的角色默認(rèn)是未激活狀態(tài)，將它授予用戶后并沒(méi)有直接起作用，還需要將其激活才能使用戶擁有對(duì)應(yīng)的權(quán)限。激活角色可以使用SETDEFAULTROLE語(yǔ)句，語(yǔ)法格式：SETDEFAULTROLE{NONE|ALL|role[,role]...}TOuser[,user]...還可以通過(guò)設(shè)置系統(tǒng)變量activate_all_roles_on_login的值來(lái)激活。該系統(tǒng)變量值默認(rèn)為off，設(shè)置成on之后再賦予其他角色給新用戶，就不需要再手動(dòng)激活了?！纠?4.21】激活角色x1。在MySQL命令行客戶端輸入命令：SETDEFAULTROLEx1tou1@localhost;或者SETGLOBALactivate_all_roles_on_login=on;14.4角色管理14.4.4