第17章 用户和权限管理

第17章用户和权限管理学习目标：了解用户和权限的相关概念，掌握创建用户的方法，掌握设置密码的方法，掌握授予用户权限的方法，掌握权限收回的方法，掌握角色权限授予及收回的方法。17.1概述17.1.1数据库的安全性数据库的安全性是指只允许合法用户进行其权限范围内的数据库相关操作，保护数据库以防止任何不合法的使用所造成的数据泄露、更改或破坏。17.1概述17.1.2MySQL用户1．系统用户和普通用户2．root用户17.1概述17.1.3用户和权限的实现1．连接验证阶段2．请求验证阶段17.2用户和权限表17.2.1user表可以使用DESE语句查看user表的结构，SQL语句如下：USEmysql;DESCuser;1．账号列例如，查看所有用户，即查看Host列和User列的值，SQL语句和运行结果如下：SELECTHost,UserFROMmysql.user;17.2用户和权限表2．权限列例如，查看所有用户的SELECT、INSERT、UPDATE权限，SQL语句和运行结果如下：SELECTSelect_priv,Insert_priv,Update_priv,Create_priv,User,HostFROMmysql.user;17.2用户和权限表3．安全列例如，查询root用户的User、plugin、authentication_string列，SQL语句和运行结果如下：SELECTUser,plugin,authentication_stringFROMmysql.userWHEREUser='root';

例如，查询root用户的password_expired、password_last_changed、password_lifetime列，SQL语句和运行结果如下：SELECTUser,password_expired,password_last_changed,password_lifetimeFROMmysql.userWHEREUser='root';17.2用户和权限表4．资源控制列资源控制列是指user表中以max_开头的字段，保存对用户可使用的服务器资源的限制，以防止浪费服务器资源。max_questions：允许用户每小时执行查询操作的最多次数。max_updates：允许用户每小时执行更新操作的最多次数。max_connections：允许用户每小时建立连接的最多次数。max_user_connections：允许单个用户同时建立连接的最多数量。17.2用户和权限表17.2.2db表DESCmysql.db;1．用户列db表的用户列有3个，分别是Host、Db和User，这3列分别表示主机名、数据库名和用户名。2．权限列db表中Create_routine_priv和Alter_routine_priv这两列表示用户是否有创建和修改存储过程的权限。17.2用户和权限表17.2.3tables_priv表、columns_priv表和procs_priv表用DESC语句可以查看tables_priv表的结构：DESCmysql.tables_priv;用DESC语句可以查看columns_priv表的结构：DESCmysql.columns_priv;用DESC语句可以查看procs_priv表的结构：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创建的用户。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权限管理17.4.1权限级别分为全局级（用户级）权限、数据库级权限、表级权限、列级权限、存储过程和代理权限等。17.4.2权限类型权限类型包括数据权限、结构权限和管理权限，具体又包括很多权限，常用的权限如增、删、改、查等，还有很多其他的权限。17.4权限管理17.4.3查看权限SHOWGRANTS[FORuser_name];SHOWGRANTSFORCURRENT_USER();【例17-10】查看用户aaa的权限。SHOWGRANTSFORaaa@localhost;

SHOWGRANTSFORCURRENT_USER();17.4权限管理17.4.4授予权限1．GRANT语句的语法格式GRANTpriv_type[(column_list)][,…]]ON[object_type]priv_levelTOuser_name[IDENTIFIEDBY'password'][,…][WITHGRANTOPTION];17.4权限管理2．使用GRANT语句为用户授权（1）授予表级权限GRANTpriv_typeON数据库名.表名TO账户名[WITHGRANTOPTION];17.4权限管理【例17-11】授予用户aaa在student_db.student表上的SELECT权限。GRANTSELECTONstudent_db.studentTOaaa@localhost;或者USEstudent_db;GRANTSELECTONstudentTOaaa@localhost;SHOWGRANTSFORaaa@localhost;

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

17.4权限管理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权限管理（2）授予列级权限GRANTpriv_type(column_list)[,…]ON数据库名.表名TO账户名[WITHGRANTOPTION];17.4权限管理【例17-12】创建新用户bbb，授予bbb在student_db.class表上的SELECT权限，在ClassID列、ClassName列上的UPDATE权限，并允许其将该权限授予其他用户，然后查看用户bbb的权限。CREATEUSERbbb@localhostIDENTIFIEDBY'123123';GRANTSELECT,UPDATE(ClassID,ClassName)ONstudent_db.classTObbb@localhostWITHGRANTOPTION;SHOWGRANTSFORbbb@localhost;

17.4权限管理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权限管理（3）授予数据库级权限GRANTpriv_typeON数据库名.*TO账户名[WITHGRANTOPTION];17.4权限管理【例17-13】创建新用户ccc，授予ccc在student_db数据库中所有表的SELECT权限，并允许其将该权限授予其他用户，然后查看用户ccc的权限。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权限管理（4）授予全局级权限GRANTpriv_typeON*.*TO账户名[WITHGRANTOPTION];17.4权限管理【例17-14】创建新建用户ddd，授予其所有的权限。CREATEUSERddd@localhostIDENTIFIEDBY'123123';GRANTALLPRIVILEGESON*.*TOddd@localhost;SHOWGRANTSFORddd@localhost;SHOWGRANTSFORroot@localhost;17.4权限管理（5）授予存储过程权限和代理权限GRANTEXECUTE|ALTERROUTINE|CREATEROUTINEON{[*.*|数据库名.*]|PROCEDURE数据库名.存储过程}TO账户名[WITHGRANTOPTION];17.4权限管理3．权限的转移将WITH子句指定为WTTHGRANTOPTION，则表示TO子句中指定的所有用户都具有把自己所拥有的权限授予其他用户的权利，而无论那些其他用户是否拥有该权限。17.4权限管理17.4.5收回权限1．收回特定的权限REVOKEpriv_type[(column_list)][,priv_type[(column_list)][,…]]ON[object_type]priv_levelFROMuser_name1[,user_name2[,…]];17.4权限管理【例17-15】收回bbb用户在student_db.class表上的UPDATE权限。SHOWGRANTSFORbbb@localhost;

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

SHOWGRANTSFOReee@localhost;

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

17.4权限管理3．重新加载用户的权限FLUSHPRIVILEGES;17.4权限管理17.5.1管理用户账号1．查看用户17.5使用NavicatforMySQL管理用户和权限2．创建用户17.5使用NavicatforMySQL管理用户和权限3．修改用户17.5使用NavicatforMySQL管理用户和权限4．删除用户17.5使用NavicatforMySQL管理用户和权限17.5.2管理权限使用NavicatforMySQL编辑用户权限也很方便。17.5使用NavicatforMySQL管理用户和权限1．创建角色CREATEROLE[IFNOTEXISTS]role_name1[,role_name2[,…]];2．为角色授予权限GRANTpriv_type[(column_list)][,priv_type[(column_list)][,…]]ON[object_type]priv_levelTOrole_name1[,role_name2[,…]];17.5使用NavicatforMySQL管理用户和权限3．给用户赋予角色GRANTrole_name1[,role_name2[,…]]TOuser_name1[,user_name2[,…]][WITHADMINOPTION];4．查看角色SHOWGRANTS[FORuser_name][USINGrole_n