我要评论在mysql数据库管理中，用户与权限管理是非常重要的部分，它涉及到如何控制对数据库的访问和数据的安全性。mysql提供了灵活的权限系统，允许管理员精确控制用户对数据库的访问权限。以下是一些关键的概念和步骤，帮助你理解如何有效地管理mysql中的用户与权限。
1 ~> mysql 用户管理
1.1 用户信息存储
1.1.1 底层原理
mysql 的所有用户账号、密码、权限信息均存储在mysql 系统数据库的 user 表中。用户管理的本质，就是对该系统表进行增删改查操作，官方 sql 语句是对底层表操作的封装。
1.1.2 user 表核心字段
| 字段名 | 作用说明 |
|---|---|
host | 允许用户登录的主机地址：localhost表示仅本地登录，指定 ip 表示仅该地址可登录，%为通配符表示任意主机 |
user | 用户名 |
authentication_string | 哈希加密后的用户密码，无明文存储 |
*_priv系列字段 | 对应各项权限，y表示拥有权限，n表示无权限 |
password_expired | 密码是否过期 |
account_locked | 账号是否被锁定 |
1.1.3 用户查询语句
-- 切换至mysql系统库 use mysql; -- 查询用户核心信息 select host, user, authentication_string from user; -- 查看user表完整结构 desc user;
1.2 创建用户
1.2.1 标准语法（唯一推荐方式）
create user '用户名'@'登录主机' identified by '密码';
- 密码以明文输入，mysql 自动通过哈希算法加密后存入系统表，不会明文存储
- 用户名与主机地址为一个整体，同一用户名对应不同主机视为不同账号
1.2.2 本地用户创建
仅允许从数据库所在服务器本机登录，安全性最高
-- 创建用户whb，仅本地登录，密码为12345678 create user 'whb'@'localhost' identified by '12345678';
1.2.3 远程用户创建
允许从外部主机登录
-- 允许任意主机登录（生产环境禁用） create user 'whb'@'%' identified by '123321'; -- 允许指定ip登录（生产环境推荐） create user 'whb'@'192.168.1.100' identified by '123456';
注意： 公网访问场景下，mysql 识别的是客户端出口公网 ip，而非客户端内网私有 ip，直接填写私有 ip 无法生效。
1.2.4 新建用户默认权限
新建用户默认仅拥有usage权限，即仅可登录数据库，无法查看业务库、无法执行数据操作，仅能访问information_schema等系统库。
1.3 删除用户
1.3.1 标准语法
drop user '用户名'@'登录主机';
- 必须完整指定用户名 + 主机，仅写用户名时 mysql 默认匹配
@'%'，会导致删除对应主机的用户失败。
1.3.2 示例
-- 删除本地登录的whb用户 drop user 'whb'@'localhost'; -- 删除允许任意主机登录的whb用户 drop user 'whb'@'%';
1.4 修改用户密码
1.4.1 管理员修改指定用户密码
-- mysql 5.7 标准语法
set password for 'whb'@'%' = password('1234abcd');
-- mysql 8.0 标准语法（password函数已废弃）
alter user 'whb'@'%' identified by '1234abcd';1.4.2 用户修改自身密码
-- mysql 5.7 语法
set password = password('新密码');
-- mysql 8.0 语法
alter user user() identified by '新密码';1.5 权限刷新机制
1.5.1 语法
flush privileges;
1.5.2 生效逻辑
- mysql 的权限校验基于内存中的权限数据，而非直接读取磁盘系统表
- 必须执行的场景：直接通过 dml 语句修改 mysql 系统表后，需手动刷新将磁盘数据加载到内存
- 无需执行的场景：使用官方 ddl 语句（create user、grant 等）操作时，mysql 自动同步内存权限
2 ~> mysql 权限管理
2.1 权限体系
2.1.1 权限粒度（作用范围）
按作用域从大到小分为 4 个层级，校验时按层级逐级匹配：
- 全局级（
*.*）：作用于 mysql 实例下所有数据库的所有对象 - 库级（
库名.*）：作用于指定数据库内的所有表、视图等对象 - 表级（
库名.表名）：作用于指定库的指定数据表 - 列级：作用于表内指定字段（需单独授权）
2.1.2 核心权限清单
| 权限名称 | 对应系统表字段 | 权限说明 |
|---|---|---|
| select | select_priv | 查询表数据 |
| insert | insert_priv | 插入表数据 |
| update | update_priv | 更新表数据 |
| delete | delete_priv | 删除表数据 |
| create | create_priv | 创建数据库、表、索引 |
| drop | drop_priv | 删除数据库、表、视图 |
| alter | alter_priv | 修改表结构 |
| index | index_priv | 创建、删除索引 |
| grant option | grant_priv | 将自身拥有的权限授予其他用户 |
| create view | create_view_priv | 创建视图 |
| show view | show_view_priv | 查看视图定义 |
| execute | execute_priv | 执行存储过程与函数 |
| file | file_priv | 读写服务器主机上的文件 |
| create user | create_user_priv | 创建、删除、修改用户 |
| show databases | show_db_priv | 查看所有数据库列表 |
| super | super_priv | 超级管理员权限，可执行各类管理操作 |
| usage | - | 基础登录权限，无其他操作权限，新建用户默认拥有 |
2.2 权限授予（grant）
2.2.1 标准语法
grant 权限1, 权限2, ... on 库名.对象名 to '用户名'@'登录主机';
- 多个权限用逗号分隔，
all privileges表示授予指定对象上的所有权限 - mysql 5.7 支持通过
identified by子句在授权时隐式创建用户，mysql 8.0 已废弃该用法
2.2.2 实操示例
-- 授予zhangsan在rootdb.user表上的所有权限 grant all privileges on rootdb.user to 'zhangsan'@'%'; -- 授予zhangsan在rootdb库所有表上的只读权限 grant select on rootdb.* to 'zhangsan'@'%'; -- 授予查询、修改、删除三项权限 grant select, update, delete on rootdb.user to 'zhangsan'@'%';
2.3 权限回收（revoke）
2.3.1 标准语法
revoke 权限1, 权限2, ... on 库名.对象名 from '用户名'@'登录主机';
2.3.2 实操示例
-- 回收zhangsan在rootdb.user表的insert权限 revoke insert on rootdb.user from 'zhangsan'@'%'; -- 回收zhangsan在rootdb库下的所有权限 revoke all privileges on rootdb.* from 'zhangsan'@'%';
2.4 权限查看
-- 查看指定用户的全部权限 show grants for 'zhangsan'@'%'; -- 查看当前登录用户的自身权限 show grants;
2.5 权限生效规则
- 权限变更后，仅对后续新建的数据库连接生效
- 已建立的连接不会自动更新权限，需用户退出重连后生效
- 直接修改系统表的权限变更，必须执行
flush privileges后才会被校验逻辑读取
3 ~> 安全最佳实践
- 最小权限原则：日常操作禁止使用 root 账号，为业务人员分配仅满足工作需求的最小权限
- 严格限定登录地址：禁止使用
%通配符开放任意主机登录，生产环境必须指定可信 ip - 端口防护：禁止将 mysql 端口直接暴露在公网，数据库服务应仅在内网环境访问
- 密码强度：设置高复杂度密码，定期更换，禁止弱口令
- 禁止直接操作系统表：所有用户与权限操作必须使用官方标准 sql 语句
结尾
到此这篇关于详解mysql用户与权限管理的文章就介绍到这了,更多相关mysql用户与权限管理内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论