距离上次更新本文已经过去了 389 天,文章部分内容可能已经过时,请注意甄别

用户管理是 mysql 权限控制的重要一环。

1.MySQL 的用户表

在 MySQL 数据库中,所有的用户都存在系统数据库 mysql 的 user 表内

plaintext
1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> select host,user,authentication_string from user;
+-----------+-------------+-----------------------+
| Host | User | authentication_string |
+-----------+-------------+-----------------------+
| localhost | mariadb.sys | |
| localhost | root | invalid |
| localhost | mysql | invalid |
+-----------+-------------+-----------------------+
3 rows in set (0.001 sec)

因为我这是一个刚刚初始化的数据库,内部只有系统自带的几个用户。当我们使用 mysql -uroot 链接数据库时,其实就是使用了 root 用户。默认情况下 root 用户没有密码,所以 authentication_string 字段显示 invalid;

sql
1
desc user; -- 查看表的结构

对这张表里的字段做一个简单的解释

plaintext
1
2
3
4
5
host:用户可以从什么主机上链接数据库,localhost代表只允许本地链接
user:用户名
password:旧版本使用的密码字段
authentication_string:新版本使用的密码字段,用户密码用password函数加密后的字符串;
很多很多以_priv结尾的字段:用户权限记录

用户和权限管理的存在,让我们可以对 MySQL 中不同的表进行一定的权限风控,避免某个用户的误操作让整个数据库产生不可逆的损失。

2. 用户管理

2.1 创建用户

在 MySQL 中创建用户的语法如下

sql
1
2
create user '用户名'@'登陆主机或IP'
identified by '密码';

示例如下

plaintext
1
2
MariaDB [(none)]> create user 'mu'@'%' identified by '123456';
Query OK, 0 rows affected (0.006 sec)

这里我使用了 % 作为登录主机,其等价于 IP 层面理解的 0.0.0.0,即该用户允许任何来源地的链接。

再次查询用户表,可以看到此时 mu 用户是有 authentication_string 字段的,其值是通过 password 函数加密后的 123456;

plaintext
1
2
3
4
5
6
7
8
9
10
MariaDB [mysql]> select host,user,authentication_string from user;
+-----------+-------------+-------------------------------------------+
| Host | User | authentication_string |
+-----------+-------------+-------------------------------------------+
| localhost | mariadb.sys | |
| localhost | root | invalid |
| localhost | mysql | invalid |
| % | mu | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------+-------------------------------------------+
4 rows in set (0.001 sec)

我们也可以自行调用 password 函数,可以看到输出值和上表中的值一致。

plaintext
1
2
3
4
5
6
7
MariaDB [mysql]> select password('123456');
+-------------------------------------------+
| password('123456') |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set (0.000 sec)

此时想用这个新用户进行登录,在 linux 的命令行中输入如下命令进行登录

plaintext
1
mysql -u用户名 -p密码

如果你现在处于 Linux 的子用户,登录 MySQL 的 root 用户需要 sudo 权限,但登录新创建的 MySQL 用户是不需要 sudo 的。

示例如下,登录 MySQL 后可以用 select user(); 语句查看当前登录的用户是谁。

plaintext
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
❯ mysql -umu -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 10.6.16-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> select user();
+--------------+
| user() |
+--------------+
| mu@localhost |
+--------------+
1 row in set (0.000 sec)

2.2 删除用户

只有高权限用户或者 root 用户才能删除其他用户。

sql
1
drop user '用户名'@'主机名';

如果在传入用户名时不提供主机名,则主机名默认为 %

sql
1
2
3
drop user '用户名';
-- 等价于
drop user '用户名'@'%';

示例如下

plaintext
1
2
MariaDB [mysql]> drop user 'mu'@'192.168.1.30';
Query OK, 0 rows affected (0.006 sec)

为什么在删除用户的时候需要指定主机名?

为什么在删除用户的时候需要指定主机名?因为同一个用户名,不同主机名是可以创建多个的,MySQL 并没有限制一个用户名只能创建一条记录。

比如我们可以允许张三在 IP1 和 IP2 登录,这样就能创建两条用户记录,这两条用户记录的用户名都是张三,但可以有不同的登录来源 host 和不同的密码。这样我们可以控制单个用户在不同主机上的不同权限。

plaintext
1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [mysql]> create user 'mu'@'192.168.1.30' identified by '123456';
Query OK, 0 rows affected (0.006 sec)

MariaDB [mysql]> select host,user,authentication_string from user;
+--------------+-------------+-------------------------------------------+
| Host | User | authentication_string |
+--------------+-------------+-------------------------------------------+
| localhost | mariadb.sys | |
| localhost | root | invalid |
| localhost | mysql | invalid |
| % | mu | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 192.168.1.30 | mu | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+--------------+-------------+-------------------------------------------+
5 rows in set (0.001 sec)

2.3 设置用户密码

