用户权限管理

MySQL的用户权限管理是数据库安全的重要组成部分。本章将详细介绍如何创建用户、授予权限、撤销权限以及管理用户账户。

用户管理概述

MySQL使用用户账户来控制谁可以访问数据库服务器以及他们可以执行什么操作。权限管理遵循最小权限原则，即只授予用户完成工作所需的最小权限。

MySQL权限系统

sql

-- MySQL权限系统的特点：
-- 1. 权限可以授予全局、数据库、表、列级别
-- 2. 权限信息存储在mysql系统数据库中
-- 3. 用户连接时进行身份验证
-- 4. 每个SQL语句执行前检查权限

-- 权限验证流程：
-- 1. 用户连接 -> 身份验证（用户名+密码+主机）
-- 2. 验证通过 -> 加载权限信息
-- 3. 执行SQL -> 检查是否有相应权限

创建用户

基本语法

sql

-- 创建用户的基本语法
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

-- 参数说明：
-- 用户名：登录用户名
-- 主机名：允许连接的主机，可以使用通配符%
-- 密码：登录密码

-- 创建本地用户
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY 'password123';

-- 创建可从任何主机连接的用户
CREATE USER 'lisi'@'%' IDENTIFIED BY 'password123';

-- 创建特定IP段用户
CREATE USER 'wangwu'@'192.168.1.%' IDENTIFIED BY 'password123';

-- 创建特定IP用户
CREATE USER 'admin'@'192.168.1.100' IDENTIFIED BY 'password123';

用户名和主机名规则

sql

-- 用户名规则：
-- 1. 最长32个字符
-- 2. 区分大小写取决于操作系统
-- 3. 可以包含特殊字符，但需要用引号

-- 主机名规则：
-- 1. localhost：仅本地连接
-- 2. %：任意主机（不包括localhost）
-- 3. 192.168.%：指定IP段
-- 4. 具体IP：指定主机

-- 注意：'user'@'localhost' 和 'user'@'%' 是两个不同的账户

-- 创建用户时指定密码过期策略
CREATE USER 'test'@'localhost' 
IDENTIFIED BY 'password123'
PASSWORD EXPIRE INTERVAL 90 DAY;  -- 密码90天后过期

-- 创建用户时指定密码历史（不能重复使用最近3次密码）
CREATE USER 'test2'@'localhost' 
IDENTIFIED BY 'password123'
PASSWORD HISTORY 3;

-- 创建用户时锁定账户
CREATE USER 'locked_user'@'localhost' 
IDENTIFIED BY 'password123'
ACCOUNT LOCK;

使用mysql_native_password认证

sql

-- MySQL 8.0默认使用caching_sha2_password
-- 某些旧客户端可能不支持，可以指定mysql_native_password

CREATE USER 'legacy_user'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'password123';

-- 修改现有用户的认证插件
ALTER USER 'legacy_user'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'newpassword123';

修改用户

修改密码

sql

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

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

-- 使用SET PASSWORD（MySQL 5.7兼容）
SET PASSWORD FOR 'zhangsan'@'localhost' = 'new_password123';

-- 修改密码并设置过期
ALTER USER 'zhangsan'@'localhost' 
IDENTIFIED BY 'new_password123' 
PASSWORD EXPIRE;

-- 解锁账户
ALTER USER 'zhangsan'@'localhost' ACCOUNT UNLOCK;

-- 锁定账户
ALTER USER 'zhangsan'@'localhost' ACCOUNT LOCK;

修改用户属性

sql

-- 修改用户密码过期策略
ALTER USER 'zhangsan'@'localhost' PASSWORD EXPIRE NEVER;  -- 永不过期
ALTER USER 'zhangsan'@'localhost' PASSWORD EXPIRE DEFAULT;  -- 使用默认策略
ALTER USER 'zhangsan'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;  -- 180天后过期

-- 修改密码重用策略
ALTER USER 'zhangsan'@'localhost' PASSWORD HISTORY 5;  -- 不能使用最近5次密码
ALTER USER 'zhangsan'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;  -- 365天后才能重用

-- 修改失败登录锁定策略
ALTER USER 'zhangsan'@'localhost' 
FAILED_LOGIN_ATTEMPTS 3  -- 3次失败后锁定
PASSWORD_LOCK_TIME 1;    -- 锁定1天

-- 重命名用户
RENAME USER 'zhangsan'@'localhost' TO 'zhangsan_new'@'localhost';

