【MySQL】用户与权限管理

一、用户管理

之前为了方便，我们学习 MySQL 时统一使用的都是 root 账号进行登录，但在实际的开发场景中必然是需要进行用户管理的，即不同层级/职能的人使用不同的账号进行登录。

这样做的最大目的是保证数据库安全 – 普通用户只具有对部分数据库中的部分表进行部分操作的权限，只有 root 管理员才具有对所有数据库进行操作的能力，从而避免普通用户对数据库的误操作。

1、用户信息表

MySQL 中的用户信息本质上也是数据，所以其也是需要被保存在数据库当中的。在 MySQL 中，用户信息被保存在 mysql 数据库的 user 表中。

image-20231022220535813

可以看到，user 表中有很多的列属性，其中最重要的列属性有：

• Host：用户进行登录的主机。其中 localhost 或 127.0.0.1 代表本地环回，即只能从 mysqld 服务所在的机器进行登录 (本地登录)。
• User：用户名。
• authentication_string：用于验证用户身份的字符串。它是由用户名、密码和可选的盐值（salt）组成的字符串，用于生成一个加密的哈希值。当用户登录时，mysqld 会将用户输入的密码以同样的方式进行加密，然后与 user 表中存储的 authentication_string 进行比对，以此来验证用户身份。我们简单理解为加密的用户密码即可。
• 注意：上面以 _priv 结尾的属性代表的都是用户的权限。

我们可以 select user 表来查看当前数据库中都有哪些用户：

image-20231022221534840

2、创建用户

既然我们已经知道了用户信息是保存在 mysql 数据库下的 user 表中的，那么创建用户本质上就是向 user 表中插入数据；即我们可以直接通过修改 user 表来创建用户，但由于 user 表的列属性实在是太多了，所以我们不建议这样做。

除了直接修改 user 表，我们还可以通过如下语法来添加用户：

create user '用户名'@'登录主机/ip' identified by '用户密码';

比如，我们要在数据库中插入 ‘thj’ 这个用户，登录主机为本地登录，登录密码为 ‘123456’：

create user 'thj'@'localhost' identified by '123456';

但是，当我们执行上面这条语句时，我们发现执行出错了，这是由于之前为了方便我们在 my.cnf 中配置了 mysql 的免密登录，所以这里设置密码时会报错。

image-20231022222848916

所以我们需要将 /etc/my.cnf 文件中的 skip-grant-tables 选项注释或删除掉，然后 restart mysqld 即可。但在这之前我们还需要 update 我们 root 账号的密码，否则我们后面就会因为不知道密码而无法登录 mysql 了。

image-20231022223426073

image-20231022223506871

image-20231022223619942

注意：

• password 是 MySQL 中用于将用户密码转化为 authentication_string 的一个函数，即密码加密函数。
• MySQL和 Linux 一样，在输入密码时密码并不会回显。

但是当我们成功登录 mysql 之后，这里出现了一个新问题 – 不管我们执行什么语句 mysql 都报错并提示我们重设密码：

image-20231022225132200

关于这个问题，我在网上找了一篇博客，大家感兴趣可以看看：https://blog.csdn.net/muziljx/article/details/81541896

简单来说就是执行如下语句之一 (密码以123456为例)：

SET PASSWORD = PASSWORD('123456'); -- MySQL5.7.6之前的版本
ALTER USER USER() IDENTIFIED BY '123456'; -- MySQL5.7.6及以后的版本

但是我们执行完毕后 mysqld 又报错说密码不符合要求：

image-20231022225734270

这是由于我们设置的密码过于简单，而 mysql 本身的认证等级比较高，一些简单的密码并不能设置；要解决这个问题很简单，将密码设复杂一点或者降低密码设置的验证强度等级即可。

这里我们由于 reset password 和 password not satisfy 两个报错撞到一起了，所以我们只能选择第一种方案。 MySQL 密码设置的默认安全规则是要求密码必须包含大小写字母、数字和特殊符号，并且长度不能少于8位。

image-20231022231117397

我们可以通过如下语句来查看 MySQL 密码设置相关要求：

show variables like 'validate_password%';

image-20231022231347152

现在，我们终于可以正常的进行用户创建了，不过需要修改一下密码，使其符合要求：

create user 'thj'@'localhost' identified by '1234Abcd@';

image-20231022232922170

然后我们也可以使用新创建的用户进行登录 mysql：

image-20231022233150549

我们也可以尝试创建允许从特定主机登录以及从任意主机登录的用户：

create user 'user1'@'10.17.40.69' identified by '1234Abcd@'; -- 我电脑的IP
create user 'user2'@'%' identified by '1234Abcd@'; -- %表示允许从任意主机进行登录

