面试题：MySQL安全性配置之高级难度：权限管理与审计

设计用户角色与权限体系

确定角色：
- 管理员角色：拥有数据库的所有权限，如创建、删除数据库，创建、修改、删除表，插入、更新、删除数据等。适用于系统管理员，负责数据库的整体维护和管理。
- 普通用户角色：根据业务需求，授予特定表的查询、插入、更新、删除等部分权限。例如，对于一个电商系统，普通用户可能只能查询商品信息、下单（插入订单数据）等。
- 只读用户角色：仅拥有对数据库表的SELECT权限，适用于需要获取数据但不允许修改数据的场景，如报表生成程序。
权限分类：
- 数据库级别权限：如CREATE DATABASE、DROP DATABASE等，用于管理数据库本身的创建和删除。
- 表级别权限：CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE等，控制对表的结构和数据操作。
- 列级别权限：可以针对表中的特定列授予SELECT、UPDATE等权限，例如只允许用户更新某表中部分列的数据。

使用GRANT和REVOKE语句赋予和回收权限

GRANT语句：
- 授予用户所有数据库权限：

GRANT ALL PRIVILEGES ON *.* TO 'username'@'host' IDENTIFIED BY 'password';

- **授予用户特定数据库的所有权限**：

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host' IDENTIFIED BY 'password';

- **授予用户特定表的部分权限**：

GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'host' IDENTIFIED BY 'password';

- **授予用户特定列的权限**：

GRANT UPDATE(column1, column2) ON database_name.table_name TO 'username'@'host' IDENTIFIED BY 'password';

REVOKE语句：
- 回收用户所有数据库权限：

REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'host';

- **回收用户特定数据库的所有权限**：

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

- **回收用户特定表的部分权限**：

REVOKE INSERT ON database_name.table_name FROM 'username'@'host';

- **回收用户特定列的权限**：

REVOKE UPDATE(column1) ON database_name.table_name FROM 'username'@'host';

开启和配置MySQL审计日志

检查MySQL版本：MySQL 5.6及以上版本支持审计日志功能。
开启审计日志：
- 修改配置文件：在MySQL配置文件（通常是my.cnf或my.ini）中添加或修改以下配置：

[mysqld]
plugin-load=audit_log.so
audit_log_format=JSON
audit_log_policy=ALL
audit_log_file=/var/log/mysql/audit.log

- **重启MySQL服务**：修改配置文件后，重启MySQL服务使配置生效。

3. 配置审计日志策略： - ALL策略：记录所有数据库操作。 - NONE策略：不记录任何操作。 - PARTIAL策略：可以根据需要配置记录特定用户、特定数据库或特定操作类型的日志。例如，只记录管理员用户的操作：

audit_log_policy=PARTIAL,USER=admin_user

查看审计日志：审计日志文件（如/var/log/mysql/audit.log）会记录符合配置策略的数据库操作，以JSON格式存储，便于查看和分析。例如：

{
  "type": "QUERY",
  "timestamp": "2023 - 10 - 01T12:00:00Z",
  "thread": 1234,
  "user": "username",
  "host": "192.168.1.100",
  "db": "database_name",
  "query": "SELECT * FROM table_name"
}

面试题：MySQL安全性配置之高级难度：权限管理与审计

知识考点

面试题答案

设计用户角色与权限体系

使用GRANT和REVOKE语句赋予和回收权限

开启和配置MySQL审计日志