第17章用戶和權(quán)限管理學習目標：了解用戶和權(quán)限的相關(guān)概念，掌握創(chuàng)建用戶的方法，掌握設置密碼的方法，掌握授予用戶權(quán)限的方法，掌握權(quán)限收回的方法，掌握角色權(quán)限授予及收回的方法。17.1概述17.1.1數(shù)據(jù)庫的安全性數(shù)據(jù)庫的安全性是指只允許合法用戶進行其權(quán)限范圍內(nèi)的數(shù)據(jù)庫相關(guān)操作，保護數(shù)據(jù)庫以防止任何不合法的使用所造成的數(shù)據(jù)泄露、更改或破壞。17.1概述17.1.2MySQL用戶1．系統(tǒng)用戶和普通用戶2．root用戶17.1概述17.1.3用戶和權(quán)限的實現(xiàn)1．連接驗證階段2．請求驗證階段17.2用戶和權(quán)限表17.2.1user表可以使用DESE語句查看user表的結(jié)構(gòu)，SQL語句如下：USEmysql;DESCuser;1．賬號列例如，查看所有用戶，即查看Host列和User列的值，SQL語句和運行結(jié)果如下：SELECTHost,UserFROMmysql.user;17.2用戶和權(quán)限表2．權(quán)限列例如，查看所有用戶的SELECT、INSERT、UPDATE權(quán)限，SQL語句和運行結(jié)果如下：SELECTSelect_priv,Insert_priv,Update_priv,Create_priv,User,HostFROMmysql.user;17.2用戶和權(quán)限表3．安全列例如，查詢root用戶的User、plugin、authentication_string列，SQL語句和運行結(jié)果如下：SELECTUser,plugin,authentication_stringFROMmysql.userWHEREUser='root';

例如，查詢root用戶的password_expired、password_last_changed、password_lifetime列，SQL語句和運行結(jié)果如下：SELECTUser,password_expired,password_last_changed,password_lifetimeFROMmysql.userWHEREUser='root';17.2用戶和權(quán)限表4．資源控制列資源控制列是指user表中以max_開頭的字段，保存對用戶可使用的服務器資源的限制，以防止浪費服務器資源。max_questions：允許用戶每小時執(zhí)行查詢操作的最多次數(shù)。max_updates：允許用戶每小時執(zhí)行更新操作的最多次數(shù)。max_connections：允許用戶每小時建立連接的最多次數(shù)。max_user_connections：允許單個用戶同時建立連接的最多數(shù)量。17.2用戶和權(quán)限表17.2.2db表DESCmysql.db;1．用戶列db表的用戶列有3個，分別是Host、Db和User，這3列分別表示主機名、數(shù)據(jù)庫名和用戶名。2．權(quán)限列db表中Create_routine_priv和Alter_routine_priv這兩列表示用戶是否有創(chuàng)建和修改存儲過程的權(quán)限。17.2用戶和權(quán)限表17.2.3tables_priv表、columns_priv表和procs_priv表用DESC語句可以查看tables_priv表的結(jié)構(gòu)：DESCmysql.tables_priv;用DESC語句可以查看columns_priv表的結(jié)構(gòu)：DESCmysql.columns_priv;用DESC語句可以查看procs_priv表的結(jié)構(gòu)：DESCcs_priv;17.3用戶管理17.3.1添加普通用戶CREATEUSER[IFNOTEXISTS]user_name1[IDENTIFIEDBY'password'][,user_name2[IDENTIFIEDBY'password'][,…]];17.3用戶管理【例17-1】用root用戶登錄MySQL服務，添加4個新用戶。用戶test1，不指定主機和密碼；用戶test2，不指定主機，密碼為123456；用戶test3，主機名為localhost，密碼為123123；用戶test4，主機名為，密碼為321321。CREATEUSER'test1','test2'IDENTIFIEDBY'123456','test3'@'localhost'IDENTIFIEDBY'123123','test4'@''IDENTIFIEDBY'321321';17.3用戶管理17.3.2查看用戶SELECT·列名FROMmysql.userWHEREUser='username'ANDHost='hostname';【例17-2】查看MySQL服務器上例17-1創(chuàng)建的用戶。SELECTUser,Host,plugin,authentication_stringFROMmysql.userWHEREUserLIKE'test_';17.3用戶管理17.3.3修改用戶密碼1．root用戶修改自己的密碼（1）使用ALTERUSERUSER()語句修改root密碼ALTERUSERUSER()IDENTIFIEDBY'password';【例17-3】用root登錄MySQL服務器，修改root賬號的密碼為123123。ALTERUSERUSER()IDENTIFIEDBY'123123';C:\Windows\System32>mysql-uroot-p"123123"17.3用戶管理（2）使用mysqladmin命令修改root密碼mysqladmin-uusername-p["userpassword"]password"newpassword"【例17-4】修改root用戶的密碼，登錄密碼為123123，把密碼改為123456。C:\Windows\System32>mysqladmin-uroot-ppassword17.3用戶管理2．root用戶修改普通用戶的密碼（1）root用戶使用ALTERUSER語句修改普通用戶的密碼ALTERUSERuser_nameIDENTIFIEDBY'newpassword';【例17-5】用root登錄MySQL服務器，修改'test1'@'%'用戶的密碼為123456。ALTERUSER'test1'@'%'IDENTIFIEDBY'123456';17.3用戶管理（2）root用戶使用SETPASSWORD語句修改普通用戶的密碼SETPASSWORDFORuser_name='newpassword';【例17-6】使用root登錄MySQL服務器，把用戶'test3'@'localhost'的密碼更改為123456。SETPASSWORDFOR'test3'@'localhost'='123456';17.3用戶管理3．普通用戶修改自己的密碼ALTERUSERUSER()IDENTIFIEDBY'password':【例17-7】以用戶test1登錄MySQL服務，把自己的密碼改為123123。C:\Windows\System32>mysql-utest1-pSELECTcurrent_user();ALTERUSERUSER()IDENTIFIEDBY'123123';17.3用戶管理17.3用戶管理17.3.4修改用戶名RENAMEUSERold_user1TOnew_user1[,old_user2TOnew_user2…]【例17-8】用root登錄MySQL服務器，把用戶'test1'@'%'的用戶名改為aaa，主機名改為localhost。RENAMEUSER'test1'@'%'TO'aaa'@'localhost';修改用戶名后，使用SELECT語句查看用戶，SQL語句如下：SELECTUser,HostFROMmysql.user;17.3用戶管理17.3.5刪除普通用戶DROPUSERuser_name1[,user_name2…];【例17-9】用root登錄MySQL服務器，刪除名為test3、test4的用戶。SELECTUser,HostFROMmysql.user;DROPUSER'test3'@'localhost','test4'@'';17.4權(quán)限管理17.4.1權(quán)限級別分為全局級（用戶級）權(quán)限、數(shù)據(jù)庫級權(quán)限、表級權(quán)限、列級權(quán)限、存儲過程和代理權(quán)限等。17.4.2權(quán)限類型權(quán)限類型包括數(shù)據(jù)權(quán)限、結(jié)構(gòu)權(quán)限和管理權(quán)限，具體又包括很多權(quán)限，常用的權(quán)限如增、刪、改、查等，還有很多其他的權(quán)限。17.4權(quán)限管理17.4.3查看權(quán)限SHOWGRANTS[FORuser_name];SHOWGRANTSFORCURRENT_USER();【例17-10】查看用戶aaa的權(quán)限。SHOWGRANTSFORaaa@localhost;