image-20231022234011086

实际上当同学们使用 user1 和 user2 这两个账号去登录的时候会发现，只有 user2 能够成功登录，而 user1 会提示 ‘Access denied’，这主要有两个原因：

1. 我们主机的 IP 地址是某一个网段下随机分配的一个 IP，并不是固定的，所以可能你创建用户时你电脑 ifconfig 得到的是 10.17.40.69 这个IP，而在登录 mysql 时你电脑的 IP 地址已经变了。
2. 我们用的 IP 都是私网 IP，即运营商路由器分配的网络 IP，它需要经过多次的NAT地址转换，转换为公网 IP 后才能向网络中发送数据，而转换后得到的公网 IP 具体是多少我们并不知道。

所以，在云服务器这里，如果我们想要创建能够在别的主机下登录 mysql 的用户，就只能将 Host 定位为 %，即允许从任意主机登录。但是在企业中，为了保证数据库安全，MySQL 是绝对不允许从任意主机进行登录的，要远程登录也只能是从特定主机登录 (拥有公网 IP 的主机)；同时，mysqld 对应的端口号一般也不会暴露在公网中，而只会在公司内部私网环境中使用。

3、删除用户

删除用户本质上也是删除 user 表中的数据，所以我们可以直接在 root 用户下删除 user 表中的用户：

image-20231023101356254

当然，我们也可以使用下面的 sql 语句来删除：

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

image-20231023101730582

需要注意的是，当我们使用 drop user 语句来删除用户时，必须是 ‘用户名’@‘主机名’，而不能仅仅是 用户名；也就是说，mysql 是通过用户名 + 主机名 一起来标定唯一用户的。

image-20231023102128165

4、修改用户密码

同样，我们可以使用 update 语句直接修改 user 表中的数据来修改密码：

update user set authentication_string = password('Abcd1234@') where user = 'thj';

image-20231023102420064

此外，如果我们想要在自己的 mysql 账户下修改自己的密码：

set password = password('新的密码');

最后，管理员也可以在 root 账号下修改所有用户的密码：

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

image-20231023103006948

二、权限管理

1、MySQL 权限

MySQL 权限管理主要用来验证用户的操作权限，确保每个用户在数据库中的操作都在其权限范围内。主要包括以下几个方面：

• 全局权限：全局权限是对整个 MySQL 服务器的控制，包括创建和删除数据库、创建和删除表等。
• 数据库权限：数据库权限是对特定数据库的控制，包括对数据库中的表进行操作（如查询、插入、修改和删除）等。
• 表权限：表权限是对特定数据表的控制，包括对表中的数据进行操作（如查询、插入、修改和删除）等。
• 字段权限：字段权限是对特定数据表中的某一列或多列的控制，包括对字段中的数据进行操作（如查询、插入、修改和删除）等。

MySQL 中的一些基本权限如下：

• create、drop、alter 表示对数据库或表结构进行操作的权限。
• insert、update、select、delete 表示对表数据进行操作的权限。
• index 表示表的索引权限。
• all [privileges] 表示全局或者全数据库对象级别的所有权限。

关于 MySQL 中更加详细的权限信息，大家可以参考下面这篇文章：https://www.cnblogs.com/xulinforDB/articles/13753838.html

2、给用户授权

在 MySQL 中，新创建的用户没有任何权限，需要我们手动给用户授权。

image-20231023111650807

MySQL 中给用户授权的语法如下：

grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码']

说明：

• 权限列表中，多个权限使用逗号分开。其中 all [privileges] 表示赋予该用户在该对象上的所有权限。
• 库.对象名可以使用通配符 *。比如 mysql.* 代表 mysql 数据库中的所有对象， *.* 代表所有数据库中的所有对象 (表，视图，存储过程等)。
• identified by 是可选项，如果用户存在，赋予权限的同时修改密码；如果该用户不存在，就是创建用户。但我们一般不建议在这里创建用户或修改密码。

比如我们可以使用前面 scott 数据库中的 emp 表，在 root 账号赋予 ‘thj’@‘localhost’ 这个用户对 emp 表进行数据查找和更新的权限，但不赋予其插入、删除等其他权限：

grant select, update on scott.emp to 'thj'@'localhost';

image-20231023160533276

image-20231023155147653

image-20231023155541588

注意：如果赋权后发现权限并没有生效，可以刷新权限：

flush privileges;

3、回收用户权限

回收权限和授予权限的语法基本一样：

revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置';

比如，我们可以回收 ‘thj’@‘localhost’ 这个用户在 scott 数据库的 emp 表中的所有权限：

revoke all on scott.emp from 'thj'@'localhost';

image-20231023160447608

image-20231023160410391