MySQL 用户管理与权限控制及常用场景实践_Mysql

为什么必须做用户管理？

核心痛点：生产环境直接使用 root 用户存在极大的安全隐患。

root 账号拥有 mysql 的最高权限，误操作drop database会直接导致全库数据丢失；
多业务、多人员共用 root 账号，无法做权限隔离和操作审计；
一旦 root 账号泄露，整个 mysql 实例的所有数据都会完全失控。

正确的做法是：按业务、按人员创建独立用户，只分配最小必要权限。 比如张三只能操作 mytest 库，李四只能操作 msg 库，互不影响，风险可控。

mysql 用户的核心存储

mysql 中的所有用户信息，都存储在系统数据库mysql的user表中，这是用户管理的核心，每一行代表一个账户。

-- 查看用户表关键字段
select host, user, authentication_string, 
       select_priv, insert_priv, update_priv, delete_priv,
       create_priv, drop_priv, grant_priv,
       account_locked, password_expired
from mysql.user;

核心字段说明：

host 允许登录的来源地址：localhost（本机）、%（任意主机）、192.168.1.%（指定网段）
user 用户名
authentication_string 加密后的密码
*_priv 各类全局权限标志（y/n）
account_locked 账户是否被锁定（y/n）
password_expired 密码是否已过期（y/n）

注意：mysql 中的用户是 '用户名'@'host' 的组合，'app'@'localhost' 和 'app'@'%' 是两个不同的账户。

实际场景中 host 非常灵活：

localhost：只允许本机登录
% ：允许任意 ip 登录（危险！）
192.168.1.%：允许 192.168.1.0/24 网段登录
192.168.1.100：只允许该固定 ip 登录（最安全）

实战案例：

-- 示例1：只允许本机登录
create user 'app_local'@'localhost' identified by 'p@ssw0rd123';
-- 示例2：允许任意主机登录（生产慎用）
create user 'app_remote'@'%' identified by 'p@ssw0rd123';
-- 示例3：只允许指定 ip 登录
create user 'dba_user'@'192.168.1.100' identified by 'p@ssw0rd123';
-- 示例4：允许指定网段
create user 'report_user'@'10.0.0.%' identified by 'p@ssw0rd123';

mysql 库中权限相关的表

┌────────────────────┬─────────────────────┬────────────────────────────────┐
│        表名        │      存储内容       │            对应操作            │
├────────────────────┼─────────────────────┼────────────────────────────────┤
│ mysql.user         │ 用户账号 + 全局权限 │ create user / grant all        │
├────────────────────┼─────────────────────┼────────────────────────────────┤
│ mysql.db           │ 数据库级别权限      │ grant ... on db.*              │
├────────────────────┼─────────────────────┼────────────────────────────────┤
│ mysql.tables_priv  │ 表级别权限          │ grant ... on db.table          │
├────────────────────┼─────────────────────┼────────────────────────────────┤
│ mysql.columns_priv │ 列级别权限          │ grant select(col) on ...       │
├────────────────────┼─────────────────────┼────────────────────────────────┤
│ mysql.procs_priv   │ 存储过程/函数权限   │ grant execute on procedure ... │
└────────────────────┴─────────────────────┴────────────────────────────────┘

用户的核心操作

1. 创建用户

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

create user 本质上是向 mysql.user 系统表中插入一条记录。

create user vs 直接 insert

-- 方式一：推荐 ✅
create user 'zhangsan'@'localhost' identified by '123456';
-- 方式二：直接操作表（不推荐 ❌）
insert into mysql.user(host, user, authentication_string, ssl_cipher, x509_issuer, x509_subject) values ('localhost', 'zhangsan', password('123456'), '', '', '');
flush privileges;

为什么不推荐直接 insert？

┌──────────────────┬─────────────┬────────────────────────────┐
│                  │ create user │        直接 insert         │
├──────────────────┼─────────────┼────────────────────────────┤
│ 自动刷新权限缓存    │ ✅ 是       │ ❌ 需手动 flush privileges   │
├──────────────────┼─────────────┼────────────────────────────┤
│ 密码加密处理       │ ✅ 自动     │ ⚠️  需手动调用 password()     │
├──────────────────┼─────────────┼────────────────────────────┤
│ 语法检查          │ ✅ 有       │ ❌ 可能写错字段               │
├──────────────────┼─────────────┼────────────────────────────┤
│ 版本兼容性         │ ✅ 好       │ ❌ 不同版本表结构可能变化      │ 
├──────────────────┼─────────────┼────────────────────────────┤
│ 审计日志           │ ✅ 记录     │ ❌ 可能绕过审计              │
└──────────────────┴─────────────┴────────────────────────────┘

