275.用户与权限管理

用户与权限管理主要有以下作用：

1. 对用户进行增删改查
2. 对用户进行密码管理
3. 限制用户登录的 IP 或域名
4. 限制用户访问哪些库、哪些表
5. 限制用户对哪些表执行 SELECT、CREATE、DELETE、DELETE、ALTER 等操作
6. 限制用户自己的权限是否可以授权给别的用户

‍ ‍

用户管理

‍

查看当前用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

‍

查看所有用户

select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

‍

创建用户

在对 MySQL 的日常管理和操作中，为了避免有人恶意使用 root 用户控制数据库，我们通常创建一些具有适当权限的用户，尽可能地不用或少用 root 用户登录系统，以此来确保数据的安全访问。

-- 命令格式：
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

字段说明：

• username：用户名
• host：指定该用户在哪个主机（IP 或域名）上可以登录。如果是本地用户可用 localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符 %
• password：登录密码 ‍ 举例：

CREATE USER 'dog'@'localhost' IDENTIFIED BY 'Aa123456';
CREATE USER 'pig'@'192.168.1.101' IDENDIFIED BY 'Aa123456';
CREATE USER 'pig'@'%' IDENTIFIED BY 'Aa123456';

‍

删除用户

语法：

drop user '用户名'@'IP 地址';

‍

修改用户名

--语法：
rename user '用户名'@'IP 地址' to '新用户名'@'IP 地址';


-- 例如：
rename user 'pig'@'%' to 'pig2'@'%';

修改密码

语法：

alter user '用户名'@'IP 地址' identified by '新密码'; 

-- 例如：
alter user 'pig2'@'%' identified by 'Aa123456';

‍ ps：在 MySQL5 版本中，还有如下方式修改密码

• ​set password for 用户名@localhost = password('新密码');​
• update user 表，例如：update user set password=password('Aa12345') where user='root' and host='localhost'; ​

但其实不推荐这样做，首先这两个命令在 MySQL8 不能用，而 alter 语句在 MySQL 5.7 开始就能用了；

其次，update user 表需要的权限比较高，而在工作中为了安全，我们通常是不能使用 root 等权限比较高的用户； ‍

这里说个题外话：工作中是如何管理数据库密码的

1. 密码分成两半，保存在安全部门中。
2. 如果要使用，需要两人申请密码；
3. 输入密码时，一人输入一半（不能明文输入）
4. 定期修改密码

刷新权限

在修改完用户密码后，需要刷新权限，以使修改生效：

FLUSH PRIVILEGES;

‍

限制登录次数

随着 MySQL 数据库被越来越多的金融场景使用，类似连续输错银行卡密码而导致的锁卡功能呼之欲出。

MySQL 从 8.0.19 开始，就推出了类似策略：Failed-Login Tracking and Temporary Account Locking 。 翻译过来就是 失败登录追踪和临时密码锁定，后面我们简称为：FLTTAL 。 ‍ 举例：

alter user 'pig2'@'%' failed_login_attempts 3 password_lock_time 3;

说明：

• failed_login_attempts N：N 代表输错次数。输错 N 次则禁用。
• password_lock_time N：代表禁用天数
• failed_login_attempts 和 password_lock_time 必须同时不为 0 ，FLTTAL 才能生效。（反之，如果想要取消用户的登录限制，则设置为 0）
• 被禁用后，即使输入正确密码也无法登录。
• 任意一次成功登录，FLTTAL 计数器重置。例如 failed_login_attempts 设置为 3 ，前两次密码连续输错，第三次输入正确的密码，FLTTAL 计数器会重置。 ‍ 测试：

mysql -upig2 -p111 -P3307
mysql -upig2 -p111 -P3307
mysql -upig2 -p111 -P3307
ERROR 3955 (HY000): Access denied for user 'pig2'@'localhost'. Account is blocked for 3 day(s) (3 day(s) remaining) due to 3 consecutive failed logins.

‍ PS：也可以在创建用户的时候，就指定登录次数：

create user 'pig'@'%' identified by 'Aa123456' failed_login_attempts 3 password_lock_time 3;

如果创建新用户不指定 failed_login_attempts 和 password_lock_time ，则默认关闭 FLTTAL 。 ‍

解除禁用

有如下方法：