SHOWGRANTSFORCURRENT_USER();17.4權(quán)限管理17.4.4授予權(quán)限1．GRANT語句的語法格式GRANTpriv_type[(column_list)][,…]]ON[object_type]priv_levelTOuser_name[IDENTIFIEDBY'password'][,…][WITHGRANTOPTION];17.4權(quán)限管理2．使用GRANT語句為用戶授權(quán)（1）授予表級權(quán)限GRANTpriv_typeON數(shù)據(jù)庫名.表名TO賬戶名[WITHGRANTOPTION];17.4權(quán)限管理【例17-11】授予用戶aaa在student_db.student表上的SELECT權(quán)限。GRANTSELECTONstudent_db.studentTOaaa@localhost;或者USEstudent_db;GRANTSELECTONstudentTOaaa@localhost;SHOWGRANTSFORaaa@localhost;

SELECTdb,table_name,table_priv,column_privFROMmysql.tables_privWHEREuser='aaa';

17.4權(quán)限管理C:\Windows\System32>mysql-uaaa-p"123123"SELECT*FROMstudent_db.studentWHERESource='上海';

UPDATEstudent_db.studentSETClassID='20235202'WHERESource='上海';ERROR1142(42000):UPDATEcommanddeniedtouser'aaa'@'localhost'fortable'student'17.4權(quán)限管理（2）授予列級權(quán)限GRANTpriv_type(column_list)[,…]ON數(shù)據(jù)庫名.表名TO賬戶名[WITHGRANTOPTION];17.4權(quán)限管理【例17-12】創(chuàng)建新用戶bbb，授予bbb在student_db.class表上的SELECT權(quán)限，在ClassID列、ClassName列上的UPDATE權(quán)限，并允許其將該權(quán)限授予其他用戶，然后查看用戶bbb的權(quán)限。CREATEUSERbbb@localhostIDENTIFIEDBY'123123';GRANTSELECT,UPDATE(ClassID,ClassName)ONstudent_db.classTObbb@localhostWITHGRANTOPTION;SHOWGRANTSFORbbb@localhost;

