跳到主要内容

MySQL 用户权限管理

本章将介绍 MySQL 的用户管理和权限控制。

用户管理​

查看用户​

-- 查看所有用户
SELECT User, Host, authentication_string FROM mysql.user;

-- 查看当前用户
SELECT CURRENT_USER();

-- 查看当前登录用户
SELECT USER();

创建用户​

-- 基本语法
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 示例
-- 本地用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password123';

-- 允许从任何主机连接
CREATE USER 'admin'@'%' IDENTIFIED BY 'password123';

-- 允许从特定网段连接
CREATE USER 'readonly'@'192.168.1.%' IDENTIFIED BY 'password123';

-- 使用 MySQL 8.0+ 的 caching_sha2_password 认证
CREATE USER 'user1'@'localhost' 
IDENTIFIED WITH caching_sha2_password BY 'password123';

-- 使用旧的 mysql_native_password 认证
CREATE USER 'user2'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'password123';

主机格式说明：

主机格式说明
localhost只能本地连接
127.0.0.1只能本地 TCP 连接
%允许任何主机
192.168.1.%允许特定网段
192.168.1.100只允许特定 IP

修改用户密码​

-- 修改当前用户密码
ALTER USER USER() IDENTIFIED BY 'new_password';

-- 修改指定用户密码
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_password';

-- 使用 SET PASSWORD
SET PASSWORD FOR 'app_user'@'localhost' = 'new_password';

-- MySQL 5.7 方式
SET PASSWORD FOR 'app_user'@'localhost' = PASSWORD('new_password');

重命名用户​

-- 重命名用户
RENAME USER 'old_user'@'localhost' TO 'new_user'@'localhost';

-- 同时修改主机
RENAME USER 'user'@'localhost' TO 'user'@'%';

删除用户​

-- 删除用户
DROP USER 'app_user'@'localhost';

-- 删除多个用户
DROP USER 'user1'@'localhost', 'user2'@'%';

权限管理​

权限类型​

全局权限​

权限说明
ALL PRIVILEGES所有权限
CREATE创建数据库和表
DROP删除数据库和表
RELOAD执行 FLUSH 命令
SHUTDOWN关闭服务器
PROCESS查看进程
FILE读写文件
GRANT OPTION授予他人权限
SUPER超级权限

数据库权限​

权限说明
SELECT查询数据
INSERT插入数据
UPDATE更新数据
DELETE删除数据
CREATE创建表
DROP删除表
INDEX创建/删除索引
ALTER修改表结构
CREATE TEMPORARY TABLES创建临时表
LOCK TABLES锁定表
CREATE VIEW创建视图
SHOW VIEW查看视图
CREATE ROUTINE创建存储过程
ALTER ROUTINE修改存储过程
EXECUTE执行存储过程
EVENT事件管理
TRIGGER触发器管理

授予权限​

-- 基本语法
GRANT 权限 ON 对象 TO '用户'@'主机';

-- 授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 授予特定数据库的所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'app_user'@'localhost';

-- 授予特定表的权限
GRANT SELECT, INSERT, UPDATE ON mydb.users TO 'web_user'@'localhost';

-- 授予只读权限
GRANT SELECT ON mydb.* TO 'readonly'@'%';

-- 授予特定列的权限
GRANT SELECT(id, name), UPDATE(name) ON mydb.users TO 'limited_user'@'localhost';

-- 授予存储过程执行权限
GRANT EXECUTE ON PROCEDURE mydb.my_procedure TO 'user'@'localhost';

-- 创建用户同时授权
GRANT SELECT, INSERT ON mydb.* TO 'new_user'@'localhost' IDENTIFIED BY 'password';

查看权限​

-- 查看当前用户权限
SHOW GRANTS;

-- 查看指定用户权限
SHOW GRANTS FOR 'app_user'@'localhost';

-- 查看特定用户的权限详情
SELECT * FROM mysql.user WHERE User='app_user' AND Host='localhost';

-- 查看数据库级别权限
SELECT * FROM mysql.db WHERE User='app_user';

-- 查看表级别权限
SELECT * FROM mysql.tables_priv WHERE User='app_user';

撤销权限​

-- 撤销权限
REVOKE 权限 ON 对象 FROM '用户'@'主机';

-- 撤销所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'admin'@'localhost';

-- 撤销特定数据库的权限
REVOKE INSERT, UPDATE ON mydb.* FROM 'app_user'@'localhost';

-- 撤销特定表的权限
REVOKE DELETE ON mydb.users FROM 'web_user'@'localhost';

-- 撤销后刷新权限
FLUSH PRIVILEGES;

权限层级​

MySQL 权限按层级管理：

全局权限 (*.*)
    │
    ├── 数据库权限 (database.*)
    │       │
    │       └── 表权限 (database.table)
    │               │
    │               └── 列权限 (database.table(column))
    │
    └── 存储过程权限 (PROCEDURE database.procedure)
-- 全局权限
GRANT SELECT ON *.* TO 'global_user'@'localhost';