删除用户

sql

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

-- 删除多个用户
DROP USER 'lisi'@'%', 'wangwu'@'localhost';

-- 注意：删除用户会同时撤销该用户的所有权限

权限类型

权限分类

sql

-- MySQL权限分为以下几类：

-- 1. 全局权限（*.*）
--    影响整个MySQL服务器
--    如：CREATE USER, PROCESS, RELOAD, SHUTDOWN等

-- 2. 数据库权限（db_name.*）
--    影响特定数据库中的所有对象
--    如：CREATE, DROP, ALTER等

-- 3. 表权限（db_name.table_name）
--    影响特定表
--    如：SELECT, INSERT, UPDATE, DELETE等

-- 4. 列权限（db_name.table_name(column_name)）
--    影响特定列
--    如：SELECT, INSERT, UPDATE

常用权限说明

sql

-- 常用权限及其说明：

-- 数据操作权限
-- SELECT：查询数据
-- INSERT：插入数据
-- UPDATE：更新数据
-- DELETE：删除数据

-- 数据库结构权限
-- CREATE：创建数据库和表
-- ALTER：修改表结构
-- DROP：删除数据库和表
-- INDEX：创建和删除索引
-- CREATE VIEW：创建视图
-- SHOW VIEW：查看视图定义

-- 管理权限
-- ALL [PRIVILEGES]：所有权限
-- CREATE USER：创建用户
-- GRANT OPTION：可以授权给其他用户
-- PROCESS：查看进程信息
-- RELOAD：执行FLUSH命令
-- SHUTDOWN：关闭服务器
-- SUPER：超级权限

-- 复制权限
-- REPLICATION SLAVE：从服务器权限
-- REPLICATION CLIENT：复制客户端权限

-- 文件权限
-- FILE：读写服务器文件

授予权限

基本语法

sql

-- 授予权限的基本语法
GRANT 权限列表 ON 权限级别 TO '用户'@'主机' [WITH GRANT OPTION];

-- 权限级别：
-- *.*           全局权限
-- db_name.*     数据库权限
-- db_name.*     数据库权限
-- db_name.table_name  表权限
-- db_name.table_name(column_name)  列权限

授予全局权限

sql

-- 授予所有权限（谨慎使用）
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 授予特定全局权限
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'data_user'@'localhost';

-- 授予创建用户权限
GRANT CREATE USER ON *.* TO 'user_admin'@'localhost';

-- 授予复制权限
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl_user'@'%';

授予数据库权限

sql

-- 创建测试数据库
CREATE DATABASE IF NOT EXISTS company;

-- 授予数据库所有权限
GRANT ALL PRIVILEGES ON company.* TO 'company_user'@'localhost';

-- 授予数据库特定权限
GRANT SELECT, INSERT, UPDATE ON company.* TO 'company_readwrite'@'localhost';

-- 授予只读权限
GRANT SELECT ON company.* TO 'company_readonly'@'localhost';

-- 授予创建表权限
GRANT CREATE, ALTER, DROP ON company.* TO 'company_dba'@'localhost';

授予表权限

sql

-- 创建测试表
USE company;
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    salary DECIMAL(10,2),
    department VARCHAR(50)
);

-- 授予表的所有权限
GRANT ALL PRIVILEGES ON company.employees TO 'emp_admin'@'localhost';

-- 授予表的特定权限
GRANT SELECT, INSERT ON company.employees TO 'emp_writer'@'localhost';

-- 授予只读权限
GRANT SELECT ON company.employees TO 'emp_reader'@'localhost';

授予列权限

sql

-- 授予特定列的查询权限
GRANT SELECT (id, name, department) ON company.employees TO 'emp_basic'@'localhost';

-- 授予特定列的更新权限
GRANT UPDATE (salary) ON company.employees TO 'salary_manager'@'localhost';

-- 授予特定列的插入权限
GRANT INSERT (name, department) ON company.employees TO 'emp_inserter'@'localhost';

-- 注意：列权限需要精确指定列名

创建用户并授权

sql

-- 创建用户并授权（一步完成）
GRANT SELECT, INSERT, UPDATE ON company.* 
TO 'newuser'@'localhost' IDENTIFIED BY 'password123';

-- 创建管理员用户
GRANT ALL PRIVILEGES ON *.* 
TO 'superadmin'@'localhost' 
IDENTIFIED BY 'strong_password' 
WITH GRANT OPTION;

