第 9 章：用户权限管理与安全

数据库安全是任何数据库管理系统都必须考虑的重要问题。MySQL 提供了完善的用户权限管理和安全机制，确保只有授权用户才能访问和操作数据库。在本章中，我们将学习如何管理 MySQL 用户、分配权限以及实施安全最佳实践。

本章内容概要

• MySQL 用户账户管理
• 创建与删除用户
• 用户权限分配与回收
• GRANT 与 REVOKE 语句
• 角色管理
• 数据库备份与恢复
• 数据库安全最佳实践
• SQL 注入防范

MySQL 用户账户管理

MySQL 使用用户名和主机名的组合来标识用户账户。这意味着 'user'@'localhost' 和 'user'@'%' 是两个不同的用户。

查看当前用户

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

-- 查看当前数据库用户
SELECT DATABASE();

-- 查看当前会话信息
SELECT CONNECTION_ID(), CURRENT_USER();

查看所有用户

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

-- 查看用户的详细信息
SELECT User, Host, authentication_string, plugin 
FROM mysql.user;

创建与删除用户

创建用户

-- 创建基本用户（无密码）
CREATE USER 'newuser'@'localhost';

-- 创建带密码的用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123';

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

-- 创建使用特定认证插件的用户
CREATE USER 'newuser'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'password123';

删除用户

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

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

-- 安全删除用户（如果存在）
DROP USER IF EXISTS 'newuser'@'localhost';

用户权限分配与回收

权限类型

MySQL 提供了多种权限类型：

全局权限

适用于所有数据库和表：

• ALL PRIVILEGES - 所有权限
• CREATE USER - 创建用户权限
• RELOAD - 重新加载权限表
• SHUTDOWN - 关闭服务器
• PROCESS - 查看进程列表

数据库级别权限

适用于特定数据库：

• CREATE - 创建数据库和表
• DROP - 删除数据库和表
• GRANT OPTION - 授权权限

表级别权限

适用于特定表：

• SELECT - 查询数据
• INSERT - 插入数据
• UPDATE - 更新数据
• DELETE - 删除数据
• ALTER - 修改表结构

列级别权限

适用于特定列：

• SELECT - 查询特定列
• INSERT - 插入特定列
• UPDATE - 更新特定列

子程序权限

适用于存储过程和函数：

• EXECUTE - 执行存储过程
• ALTER ROUTINE - 修改存储过程
• CREATE ROUTINE - 创建存储过程

GRANT 语句 - 分配权限

-- 语法
GRANT 权限类型 ON 数据库.表 TO '用户名'@'主机';

-- 授予所有权限（生产环境慎用）
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

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

-- 授予特定表的查询权限
GRANT SELECT ON student_management.students TO 'reader'@'%';

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

-- 授予存储过程执行权限
GRANT EXECUTE ON student_management.* TO 'app_user'@'%';

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

-- 授予读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON student_management.* TO 'readwrite'@'%';

REVOKE 语句 - 回收权限

-- 语法
REVOKE 权限类型 ON 数据库.表 FROM '用户名'@'主机';

-- 回收特定权限
REVOKE DELETE ON student_management.* FROM 'readwrite'@'%';

-- 回收特定列的权限
REVOKE SELECT (email) ON student_management.students FROM 'limited_user'@'localhost';

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

查看用户权限

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

-- 查看特定用户权限
SHOW GRANTS FOR 'newuser'@'localhost';

-- 查看所有权限（需要高级权限）
SELECT * FROM mysql.user WHERE User = 'newuser';

角色管理

MySQL 8.0 开始支持角色管理，角色是一组权限的集合。

创建角色

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

-- 为角色分配权限
GRANT SELECT ON student_management.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON student_management.* TO 'app_write';
GRANT ALL PRIVILEGES ON student_management.* TO 'app_admin';

分配角色给用户

-- 将角色分配给用户
GRANT 'app_read' TO 'newuser'@'localhost';
GRANT 'app_write' TO 'developer'@'localhost';

-- 激活用户角色
SET DEFAULT ROLE 'app_read' TO 'newuser'@'localhost';
SET DEFAULT ROLE ALL TO 'developer'@'localhost';

-- 或者用户手动激活角色
SET ROLE 'app_read';

管理角色

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

-- 查看用户拥有的角色
SHOW GRANTS FOR 'newuser'@'localhost' USING 'app_read';

-- 撤销角色
REVOKE 'app_read' FROM 'newuser'@'localhost';

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

数据库备份与恢复

逻辑备份（mysqldump）

# 备份整个数据库
mysqldump -u root -p student_management > student_management_backup.sql

# 备份多个数据库
mysqldump -u root -p --databases student_management test_db > multiple_backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_databases_backup.sql

# 备份特定表
mysqldump -u root -p student_management students grades > tables_backup.sql

# 备份并压缩
mysqldump -u root -p student_management | gzip > student_management_backup.sql.gz

逻辑恢复

# 恢复数据库
mysql -u root -p student_management < student_management_backup.sql

# 恢复压缩备份
gunzip < student_management_backup.sql.gz | mysql -u root -p student_management

物理备份