-- 数据库权限
GRANT SELECT ON mydb.* TO 'db_user'@'localhost';

-- 表权限
GRANT SELECT ON mydb.users TO 'table_user'@'localhost';

-- 列权限
GRANT SELECT(name, email) ON mydb.users TO 'column_user'@'localhost';

角色管理（MySQL 8.0+）​

MySQL 8.0 引入了角色（Role）功能，方便权限管理：

创建和管理角色​

-- 创建角色
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- 为角色授权
GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';
GRANT ALL PRIVILEGES ON mydb.* TO 'app_admin';

-- 将角色授予用户
GRANT 'app_read' TO 'readonly_user'@'localhost';
GRANT 'app_read', 'app_write' TO 'editor_user'@'localhost';
GRANT 'app_admin' TO 'admin_user'@'localhost';

-- 查看角色权限
SHOW GRANTS FOR 'app_read';

使用角色​

-- 设置默认激活的角色
SET DEFAULT ROLE ALL TO 'editor_user'@'localhost';

-- 查看当前激活的角色
SELECT CURRENT_ROLE();

-- 手动激活角色
SET ROLE 'app_read';

-- 激活所有授予的角色
SET ROLE ALL;

-- 取消激活角色
SET ROLE NONE;

删除角色​

-- 删除角色
DROP ROLE 'app_read', 'app_write';

-- 从用户撤销角色
REVOKE 'app_read' FROM 'readonly_user'@'localhost';

用户管理最佳实践​

1. 最小权限原则​

-- 不好：授予过多权限
GRANT ALL PRIVILEGES ON *.* TO 'web_user'@'localhost';

-- 好：只授予必要权限
GRANT SELECT, INSERT, UPDATE ON webapp.* TO 'web_user'@'localhost';

2. 使用角色管理权限​

-- 创建角色
CREATE ROLE 'read_only', 'read_write', 'administrator';

GRANT SELECT ON *.* TO 'read_only';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'read_write';
GRANT ALL PRIVILEGES ON *.* TO 'administrator' WITH GRANT OPTION;

-- 分配角色给用户
GRANT 'read_only' TO 'analyst1'@'%';
GRANT 'read_write' TO 'developer1'@'%';
GRANT 'administrator' TO 'dba'@'localhost';

3. 限制远程访问​

-- 不推荐
CREATE USER 'user'@'%' IDENTIFIED BY 'password';

-- 推荐：限制 IP 范围
CREATE USER 'user'@'192.168.1.%' IDENTIFIED BY 'password';
CREATE USER 'user'@'10.0.0.100' IDENTIFIED BY 'password';

4. 密码策略​

-- 设置密码过期
ALTER USER 'user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 设置密码历史（不能重复使用最近 5 个密码）
ALTER USER 'user'@'localhost' PASSWORD HISTORY 5;

-- 设置密码重用间隔
ALTER USER 'user'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;

-- 设置登录失败锁定（MySQL 8.0+）
CREATE USER 'user'@'localhost' 
IDENTIFIED BY 'password'
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1;

5. 定期审计​

-- 查看所有用户
SELECT User, Host, account_locked, password_expired 
FROM mysql.user;

-- 查看所有权限
SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv
FROM mysql.user;

-- 查看空密码用户
SELECT User, Host FROM mysql.user WHERE authentication_string = '';

-- 查看近期连接历史（需要开启审计插件）
SELECT * FROM mysql.general_log 
WHERE command_type = 'Connect' 
ORDER BY event_time DESC LIMIT 100;

连接安全​

SSL 连接​

-- 查看SSL状态
SHOW VARIABLES LIKE '%ssl%';

-- 强制用户使用 SSL
ALTER USER 'user'@'%' REQUIRE SSL;

-- 要求客户端证书
ALTER USER 'user'@'%' REQUIRE X509;

-- 指定证书
ALTER USER 'user'@'%' 
REQUIRE SUBJECT '/CN=user.example.com'
AND ISSUER '/CN=CA.example.com';

连接限制​

-- 限制每小时查询次数
ALTER USER 'user'@'localhost' WITH MAX_QUERIES_PER_HOUR 1000;

-- 限制每小时更新次数
ALTER USER 'user'@'localhost' WITH MAX_UPDATES_PER_HOUR 500;

-- 限制每小时连接次数
ALTER USER 'user'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 100;

-- 限制同时连接数
ALTER USER 'user'@'localhost' WITH MAX_USER_CONNECTIONS 10;

小结​

本章我们学习了：

  1. 用户管理：创建、修改、删除用户
  2. 权限类型：全局、数据库、表、列权限
  3. 授予权限：GRANT 语句的使用
  4. 撤销权限：REVOKE 语句的使用
  5. 角色管理：MySQL 8.0 的角色功能
  6. 最佳实践：最小权限、密码策略、审计

练习​

  1. 创建一个只读用户和一个读写用户
  2. 创建角色并分配给用户
  3. 设置用户的密码过期策略
  4. 审计当前数据库的用户权限

参考资源​