第14章MySQL数据库安全管理

第14章MySQL数据库安全管理主要内容14.1数据库安全管理概述14.2用户管理14.3权限管理14.4角色管理14.5本章小结14.1数据库安全管理概述数据库的安全性是指有效保护数据库，防止不合法地使用造成数据泄露、更改或者毁坏，其本质是要保护数据的安全性、完整性和一致性。MySQL是一个多用户的数据库管理系统，它提供了功能强大的访问控制系统，以此来确保MySQL服务器的安全访问，即它可以为不同用户指定不同的访问权限，从而进一步减少数据泄露或被损坏的风险。数据库安全管理最重要的是要确保有资格的用户访问数据库的权限，同时令所有未授权的用户无法获取数据。14.2用户管理14.2.1查看用户信息使用user表查看用户信息MySQL的用户管理包括管理用户的账号、权限和角色等。MySQL用户可以分为两种：root用户和普通用户。root用户是超级管理员，拥有所有权限，普通用户只拥有被授予的各种权限。MySQL的用户账号及相关信息都存储在mysql数据库的user表中，我们可以用SELECT语句去user表中查看所有的用户信息。【例14.1】查看MySQL中所有用户。在MySQL命令行客户端输入命令：SELECTuser,hostFROMmysql.user;14.2用户管理14.2.1查看用户信息使用user表查看用户信息user表是MySQL中非常重要的一个权限表，以下是它的四类字段：范围列：包括user字段和host字段。权限列：决定了用户的权限，表示了在全局范围内允许对数据和数据库进行的操作。安全列：都是和加密、标识用户、授权插件相关的字段。资源控制列：用来限制用户使用的资源。14.2用户管理14.2.1查看用户信息使用函数查看当前用户第11章我们介绍过部分系统函数，可以使用这些函数来查看当前登录用户的名称。【例14.2】查看当前用户。在MySQL命令行客户端输入命令：SELECTUSER();SELECTCURRENT_USER();

14.2用户管理14.2.1查看用户信息使用图形化工具查看用户打开Workbench工具，连接到MySQL服务器。选中“Administration”标签，单击“MANAGEMENT”下的“UsersandPrivileges”选项，出现界面如图所示。

14.2用户管理14.2.2登录和退出MySQL服务器启动MySQL服务后，可以在DOS窗口通过mysql命令登录到MySQL服务器，基本语法格式如下：mysql-hhostname|hostIP-Pport-uusername-p-DDatabaseName-e“SQL语句”其余参数可以通过mysql--help或者mysql-?语句来查询退出登录直接输入QUIT或者EXIT命令即可【例4.3】登录MySQL服务器中的jwgl数据库。在MySQL命令行客户端输入命令：mysql-uroot-pjwgl回车后在下一行出现“Enterpassword：”时输入密码即可。

14.2用户管理14.2.3创建用户使用命令创建用户可以使用CREATEUSER语句来创建用户，语法格式：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创建用户使用命令创建用户【例14.4】创建新用户u1，主机名为localhost，密码为“123456”。在MySQL命令行客户端输入命令：CREATEUSERu1@localhostIDENTIFIEDBY'123456';

新创建的用户拥有的权限很少，它们可以登录到MySQL服务器，只允许进行不需要权限的操作。14.2用户管理14.2.3创建用户使用图形化工具创建用户打开Workbench工具，连接到MySQL服务器。进入如图14.2所示界面，单击左下角的“AddAccount”按钮，进入如图14.4所示的创建用户界面。将新用户信息填入后，单击“Apply”，新用户u2已创建完成，此时在左侧“User”列表中就出现了新用户u2。14.2用户管理14.2.4修改用户使用RENAMEUSER语句RENAMEUSER语句可以修改一个或多个已经存在的用户账号名称，语法格式：RENAMEUSERold_userTOnew_user[,old_userTOnew_user]...【例14.5】将用户u2名称修改为user2。在MySQL命令行客户端输入命令：RENAMEUSERu2@localhosttouser2@localhost;14.2用户管理14.2.4修改用户使用ALTERUSER语句使用ALTERUSER语句可以修改MySQL用户，它可以为现有用户修改身份验证、角色、资源限制、密码管理、锁定或解锁账户等。语法格式：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';执行结果如图所示。14.2用户管理14.2.4修改用户使用ALTERUSER语句【例14.8】将u1用户每小时查询、修改的最大次数分别设置为100、50。在MySQL命令行客户端输入命令：ALTERUSERu1@localhostWITHMAX_QUERIES_PER_HOUR100MAX_UPDATES_PER_HOUR50;【例14.9】将u1用户密码设置为立即过期。在MySQL命令行客户端输入命令：ALTERUSERu1@localhostPASSWORDEXPIRE;此时退出root用户，用u1用户连接服务器，查看其密码是否过期。EXIT;mysql-uu1-p执行结果如图所示。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是关键字，而不是指旧密码。旧密码是在按下回车键后根据提示输入，并且新密码须用双引号括起来，不能用单引号。【例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”选项，出现如图所示界面。所有想要修改的选项设置完成后，单击“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数据库中的user表、db表、host表、tables_priv表、columns_priv表和proc_priv表中。在MySQL启动时，服务器将这些数据库表中的权限信息内容读入内存。MySQL提供了GRANT和REVOKE命令来管理访问权限。14.3权限管理14.3.1授予权限常用管理权限14.3权限管理14.3.1授予权限使用命令行授予用户权限语法格式：GRANTpriv_type[(column_list)]ONdatabase.tableTOuser[,user]…[WITHGRANTOPTION]其中：ONdatabase.table：ON后面跟的是该权限的适用对象，有几种情况：全局权限，适用于一个给定服务器中的所有数据库，可以用“*.*”表示。数据库权限，适用于一个给定数据库中的所有目标，可以用“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”标签，出现如图所示界面。14.3权限管理14.3.1授予权限使用图形化工具授予用户权限之前例题给u1用户授予了部分权限，此时我们单击u1用户在jwgl数据库中拥有的这些权限，出现如图所示界面。

