Akemi

Mysql用户权限与密码管理、角色管理

mysql
2024/07/22

SQL语句的分类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DDL（Data Definition Language，数据定义语言）
用于定义或修改数据库的结构，包括创建、修改、删除数据库、表、索引、视图、存储过程等。
create drop alter tuncate

DML（Data Manipulation Language，数据操纵语言）
用于对数据库中的数据进行增、删、改操作
insert update delete merge

DQL（Data Query Language，数据查询语言）
用于从数据库中检索数据，它不会修改数据库中的数据
SELECT

DCL（Data Control Language，数据控制语言）
用于定义数据库、表、字段的访问权限和安全级别
GRANT（授权）、REVOKE（撤销权限）、DENY（拒绝）

TCL（Transaction Control Language，事务控制语言）
COMMIT（提交事务）、ROLLBACK（回滚事务）、SAVEPOINT（保存点）、SET TRANSACTION（事物隔离级别）

用户权限管理的作用和场景

1.限制用户访问表和库
2.限制用户对表执行某些sql操作
3.限制用户登录IP和域名
4.限制用户是否有授权的权限
用户分类
普通用户、ROOT用户

mysql存储权限的表

user、db、tables_prvi、columns_prvi、proxies_prvi、procs_priv

user表

user表分为四列：用户列、权限列、安全列、资源控制列
use mysql;
select * from user;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
Host: 允许用户连接的主机名或IP地址，可以使用通配符（如'%'表示任何主机）。  
User: 用户名。  

# 权限相关  
Select_priv: 是否拥有SELECT权限。  
Insert_priv: 是否拥有INSERT权限。  
Update_priv: 是否拥有UPDATE权限。  
Delete_priv: 是否拥有DELETE权限。  
Create_priv: 是否拥有CREATE权限（用于创建表、索引等）。  
Drop_priv: 是否拥有DROP权限（用于删除表、视图等）。  
Reload_priv: 是否拥有重新加载权限（如FLUSH PRIVILEGES, FLUSH TABLES等）。  
Shutdown_priv: 是否拥有关闭MySQL服务器的权限。  
Process_priv: 是否可以查看当前MySQL中的进程信息（SHOW PROCESSLIST）。  
File_priv: 是否拥有读写服务器上文件的权限（如SELECT ... INTO OUTFILE, LOAD DATA INFILE）。  
Grant_priv: 是否可以授予或移除其他用户的权限。  
References_priv: 是否拥有外键权限（在MySQL 5.7中可能不直接作为列出现，因为外键权限通常与其他权限一起处理）。  
Index_priv: 是否拥有创建或删除索引的权限。  
Alter_priv: 是否拥有ALTER TABLE权限（用于修改表结构）。  
Show_db_priv: 是否可以查看服务器上所有数据库的权限（SHOW DATABASES）。  
Super_priv: 是否拥有超级用户权限（可以执行大多数管理操作）。  
Create_tmp_table_priv: 是否可以创建临时表。  
Lock_tables_priv: 是否可以锁定表（使用LOCK TABLES语句）。  
Execute_priv: 是否可以执行存储过程和函数。  
Repl_slave_priv: 是否可以作为复制从服务器的权限（用于设置复制从服务器）。  
Repl_client_priv: 是否可以查询主服务器或从服务器的位置（SHOW SLAVE STATUS, SHOW MASTER STATUS等）。  
Create_view_priv: 是否可以创建视图。  
Show_view_priv: 是否可以查看视图（SHOW CREATE VIEW）。  
Create_routine_priv: 是否可以创建存储过程和函数。  
Alter_routine_priv: 是否可以修改或删除存储过程和函数。  
Create_user_priv: 是否可以创建新用户（在MySQL 5.7中，这通常通过GRANT语句实现，而不是直接作为列出现）。  
Event_priv: 是否可以创建、修改、删除事件（MySQL 5.1.6及以上版本支持）。  
Trigger_priv: 是否可以创建或删除触发器。  

