[MySQL实践] MySQL中的用户创建与授权

文章目录

一、环境说明

操作系统：win10专业版
MySQL版本：8.0.19

二、对用户的操作

2.1、创建用户

可以简单地创建用户：

CREATEUSER xh;

也可以创建用户的同时设置密码：

CREATEUSER xh IDENTIFIED BY'12345678';

也可以创建用户的时候，设置可以登录的Host：

# 只能在MySQL服务器登录CREATEUSER'xh'@localhost IDENTIFIED BY'12345678';# or 只能在指定ip登录CREATEUSER'xh'@'192.168.10.1' IDENTIFIED BY'12345678';# or 在所有ip都可以登录CREATEUSER'xh'@'%' IDENTIFIED BY'12345678';

说明：

用户名可以不指定Host，此时默认的Host就是%，表示在所有ip的主机都可以登录。
用户名会出现在如下sql语句中：CREATE USER , GRANT 和 SET PASSWORD。
如用户名中含特殊字符（如：空格 或 -），则必须使用引号。例如：test-user 或是xiao hui。
如主机名中含有特殊字符（如：. 或 %），则必须使用引号。例如：192.168.10.2 或是% 。
如果使用引号，用户名和主机名必须单独引用。如'[email protected].%' 和'test-user'@'192.168.10.%' 是不同的。

关于账户名的详细规则，可以参考官方文档：

https://dev.mysql.com/doc/refman/8.0/en/account-names.html

当你想深入了解用户创建的语法，或是记不清

create user

的用法了，可以通过如下方式打开帮助：

# 终端中执行。如果使用的是Navicat，F6可以打开命令行终端。
mysql> HELP 'CREATE USER';# 结果|CREATEUSER| Syntax:
CREATEUSER[IFNOTEXISTS]user[auth_option][,user[auth_option]]...DEFAULT ROLE role [, role ]...[REQUIRE {NONE | tls_option [[AND] tls_option]...}][WITH resource_option [resource_option]...][password_option | lock_option]......
URL: https://dev.mysql.com/doc/refman/8.0/en/create-user.html

2.2、查看用户

创建好的用户信息将会保存到mysql系统数据库的user表中。这个表的字段如下（部分）：

-- DESCRIBE mysql.`user`;+--------------------------+-----------------------------------+------+-----+-----------------------+-------+| Field                    |Type|Null|Key|Default| Extra |+--------------------------+-----------------------------------+------+-----+-----------------------+-------+| Host                     |char(255)|NO| PRI ||||User|char(32)|NO| PRI |||| Select_priv              |enum('N','Y')|NO|| N                     ||| Insert_priv              |enum('N','Y')|NO|| N                     ||| Update_priv              |enum('N','Y')|NO|| N                     ||| Delete_priv              |enum('N','Y')|NO|| N                     ||| Create_priv              |enum('N','Y')|NO|| N                     ||| Drop_priv                |enum('N','Y')|NO|| N                     ||...+--------------------------+-----------------------------------+------+-----+-----------------------+-------+51rowsinset(0.14 sec)

字段说明：

User列：存储所有的MySQL账户名
Host列：存储可登录的主机名
其他列：存储了该用户拥有的权限、登录密码等信息

1).查看当前用户
可使用函数查看当前用户

-- 返回当前用户的用户名和可登录主机名-- 或者： SELECT USER();
mysql>selectuser()from dual;+----------------+|user()|+----------------+| root@localhost|+----------------+1rowinset(0.03 sec)-- 或者使用current_user()函数
mysql>selectcurrent_user();+----------------+|current_user()|+----------------+| root@%|+----------------+1rowinset(0.02 sec)--help 'user';

2).查看指定用户

SELECTUser,Host,authentication_string FROM mysql.userWHEREUser='xh';

执行结果如下：

+------+-----------+-------------------------------------------+|User| Host      | authentication_string                     |+------+-----------+-------------------------------------------+| xh   |%||| xh   | localhost |*84AAC12F54AB666ECFC2A83C676908C8BBC381B1 |+------+-----------+-------------------------------------------+2rowsinset(0.06 sec)

3).查看所有的用户

SELECTUser,Host FROM mysql.user;

执行结果如下：

+------------------+-----------+|User| Host      |+------------------+-----------+| nacos            |%|| root             |%|| xh               |%|| mysql.infoschema | localhost || mysql.session| localhost || mysql.sys        | localhost |+------------------+-----------+8rowsinset(0.05 sec)

2.3、修改密码

1).修改当前用户的密码

可通过如下脚本修改当前用户的密码：

SET PASSWORD='3456789';

2).修改指定用户的密码

可通过如下脚本修改指定用户的密码：

SET PASSWORD FOR xh='2345678';-- or 更建议的密码修改方式（修改账号xh的密码）ALTERUSER xh IDENTIFIED BY'2345678';

更推荐使用

ALTER USER

的方式修改用户密码。

本节参考：

https://dev.mysql.com/doc/refman/8.0/en/set-password.html

2.4、用户登录

打开命令行cmd，然后输入下面的命令：

mysql -u xh -p# 提示输入密码
Enter password:

输入正确的密码后，就可以进入mysql的命令行终端。

新创建的用户还没有授权，那么它的授权信息会是什么呢？

SHOW GRANTS;

结果如下：

