第29章用户与权限管理

本章目标

理解 MySQL 用户标识的组成（用户名 + 主机 = 唯一身份）
掌握创建、修改、删除用户的完整语法
深入理解 MySQL 8.0 认证插件的变迁（mysql_native_password → caching_sha2_password）
熟练掌握 GRANT 和 REVOKE 的权限粒度（全局、数据库、表、列、存储过程/函数）
理解 MySQL 8.0 角色（Role）机制及其应用场景
掌握权限审计和安全最佳实践

前置知识

能够使用 mysql 命令行客户端连接 MySQL 服务器
了解 SQL 基本语法
了解 MySQL 数据库的基本概念（库、表、用户）
具有 root 或具有 CREATE USER 权限的账户访问权限

29.1 MySQL 用户标识基础

29.1.1 用户唯一标识：用户名 + 主机

在 MySQL 中，一个用户由两个部分共同标识：

'username'@'host'

也就是说，'john'@'localhost' 和 'john'@'%' 是两个完全不同的账户。它们可以有不同的密码、不同的权限。这是很多初学者容易忽略的关键点。

sql

-- 这两个是不同的用户！
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password1';
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password2';

当客户端连接时，MySQL 会按照以下规则匹配用户：

首先检查是否存在与连接 IP 完全匹配的 host（如 '192.168.1.100'）
如果没有，检查通配符匹配（如 '192.168.1.%'）
最后匹配 '%'（任何主机）
排序规则：精确匹配优先于通配符匹配

29.1.2 查看所有用户

sql

-- 查看 mysql.user 表
SELECT Host, User, plugin, authentication_string, account_locked, password_expired
FROM mysql.user;