-- 创建只读用户
GRANT SELECT ON company.* 
TO 'readonly'@'%' 
IDENTIFIED BY 'readonly_password';

撤销权限

基本语法

sql

-- 撤销权限的基本语法
REVOKE 权限列表 ON 权限级别 FROM '用户'@'主机';

-- 撤销所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM '用户'@'主机';

撤销权限示例

sql

-- 撤销全局权限
REVOKE CREATE USER ON *.* FROM 'user_admin'@'localhost';

-- 撤销数据库权限
REVOKE INSERT, UPDATE, DELETE ON company.* FROM 'company_readwrite'@'localhost';

-- 撤销表权限
REVOKE INSERT ON company.employees FROM 'emp_writer'@'localhost';

-- 撤销列权限
REVOKE UPDATE (salary) ON company.employees FROM 'salary_manager'@'localhost';

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'admin'@'localhost';

-- 撤销GRANT OPTION权限
REVOKE GRANT OPTION ON *.* FROM 'admin'@'localhost';

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

查看权限

查看用户权限

sql

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

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

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

-- 查看所有用户的权限（需要管理员权限）
SELECT 
    User AS '用户',
    Host AS '主机',
    Select_priv AS '查询',
    Insert_priv AS '插入',
    Update_priv AS '更新',
    Delete_priv AS '删除',
    Create_priv AS '创建',
    Drop_priv AS '删除表',
    Grant_priv AS '授权'
FROM mysql.user;

查看权限详细信息

sql

-- 查看数据库级别权限
SELECT 
    User AS '用户',
    Host AS '主机',
    Db AS '数据库',
    Select_priv AS '查询',
    Insert_priv AS '插入',
    Update_priv AS '更新',
    Delete_priv AS '删除'
FROM mysql.db;

-- 查看表级别权限
SELECT 
    User AS '用户',
    Host AS '主机',
    Db AS '数据库',
    Table_name AS '表名',
    Table_priv AS '表权限'
FROM mysql.tables_priv;

-- 查看列级别权限
SELECT 
    User AS '用户',
    Host AS '主机',
    Db AS '数据库',
    Table_name AS '表名',
    Column_name AS '列名',
    Column_priv AS '列权限'
FROM mysql.columns_priv;

角色管理

MySQL 8.0引入了角色（Role）功能，可以更方便地管理权限。

创建和使用角色

sql

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

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

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

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

-- 查看用户的角色
SHOW GRANTS FOR 'normal_user'@'localhost';

激活角色

sql

-- 用户登录后，默认角色不会自动激活
-- 需要手动激活或设置默认角色

-- 手动激活角色
SET ROLE 'app_read';
SET ROLE 'app_read', 'app_write';
SET ROLE ALL;  -- 激活所有角色
SET ROLE NONE;  -- 取消所有角色

-- 设置用户的默认角色
ALTER USER 'normal_user'@'localhost' DEFAULT ROLE 'app_read', 'app_write';

-- 设置所有用户的默认激活所有角色
SET GLOBAL activate_all_roles_on_login = ON;

管理角色

sql

-- 查看所有角色
SELECT User, Host FROM mysql.user WHERE authentication_string = '';

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

-- 撤销用户的角色
REVOKE 'app_read' FROM 'reader_user'@'localhost';

-- 撤销角色的权限
REVOKE SELECT ON company.* FROM 'app_read';

权限最佳实践

最小权限原则

sql

-- 最小权限原则：只授予完成工作所需的最小权限

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

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

-- 对于只需要读取数据的应用
GRANT SELECT ON reporting.* TO 'reporter'@'localhost';

-- 对于只需要写入数据的应用
GRANT INSERT ON logs.* TO 'logger'@'localhost';

分离职责

sql

-- 创建不同职责的用户

-- 数据库管理员
CREATE USER 'dba'@'localhost' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;

-- 应用程序用户（读写）
CREATE USER 'app_rw'@'localhost' IDENTIFIED BY 'app_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_rw'@'localhost';

-- 应用程序用户（只读）
CREATE USER 'app_ro'@'localhost' IDENTIFIED BY 'app_password';
GRANT SELECT ON myapp.* TO 'app_ro'@'localhost';

-- 备份用户
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'backup_password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, RELOAD ON *.* TO 'backup'@'localhost';

-- 监控用户
CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'monitor_password';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';

