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

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

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

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

14.2用戶管理14.2.3創(chuàng)建用戶使用命令創(chuàng)建用戶可以使用CREATEUSER語句來創(chuàng)建用戶，語法格式：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，主機名為localhost，密碼為“123456”。在MySQL命令行客戶端輸入命令：CREATEUSERu1@localhostIDENTIFIEDBY'123456';

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

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

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

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

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

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