# 认证和账户管理相关  
ssl_type: SSL/TLS加密类型（如果启用了SSL/TLS）。  
ssl_cipher: SSL/TLS加密算法（如果启用了SSL/TLS）。  
x509_issuer: X.509证书颁发者（如果使用了X.509证书）。  
x509_subject: X.509证书主题（如果使用了X.509证书）。  
max_questions: 用户每小时可以执行的查询数量（0表示无限制）。  
max_updates: 用户每小时可以执行的更新操作数量（0表示无限制）。  
max_connections: 用户每小时可以建立的连接数（0表示无限制）。  
max_user_connections: 用户可以同时建立的连接数（0表示使用全局max_user_connections限制）。  
plugin: 认证插件的名称，用于用户认证（如mysql_native_password, caching_sha2_password等）。  
authentication_string: 用户的密码加密字符串（在MySQL 5.7及更新版本中替代了旧版本的password列）。  

# MySQL 5.7新增或修改的  
password_expired: 密码是否已过期（MySQL 5.6.6及以上版本引入）。  
password_last_changed: 密码最后一次更改的时间（MySQL 5.6.6及以上版本引入）。  
password_lifetime: 密码的生存周期（天数），过期后需要更改密码（MySQL 5.7.6及以上版本引入）。  
account_locked: 账户是否被锁定（MySQL 8.0.11及以上版本引入，但在5.7中可能通过其他方式实现账户锁定）。

db表

存取用户对某个库的操作权限，决定了用户能从哪个主机访问哪个数据库
select * from mysql.db;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Host: 允许用户连接的主机名或IP地址，可以使用通配符（如'%'表示任何主机）。  
Db: 数据库名，对于全局权限，此列的值可能是空字符串（''）或者一个特殊值（如'mysql'表示该权限适用于`mysql`系统数据库）。  
User: 用户名。  
  
# 权限相关  
Select_priv: 是否拥有SELECT权限。  
Insert_priv: 是否拥有INSERT权限。  
Update_priv: 是否拥有UPDATE权限。  
Delete_priv: 是否拥有DELETE权限。  
Create_priv: 是否拥有CREATE权限（用于在数据库中创建表、索引等）。  
Drop_priv: 是否拥有DROP权限（用于删除数据库中的表、视图等）。  
Grant_priv: 是否可以授予或移除其他用户的权限（仅在该用户对数据库有GRANT OPTION时有效）。  
References_priv: 是否拥有外键权限（在MySQL中，这通常与其他权限一起处理，但在此表中作为单独的列存在）。  
Index_priv: 是否拥有创建或删除索引的权限。  
Alter_priv: 是否拥有ALTER TABLE权限（用于修改表结构）。  
Create_tmp_table_priv: 是否可以在该数据库中创建临时表。  
Lock_tables_priv: 是否可以锁定该数据库中的表。  
Execute_priv: 是否可以执行该数据库中的存储过程和函数（注意：这与全局的Execute_priv不同，它指的是数据库级别的执行权限）。  
Create_view_priv: 是否可以创建视图。  
Show_view_priv: 是否可以查看视图（SHOW CREATE VIEW）。  
Create_routine_priv: 是否可以创建存储过程和函数。  
Alter_routine_priv: 是否可以修改或删除存储过程和函数。  
Event_priv: 是否可以创建、修改、删除该数据库中的事件。  
Trigger_priv: 是否可以创建或删除该数据库中的触发器。

tables_priv表

设置对某一表的操作权限
select * from mysql.tables_priv;

columns_priv表

用来对表的某一列设置操作权限
select * from mysql.columns_priv;
默认是空的，需要手动添加

procs_priv表

用来对存储过程和存储函数设置操作权限
select * from mysql.procs_priv;
默认是空的，需要手动添加

proxies_priv表