# 停止 MySQL 服务
sudo systemctl stop mysql

# 复制数据目录
sudo cp -r /var/lib/mysql /backup/mysql_backup

# 启动 MySQL 服务
sudo systemctl start mysql

使用 mysqlbackup 工具

# 安装 MySQL Enterprise Backup（商业工具）
# 全量备份
mysqlbackup --user=root --password --backup-dir=/backup/mysql backup

# 增量备份
mysqlbackup --user=root --password --backup-dir=/backup/mysql --incremental backup

# 恢复备份
mysqlbackup --user=root --password --backup-dir=/backup/mysql copy-back

数据库安全最佳实践

1. 密码安全策略

-- 查看密码策略
SELECT @@validate_password.policy;

-- 设置密码策略
SET GLOBAL validate_password.policy = MEDIUM;

-- 设置密码长度要求
SET GLOBAL validate_password.length = 12;

-- 创建强密码用户
CREATE USER 'secure_user'@'localhost' 
IDENTIFIED BY 'MyStr0ng!P@ssw0rd';

2. 限制用户连接

-- 限制用户最大连接数
GRANT USAGE ON *.* TO 'limited_user'@'localhost' 
WITH MAX_USER_CONNECTIONS 5;

-- 限制用户每小时查询次数
GRANT USAGE ON *.* TO 'limited_user'@'localhost' 
WITH MAX_QUERIES_PER_HOUR 1000;

-- 限制用户每小时连接次数
GRANT USAGE ON *.* TO 'limited_user'@'localhost' 
WITH MAX_CONNECTIONS_PER_HOUR 100;

3. 使用 SSL 加密连接

-- 查看 SSL 支持
SHOW VARIABLES LIKE '%ssl%';

-- 要求用户使用 SSL 连接
GRANT ALL PRIVILEGES ON student_management.* TO 'secure_user'@'%' 
REQUIRE SSL;

-- 要求特定的 SSL 选项
GRANT ALL PRIVILEGES ON student_management.* TO 'secure_user'@'%' 
REQUIRE CIPHER 'AES128-SHA';

4. 定期审计用户权限

-- 审计用户权限的存储过程
DELIMITER //

CREATE PROCEDURE sp_audit_user_privileges()
BEGIN
    SELECT 
        User,
        Host,
        COUNT(*) as privilege_count
    FROM mysql.user 
    GROUP BY User, Host
    ORDER BY privilege_count DESC;
    
    SELECT 
        User,
        Host,
        Db,
        COUNT(*) as db_privilege_count
    FROM mysql.db 
    GROUP BY User, Host, Db
    ORDER BY User, Host;
END //

DELIMITER ;

-- 执行审计
CALL sp_audit_user_privileges();

SQL 注入防范

1. 使用预处理语句