17.4權(quán)限管理C:\Windows\System32>mysql-ubbb-p"123123"SELECT*FROMstudent_db.class;UPDATEstudent_db.classSETClassName='人工智能AI'WHEREClassID='20235203';UPDATEstudent_db.classSETClassNum=20WHEREClassID='20235203';ERROR1143(42000):UPDATEcommanddeniedtouser'bbb'@'localhost'forcolumn'ClassNum'intable'class'17.4權(quán)限管理（3）授予數(shù)據(jù)庫級權(quán)限GRANTpriv_typeON數(shù)據(jù)庫名.*TO賬戶名[WITHGRANTOPTION];17.4權(quán)限管理【例17-13】創(chuàng)建新用戶ccc，授予ccc在student_db數(shù)據(jù)庫中所有表的SELECT權(quán)限，并允許其將該權(quán)限授予其他用戶，然后查看用戶ccc的權(quán)限。CREATEUSERccc@localhostIDENTIFIEDBY'123123';GRANTSELECTONstudent_db.*TOccc@localhostWITHGRANTOPTION;SHOWGRANTSFORccc@localhost;

C:\Windows\System32>mysql-uccc-p"123123"在ccc登錄的MySQL客戶端中輸入SELECT語句，SQL語句如下：SELECT*FROMstudent_db.score;17.4權(quán)限管理（4）授予全局級權(quán)限GRANTpriv_typeON*.*TO賬戶名[WITHGRANTOPTION];17.4權(quán)限管理【例17-14】創(chuàng)建新建用戶ddd，授予其所有的權(quán)限。CREATEUSERddd@localhostIDENTIFIEDBY'123123';GRANTALLPRIVILEGESON*.*TOddd@localhost;SHOWGRANTSFORddd@localhost;SHOWGRANTSFORroot@localhost;17.4權(quán)限管理（5）授予存儲過程權(quán)限和代理權(quán)限GRANTEXECUTE|ALTERROUTINE|CREATEROUTINEON{[*.*|數(shù)據(jù)庫名.*]|PROCEDURE數(shù)據(jù)庫名.存儲過程}TO賬戶名[WITHGRANTOPTION];17.4權(quán)限管理3．權(quán)限的轉(zhuǎn)移將WITH子句指定為WTTHGRANTOPTION，則表示TO子句中指定的所有用戶都具有把自己所擁有的權(quán)限授予其他用戶的權(quán)利，而無論那些其他用戶是否擁有該權(quán)限。17.4權(quán)限管理17.4.5收回權(quán)限1．收回特定的權(quán)限REVOKEpriv_type[(column_list)][,priv_type[(column_list)][,…]]ON[object_type]priv_levelFROMuser_name1[,user_name2[,…]];17.4權(quán)限管理【例17-15】收回bbb用戶在student_db.class表上的UPDATE權(quán)限。SHOWGRANTSFORbbb@localhost;

REVOKEUPDATE(ClassID,ClassName)ONstudent_db.classFROMbbb@localhost;SHOWGRANTSFORbbb@localhost;

SHOWGRANTSFOReee@localhost;

17.4權(quán)限管理2．收回所有權(quán)限REVOKEALLPRIVILEGES,GRANTOPTIONFROMuser_name1[,user_name2[,…]];【例17-16】收回ccc用戶的所有權(quán)限，包括GRANT權(quán)限。REVOKEALLPRIVILEGES,GRANTOPTIONFROMccc@localhost;SHOWGRANTSFORccc@localhost;

17.4權(quán)限管理3．重新加載用戶的權(quán)限FLUSHPRIVILEGES;17.4權(quán)限管理17.5.1管理用戶賬號1．查看用戶17.5使用NavicatforMySQL管理用戶和權(quán)限2．創(chuàng)建用戶17.5使用NavicatforMySQL管理用戶和權(quán)限3．修改用戶17.5使用NavicatforMySQL管理用戶和權(quán)限4．刪除用戶17.5使用NavicatforMySQL管理用戶和權(quán)限17.5.2管理權(quán)限使用NavicatforMySQL編輯用戶權(quán)限也很方便。17.5使用NavicatforMySQL管理用戶和權(quán)限1．創(chuàng)建角色CREATEROLE[IFNOTEXISTS]role_name1[,role_name2[,…]];2．為角色授予權(quán)限GRANTpriv_type[(column_list)][,priv_type[(column_list)][,…]]ON[object_type]priv_levelTOrole_name1[,role_name2[,…]];17.5使用NavicatforMySQL管理用戶和權(quán)限3．給用戶賦予角色GRANTrole_name1[,role_name2[,…]]TOuser_name1[,user_name2[,…]][WITHADMINOPTION];4．查看角色SHOWGRANTS[FORuser_name][USINGrole_n