总结：create user 操作的是 mysql.user 表，但永远应该用 create user 语句而不是直接操作表。

实战案例：创建仅能本机登录的用户 lotso，密码为 12345678

create user 'lotso'@'localhost' identified by '12345678';

创建完成后，再次查询 user 表，就能看到新增的用户信息。

避坑提示：如果创建时出现error 1819 (hy000): your password does not satisfy the current policy requirements报错，是因为 mysql 开启了密码强度校验。

解决方案：通过show variables like 'validate_password%';查看密码策略要求，设置符合复杂度的密码，或临时调整密码策略。

2. 查看当前登录用户

select current_user();       -- 当前验证用户（含host）
select user();               -- 当前连接用户

3. 重命名用户

-- 将用户 lotso@localhost 重命名为 bearlotso@localhost
rename user 'lotso'@'localhost' to 'bearlotso'@'localhost';
-- 同时修改用户名和 host
rename user 'lotso'@'localhost' to 'bearlotso'@'192.168.1.%';

4. 账户锁定与解锁

在某些场景下（如员工离职、账号审计），需要临时禁用账号而不删除：

-- 创建时直接锁定                                                                                                                                                                             
create user 'temp_user'@'%' identified by 'temp@123' account lock;
-- 锁定已有账号
alter user 'lotso'@'localhost' account lock;
-- 解锁账号
alter user 'lotso'@'localhost' account unlock;

注意：被锁定用户尝试登录时，会收到 error 3118: access denied for user 'lotso'@'localhost'. account is locked.

5. 删除用户

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

错误示范

-- 直接写用户名会报错，默认匹配%主机，和创建的localhost用户不匹配
drop user lotso;

正确示范

-- 必须和创建时的用户名+主机名完全匹配
drop user 'lotso'@'localhost';

6. 修改用户密码

用户自己修改自己的密码

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

root 用户修改指定用户的密码（生产环境常用）

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

实战案例：修改 lotso 用户的密码为 87654321

set password for 'lotso'@'localhost'=password('87654321');

mysql 权限体系

权限列表我们按使用场景分类整理，方便大家按需分配：

权限分类	核心权限	适用范围
基础 dml 权限	select、insert、update、delete	表
结构操作权限	create、drop、alter、index	数据库 / 表
视图专属权限	create view、show view	视图
存储过程权限	create routine、alter routine、execute	存储过程 / 函数
管理类权限	create user、super、process、reload、shutdown	服务器全局
全权限	all [privileges]	对应范围的所有权限

权限粒度说明：

*.*：mysql 实例中所有数据库的所有对象（表、视图、存储过程等）
库名.*：指定数据库中的所有对象
库名.表名：指定数据库中的指定表

举例说明：

-- 全局级：对所有库所有表
grant select on *.* to 'user'@'%';
-- 库级：对某个数据库的所有表
grant select, insert on mydb.* to 'user'@'%';
-- 表级：对某张表
grant select, update on mydb.orders to 'user'@'%';
-- 列级：只能访问指定列（用于脱敏场景）
grant select (id, name, created_at) on mydb.users to 'user'@'%';
-- 注意：列级权限不能使用 select *，只能 select id, name, created_at

权限的核心操作

1. grant 给用户授权

刚创建的用户默认没有任何权限，只能登录 mysql，无法查看任何业务库，必须手动授权。

基本语法：

-- 基本语法
grant 权限列表 on 库.表 to '用户'@'host';
-- 授予只读权限
grant select on mydb.* to 'readonly_user'@'localhost';
-- 授予读写权限
grant select, insert, update, delete on mydb.* to 'app_user'@'%';
-- 授予全部权限（不含 grant option）
grant all privileges on mydb.* to 'admin_user'@'localhost';
-- 授予权限并允许该用户将权限转授给其他用户
grant select on mydb.* to 'team_lead'@'%' with grant option;
-- 授权后立即生效（mysql 8.0 通常自动刷新，但保险起见）
flush privileges;

说明：

多个权限用英文逗号分隔，比如select,insert,update；
identified by是可选的：如果用户已存在，授权的同时会修改密码；如果用户不存在，会直接创建该用户；
授权完成后，若权限未生效，执行flush privileges;刷新权限。

实战案例 1：给 lotso 用户分配 test 库下所有表的只读权限

grant select on test.* to 'lotso'@'localhost';
-- 刷新权限,这个别忘了
flush privileges;

授权后，用 whb 账号登录，就能看到 test 库，并且只能执行 select 查询，无法执行 delete、update 等操作。

实战案例 2：给 lotso 用户分配 test 库的所有权限