-- 不安全的查询方式
SET @sql = CONCAT('SELECT * FROM students WHERE name = ''', user_input, '''');
PREPARE stmt FROM @sql;
EXECUTE stmt;

-- 安全的预处理语句
PREPARE stmt FROM 'SELECT * FROM students WHERE name = ?';
SET @name = '张三';
EXECUTE stmt USING @name;

2. 应用层防范

-- 在应用中使用参数化查询（以 Python 为例）
"""
cursor.execute(
    "SELECT * FROM students WHERE name = %s AND major = %s",
    (user_name, user_major)
)
"""

-- 在应用中转义特殊字符
"""
user_input = connection.escape_string(user_input)
query = f"SELECT * FROM students WHERE name = '{user_input}'"
"""

3. 输入验证

-- 创建输入验证函数
DELIMITER //

CREATE FUNCTION fn_validate_input(input_str VARCHAR(255))
RETURNS BOOLEAN
READS SQL DATA
DETERMINISTIC
BEGIN
    -- 检查是否包含危险字符
    IF input_str REGEXP '[\'";\\-\\-]' THEN
        RETURN FALSE;
    END IF;
    
    RETURN TRUE;
END //

DELIMITER ;

-- 使用验证函数
SELECT fn_validate_input('张三') AS is_valid;  -- 返回 1
SELECT fn_validate_input('张三''; DROP TABLE students;') AS is_valid;  -- 返回 0

实战练习：构建安全的数据库访问系统

让我们创建一个安全的数据库访问系统示例。

1. 创建应用用户

-- 创建应用只读用户
CREATE USER 'app_reader'@'%' IDENTIFIED BY 'reader_password_123';
GRANT SELECT ON student_management.* TO 'app_reader'@'%';

-- 创建应用读写用户
CREATE USER 'app_writer'@'%' IDENTIFIED BY 'writer_password_123';
GRANT SELECT, INSERT, UPDATE, DELETE ON student_management.* TO 'app_writer'@'%';

-- 创建应用管理员用户
CREATE USER 'app_admin'@'localhost' IDENTIFIED BY 'admin_password_123';
GRANT ALL PRIVILEGES ON student_management.* TO 'app_admin'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

2. 创建安全视图

-- 创建不包含敏感信息的学生视图
CREATE VIEW v_student_public AS
SELECT 
    id,
    student_number AS 学号,
    name AS 姓名,
    major AS 专业,
    class_name AS 班级,
    enrollment_date AS 入学日期
FROM students
WHERE is_active = TRUE;

-- 授予只读用户访问视图的权限
GRANT SELECT ON student_management.v_student_public TO 'app_reader'@'%';

3. 创建安全存储过程

-- 创建安全的成绩查询存储过程
DELIMITER //

CREATE PROCEDURE sp_get_student_grades(IN student_id INT)
READS SQL DATA
SQL SECURITY DEFINER
BEGIN
    -- 验证输入参数
    IF student_id <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '无效的学生ID';
    END IF;
    
    -- 查询学生成绩
    SELECT 
        s.name AS 学生姓名,
        c.course_name AS 课程名称,
        g.grade AS 成绩,
        g.exam_date AS 考试日期,
        g.semester AS 学期
    FROM students s
    JOIN grades g ON s.id = g.student_id
    JOIN courses c ON g.course_id = c.id
    WHERE s.id = student_id
    ORDER BY g.exam_date DESC;
END //

DELIMITER ;

-- 授予执行权限
GRANT EXECUTE ON PROCEDURE student_management.sp_get_student_grades TO 'app_reader'@'%';

4. 创建权限审计视图

-- 创建权限审计视图
CREATE VIEW v_user_privileges_audit AS
SELECT 
    User,
    Host,
    CASE 
        WHEN Select_priv = 'Y' THEN 'SELECT '
        ELSE ''
    END AS select_priv,
    CASE 
        WHEN Insert_priv = 'Y' THEN 'INSERT '
        ELSE ''
    END AS insert_priv,
    CASE 
        WHEN Update_priv = 'Y' THEN 'UPDATE '
        ELSE ''
    END AS update_priv,
    CASE 
        WHEN Delete_priv = 'Y' THEN 'DELETE '
        ELSE ''
    END AS delete_priv,
    CASE 
        WHEN Create_priv = 'Y' THEN 'CREATE '
        ELSE ''
    END AS create_priv,
    CASE 
        WHEN Drop_priv = 'Y' THEN 'DROP '
        ELSE ''
    END AS drop_priv
FROM mysql.user;

-- 授权管理员查看权限审计
GRANT SELECT ON student_management.v_user_privileges_audit TO 'app_admin'@'localhost';

5. 创建安全日志表

-- 创建安全操作日志表
CREATE TABLE security_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(50),
    operation VARCHAR(100),
    table_name VARCHAR(50),
    record_id INT,
    old_values JSON,
    new_values JSON,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建安全日志触发器
DELIMITER //

CREATE TRIGGER tr_security_log_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO security_logs (
        user_name, operation, table_name, record_id, new_values, ip_address
    ) VALUES (
        USER(),
        'INSERT',
        'students',
        NEW.id,
        JSON_OBJECT(
            'student_number', NEW.student_number,
            'name', NEW.name,
            'major', NEW.major
        ),
        CONNECTION_ID()
    );
END //

DELIMITER ;

6. 测试安全配置

-- 使用只读用户测试
-- mysql -u app_reader -p

-- 只能查询视图
SELECT * FROM v_student_public LIMIT 5;

-- 调用安全存储过程
CALL sp_get_student_grades(1);

-- 不能执行写操作
-- INSERT INTO students (name) VALUES ('测试'); -- 会被拒绝

-- 使用管理员用户查看权限审计
-- mysql -u app_admin -p
SELECT * FROM v_user_privileges_audit 
WHERE User IN ('app_reader', 'app_writer', 'app_admin');

安全监控和维护

1. 监控失败的登录尝试

-- 启用通用查询日志（谨慎使用）
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- 查看连接错误日志
SHOW VARIABLES LIKE 'log_error';

2. 定期更新密码

-- 创建密码更新存储过程
DELIMITER //

CREATE PROCEDURE sp_rotate_password(
    IN user_name VARCHAR(50),
    IN host_name VARCHAR(50),
    IN new_password VARCHAR(100)
)
BEGIN
    SET @sql = CONCAT(
        "ALTER USER '", user_name, "'@'", host_name, 
        "' IDENTIFIED BY '", new_password, "'"
    );
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    FLUSH PRIVILEGES;
    
    INSERT INTO security_logs (user_name, operation, table_name)
    VALUES (USER(), 'PASSWORD_CHANGE', CONCAT(user_name, '@', host_name));
END //

DELIMITER ;

小结

在本章中，我们学习了 MySQL 用户权限管理与安全的重要内容：

1. 用户管理 - 创建、删除和管理 MySQL 用户
2. 权限控制 - 使用 GRANT 和 REVOKE 管理用户权限
3. 角色管理 - 使用角色简化权限管理
4. 备份恢复 - 数据库备份与恢复策略
5. 安全实践 - 密码策略、连接限制、SSL 加密等
6. SQL 注入防范 - 预处理语句和输入验证
7. 实战练习 - 构建安全的数据库访问系统

数据库安全是一个持续的过程，需要定期审计、更新和监控。在下一章中，我们将学习数据库设计与范式，这是构建高质量数据库应用的基础。