MySQL 用户权限管理：授权、撤销、密码更新和用户删除（图文解析）

前言

公司内部的数据库权限一般针对不同人员有不同的权限分配，而不都统一给一个root权限

1. 授予权限

授予用户权限的基本命令是GRANT

可以授予的权限种类很多，涵盖从数据库和表级别到列和存储过程级别的各种操作

基本的语法如下：

GRANT privileges ON database.table TO 'username'@'host';

对应的参数如下：

• privileges：要授予的权限列表，可以是一个或多个权限，用逗号分隔
• database：数据库名
• table：表名，可以使用通配符*表示所有数据库或所有表
• 'username'@'host'：用户及其允许连接的主机

常见的权限如下：

权限类型	说明
ALL PRIVILEGES	授予用户所有权限
SELECT	允许用户执行SELECT查询
INSERT	允许用户插入数据
UPDATE	允许用户更新数据
DELETE	允许用户删除数据
CREATE	允许用户创建新数据库或表
DROP	允许用户删除数据库或表
GRANT OPTION	允许用户将自己拥有的权限授予其他用户
RELOAD	允许用户执行FLUSH操作（刷新表、日志等）
SHUTDOWN	允许用户关闭MySQL服务器
PROCESS	允许用户查看当前MySQL服务器的线程信息
FILE	允许用户读写服务器上的文件
REFERENCES	允许用户创建外键
INDEX	允许用户创建和删除索引
ALTER	允许用户修改表结构
SHOW DATABASES	允许用户执行SHOW DATABASES查看所有数据库
SUPER	允许用户执行超级用户操作（如设置全局变量、停止复制等）
CREATE TEMPORARY TABLES	允许用户创建临时表
LOCK TABLES	允许用户使用LOCK TABLES命令
EXECUTE	允许用户执行存储过程
REPLICATION SLAVE	允许用户作为复制从服务器连接
REPLICATION CLIENT	允许用户查询主从复制服务器信息
CREATE VIEW	允许用户创建视图
SHOW VIEW	允许用户执行SHOW CREATE VIEW查看视图定义
CREATE ROUTINE	允许用户创建存储过程和函数
ALTER ROUTINE	允许用户修改和删除存储过程和函数
EVENT	允许用户创建、修改和删除事件
TRIGGER	允许用户创建和删除触发器
CREATE USER	允许用户创建、删除和修改用户帐号
ALTER USER	允许用户修改已有用户的属性（如密码）

类似如下：

# 授予多种权限：
# 授予user1对mydatabase数据库中所有表的SELECT、INSERT和UPDATE权限：
GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'user1'@'localhost';# 授予所有权限：
# 授予user1对mydatabase数据库中所有表的所有权限：
GRANT ALL PRIVILEGES ON mydatabase.* TO 'user1'@'localhost';# 授予全局权限：
# 授予user1全局CREATE和DROP权限：
GRANT CREATE, DROP ON *.* TO 'user1'@'localhost'

2. 撤销权限

撤销特定的权限或收回用户所有权限

基本的语法如下：

REVOKE privileges ON database.table FROM 'username'@'host';

• privileges：要收回的权限列表，可以是一个或多个权限，用逗号分隔
• database：数据库名
• table：表名，可以使用通配符*表示所有数据库或所有表
• 'username'@'host'：用户及其允许连接的主机

收回特定的权限：REVOKE SELECT, INSERT ON mydatabase.* FROM 'user1'@'localhost';

收回所有的权限：REVOKE SELECT, INSERT, UPDATE, DELETE ON mydatabase.* FROM 'user1'@'localhost';

3. 查询权限

查询当前用户的权限：show grants;
查询特定用户的权限：SHOW GRANTS FOR 'root'@'localhost';

截图如下所示：

查看MySQL中名为root的用户的详细信息和权限配置
mysql.user表包含所有MySQL用户的详细信息，包括用户名、主机、权限以及其他安全相关设置

SELECT * FROM mysql.user WHERE user='root';

如图所示：

对于该表的基本含义如下：

属性	描述
Host	从哪些主机连接到MySQL服务器，例如localhost、%（表示任意主机）等
User	用户名
Password	加密后的用户密码（从MySQL 5.7.6版本开始，改为authentication_string）
Select_priv、Insert_priv、Update_priv、Delete_priv 等	这些列存储用户在全局级别上的权限，值为'Y'（有权限）或'N'（无权限）
Create_priv、Drop_priv	是否有创建和删除数据库、表等的权限
Grant_priv	是否有授予权限的能力
References_priv、Index_priv、Alter_priv	其他特定操作权限
Create_user_priv	是否有创建新用户的权限
ssl_type、ssl_cipher、x509_issuer、x509_subject	与SSL/TLS连接相关的信息
max_questions、max_updates、max_connections、max_user_connections	限制用户在一定时间内的最大查询数、更新数、连接数等

4. Demo

结合Demo深入理解

-- 1. 创建用户
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';-- 2. 授予用户对mydatabase数据库所有表的所有权限
GRANT ALL PRIVILEGES ON mydatabase.* TO 'user1'@'localhost';-- 3. 查看用户权限
SHOW GRANTS FOR 'user1'@'localhost';-- 4. 收回用户对mydatabase数据库所有表的SELECT和INSERT权限
REVOKE SELECT, INSERT ON mydatabase.* FROM 'user1'@'localhost';-- 5. 查看用户权限（确认SELECT和INSERT权限已收回）
SHOW GRANTS FOR 'user1'@'localhost';

查看用户权限：

再次查看用户权限：

---

对于用户的基本操作

先科普基本的用户创建还有删除命令：

• 创建用户：CREATE USER 'username'@'host' IDENTIFIED BY 'password';
• 删除用户：`DROP USER ‘username’@‘host’;

如果更新用户的密码，推荐阅读：修改sql密码（涵盖多个版本）

-- 1. 创建用户
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';-- 2. 更新用户密码（方法1：使用 ALTER USER）
ALTER USER 'user1'@'localhost' IDENTIFIED BY 'new_password123';-- 3. 更新用户密码（方法2：使用 SET PASSWORD）
SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('new_password123');-- 4. 删除用户
DROP USER 'user1'@'localhost';

截图如下：