grant all privileges on test.* to 'lotso'@'localhost';
-- 刷新权限
flush privileges;

2. 列级权限

权限可以精细到某张表的某几列，适用于隐私数据保护场景：

-- 场景：财务系统中，报表岗只能看订单号和状态，不能看金额                                                                                                                                     
grant select (order_id, status) on shop.orders to 'report_user'@'localhost';
-- 允许插入时只能填写指定列
grant insert (order_id, product_id, quantity) on shop.orders to 'insert_user'@'localhost';

验证效果：

-- report_user 执行以下语句会报错
select amount from shop.orders;
-- error 1143 (42000): select command denied to user 'report_user' for column 'amount'
-- 只能查询被授权的列
select order_id, status from shop.orders;  -- 正常

3. 查看用户权限

-- 查看指定用户的权限
show grants for 'app_user'@'%';
-- 查看当前登录用户的权限
show grants;
show grants for current_user();
-- 从 information_schema 查询权限（更结构化）
select * from information_schema.user_privileges 
where grantee = "'app_user'@'%'";
-- 查看库级权限
select * from information_schema.schema_privileges 
where grantee = "'app_user'@'%'";
-- 查看表级权限
select * from information_schema.table_privileges 
where grantee = "'app_user'@'%'";
-- 查看列级权限
select * from information_schema.column_privileges 
where grantee = "'app_user'@'%'";

4. revoke 回收权限

-- 撤销指定权限
revoke insert, update on mydb.* from 'app_user'@'%';
-- 撤销所有权限
revoke all privileges on mydb.* from 'app_user'@'%';
-- 撤销 grant option
revoke grant option on mydb.* from 'team_lead'@'%';
-- 撤销全局所有权限
revoke all privileges, grant option from 'app_user'@'%';

实战案例：回收 lotso 用户对 test 库的所有权限

revoke all on test.* from 'lotso'@'localhost';
-- 刷新权限
flush privileges;

回收完成后，lotso 账号再次登录，就无法看到 test 库。

mysql 8.0 角色管理（role）

角色（role）是 mysql 8.0 引入的权限管理机制，可将一组权限打包成角色，批量授予用户，避免重复 grant。

1. 创建角色

-- 创建三个业务角色
create role 'role_read_only';       -- 只读角色
create role 'role_write';           -- 读写角色
create role 'role_dba_dev';         -- 开发dba角色

2. 给角色分配权限

-- 只读角色：可以查询所有表
grant select on shop.* to 'role_read_only';
-- 读写角色：增删改查
grant select, insert, update, delete on shop.* to 'role_write';
-- 开发dba角色：结构变更 + 读写
grant select, insert, update, delete, create, drop, index, alter on shop.* to 'role_dba_dev';

3. 将角色授予用户

-- 创建三个用户并分配角色
create user 'analyst'@'%'    identified by 'analyst@123';
create user 'developer'@'%'  identified by 'dev@2024';
create user 'dba_dev'@'%'    identified by 'dba@2024';
grant 'role_read_only' to 'analyst'@'%';
grant 'role_write'     to 'developer'@'%';
grant 'role_dba_dev'   to 'dba_dev'@'%';

4. 激活角色

mysql 8.0 中角色需要激活才能生效：

-- 方式a：用户登录后手动激活
set role 'role_readonly';
set role all;  -- 激活所有已授予的角色
-- 方式b：设置默认角色（登录即自动激活，推荐）
set default role all to 'analyst'@'%';
set default role 'role_readonly' to 'analyst'@'%';
-- 方式c：全局开启自动激活（对所有用户生效）
set global activate_all_roles_on_login = on;

5. 查看角色权限

-- 查看角色拥有的权限
show grants for 'role_read_only';
-- 查看用户拥有的角色
show grants for 'analyst'@'%';
-- 查看用户当前激活的角色（用户自己执行）                                                                                                                                                     
select current_role();

6. 撤销角色

revoke 'role_readonly' from 'analyst'@'%';

7. 删除角色

drop role 'role_readonly';

角色 vs 直接授权对比

假设公司新来 5 个运营人员，都需要只读权限：

-- 没有角色（旧方式）：重复写 5 次
grant select on shop.* to 'op1'@'%';
grant select on shop.* to 'op2'@'%';
-- ...
-- 使用角色（新方式）：创建用户后一行搞定
grant 'role_read_only' to 'op1'@'%', 'op2'@'%', 'op3'@'%', 'op4'@'%', 'op5'@'%';
-- 如果某天需要给所有运营人员加 insert 权限
grant insert on shop.* to 'role_read_only';  -- 一行修改，所有人立即生效！