-- 简洁视图
SELECT CONCAT(''', User, ''@''', Host, '''') AS user_identity
FROM mysql.user;

29.1.3 MySQL 8.0 认证插件

MySQL 经历了三代主要的认证插件：

1. mysql_native_password（传统）

MySQL 5.6 及更早版本的默认认证方式
使用 SHA-1 哈希，客户端发送两次哈希结果
优点：广泛兼容，所有客户端都支持
缺点：SHA-1 的安全性已不再被认为是最佳选择（碰撞攻击）

2. caching_sha2_password（MySQL 8.0 默认）

MySQL 8.0 引入，成为默认认证插件
使用 SHA-256 哈希，更安全
支持缓存（TLS 加密连接后，缓存验证结果，减少服务器负载）
支持非加密连接上的 RSA 密钥对认证
某些旧版客户端可能不支持（如 MySQL 5.7 的 mysql 客户端连接 8.0 服务器时）

3. sha256_password

MySQL 5.6 引入的增强认证
使用 SHA-256，强制要求 TLS 或 RSA 加密
不支持缓存，每次连接都需要完整的公钥加密流程

sql

-- 指定认证插件创建用户
CREATE USER 'secure_user'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'strong_password_here';

CREATE USER 'legacy_user'@'192.168.1.%'
  IDENTIFIED WITH mysql_native_password BY 'legacy_pass';

-- 修改用户的认证插件
ALTER USER 'existing_user'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'new_password';

29.1.4 主机（Host）说明符详解

Host 值	含义	匹配范围
`'localhost'`	仅本地连接（通过 Unix socket 或命名管道）	仅本机
`'127.0.0.1'`	仅通过 TCP/IP 本地连接	仅本机 TCP
`'192.168.1.100'`	指定 IP 地址	单个主机
`'192.168.1.%'`	192.168.1 网段	254 个主机
`'192.168.%.%'`	192.168 网段	65534 个主机
`'10.0.%.%'`	10.0 网段	65534 个主机
`'%'`	任何主机（包括 localhost）	所有主机
`'%.example.com'`	example.com 域名下的所有主机	该域下所有主机

重要注意：'localhost' 和 '127.0.0.1' 是不同的！当使用 mysql -h localhost 时，如果 --protocol=TCP 则匹配 'user'@'127.0.0.1'，但默认行为（Unix socket）匹配 'user'@'localhost'。这是常见的连接问题来源。

29.2 用户创建与管理

29.2.1 CREATE USER 完整语法

sql

CREATE USER [IF NOT EXISTS]
    'username'@'host'
    IDENTIFIED [WITH auth_plugin] BY 'password'
    [REQUIRE {NONE | SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subject'}]
    [WITH resource_option_list]
    [PASSWORD EXPIRE {DEFAULT | NEVER | INTERVAL N DAY}]
    [ACCOUNT {LOCK | UNLOCK}]
    [password_lock_option]
    [DEFAULT ROLE role_list];

资源限制选项（resource_option_list）：

sql

-- 限制用户资源使用
CREATE USER 'limited_user'@'%'
  IDENTIFIED BY 'password123'
  WITH
    MAX_QUERIES_PER_HOUR 1000          -- 每小时最大查询数
    MAX_UPDATES_PER_HOUR 100           -- 每小时最大更新数
    MAX_CONNECTIONS_PER_HOUR 50        -- 每小时最大连接次数
    MAX_USER_CONNECTIONS 5;            -- 同时最大连接数

SSL/TLS 要求：

sql

-- 要求 SSL 连接
CREATE USER 'secure_api'@'%'
  IDENTIFIED BY 'api_password'
  REQUIRE SSL;

-- 要求 X509 证书
CREATE USER 'cert_user'@'%'
  IDENTIFIED BY 'cert_pass'
  REQUIRE X509;

-- 指定证书颁发者和主题
CREATE USER 'specific_cert_user'@'%'
  IDENTIFIED BY 'cert_pass'
  REQUIRE SUBJECT '/CN=myapp/O=MyCompany/C=CN'
  AND ISSUER '/CN=MyCA/O=MyCompany/C=CN';

密码策略：

sql

-- 密码永不过期
CREATE USER 'eternal_user'@'%'
  IDENTIFIED BY 'pass'
  PASSWORD EXPIRE NEVER;

-- 密码 90 天后过期
CREATE USER 'rotating_user'@'%'
  IDENTIFIED BY 'pass'
  PASSWORD EXPIRE INTERVAL 90 DAY;

-- 创建时立即过期（用户首次登录必须修改密码）
CREATE USER 'new_employee'@'%'
  IDENTIFIED BY 'temp_password'
  PASSWORD EXPIRE;

-- 账号锁定（创建后不可登录）
CREATE USER 'disabled_user'@'%'
  IDENTIFIED BY 'some_pass'
  ACCOUNT LOCK;

29.2.2 ALTER USER 修改用户

sql

-- 1. 修改密码
ALTER USER 'user'@'host' IDENTIFIED BY 'new_password';

-- 2. 修改当前用户自己的密码（无需指明用户名）
ALTER USER USER() IDENTIFIED BY 'my_new_password';

-- 3. 锁定/解锁账号
ALTER USER 'user'@'host' ACCOUNT LOCK;
ALTER USER 'user'@'host' ACCOUNT UNLOCK;

-- 4. 密码过期
ALTER USER 'user'@'host' PASSWORD EXPIRE;           -- 立即过期
ALTER USER 'user'@'host' PASSWORD EXPIRE NEVER;     -- 永不过期
ALTER USER 'user'@'host' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 5. 修改认证插件
ALTER USER 'user'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

-- 6. 修改资源限制
ALTER USER 'user'@'host' WITH MAX_QUERIES_PER_HOUR 500;

-- 7. 组合修改
ALTER USER 'user'@'host'
  IDENTIFIED BY 'new_secure_password'
  PASSWORD EXPIRE INTERVAL 60 DAY
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LOCK_TIME 2;

MySQL 8.0 新增：失败登录限制

sql

-- 5 次失败登录后锁定 2 天
ALTER USER 'user'@'host'
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LOCK_TIME 2;

-- 5 次失败后永久锁定（需管理员手动解锁）
ALTER USER 'user'@'host'
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LOCK_TIME UNBOUNDED;

-- 查看失败登录状态
SELECT User, Host, Failed_login_attempts, Password_locked
FROM mysql.user
WHERE Failed_login_attempts > 0;

29.2.3 DROP USER 和 RENAME USER

sql

-- 删除用户（如果用户不存在会报错）
DROP USER 'user'@'host';

-- 安全删除（不存在时不报错）
DROP USER IF EXISTS 'user'@'host';

-- 同时删除多个用户
DROP USER 'user1'@'host1', 'user2'@'host2';

-- 重命名用户（会保留原有权限）
RENAME USER 'old_name'@'host' TO 'new_name'@'host';

-- 同时重命名多个用户
RENAME USER
  'old1'@'host1' TO 'new1'@'host1',
  'old2'@'host2' TO 'new2'@'host2';

29.3 权限管理

29.3.1 权限级别

MySQL 权限分为五个级别，从粗到细：

全局级 (*.*)           → 对整个 MySQL 服务器
数据库级 (db.*)        → 对某个数据库的所有表
表级   (db.table)      → 对某个特定的表
列级   (db.table.col)  → 对某个表的特定列
存储程序级 (PROCEDURE/FUNCTION db.routine) → 对特定存储过程或函数

sql

-- 全局权限：对所有数据库的所有对象
GRANT SELECT ON *.* TO 'reader'@'%';

-- 数据库级权限：对 testdb 的所有表
GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'app_user'@'%';

-- 表级权限：仅对 testdb.users 表
GRANT SELECT ON testdb.users TO 'analyst'@'%';

-- 列级权限：仅对 users 表的 email 和 phone 列
GRANT SELECT (id, name, email) ON testdb.users TO 'support'@'%';

-- 存储程序级权限：对特定存储过程
GRANT EXECUTE ON PROCEDURE testdb.cleanup_old_data TO 'batch_user'@'%';
GRANT EXECUTE ON FUNCTION testdb.calculate_bonus TO 'hr_user'@'%';

29.3.2 常见权限详解

数据操作权限：

权限	说明	典型用法
SELECT	读取数据	只读用户
INSERT	插入新行	写入用户
UPDATE	修改现有行	普通应用用户
DELETE	删除行	高级应用用户
ALL PRIVILEGES	所有权限（不包括 GRANT OPTION）	管理员

结构修改权限：

权限	说明
CREATE	创建数据库和表
DROP	删除数据库和表
ALTER	修改表结构
INDEX	创建和删除索引
CREATE VIEW	创建视图
CREATE ROUTINE	创建存储过程和函数
ALTER ROUTINE	修改存储过程和函数
TRIGGER	创建触发器
EVENT	创建和管理定时事件
CREATE TEMPORARY TABLES	创建临时表
LOCK TABLES	显式锁定表

管理权限（需要 SUPER 遗留或对应新权限）：

8.0 权限	说明	替代的 SUPER 子功能
BINLOG_ADMIN	管理二进制日志	PURGE BINARY LOGS
CONNECTION_ADMIN	管理连接	KILL 会话
REPLICATION_APPLIER	复制应用端	复制通道管理
REPLICATION_SLAVE_ADMIN	复制管理	START/STOP SLAVE
ROLE_ADMIN	角色管理	GRANT/REVOKE 角色
SET_USER_ID	设置 DEFINER	存储程序定义者
SYSTEM_USER	系统用户	特定保护操作
SYSTEM_VARIABLES_ADMIN	修改全局系统变量	SET GLOBAL
TABLE_ENCRYPTION_ADMIN	表加密管理	加密操作

sql

-- 8.0 之前的做法（已废弃）
-- GRANT SUPER ON *.* TO 'admin'@'%';

-- 8.0 正确做法：仅授予需要的具体权限
GRANT BINLOG_ADMIN, CONNECTION_ADMIN, REPLICATION_SLAVE_ADMIN
  ON *.* TO 'dba'@'%';

特殊权限：

sql

-- USAGE: 无实际操作权限，仅允许连接服务器
-- 通常用于先创建用户再逐步授权，或用于纯监控用户
GRANT USAGE ON *.* TO 'monitor'@'%';
-- 等价于创建用户后不给任何权限

-- WITH GRANT OPTION: 允许用户将自己拥有的权限授予其他用户
GRANT SELECT, INSERT ON app_db.* TO 'team_lead'@'%' WITH GRANT OPTION;
-- team_lead 现在可以将 app_db 的 SELECT 和 INSERT 权限授予他人

-- GRANT OPTION 不能单独授予，必须伴随实际权限
-- GRANT GRANT OPTION ON ... -- 错误！不存在单独的 GRANT OPTION 权限
-- 正确的做法是在 GRANT 语句末尾加上 WITH GRANT OPTION

29.3.3 GRANT 完整语法

sql

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO 'user'@'host' [, 'user2'@'host2' ...]
    [REQUIRE {NONE | SSL | X509 | ...}]
    [WITH with_option ...];

-- 完整示例
GRANT
    SELECT, INSERT, UPDATE (name, email, phone), DELETE
  ON company_db.*
  TO 'app_user'@'192.168.1.%'
  WITH MAX_QUERIES_PER_HOUR 1000
       MAX_CONNECTIONS_PER_HOUR 100
       GRANT OPTION;

29.3.4 REVOKE 撤销权限

sql

-- 撤销特定权限
REVOKE INSERT, UPDATE, DELETE ON company_db.* FROM 'app_user'@'192.168.1.%';

-- 撤销所有权限（退回到仅有 USAGE）
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host';

-- 撤销所有数据库级别的权限
REVOKE ALL PRIVILEGES ON mydb.* FROM 'user'@'%';

-- 撤销特定列权限
REVOKE UPDATE (email) ON company_db.employees FROM 'user'@'%';

-- 撤销 WITH GRANT OPTION（保留实际权限）
REVOKE GRANT OPTION ON company_db.* FROM 'team_lead'@'%';

-- 如果只想撤销 GRANT OPTION 而不影响权限，使用：
-- REVOKE GRANT OPTION 只会移除传递权限的能力，不会移除实际的 SELECT/INSERT 等权限

-- 重要：REVOKE 只影响当前已授予的权限。如果用户通过角色获得了权限，
-- 需要用 REVOKE 从角色中移除，或者 SET ROLE 来控制

29.3.5 查看权限

sql

-- 查看指定用户的权限（显示为 GRANT 语句）
SHOW GRANTS FOR 'app_user'@'%';

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

-- 查看当前用户权限（同上）
SHOW GRANTS FOR CURRENT_USER();

-- 查看所有用户及其权限摘要
SELECT User, Host,
       Select_priv, Insert_priv, Update_priv, Delete_priv,
       Create_priv, Drop_priv, Grant_priv, Super_priv
FROM mysql.user;

-- 使用 INFORMATION_SCHEMA 查看
SELECT * FROM information_schema.USER_PRIVILEGES
WHERE GRANTEE = '''app_user''@''%''';

SELECT * FROM information_schema.SCHEMA_PRIVILEGES
WHERE GRANTEE LIKE '%app_user%';

SELECT * FROM information_schema.TABLE_PRIVILEGES
WHERE GRANTEE LIKE '%app_user%';

SELECT * FROM information_schema.COLUMN_PRIVILEGES
WHERE GRANTEE LIKE '%app_user%';

29.3.6 FLUSH PRIVILEGES

sql

-- 重新加载权限表
FLUSH PRIVILEGES;

何时需要 FLUSH PRIVILEGES？

通过 GRANT、REVOKE、CREATE USER、ALTER USER、DROP USER 等 SQL 语句修改权限后：不需要 FLUSH PRIVILEGES，MySQL 会自动更新内存中的权限缓存。
直接通过 INSERT、UPDATE、DELETE 语句修改 mysql.user、mysql.db、mysql.tables_priv 等权限表后：必须执行 FLUSH PRIVILEGES 才能使更改生效。
通过 MySQL 管理工具直接编辑 mysql 系统表后：必须 FLUSH PRIVILEGES。

sql

-- 强烈不推荐直接修改 mysql.user 表，但如果你这样做了：
UPDATE mysql.user SET Password = PASSWORD('newpass') WHERE User = 'someuser';
-- 必须执行：
FLUSH PRIVILEGES;

29.4 角色管理（MySQL 8.0+）

角色（Role）是 MySQL 8.0 引入的重要功能，允许你将一组权限打包成一个"角色"，然后将角色授予用户。这大大简化了权限管理。

29.4.1 创建和使用角色

sql

-- 1. 创建角色
CREATE ROLE 'app_developer', 'app_readonly', 'app_admin';

-- 2. 给角色授予权限
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_readonly';
GRANT ALL PRIVILEGES ON app_db.* TO 'app_admin';

-- 3. 将角色授予用户
GRANT 'app_developer' TO 'dev_user1'@'%', 'dev_user2'@'%';
GRANT 'app_readonly' TO 'analyst'@'%';
GRANT 'app_admin' TO 'admin_user'@'%';

-- 4. 激活角色（在会话中）
SET ROLE 'app_developer';
-- 或激活所有已授予的角色
SET ROLE ALL;
-- 或激活所有角色除了某个
SET ROLE ALL EXCEPT 'app_admin';

-- 5. 检查当前活动角色
SELECT CURRENT_ROLE();

-- 6. 设置默认角色（登录时自动激活）
SET DEFAULT ROLE 'app_developer' TO 'dev_user1'@'%';
SET DEFAULT ROLE ALL TO 'admin_user'@'%';
-- 设置为所有角色（包括未来新授予的）
SET DEFAULT ROLE ALL TO 'dev_user2'@'%';

-- 7. 撤销角色
REVOKE 'app_developer' FROM 'dev_user1'@'%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app_developer';  -- 清空角色的权限

-- 8. 删除角色
DROP ROLE 'app_developer';

29.4.2 角色层级

角色可以授予给其他角色，形成角色层级：

sql

-- 创建角色层级
CREATE ROLE 'base_user', 'power_user', 'admin_user';

-- 基础用户有读写权限
GRANT SELECT, INSERT, UPDATE ON app_db.* TO 'base_user';

-- 高级用户继承基础用户权限，并额外拥有删除权限
GRANT 'base_user' TO 'power_user';
GRANT DELETE, CREATE ON app_db.* TO 'power_user';

-- 管理员继承高级用户权限，并额外拥有所有权限
GRANT 'power_user' TO 'admin_user';
GRANT ALL PRIVILEGES ON app_db.* TO 'admin_user';

-- 将 admin_user 角色授予具体用户
GRANT 'admin_user' TO 'dba_john'@'%';

-- dba_john 登录后激活角色
SET ROLE 'admin_user';
-- 现在 dba_john 拥有：SELECT, INSERT, UPDATE, DELETE, CREATE, 以及所有权限

29.4.3 角色使用的最佳实践

sql

-- 1. 按功能分离角色
CREATE ROLE 'db_reader', 'db_writer', 'db_schema_manager', 'db_backup_operator';

GRANT SELECT ON *.* TO 'db_reader';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'db_writer';
GRANT CREATE, ALTER, DROP, INDEX ON app_db.* TO 'db_schema_manager';
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'db_backup_operator';

-- 2. 为不同环境创建角色
CREATE ROLE 'prod_readonly', 'prod_readwrite', 'dev_readwrite', 'staging_readwrite';

-- 3. 定期审计角色分配
SELECT
  r.from_user AS role_name,
  r.from_host AS role_host,
  r.to_user AS grantee_user,
  r.to_host AS grantee_host
FROM mysql.role_edges r
ORDER BY r.from_user, r.to_user;

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

29.5 权限管理最佳实践

29.5.1 最小权限原则

sql

-- 错误做法：给应用用户 ALL PRIVILEGES
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp'@'%';  -- 太危险！

-- 正确做法：仅授予应用所需的权限
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'myapp'@'%';
-- 如果应用从不删除数据：
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'myapp'@'%';
-- 如果应用只有特定表需要更新：
GRANT SELECT ON myapp.* TO 'myapp'@'%';
GRANT SELECT, INSERT, UPDATE ON myapp.orders TO 'myapp'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.cart_items TO 'myapp'@'%';

29.5.2 账号分离策略

sql

-- 不同用途使用不同账号
-- 1. 应用账号：仅 CRUD
CREATE USER 'myapp_app'@'10.0.0.%' IDENTIFIED BY 'strong_app_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'myapp_app'@'10.0.0.%';

-- 2. 迁移/管理账号：有 DDL 权限
CREATE USER 'myapp_admin'@'10.0.0.1' IDENTIFIED BY 'strong_admin_password';
GRANT CREATE, ALTER, DROP, INDEX ON myapp_db.* TO 'myapp_admin'@'10.0.0.1';

-- 3. 只读报表账号：仅 SELECT
CREATE USER 'myapp_report'@'10.0.%.%' IDENTIFIED BY 'strong_report_password';
GRANT SELECT ON myapp_db.* TO 'myapp_report'@'10.0.%.%';

-- 4. 备份账号
CREATE USER 'myapp_backup'@'localhost' IDENTIFIED BY 'strong_backup_password';
GRANT SELECT, LOCK TABLES, RELOAD, REPLICATION CLIENT,
      SHOW VIEW, EVENT, TRIGGER ON *.* TO 'myapp_backup'@'localhost';

-- 5. 监控账号
CREATE USER 'myapp_monitor'@'10.0.%.%' IDENTIFIED BY 'strong_monitor_password';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'myapp_monitor'@'10.0.%.%';

29.5.3 主机限制

sql

-- 尽量限制主机范围
CREATE USER 'app'@'10.0.1.%' IDENTIFIED BY 'pass';       -- 仅应用服务器网段
CREATE USER 'admin'@'10.0.0.1' IDENTIFIED BY 'pass';      -- 仅堡垒机
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'pass';     -- 仅本机

-- 避免使用 %
-- CREATE USER 'app'@'%' IDENTIFIED BY 'pass';  -- 除非真的需要从任何IP连接

29.5.4 定期审计

sql

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

-- 2. 查找无密码用户
SELECT User, Host FROM mysql.user
WHERE authentication_string = '' OR authentication_string IS NULL;

-- 3. 查找使用旧认证插件的用户
SELECT User, Host, plugin FROM mysql.user
WHERE plugin = 'mysql_native_password';

-- 4. 查找有全局权限的用户
SELECT * FROM information_schema.USER_PRIVILEGES
WHERE PRIVILEGE_TYPE = 'ALL PRIVILEGES'
   OR PRIVILEGE_TYPE = 'SUPER';

-- 5. 查找有 WITH GRANT OPTION 的用户
SELECT User, Host FROM mysql.user WHERE Grant_priv = 'Y';

-- 6. 查找密码过期或即将过期的用户
SELECT User, Host, password_last_changed, password_lifetime
FROM mysql.user
WHERE password_lifetime IS NOT NULL
  AND (password_last_changed + INTERVAL password_lifetime DAY) < NOW() + INTERVAL 7 DAY;

-- 7. 移除匿名用户
SELECT User, Host FROM mysql.user WHERE User = '';
-- DELETE FROM mysql.user WHERE User = '';

-- 8. 删除测试数据库
SHOW DATABASES LIKE 'test%';
-- DROP DATABASE IF EXISTS test;

常见错误

错误 1：混淆 'user'@'localhost' 和 'user'@'%'

问题：我在服务器上创建了 CREATE USER 'app'@'%' IDENTIFIED BY 'pass';
      然后在服务器本机使用 mysql -u app -p 连接，输入密码后报 Access denied。

原因：当在服务器本机使用 mysql 命令不带 -h 参数时，默认连接到 localhost。
     MySQL 优先匹配 'app'@'localhost'（如果存在），或尝试通过 Unix socket 连接。
     'app'@'%' 不匹配通过 Unix socket 的本地连接。

解决：
  - 方式1：同时创建 'app'@'localhost' 和 'app'@'%'
  - 方式2：使用 mysql -h 127.0.0.1 -u app -p 强制 TCP 连接
  - 方式3：使用 mysql -u app -p --protocol=TCP 强制 TCP 连接

错误 2：忘记 FLUSH PRIVILEGES

问题：直接修改了 mysql.user 表中的数据，但权限没有生效。

解决：如果使用了 DML 语句（INSERT/UPDATE/DELETE）直接操作 mysql.* 系统表，
     必须执行 FLUSH PRIVILEGES 才能使更改生效。
     
     但如果使用 GRANT、REVOKE、CREATE USER、ALTER USER 等语句，
     不需要手动执行 FLUSH PRIVILEGES。

错误 3：REVOKE 没有生效

问题：执行了 REVOKE SELECT ON db.* FROM 'user'@'host'，
     但用户仍然能查询数据。

可能原因：
  1. 用户通过角色获得了 SELECT 权限（MySQL 8.0）
     → 检查 SHOW GRANTS FOR 'user'@'host' USING 'role_name';
  
  2. 用户在更高级别有相同权限（如全局 SELECT）
     → 检查 SELECT * FROM mysql.user WHERE User='user';
  
  3. 用户被重复授予了权限（多个 GRANT 语句）
     → SHOW GRANTS FOR 'user'@'host' 查看所有权限来源

错误 4：缓存插件不兼容

问题：MySQL 5.7 客户端连接 MySQL 8.0 服务器时，
     报错 "Authentication plugin 'caching_sha2_password' cannot be loaded"。

解决：
  - 方式1：升级客户端到 MySQL 8.0
  - 方式2：将用户的认证方式改为旧版：
    ALTER USER 'user'@'host' IDENTIFIED WITH mysql_native_password BY 'password';
  - 方式3：在 my.cnf 中设置默认认证插件：
    [mysqld]
    default_authentication_plugin=mysql_native_password

错误 5：赋予过多权限的安全风险

问题：为方便起见，给应用程序账号 GRANT ALL PRIVILEGES ON *.*，
     导致 SQL 注入攻击可以直接执行 DROP DATABASE 等操作。

解决：坚持最小权限原则：
  - 应用账号：仅 CRUD 权限，仅在业务库上
  - 分离不同用途的账号
  - 利用角色简化管理，但不降低安全性
  - 定期审计权限分配

本章练习

练习 1：创建完整的用户权限体系

创建一个电商系统的用户权限体系：

创建 4 个角色：shop_reader、shop_writer、shop_manager、shop_admin
shop_reader 仅有 SELECT 权限
shop_writer 继承 shop_reader，额外拥有 INSERT、UPDATE、DELETE
shop_manager 继承 shop_writer，额外拥有管理权限（CREATE、ALTER、INDEX、CREATE VIEW）
shop_admin 拥有所有权限，包含 GRANT OPTION
创建 5 个用户并分配相应角色，设置默认角色
查看每个用户的完整权限（含角色展开）

练习 2：安全审计

编写 SQL 语句完成以下审计任务：

找出所有可以全局 SELECT 的用户
找出所有密码超过 90 天未修改的用户
找出所有未使用 caching_sha2_password 认证的用户
列出所有通过角色间接获得 DELETE 权限的用户
找出管理员用户（拥有 GRANT OPTION 或 SUPER/管理权限）

练习 3：账户锁定与安全策略

创建用户并设置失败登录 5 次锁定 1 天
设置密码 60 天过期
创建用户并设置每小时最多 100 次查询、最多 10 个并发连接
锁定一个用户，验证其无法登录
解锁用户，验证恢复登录

练习 4：角色层级设计

设计一个三级角色层级（初级、中级、高级），要求：

初级有只读权限
中级继承初级，有读写权限
高级继承中级，有管理权限
验证：使用高级角色的用户可以通过 SET ROLE 切换到初级角色以限制自己的权限
查看角色层级关系

练习 5：权限调试

模拟以下场景并给出解决方案：

用户报告可以查询但是不能插入数据 → 诊断权限问题
从远程连接可以但本地连接不行 → 诊断 Host 匹配问题
用户昨天还能用今天不行了 → 诊断密码过期/锁定问题
用户抱怨权限太多 → 使用角色重新组织权限
需要紧急撤销一个用户的所有权限 → 执行正确操作

练习 6：综合实践

搭建一个小型团队的完整权限架构（假设 10 人团队，3 个角色：开发/测试/运维），并写出：

所有 CREATE ROLE、CREATE USER、GRANT 语句
测试每个角色的权限是否正确
角色权限变更时如何影响已分配的用户
仅授予运维角色 BINLOG_ADMIN 和 CONNECTION_ADMIN 而不使用 SUPER
审计脚本：定期检查是否有未经授权的权限变更

第29章 用户与权限管理 ​

本章目标 ​

前置知识 ​

29.1 MySQL 用户标识基础 ​

29.1.1 用户唯一标识：用户名 + 主机 ​

29.1.2 查看所有用户 ​

29.1.3 MySQL 8.0 认证插件 ​

29.1.4 主机（Host）说明符详解 ​

29.2 用户创建与管理 ​

29.2.1 CREATE USER 完整语法 ​

29.2.2 ALTER USER 修改用户 ​

29.2.3 DROP USER 和 RENAME USER ​

29.3 权限管理 ​

29.3.1 权限级别 ​

29.3.2 常见权限详解 ​

29.3.3 GRANT 完整语法 ​

29.3.4 REVOKE 撤销权限 ​

29.3.5 查看权限 ​

29.3.6 FLUSH PRIVILEGES ​

29.4 角色管理（MySQL 8.0+） ​

29.4.1 创建和使用角色 ​

29.4.2 角色层级 ​

29.4.3 角色使用的最佳实践 ​

29.5 权限管理最佳实践 ​

29.5.1 最小权限原则 ​

29.5.2 账号分离策略 ​

29.5.3 主机限制 ​

29.5.4 定期审计 ​

常见错误 ​

错误 1：混淆 'user'@'localhost' 和 'user'@'%' ​

错误 2：忘记 FLUSH PRIVILEGES ​

错误 3：REVOKE 没有生效 ​

错误 4：缓存插件不兼容 ​

错误 5：赋予过多权限的安全风险 ​

本章练习 ​

练习 1：创建完整的用户权限体系 ​

练习 2：安全审计 ​

练习 3：账户锁定与安全策略 ​

练习 4：角色层级设计 ​

练习 5：权限调试 ​

练习 6：综合实践 ​