用户与权限管理

# 275.用户与权限管理

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

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

‍

# 用户管理

‍

# 查看当前用户

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

1
2
3
4
5
6
7

‍

# 查看所有用户

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

1
2
3
4
5
6
7
8
9

‍

# 创建用户

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

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

1
2

字段说明：

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';

1
2
3

‍

# 删除用户

语法：

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

‍

# 修改用户名

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


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

1
2
3
4
5
6

‍

# 修改密码

语法：

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

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

1
2
3
4

‍

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 等权限比较高的用户；

‍

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

密码分成两半，保存在安全部门中。

如果要使用，需要两人申请密码；

输入密码时，一人输入一半（不能明文输入）

定期修改密码

‍

# 刷新权限

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

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.

1
2
3
4

‍

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

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

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

‍

# 解除禁用

有如下方法：

到期自动解除
让管理员执行 unlock 命令，alter user pig2@'%' account unlock;
执行 FLUSH PRIVILEGES，刷新用户权限数据
管理员重新更改 failed_login_attempts 或者 password_lock_time 选项，FLTTAL 计数器重置。
重启 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'

1
2
3
4
5
6

‍

此时就得授权。语法：

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 中所有的数据库相关。例如，可以删除已有的数据库或者创建一个新的数据库的权限。

‍

这里列出部分权限，更多可以参考官方文档 (opens new window)：

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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

‍

# 回收权限

-- 语法：
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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

‍

# 参考

mysql grant 用户权限总结_mysqlgrant-CSDN 博客 (opens new window)

新特性解读 | MySQL 8.0 新密码策略（终篇） - 知乎 (opens new window)

‍

上次更新: 2024/12/4 21:15:24

← 命令行的一些用法 MySQL 的权限管理→