密码安全策略

密码过期管理

-- 创建时设置密码 90 天后过期
create user 'expire_user'@'%' identified by 'pass@123' password expire interval 90 day;                                                                                                       
-- 立即让某用户密码过期（强制其下次登录时修改）                                                                                                                                               
alter user 'lotso'@'localhost' password expire;                 
-- 设置永不过期
alter user 'lotso'@'localhost' password expire never;
-- 恢复使用全局策略
alter user 'lotso'@'localhost' password expire default;

密码强度插件（validate_password）

mysql 内置密码强度验证插件，生产环境建议开启：

-- 查看当前密码策略
show variables like 'validate_password%';

说明如下：

validate_password.policy：策略级别：low / medium / strong
validate_password.length：最小密码长度（默认 8）
validate_password.mixed_case_count：最少大小写字母数
validate_password.number_count：最少数字个数

常见场景实战

场景1：创建只读用户（数据分析师）

-- 创建用户
create user 'analyst'@'10.0.0.%' identified by 'an@lyst2026!';
-- 仅授予读权限
grant select on datawarehouse.* to 'analyst'@'10.0.0.%';
-- 设置密码 90 天过期
alter user 'analyst'@'10.0.0.%' password expire interval 90 day;
-- 验证权限
show grants for 'analyst'@'10.0.0.%';

场景2：创建应用程序账户（只操作特定库）

-- 创建应用账户，只允许从应用服务器 ip 登录
create user 'webapp'@'192.168.1.50' identified by 'w3bapp@2026';

-- 只授予业务库的 crud 权限，禁止 drop/alter
grant select, insert, update, delete on appdb.* to 'webapp'@'192.168.1.50';

-- 验证：尝试创建表应该报错
-- grant create on appdb.* to 'webapp'@'192.168.1.50';  -- 不授予

show grants for 'webapp'@'192.168.1.50';

场景3：创建 dba 运维账户

-- 创建 dba 账户，只允许本地或跳板机登录
create user 'dba_ops'@'localhost' identified by 'db@admin2026!';

-- 授予所有权限（含管理权限）
grant all privileges on *.* to 'dba_ops'@'localhost' with grant option;

-- 验证
show grants for 'dba_ops'@'localhost';

场景4：使用角色管理多个开发人员

-- 创建角色
create role 'dev_role';
grant select, insert, update, delete on appdb.* to 'dev_role';
grant select on appdb.* to 'dev_role';  -- 补充对日志表的只读
-- 批量创建开发人员账户
create user 'dev_alice'@'%' identified by 'alice@2026!';
create user 'dev_bob'@'%'   identified by 'bob@2026!';
create user 'dev_carol'@'%' identified by 'carol@2026!';
-- 批量授角色
grant 'dev_role' to 'dev_alice'@'%', 'dev_bob'@'%', 'dev_carol'@'%';
-- 设置默认角色（登录自动激活）
set default role all to 'dev_alice'@'%', 'dev_bob'@'%', 'dev_carol'@'%';
-- 验证
show grants for 'dev_alice'@'%' using 'dev_role';

场景5：员工离职处理流程

-- step 1: 立即锁定账户（保留权限以便审计）
alter user 'ex_employee'@'%' account lock;
-- step 2: 审计该用户最近操作（需开启 general_log 或审计插件）
-- 此处略，依赖审计工具
-- step 3: 确认交接完成后彻底删除
drop user 'ex_employee'@'%';

场景6：列级权限脱敏

-- 场景：客服人员只能看到用户名和邮箱，不能看到手机号和身份证
create user 'cs_agent'@'%' identified by 'cs@gent2026!';
grant select (id, username, email, created_at) on appdb.users to 'cs_agent'@'%';
-- 注意：手机号(phone)、身份证(id_card)字段不在授权列中
-- 测试：cs_agent 执行以下 sql 会报错
-- select phone from appdb.users;  -- error 1143: select command denied
show grants for 'cs_agent'@'%';

生产环境权限最佳实践

mysql 权限管理遵循"最小权限原则"——用户只应拥有完成工作所必需的最低权限。生产环境建议：

禁用 root 远程登录（delete from mysql.user where user='root' and host='%'）
每个应用/服务使用独立账号
使用角色（mysql 8.0+）统一管理权限模板
开启密码强度验证和过期策略
定期审计 mysql.user 和 show grants 输出

到此这篇关于mysql 用户管理与权限控制及常用场景实践的文章就介绍到这了,更多相关mysql 用户管理与权限控制内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

MySQL 用户管理与权限控制及常用场景实践

2026年08月26日 • Mysql •我要评论