MySQL进阶——用户权限和管理（详细版！！！）

        本文将深入探讨MySQL权限管理的核心知识点，从权限管理的基础概念，到用户与权限的关系以及最小权限原则。以及用户账号管理，如创建、修改和删除用户的操作。在权限的授予与回收方面，等等不同层级的权限范围以及实际使用场景。

一、权限管理简介

用户与权限的关系

MySQL 通过用户账号和权限来控制数据库访问。每个用户被授予特定权限，这些权限限定了用户可以执行的操作范围，如查询、修改、删除等。

最小权限原则

为了降低误操作或攻击风险，用户应仅拥有完成其任务所需的最小权限。

二、用户账号管理

创建用户

使用 CREATE USER 语句创建新用户，并指定登录密码和允许登录的主机。
仅允许本地登录：

CREATE USER 'shop_admin'@'localhost' IDENTIFIED BY '123@ss123!';

允许任意 IP 访问：

CREATE USER 'reporter'@'%' IDENTIFIED BY '123@2023';

修改用户

可以使用 ALTER USER 语句修改用户密码，使用 RENAME USER 语句重命名用户并限制 IP 段。

ALTER USER 'shop_admin'@'localhost' IDENTIFIED BY 'NewP@ss!';
RENAME USER 'reporter'@'%' TO 'auditor'@'10.0.0.%';

删除用户

使用 DROP USER 语句删除用户及其权限。

DROP USER 'old_user'@'%';

三、权限的授予与回收

权限层级

MySQL 的权限分为多个层级，包括全局权限、数据库权限、表权限、列权限和存储程序权限。

层级	权限范围	示例
全局权限	所有数据库（*.*）	GRANT SELECT ON *.* TO user;
数据库权限	指定数据库（db_name.*）	GRANT INSERT ON shop_db.* TO user;
表权限	指定表（db_name.table_name）	GRANT DELETE ON shop_db.orders TO user;
列权限	表中特定列	GRANT UPDATE (price) ON shop_db.products TO user;
存储程序权限	存储过程/函数	GRANT EXECUTE ON PROCEDURE shop_db.update_stock TO user;

授予权限

使用 GRANT 语句授予用户特定权限。

GRANT SELECT ON shop_db.* TO 'customer_service'@'localhost';
GRANT CREATE, ALTER, INDEX ON shop_db.* TO 'dev_user'@'10.0.0.%';

回收权限

使用 REVOKE 语句回收用户的特定权限。

-- 回收用户删除数据的权限
REVOKE DELETE ON shop_db.orders FROM 'shop_admin'@'localhost';
-- 回收所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'reporter'@'%';

四、权限系统表

系统表

MySQL 将权限信息存储在系统库 mysql 的表中，包括 user，全局权限和用户基本信息、db数据库级权限、tables_priv表级权限、columns_priv列级权限和 procs_priv存储程序权限。

五、角色管理

角色作用

角色管理将一组权限打包为角色，简化了用户权限分配，适用于团队权限管理，如客服、开发、运维等角色。

创建角色并授权

CREATE ROLE 'shop_reader', 'shop_writer';
GRANT SELECT ON shop_db.* TO 'shop_reader';
GRANT INSERT, UPDATE, DELETE ON shop_db.* TO 'shop_writer';

分配角色给用户

GRANT 'shop_reader' TO 'reporter'@'%';
GRANT 'shop_reader', 'shop_writer' TO 'manager'@'10.0.0.5';

激活角色

SET DEFAULT ROLE 'shop_reader' TO 'reporter'@'%';  -- 设置默认角色
SET ROLE 'shop_writer';                            -- 会话中临时激活角色

六、安全增强配置

密码策略

可以设置密码复杂度和密码过期策略，提高密码安全性。

-- 设置密码复杂度
SET GLOBAL validate_password.policy = 'MEDIUM';  -- 密码需包含数字、大小写字母、特殊字符
-- 密码过期策略
ALTER USER 'shop_admin'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

SSL/TLS 连接强制

要求用户使用 SSL 连接数据库，增强数据传输的安全性。

CREATE USER 'user'@'%' IDENTIFIED BY 'P@ssw0rd' REQUIRE SSL;

七、权限生效

刷新权限

修改权限后，需使用 FLUSH PRIVILEGES 语句刷新权限缓存，使其生效。

FLUSH PRIVILEGES;  -- 修改系统表（如直接操作 mysql.user）后必须执行

查看用户权限

使用 SHOW GRANTS 语句查看用户当前权限。

SHOW GRANTS FOR 'customer_service'@'localhost';  -- 显示用户当前权限

← 上一篇 Java进阶——常用类及常用方法详解

记得点赞、关注、收藏哦！

下一篇 Java进阶——数组超详细整理 →