设置当前用户的密码

sql
1
set password=password('新密码');

修改特定用户的密码

sql
1
set password for '用户名'@'主机名'=password('新密码');

示例如下

plaintext
1
2
3
4
5
6
7
8
9
10
11
12
13
MariaDB [mysql]> set password for 'mu'@'%'=password('654321');
Query OK, 0 rows affected (0.006 sec)

MariaDB [mysql]> select host,user,authentication_string from user;
+-----------+-------------+-------------------------------------------+
| Host | User | authentication_string |
+-----------+-------------+-------------------------------------------+
| localhost | mariadb.sys | |
| localhost | root | invalid |
| localhost | mysql | invalid |
| % | mu | *2A032F7C5BA932872F0F045E0CF6B53CF702F2C5 |
+-----------+-------------+-------------------------------------------+
4 rows in set (0.001 sec)

2.4 命令汇总小结

在我的实际使用中,最常用的用户管理就是在配置需要使用 MySQL 数据库的 docker 时的操作了。需要给一个新的 docker 设置新的数据库,并创建一个用户给这个 docker 来操作。

sql
1
2
3
4
5
6
7
8
9
10
11
-- 如果是docker安装的MySQL
-- 需要先在Linux系统中进入MySQL容器的终端里面
docker exec -it 容器名 /bin/sh
-- 在终端命令行中执行如下命令
mysql -uroot -p密码
-- 进入MySQL命令行后,创建数据库和用户
create database 数据库名字;
-- 数据库名字要和上面创建的这个数据库对应
-- 给定新表的所有权限到新用户上
CREATE USER '用户名'@'%' IDENTIFIED BY '用户的密码';
GRANT ALL ON 数据库名字.* TO '用户名'@'%';

3. 权限管理

3.1 权限列表

MySQL 数据库提供的权限非常细致,列表如下

image-20240130103510001

3.2 给用户授权

授权的语法

sql
1
2
grant 权限列表 on 数据库.对象名 
to '用户名'@'主机' [identified by '密码']

如果需要授予多个权限,用逗号分隔。

sql
1
2
grant select,delete on 数据库.对象名 
to '用户名'@'主机' [identified by '密码']

如果想授予所有权限,可以使用 all,或者 all privileges,二者都代表所有权限,没有区别。

sql
1
grant all [privileges] on ... 

几点说明

  • 命令中的 [identified by '密码'] 是可选项,如果用户存在,则修改其密码。如果用户不存在,则以该密码创建用户。
  • 数据库.对象名可使用 *.* ,代表当前 MySQL 中的所有数据对象;
  • 数据库.* 代表某个数据库中的所有对象;

授权示例

刚创建的用户是没有任何权限的,如下所示,我刚刚创建的 mu 用户并没有看到原本已有的 stu_test 数据库。

plaintext
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> select user();
+--------------+
| user() |
+--------------+
| mu@localhost |
+--------------+
1 row in set (0.000 sec)

使用如下命令给用户授权

sql
1
grant select on stu_test.student to 'mu'@'%';

授权之后,mu 用户就能看到这个表了

plaintext
1
2
3
4
5
6
7
8
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| stu_test |
+--------------------+
2 rows in set (0.001 sec)

mu 用户可以进行查询操作,但是不能删改 student 表,操作会被数据库拒绝。

plaintext
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
MariaDB [(none)]> use stu_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [stu_test]> select * from student;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 赵六 |
| 5 | 刘七 |
+------+--------+
5 rows in set (0.000 sec)

MariaDB [stu_test]> insert into student values (6,'王璐');
ERROR 1142 (42000): INSERT command denied to user 'mu'@'localhost' for table `stu_test`.`student`

再次给予更新和删除的权限

sql
1
grant select,update,delete,insert on stu_test.student to 'mu'@'%';

此时 mu 用户就能进行插入和删除等操作了

plaintext
1
2
3
4
5
6
MariaDB [stu_test]> insert into student values (6,'王璐');
Query OK, 1 row affected (0.006 sec)

MariaDB [stu_test]> delete from student where id = 5;
Query OK, 1 row affected (0.007 sec)

刷新权限

如果发现赋予权限后用户还是不能执行某些操作,可以用如下命令刷新权限

sql
1
flush privileges;

3.3 回收权限

语法

sql
1
revoke 权限列表 on 数据库.对象名 from '用户名'@'主机';

比如回收 mu 用户对于 student 表的插入权限

sql
1
revoke insert on stu_test.student from 'mu'@'%';

此时 mu 用户又不能进行插入操作了

plaintext
1
2
MariaDB [stu_test]> insert into student values (7,'你好');
ERROR 1142 (42000): INSERT command denied to user 'mu'@'localhost' for table `stu_test`.`student`

The end

MySQL 中用户管理的基本操作就是这些了。在实际项目中,用户管理是数据库安全性的重要一环,得多加注意。