密码安全

sql

-- 设置密码复杂度策略（MySQL 5.6+）
-- 在my.cnf中配置：
-- validate_password.length=8
-- validate_password.policy=MEDIUM
-- validate_password.mixed_case_count=1
-- validate_password.number_count=1
-- validate_password.special_char_count=1

-- 查看密码策略
SHOW VARIABLES LIKE 'validate_password%';

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

-- 强制用户下次登录修改密码
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE;

-- 设置密码历史（防止重复使用）
ALTER USER 'app_user'@'localhost' PASSWORD HISTORY 5;

定期审计

sql

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

-- 查看空密码用户（安全风险）
SELECT User, Host FROM mysql.user WHERE authentication_string = '';

-- 查看有过高权限的用户
SELECT User, Host FROM mysql.user 
WHERE Super_priv = 'Y' OR Grant_priv = 'Y';

-- 查看用户最后登录时间（MySQL 5.7+）
SELECT User, Host, 
       MAX(authentication_time) AS last_login
FROM mysql.user 
GROUP BY User, Host;

-- 定期检查并清理不需要的用户
DROP USER IF EXISTS 'unused_user'@'localhost';

连接和认证

远程连接配置

sql

-- 允许远程连接的用户
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON myapp.* TO 'remote_user'@'%';

-- 限制特定IP段
CREATE USER 'remote_user'@'192.168.1.%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON myapp.* TO 'remote_user'@'192.168.1.%';

-- 注意：需要在MySQL配置文件中设置bind-address
-- my.cnf:
-- bind-address = 0.0.0.0  # 允许所有IP连接
-- 或
-- bind-address = 192.168.1.100  # 只监听特定IP

SSL连接

sql

-- 要求用户使用SSL连接
CREATE USER 'ssl_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;

-- 要求用户使用SSL并提供证书
CREATE USER 'ssl_cert_user'@'%' 
IDENTIFIED BY 'password' 
REQUIRE X509;

-- 要求特定的SSL证书
CREATE USER 'ssl_specific'@'%' 
IDENTIFIED BY 'password' 
REQUIRE SUBJECT '/CN=client.example.com'
AND ISSUER '/CN=CA.example.com';

-- 修改现有用户要求SSL
ALTER USER 'existing_user'@'%' REQUIRE SSL;

本章小结

本章详细介绍了MySQL用户权限管理的相关知识：

用户管理：掌握创建、修改、删除用户的操作
权限类型：理解全局、数据库、表、列四个级别的权限
授予权限：学会使用GRANT语句授予各种权限
撤销权限：掌握REVOKE语句的使用方法
角色管理：学会使用角色简化权限管理（MySQL 8.0+）
最佳实践：了解最小权限原则、密码安全、定期审计等安全措施
连接认证：掌握远程连接配置和SSL连接设置

用户权限管理 ​

用户管理概述 ​

MySQL权限系统 ​

创建用户 ​

基本语法 ​

用户名和主机名规则 ​

使用mysql_native_password认证 ​

修改用户 ​

修改密码 ​

修改用户属性 ​

删除用户 ​

权限类型 ​

权限分类 ​

常用权限说明 ​

授予权限 ​

基本语法 ​

授予全局权限 ​

授予数据库权限 ​

授予表权限 ​

授予列权限 ​

创建用户并授权 ​

撤销权限 ​

基本语法 ​

撤销权限示例 ​

查看权限 ​

查看用户权限 ​

查看权限详细信息 ​

角色管理 ​

创建和使用角色 ​

激活角色 ​

管理角色 ​

权限最佳实践 ​

最小权限原则 ​

分离职责 ​

密码安全 ​

定期审计 ​

连接和认证 ​

远程连接配置 ​

SSL连接 ​

本章小结 ​

用户权限管理

用户管理概述

MySQL权限系统

创建用户

基本语法

用户名和主机名规则

使用mysql_native_password认证

修改用户

修改密码

修改用户属性

删除用户

权限类型

权限分类

常用权限说明

授予权限

基本语法

授予全局权限

授予数据库权限

授予表权限

授予列权限

创建用户并授权

撤销权限

基本语法

撤销权限示例

查看权限

查看用户权限

查看权限详细信息

角色管理

创建和使用角色

激活角色

管理角色

权限最佳实践

最小权限原则

分离职责

密码安全

定期审计

连接和认证

远程连接配置

SSL连接

本章小结