MySQL系列复习（3）MySQL管理员常⽤的⼀些命令

最新推荐文章于 2023-08-02 17:35:45 发布

一棵树～

最新推荐文章于 2023-08-02 17:35:45 发布

阅读量315

点赞数

分类专栏：【数据库-MySQL】

本文链接：https://blog.csdn.net/MyronCham/article/details/108996955

版权

【数据库-MySQL】专栏收录该内容

30 篇文章 1 订阅

订阅专栏

1、MySQL权限⼯作原理

1.1、MySQL是如何来识别⼀个用户的呢？

环境：mysql5.7.30，cmd命令中进⾏演示。

1、MySQL权限⼯作原理

1.1、MySQL是如何来识别⼀个用户的呢？

MySQL为了安全性考虑，采⽤【主机名+⽤户名】来判断⼀个⽤户的⾝份，因为在互联⽹中很难通过⽤户名来判断⼀个⽤户的⾝份，但是我们可以通过ip或者主机名判断⼀台机器，某个⽤户通过这个机器过来的，我们可以识别为⼀个⽤户，所以MySQL中采⽤⽤【户名+主机名】来识别⽤户的⾝份。当⼀个⽤户对MySQL发送指令的时候，mysql就是通过⽤户名和来源（主机）来断定⽤户的权限。

1.2、权限验证分为2个阶段

阶段1：连接数据库，此时MySQL会根据你的⽤户名及你的来源（ip或者主机名称）判断是否有权限连接。
阶段2：对MySQL服务器发起请求操作，如create table、select、delete、update、create index等操作，此时mysql会判断你是否有权限操作这些指令。

2、权限生效时间

⽤户及权限信息放在库名为mysql的库中，mysql启动时，这些内容被读进内存并且从此时⽣效，所以如果通过直接操作这些表来修改⽤户及权限信息的，需要重启mysql 或者执⾏ flush privileges; 才可以⽣效。

⽤户登录之后，mysql会和当前⽤户之间创建⼀个连接，此时⽤户相关的权限信息都保存在这个连接中，存放在内存中，此时如果有其他地⽅修改了当前⽤户的权限，这些变更的权限会在下⼀次登录时才会⽣效。

3、查看所有用户

⽤户信息在 mysql.user 表中，如下：

mysql> use mysql;
Database changed
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.03 sec)

mysql>

4、创建⽤户

语法：
create user ⽤户名[@主机名] [identified by '密码'];
说明：

1.主机名默认值为%，表⽰这个⽤户可以从任何主机连接mysql服务器
2.密码可以省略，表⽰⽆密码登录

4.1、示例1：不指定主机名

不指定主机名时，表⽰这个⽤户可以从任何主机连接mysql服务器。

mysql> use mysql;
Database changed
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

mysql> create user test1;
Query OK, 0 rows affected (0.09 sec)

mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| test1         | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)


mysql> exit
Bye
PS C:\Windows\system32>
PS C:\Windows\system32> mysql -u test1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

上⾯创建了⽤户名为 test1 ⽆密码的⽤户，没有指定主机，可以看出host的默认值为 % ，表⽰ test1 可以从任何机器登录到mysql中。

⽤户创建之后可以在 mysql 库中通过 select user,host from user; 查看到。

4.2、其他示例

create user 'test2'@'localhost' identified by '123';
说明：test2的主机为localhost表⽰本机，此⽤户只能登陆本机的mysql

create user 'test3'@'%' identified by '123';
说明：test3可以从任何机器连接到mysql服务器

create user 'test4'@'192.168.11.%' identified by '123';
说明：test4可以从192.168.11段的机器连接mysql

5、修改密码【3种⽅式】

⽅式1：通过管理员修改密码
SET PASSWORD FOR '⽤户名'@'主机' = PASSWORD('密码');

⽅式2：create user ⽤户名[@主机名] [identified by '密码'];
set password = password('密码');

⽅式3：通过修改mysql.user表修改密码

mysql> use mysql;
Database changed
mysql> update user set   authentication_string=password('321') where user='test1' and host='%';
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)

注意：

通过表的⽅式修改之后，需要执⾏ flush privileges; 才能对⽤户⽣效。
5.7中user表中的authentication_string字段表⽰密码，⽼的⼀些版本中密码字段是password。

6、给⽤户授权

创建⽤户之后，需要给⽤户授权，才有意义。
语法：
grant privileges ON database.table TO 'username'[@'host'] [with grant option]

6.1、grant命令说明

priveleges (权限列表)，可以是 all ，表⽰所有权限，也可以是 select、update 等权限，多个权限之间⽤逗号分开。
ON ⽤来指定权限针对哪些库和表，格式为数据库.表名，点号前⾯⽤来指定数据库名，点号后⾯⽤来指定表名， *.* 表⽰所有数据库所有表。
TO 表⽰将权限赋予某个⽤户, 格式为 username@host ，@前⾯为⽤户名，@后⾯接限制的主机，可以是IP、IP段、域名以及%，%表⽰任何地⽅。
WITH GRANT OPTION 这个选项表⽰该⽤户可以将⾃⼰拥有的权限授权给别⼈。注意：经常有⼈在创建操作⽤户的时候不指定WITH GRANT OPTION选项导致后来该⽤户不能使⽤GRANT命令创建⽤户或者给其它⽤户授权。备注：可以使⽤GRANT重复
给⽤户添加权限，权限叠加，⽐如你先给⽤户添加⼀个select权限，然后又给⽤户添加⼀个insert权限，那么该⽤户就同时拥有了select和insert权限。