select * from mysql.proxies_priv;

用户权限管理操作

权限授予

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
mysql -u root -p
#查看帮助
help account management;
topics:
   ALTER USER
   CREATE USER
   DROP USER
   GRANT
   RENAME USER
   REVOKE
   SET PASSWORD

#创建用户
CREATE USER 'username'@'host' IDENTIFIED BY '密码';
可选项：
加密套件
CREATE USER 'username'@'host' IDENTIFIED WITH '加密套件' BY '密码';
加密套件有mysql_native_password、caching_sha2_password

设置密码过期时间
WITH PASSWORD EXPIRE INTERVAL 90 DAY;

设置账户锁定
WITH ACCOUNT LOCK;

例：
CREATE USER 'xhy'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'Admin@123!' WITH PASSWORD EXPIRE INTERVAL 90 DAY,WITH ACCOUNT LOCK;

#删除用户
DROP USER 'user'@'localhost'
可选项：
删除多个
DROP USER 'user1'@'localhost', 'user2'@'localhost';

避免错误，使用IF EXISTS
DROP USER IF EXISTS 'username'@'localhost';

#重命名用户
rename user 'newwangsheng'@'%' to 'wangsheng'@'%';


#用户授权
格式：
GRANT 权限类型 ON 数据库名.表名 TO '用户名'@'登录主机' [IDENTIFIED BY '密码' [WITH GRANT OPTION]];
- 权限类型：可以是单个权限（如SELECT、INSERT、UPDATE、DELETE等）或多个权限的组合，甚至可以是ALL PRIVILEGES表示所有权限

例：
授予用户所有权限
GRANT ALL PRIVILEGES ON *.* TO '用户名'@'%';  

授予用户对特定数据库中的特定表的权限
GRANT SELECT, INSERT, UPDATE ON 数据库名.表名.列名 TO '用户名'@'%';

授予用户可以进行授权操作的权限
WITH GRANT OPTION

#权限刷新
flush privileges;

#授权总结案例
host可选：%,localhost,127.0.0.1,::1

1.授权普通用户具有查询插入更新删除wsdb的所有数据表的权限
grant select,insert,update,delete on wsdb.* to wangsheng@'%';
flush privileges;
show grants for wangshen@'%';

2.开发人员授权具有创建索引、视图、表、存储过程等的权限
create user dev@'192.168.10.%' identified by 'Admin@123!';
grant create,alter,drop on wsdb1.* to dev@'%';
flush privileges;
show grants for dev@'%';

3.操作mysql外键的权限
grant references on wsdb.* to dev@'%';
4.创建mysql临时表的权限
grant create temportary tables on wsdb.* to dev@'%';
5.操作mysql索引的权限
grant index on wsdb.* to dev@'%';
6.操作视图的权限
grant create view,show view on wsdb.* to dev@'%';
7.操作存储过程和函数的权限
grant create routine,alter routine,execute on wsdb.* to dev@'%';
8.授权普通用户操作某个数据库
grant all privileges on wsdb.* to dev@'%';
9.授权高级用户管理所有数据库
grant all privileges *.* to dev@'%';
10.授权针对单个表
grant select on wsdb.table01 to dev@'%';
11.授权针对单个列
grant select(列名1,列名2) on wsdb.table01 to dev@'%';
12.授权针对函数存储过程和函数
grant execute on procedure 库名.过程名 to dev@'%';
grant execute on function 库名.函数名 to dev@'%';

权限回收

1
2
3
4
5
语法
revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置'；

和grant类似，只需要将to改为from就行
revoke

用户密码管理

修改用户密码
1.修改root密码
(1)mysqladmin 修改密码
mysqladmin -u -root -h localhost -p password "newpasswd";
(2)修改mysql.user表
user mysql;
update mysql.user set authentication_string=PASSWORD('newpasswd') where user='root';
flush privileges;
(3)使用set语句
set password=PASSWORD('newpasswd');

