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

一、环境说明

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

二、对用户的操作

2.1、创建用户

可以简单地创建用户：

CREATE USER xh;
1

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

CREATE USER xh IDENTIFIED BY '12345678';
1

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

# 只能在MySQL服务器登录
CREATE USER 'xh'@localhost IDENTIFIED BY '12345678';
# or 只能在指定ip登录
CREATE USER 'xh'@'192.168.10.1' IDENTIFIED BY '12345678';
# or 在所有ip都可以登录
CREATE USER 'xh'@'%' IDENTIFIED BY '12345678';
1
2
3
4
5
6

说明：

• 用户名可以不指定Host，此时默认的Host就是%，表示在所有ip的主机都可以登录。
• 用户名会出现在如下sql语句中：CREATE USER , GRANT 和 SET PASSWORD。
• 如用户名中含特殊字符（如：空格 或 -），则必须使用引号。例如：test-user 或是xiao hui。
• 如主机名中含有特殊字符（如：. 或 %），则必须使用引号。例如：192.168.10.2 或是% 。
• 如果使用引号，用户名和主机名必须单独引用。如'test-user@192.168.10.%' 和'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';
# 结果
| CREATE USER | Syntax:
CREATE USER [IF NOT EXISTS]
    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
1
2
3
4
5
6
7
8
9
10
11
12

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                     |       |
...
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.14 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

字段说明：

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

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

-- 返回当前用户的用户名和可登录主机名
-- 或者： SELECT USER();
mysql> select user() from dual;
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.03 sec) 

-- 或者使用current_user()函数
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.02 sec)

--help 'user';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2).查看指定用户

SELECT User,Host,authentication_string FROM mysql.user WHERE User='xh';
1

执行结果如下：

+------+-----------+-------------------------------------------+
| User | Host      | authentication_string                     |
+------+-----------+-------------------------------------------+
| xh   | %         |                                           |
| xh   | localhost | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 |
+------+-----------+-------------------------------------------+
2 rows in set (0.06 sec)
1
2
3
4
5
6
7

3).查看所有的用户

SELECT User,Host FROM mysql.user;
1

执行结果如下：

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| nacos            | %         |
| root             | %         |
| xh               | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
8 rows in set (0.05 sec)
1
2
3
4
5
6
7
8
9
10
11

 

2.3、修改密码

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

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

SET PASSWORD='3456789';
1

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

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

SET PASSWORD FOR xh='2345678';

-- or 更建议的密码修改方式（修改账号xh的密码）
ALTER USER xh IDENTIFIED BY '2345678';
1
2
3
4

更推荐使用 ALTER USER 的方式修改用户密码。

本节参考：

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

 

2.4、用户登录

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

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

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

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

SHOW GRANTS;
1

结果如下：

+----------------------------------------+
| Grants for xh@localhost                |
+----------------------------------------+
| GRANT USAGE ON *.* TO `xh`@`localhost` |
+----------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6

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

show databases;
1

结果如下所示：

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

说明该账号只能访问表：information_schema。

2.5、删除用户

删除账号xh：

DROP USER 'xh';
1

 

三、授权操作

3.1、用户授权

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

-- 方法一：
USE my_database;
GRANT SELECT ON TABLE t_user TO xh;

-- or 方法二：
GRANT SELECT ON my_database.t_user TO xh;
1
2
3
4
5
6

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

3.2、角色授权

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

-- 创建角色
create role analysis;
-- 给角色添加成员
grant analysis to xh, nacos;
-- 将权限赋给角色
grant select on  my_database.* to analysis;
-- 刷新权限
flush privileges;
1
2
3
4
5
6
7
8

 

3.3、查看授权

查看xh用户的授权：

SHOW GRANTS FOR xh;
1

结果如下：

+----------------------------------------------------+
| Grants for xh@%                                    |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `xh`@`%`                     |
| GRANT SELECT ON `my_database`.`t_user` TO `xh`@`%` |
+----------------------------------------------------+
2 rows in set (0.02 sec)
1
2
3
4
5
6
7

查看当前用户的授权：

SHOW GRANTS;

--  查看当前用户
SELECT USER();
1
2
3
4

 

3.4、修改授权

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

GRANT INSERT,UPDATE,DELETE ON my_database.t_user TO 'xh';
-- or
GRANT INSERT,UPDATE,DELETE,CREATE,DROP ON my_database.t_user TO 'xh';
1
2
3

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

GRANT INSERT,UPDATE,DELETE,CREATE,DROP ON my_database.* TO 'xh';
1

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

GRANT ALL ON my_database.* TO 'xh';
1

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

GRANT ALL ON *.* TO 'xh';
1

 

3.5、取消授权

取消所有权限

REVOKE ALL ON *.* FROM 'xh'@'%';
1

取消某个库的所有权限

REVOKE ALL ON my_database.* FROM 'xh'@'%';
1

取消某个库的修改权限

REVOKE INSERT,UPDATE,DELETE,CREATE,DROP ON my_database.* TO 'xh';
1

取消某张表的修改权限

REVOKE INSERT,UPDATE,DELETE,CREATE,DROP ON my_database.t_user TO 'xh';
1

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

flush privileges;
1

 

四、问题

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

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

答：

-- 创建用户，并设置密码（登录后即失效）
create user 'john'@'192.168.7.42' identified by 'goods123' password expire;
-- 授权select
grant select on goods.* to 'john'@'192.168.7.42';
-- 刷新权限
flush privileges;
1
2
3
4
5
6

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

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

答：

ALTER USER 'fred'@'%' IDENTIFIED BY 'goods123fred' password expire;

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

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

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

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

4.3、如何限制账号的资源

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

答：

alter user analysis set MAX_QUERIES_PER_HOUR 10000;
1

五、总结

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

---

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