+----------------------------------------+| Grants for xh@localhost|+----------------------------------------+|GRANTUSAGEON*.*TO`xh`@`localhost`|+----------------------------------------+1rowinset(0.00 sec)

那么再看一下它可以访问哪些数据库吧：

showdatabases;

结果如下所示：

showdatabases;+--------------------+|Database|+--------------------+| information_schema |+--------------------+1rowinset(0.00 sec)

说明该账号只能访问表：

information_schema

。

2.5、删除用户

删除账号xh：

DROPUSER'xh';

三、授权操作

3.1、用户授权

创建用户xh，并只允许其查看my_database上t_user表的权限：

-- 方法一：USE my_database;GRANTSELECTONTABLE t_user TO xh;-- or 方法二：GRANTSELECTON my_database.t_user TO xh;

以xh用户登录，看到其可以访问数据库

my_database

了：
在这里插入图片描述

3.2、角色授权

公司有一个数据分析组，有xh和nacos两个成员；要求数据分析组拥有my_database数据库中所有表的查询权限：

-- 创建角色create role analysis;-- 给角色添加成员grant analysis to xh, nacos;-- 将权限赋给角色grantselecton  my_database.*to analysis;-- 刷新权限
flush privileges;

3.3、查看授权

查看xh用户的授权：

SHOW GRANTS FOR xh;

结果如下：

+----------------------------------------------------+| Grants for xh@%|+----------------------------------------------------+|GRANTUSAGEON*.*TO`xh`@`%`||GRANTSELECTON`my_database`.`t_user`TO`xh`@`%`|+----------------------------------------------------+2rowsinset(0.02 sec)

查看当前用户的授权：

SHOW GRANTS;--  查看当前用户SELECTUSER();

3.4、修改授权

增加表的修改权限
由于xh用户只能查看t_user表的数据，但是作为开发人员，需要表的所有操作权限。故追加相关授权：

GRANTINSERT,UPDATE,DELETEON my_database.t_user TO'xh';-- orGRANTINSERT,UPDATE,DELETE,CREATE,DROPON my_database.t_user TO'xh';

增加库的修改权限
后续又需要增加在my_database中建表和删除表的权限：

GRANTINSERT,UPDATE,DELETE,CREATE,DROPON my_database.*TO'xh';

增加库的所有权限
可以进一步放开在my_database中的权限：

GRANTALLON my_database.*TO'xh';

增加所有权限
可以访问MySQL上的所有资源：

GRANTALLON*.*TO'xh';

3.5、取消授权

取消所有权限

REVOKEALLON*.*FROM'xh'@'%';

取消某个库的所有权限

REVOKEALLON my_database.*FROM'xh'@'%';

取消某个库的修改权限

REVOKEINSERT,UPDATE,DELETE,CREATE,DROPON my_database.*TO'xh';

取消某张表的修改权限

REVOKEINSERT,UPDATE,DELETE,CREATE,DROPON my_database.t_user TO'xh';

注：只要对权限进行了修改，最后都不要忘记执行下面的语句，否则设置会不生效：

flush privileges;

四、问题

4.1、如何创建用户并指定访问的数据库，并要求登录后必须设定新密码？

Joe 要给数据组的 John 创建一个用户，他希望John 能够从 192.168.7.42 登录 goods 数据库查询数据，用户第一次登录时使用密码 goods123，登录后必须设定一个新密码，那么应该用哪个语句？

答：

-- 创建用户，并设置密码（登录后即失效）createuser'john'@'192.168.7.42' identified by'goods123' password expire;-- 授权selectgrantselecton goods.*to'john'@'192.168.7.42';-- 刷新权限
flush privileges;

4.2、如何修改密码，并要求登录后修改新密码？

Fred 有一个名为 ‘fred’@‘%’ 的 MySQL 账户，但是他忘了密码，需要 Joe 帮他修改一个新口令， Joe 准备将这个账户的口令初始化为 goods123fred ，并设置为登录后修改新口令。他应该怎么做？

答：

ALTERUSER'fred'@'%' IDENTIFIED BY'goods123fred' password expire;-- 错误的修改密码方法：-- ALTER USER 'fred'@'%' set password 'goods123fred' expire;-- 不可以使用GRANT方式修改用户密码-- GRANT USAGE ON *.* TO 'fred'@'%' IDENTIFIED BY 'goods123fred';

MySQL8.0中不可以使用GRANT来创建用户或修改密码了

-- 创建用户GRANTUSAGEON*.*TO'xh_01'@'localhost' IDENTIFIED BY'123456';-- 会报错：1410 - You are not allowed to create a user with GRANT

参考：https://zhuanlan.zhihu.com/p/266149646

4.3、如何限制账号的资源

Joe 需要限制数据分析组（role analysis）的用户, 每小时查询次数不能超过10000次。应该怎么操作？
题目来源：限制用户使用资源

答：

alteruser analysis set MAX_QUERIES_PER_HOUR 10000;

五、总结

本文讲解了在MySQL8.0中如何创建用户和如何进行授权的相关知识。通过场景和代码相结合的方式，使得知识的学习更直观。

参考：
https://blog.csdn.net/qq_42826747/article/details/106203232

标签： mysql android

本文转载自: https://blog.csdn.net/xu7382/article/details/136521077
版权归原作者 写代码的老书生 所有，如有侵权，请联系我们删除。