2.修改普通用户密码
(1)修改mysql.user表
user mysql;
update mysql.user set authentication_string=PASSWORD('newpasswd') where user='wangsheng';
(2)使用grant语句
grant usage on *.* to 'wsdb'@'%' identified by 'newpasswd';
(3)当前用户修改自己的密码方法1
set password=PASSWORD('newpasswd');
(4)当前用户修改自己的密码方法2
alter user 'wsdb'@'%' identified by 'newpasswd'

用户过期问题
相关参数：
show variables like 'default_password_lifetime';
修改默认策略：
(1)my.cnf参数
[mysqld]
default_passwod_lifetime=0

(2)使用alter
默认90天
alter user 'wangsheng'@'localhost' password expire interval 90 day;
默认没有
alter user 'wangsheng'@'localhost' password expire interval never;
默认使用my.cnf的默认设置
alter user 'wangsheng'@'localhost' password expire interval default;

root用户密码丢失问题
1.windows系统
加入参数到my.ini
--skip-grant-tables选项启动数据库
update mysql.user set authentication_string=PASSWORD('newpasswd') where user='root';
flush privileges;
修改完成后移除参数

2.linux系统
与win类似

mysql免密登录问题
方法1：
直接在my.cnf中添加client选项

1
2
3
[client]
user="root"
password="Admin@123!"

方法2：
针对不同客户端

1
2
3
4
5
6
7
[mysql]
user="root"
password="Admin@123!"

[mysqladmin]
user="root"
password="Admin@123!"

方法3：
使用环境变量MYSQL_PWD
export MYSQL_PWD=wangsheng

方法4：
使用login-path（最安全）
mysql_config_editor print --all
mysql_config_editor set --login-path=wangsheng --user=root --password
输入密码

1
2
3
4
5
6
7
mysql_config_editor print --all
[wangsheng]
user = root
password = wangsheng

登录方式
mysql --login-path=wangsheng

清除login-path
mysql_config_editor remove --login-path=wangsheng

角色管理RBAC

mysql 5.7中角色管理使用的是proxies_priv
角色是一种用来批量管理用户的方法，相同角色的用户具有相同权限

my.cnf相关参数
check_proxy_users=on
mysql_native_password_porxy_users=on

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
show variables like '%proxy%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| check_proxy_users                 | OFF   |
| mysql_native_password_proxy_users | OFF   |
| proxy_user                        |       |
| sha256_password_proxy_users       | OFF   |
+-----------------------------------+-------+

set global check_proxy_users=on;
set global mysql_native_password_proxy_users=on;

create user 'wangsheng_dba';
将wangsheng_dba作为初始的角色，起到角色的作用
create user 'wangsheng01';
create user 'wangsheng02';

将角色的权限授权给子用户
grant proxy on wangsheng_dba to wangsheng01;
grant proxy on wangsheng_dba to wangsheng02;

给初始的角色添加权限
grant select on *.* to wangsheng_dba;
flush privileges;

取消代理权限
revoke proxy on wangsheng_dba from wangsheng01;

原文作者：王盛

原文链接：https://akemi.zj.cn/2024/07/22/MySQL-Auth/

发表日期：July 22nd 2024, 1:35:12 pm

更新日期：February 20th 2025, 6:37:27 pm

版权声明：本文采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可

CATALOG
  1. 1. SQL语句的分类
  2. 2. 用户权限管理的作用和场景
  3. 3. mysql存储权限的表
    1. 3.1. user表
    2. 3.2. db表
    3. 3.3. tables_priv表
    4. 3.4. columns_priv表
    5. 3.5. procs_priv表
    6. 3.6. proxies_priv表
  4. 4. 用户权限管理操作
  5. 5. 用户密码管理
  6. 6. 角色管理RBAC
Total : 203
2025
2024
2023
Linux 云原生 云计算 开发 mysql