6.2、示例

grant all on *.* to 'test1'@'%';
说明：给test1授权可以操作所有库所有权限，相当于dba

grant select on apolloconfigdbpro.* to 'test1'@'%';
说明：test1可以对apolloconfigdbpro库中所有的表执⾏select

grant select,update on apolloconfigdbpro.* to 'test1'@'%';
说明：test1可以对apolloconfigdbpro库中所有的表执⾏select、update

grant select(user,host) on mysql.user to 'test1'@'localhost';
说明：test1⽤户只能查询mysql.user表的user,host字段

7、查看⽤户权限

show grants for '⽤户名'[@'主机']
主机可以省略，默认值为%，示例：

mysql> show grants for 'test1'@'localhost';
+--------------------------------------------------------------------+
| Grants for test1@localhost                                         |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'localhost'                          |
| GRANT SELECT (host, user) ON `mysql`.`user` TO 'test1'@'localhost' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'test1'@'%';
+--------------------------------------------------------------+
| Grants for test1@%                                           |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test1'@'%'                   |
| GRANT SELECT, UPDATE ON `apolloconfigdbpro`.* TO 'test1'@'%' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

show grants;
查看当前⽤户的权限，如：

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

8、撤销⽤户权限

语法
revoke privileges ON database.table FROM '⽤户名'[@'主机'];
可以先通过 show grants 命令查询⼀下⽤户对于的权限，然后使⽤ revoke 命令撤销⽤户
对应的权限，示例：

mysql> show grants for 'test1'@'localhost';
+--------------------------------------------------------------------+
| Grants for test1@localhost                                         |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'localhost'                          |
| GRANT SELECT (host, user) ON `mysql`.`user` TO 'test1'@'localhost' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke select(host) on mysql.user from test1@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'test1'@'localhost';
+--------------------------------------------------------------+
| Grants for test1@localhost                                   |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'localhost'                    |
| GRANT SELECT (user) ON `mysql`.`user` TO 'test1'@'localhost' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

上⾯我们先通过 grants 命令查看test1的权限，然后调⽤revoke命令撤销对 mysql.user 表 host 字段的查询权限，最后又通过grants命令查看了test1的权限，和预期结果⼀致。

9、删除⽤户【2种⽅式】

⽅式1：drop user '⽤户名'[@‘主机’]，

示例：

mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| test1         | %         |
| test3         | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| test1         | localhost |
| test2         | localhost |
+---------------+-----------+
7 rows in set (0.00 sec)

mysql> drop user 'test1'@'localhost';
Query OK, 0 rows affected (0.01 sec)

# 删除test1用户后查看是否删除，和预期结果一致
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| test1         | %         |
| test3         | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| test2         | localhost |
+---------------+-----------+
6 rows in set (0.01 sec)

mysql>

drop的⽅式删除⽤户之后，⽤户下次登录就会起效。

方式2：

通过删除mysql.user表数据的⽅式删除，如下：
delete from user where user='⽤户名' and host='主机';
flush privileges;
注意通过表的⽅式删除的，需要调⽤ flush privileges; 刷新权限信息（权限启动的时候在内存中保存着，通过表的⽅式修改之后需要刷新⼀下）。

mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| test1         | %         |
| test3         | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| test2         | localhost |
+---------------+-----------+
6 rows in set (0.00 sec)

mysql> delete from user where user='test2' and host='localhost';
Query OK, 1 row affected (0.02 sec)

mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| test1         | %         |
| test3         | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

mysql>

10、授权原则说明

只授予能满⾜需要的最⼩权限，防⽌⽤户⼲坏事，⽐如⽤户只是需要查询，那就只给select权限就可以了，不要给⽤户赋予update、insert或者delete权限
创建⽤户的时候限制⽤户的登录主机，⼀般是限制成指定IP或者内⽹IP段
初始化数据库的时候删除没有密码的⽤户，安装完数据库的时候会⾃动创建⼀些⽤户，这些⽤户默认没有密码
为每个⽤户设置满⾜密码复杂度的密码
定期清理不需要的⽤户，回收权限或者删除⽤户

11、总结

通过命令的⽅式操作⽤户和权限不需要刷新，下次登录⾃动⽣效
通过操作mysql库中表的⽅式修改、⽤户信息，需要调⽤ flush privileges; 刷新⼀下，下次登录⾃动⽣效
mysql识别⽤户⾝份的⽅式是：⽤户名+主机
本⽂中讲到的⼀些指令中带主机的，主机都可以省略，默认值为%，表⽰所有机器
mysql中⽤户和权限的信息在库名为mysql的库中

一棵树～

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL系列复习（3）MySQL管理员常⽤的⼀些命令

环境：mysql5.7.30，cmd命令中进⾏演⽰。1、MySQL权限⼯作原理1.1、MySQL是如何来识别⼀个用户的呢？MySQL为了安全性考虑，采⽤【主机名+⽤户名】来判断⼀个⽤户的⾝份，因为在互联⽹中很难通过⽤户名来判断⼀个⽤户的⾝份，但是我们可以通过ip或者主机名判断⼀台机器，某个⽤户通过这个机器过来的，我们可以识别为⼀个⽤户，所以MySQL中采⽤⽤【户名+主机名】来识别⽤户的⾝份。当⼀个⽤户对MySQL发送指令的时候，mysql就是通过⽤户名和来源（主机）来断定⽤户的权限。1.2
复制链接

扫一扫

专栏目录