14.3权限管理14.3.1授予权限使用图形化工具授予用户权限如果想授予u1用户在jwgl数据库上的权限，那么直接在上图中将想要授予的权限前面的复选框选中。假如选中CREATE和ALTER两个权限，单击“Apply”，此时给用户u1授予jwgl数据库上的权限完成，得到如图所示界面。如果在第（2）步，发现u1用户目前没有任何权限，或者想给用户u1授予除jwgl之外的其他数据库权限，则从第（2）步直接跳到第（5）步执行。此时单击图14.16中的按钮“AddEntry…”，进入到选择数据库界面，如图所示。

14.3权限管理14.3.1授予权限使用图形化工具授予用户权限选好之后单击“OK”按钮，进入到选择具体权限设置界面，此时出现一条在mysql数据库上none权限的记录，如图所示。选中这行记录，然后会出现图14.16所示界面，接下来跳回第（3）步和第（4）步继续执行，即可完成授予用户u1在mysql数据库上的权限，如图所示。14.3权限管理14.3.2查看用户权限使用命令查看用户权限mysql数据库下的user表中存储着用户的基本权限，所以可以使用SELECT语句去user表中查询各用户的权限。SELECT*FROMmysql.user\G也可以使用SHOWGRANTSFOR语句来查询用户的权限。【例14.16】创建一个新用户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所示界面，把想要撤销的权限前面的复选框对勾去掉，即可撤销该用户权限。如果想撤销所有权限，单击“RevokeAllPrivileges”按钮即可完成14.4角色管理MySQL8.0中增加了部分功能，在用户权限方面增加了角色的管理，而角色实质上就是权限的集合。既然已经设置了权限，为什么还要设置角色呢？在给用户授权时，现实中用户数量较多，如果单独给每一个用户授予多个权限，语句重复度高，代码执行效率低。为了避免这种情况，我们可以先将要授权的权限放入角色中，然后再轻松地把角色授予相应的用户。14.4角色管理14.4.1创建角色可以使用CREATEROLE语句创建角色，语法格式：CREATEROLE[IFNOTEXISTS]role[,role]...【例14.18】创建角色x1和x2。

在MySQL命令行客户端输入命令：CREATEROLEx1,x2@localhost;14.4角色管理14.4.2角色授权与撤销权限创建成功之后的角色是空的权限集合，我们需要使用GRANT语句给角色授权。给角色授权的语法格式和14.3.1小节中给用户授权的语法格式相似，区别是把TO后面的用户换成了角色。当然我们也可以从角色中把某些权限撤回，使用的仍然是REVOKE语句，语法格式和14.3.3小节中的撤销用户权限语法格式相同，只是把FROM后面从用户换成了角色。【例14.19】给角色x1授权和撤销权限。在MySQL命令行客户端输入命令：GRANTSELECT(sno,sname)ONjwgl.studentTOx1;GRANTDROP,CREATE,CREATEROUTINEONjwgl.*tox1;REVOKECREATEROUTINEONjwgl.*FROMx1;14.4角色管理14.4.3授予用户角色与撤销角色角色创建并授权成功后，需要将角色授予用户才能发挥其作用。授予用户角色的语法格式与14.3.1小节中给用户授权的语法格式相似。区别有两点：一是把GRANT后面的权限换成角色，二是去掉了ONdatabase.table子句。我们可以通过这一点来区分到底是给用户授予权限还是给用户授予角色：有ON子句的是给用户授予权限，没有ON子句的则是给用户授予角色。当用户不需要某些操作权限时，出于数据安全性的考虑，我们可以撤销权限或角色。撤销角色使用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授予用户角色与撤销角色创建好的角色默认是未激活状态，将它授予用户后并没有直接起作用，还需要将其激活才能使用户拥有对应的权限。激活角色可以使用SETDEFAULTROLE语句，语法格式：SETDEFAULTROLE{NONE|ALL|role[,role]...}TOuser[,user]...还可以通过设置系统变量activate_all_roles_on_login的值来激活。该系统变量值默认为off，设置成on之后再赋予其他角色给新用户，就不需要再手动激活了。【例14.21】激活角色x1。在MySQL命令行客户端输入命令：SETDEFAULTROLEx1tou1@localhost;或者SETGLOBALactivate_all_roles_on_login=on;14.4角色管理14.4.4