1. 到期自动解除
2. 让管理员执行 unlock 命令，alter user pig2@'%' account unlock;​
3. 执行 FLUSH PRIVILEGES，刷新用户权限数据
4. 管理员重新更改 failed_login_attempts 或者 password_lock_time 选项，FLTTAL 计数器重置。
5. 重启 MySQL ‍

DBA

在数据库领域中，还有一种称为 DBA 的角色。DBA 全称 Database Administrator，数据库管理员，通常由运维人员负责。

开发人员平时没有 root 权限，只有一个普通用户的权限，只对某个数据库有权限。 ‍

权限管理

‍

授权

默认情况下新用户是没有任何操作权限的：

mysql> use shop;
ERROR 1044 (42000): Access denied for user 'pig'@'%' to database 'shop'

mysql> use mysql;
ERROR 1044 (42000): Access denied for user 'pig'@'%' to database 'mysql'

‍ 此时就得授权。语法：

grant 权限 列名 on 数据库名.表名 to '用户名'@'IP 地址' with grant option;

说明：

• 权限：例如 select 权限，update 权限，多个权限用逗号分割。授权全部权限则使用 all privileges
• 列名：可选项，表示权限作用于哪些列上，省略该参数时，表示作用于整个表；
• 数据库名.表名（或存储过程名）：对哪个数据库的哪张表（或存储过程）有权限，可以用星号 * 表示所有。
• ​to​​：将权限授予哪个用户
• ​with grant option​​：可选项，表示允许用户将自己的权限授权给其它用户
• 授权后，记得刷新下权限 FLUSH PRIVILEGES;​​

权限是可以叠加的，不会覆盖之前授予的权限，比如你给用户添加一个 select 权限，后来又给用户添加了一个 update 权限，那么该用户就同时拥有了 select 和 update 权限。 ‍ 举例：将所有数据库的所有操作权限，授权给 pig2 用户

grant all privileges on *.* to 'pig2'@'%'  with grant option;

‍ MySQL 中可以授予的权限有如下几组：

• 列权限，和表中的一个具体列相关。例如，可以使用 UPDATE 语句更新表 students 中 name 列的值的权限。
• 表权限，和一个具体表中的所有数据相关。例如，可以使用 SELECT 语句查询表 students 的所有数据的权限。
• 数据库权限，和一个具体的数据库中的所有表相关。例如，可以在已有的数据库 mytest 中创建新表的权限。
• 用户权限，和 MySQL 中所有的数据库相关。例如，可以删除已有的数据库或者创建一个新的数据库的权限。 ‍ 这里列出部分权限，更多可以参考官方文档：

Privilege	Grant Table Column	Context
​ALL [PRIVILEGES]​	​Synonym for all privileges​	Server administration
​ALTER​	​Alter_priv​	Tables
​ALTER ROUTINE​	​Alter_routine_priv​	Stored routines
​CREATE​	​Create_priv​	Databases, tables, or indexes
​CREATE ROLE​	​Create_role_priv​	Server administration
.....	.....	......

查看用户权限

show grants for '用户名'@'IP地址';

‍ 例如：

-- 新建一个用户来测试：
create user 'testgrant'@'%' identified by 'Aa123456';

-- 授权：
grant select,create,drop,update,alter on *.* to 'testgrant'@'%' with grant option;

-- 查看权限：
show grants for 'testgrant'@'%';
+---------------------------------------------------------------------------------------+
| Grants for testgrant@%                                                                |
+---------------------------------------------------------------------------------------+
| GRANT SELECT, UPDATE, CREATE, DROP, ALTER ON *.* TO `testgrant`@`%` WITH GRANT OPTION |
+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

‍

回收权限

-- 语法：
revoke 权限名 on *.* from '用户名'@'IP地址';

-- 例如从testgrant用户手里回收create权限
revoke create on *.* from 'testgrant'@'%';



-- 再次查看（如果没生效，则刷新下 flush privileges;）：
show grants for 'testgrant'@'%';
+-------------------------------------------------------------------------------+
| Grants for testgrant@%                                                        |
+-------------------------------------------------------------------------------+
| GRANT SELECT, UPDATE, DROP, ALTER ON *.* TO `testgrant`@`%` WITH GRANT OPTION |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

‍

参考

mysql grant 用户权限总结_mysqlgrant-CSDN 博客

新特性解读 | MySQL 8.0 新密码策略（终篇） - 知乎 ‍