MariaDB/MySQL用户和权限管理的示例分析

发布时间：2022-01-05 17:19:20 作者：小新
来源：亿速云阅读：138

# MariaDB/MySQL用户和权限管理的示例分析

## 目录
1. [用户与权限体系概述](#一用户与权限体系概述)
2. [用户账户管理实战](#二用户账户管理实战)
3. [权限系统深度解析](#三权限系统深度解析)
4. [角色管理高级应用](#四角色管理高级应用)
5. [安全加固最佳实践](#五安全加固最佳实践)
6. [审计与故障排查](#六审计与故障排查)
7. [总结与常见问题](#七总结与常见问题)

---

## 一、用户与权限体系概述

### 1.1 安全模型架构
MariaDB/MySQL采用分层权限体系：
- **连接层**：验证用户名/密码和主机来源
- **对象层**：控制数据库/表/列的操作权限
- **管理层**：授予服务器管理权限（如SHUTDOWN）

```sql
-- 查看权限系统表结构
SHOW TABLES FROM mysql LIKE '%priv%';

1.2 核心系统表解析

表名	存储内容	关键字段示例
user	全局权限和用户属性	Host,User,Select_priv,plugin
db	数据库级权限	Db,User,Insert_priv
tables_priv	表级特殊权限	Table_name,Column_priv
procs_priv	存储过程/函数权限	Routine_type,Execute_priv
roles_mapping	角色映射（MariaDB特有）	User,Role

二、用户账户管理实战

2.1 用户生命周期管理

-- 创建带密码策略的用户（MySQL 8.0+）
CREATE USER 'app_user'@'192.168.1.%' 
IDENTIFIED BY 'SecurePass123!'
PASSWORD EXPIRE INTERVAL 90 DAY
FLED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 2;

-- 修改用户认证插件（适用于迁移场景）
ALTER USER 'legacy_user'@'%' 
IDENTIFIED WITH mysql_native_password BY 'old_password';

2.2 主机名匹配规则详解

主机模式	匹配范围	安全建议
‘192.168.1.1’	精确IP	生产环境推荐
‘192.168.1.%’	C类子网	测试环境常用
’%.example.com’	域名通配	需DNS验证
“	匿名用户（高危！）	必须删除

三、权限系统深度解析

3.1 权限类型全景图

pie
    title 权限类型占比
    "数据操作(SELECT/INSERT)" : 45
    "结构修改(ALTER/CREATE)" : 30
    "管理权限(SUPER/REPLICATION)" : 15
    "其他权限(PROCESS/TRIGGER)" : 10

3.2 精确权限控制示例

-- 列级权限控制（GDPR合规场景）
GRANT SELECT(id,name), UPDATE(phone) 
ON customer.contact_info 
TO 'hr_staff'@'internal';

-- 存储过程执行权限
GRANT EXECUTE ON PROCEDURE inventory.update_stock 
TO 'warehouse'@'10.0.0.%';

四、角色管理高级应用

4.1 角色使用完整流程（MariaDB 10.0+/MySQL 8.0+）

-- 创建角色
CREATE ROLE inventory_manager;

-- 权限绑定
GRANT SELECT, INSERT ON warehouse.* TO inventory_manager;
GRANT UPDATE(shelf_qty) ON warehouse.stock_items TO inventory_manager;

-- 用户分配角色
GRANT inventory_manager TO 'staff1'@'%', 'staff2'@'10.%';

-- 激活角色（会话级）
SET ROLE inventory_manager;

4.2 角色继承模式对比

特性	MariaDB实现	MySQL实现
默认激活	需要SET ROLE	通过activate_all_roles_on_login控制
密码保护	支持角色密码	不支持
层级继承	允许角色嵌套	平级结构

五、安全加固最佳实践

5.1 密码策略配置

# my.cnf 安全配置项
[mysqld]
default_password_lifetime=90
password_history=6
password_reuse_interval=365
password_require_current=ON

5.2 权限回收陷阱

-- 错误示例：权限残留问题
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'%'; -- 不回收GRANT权限
FLUSH PRIVILEGES;

-- 正确做法
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'%';
DROP USER IF EXISTS 'user'@'%';

六、审计与故障排查

6.1 权限问题诊断工具

-- 查看有效权限（MySQL 8.0+）
SHOW GRANTS FOR CURRENT_USER;

-- 检查权限应用路径
SELECT * FROM information_schema.user_privileges;
SELECT * FROM information_schema.schema_privileges;

-- 模拟权限验证
EXPLN SELECT * FROM secure_table; -- 检查preview_acl

6.2 审计日志配置

-- 启用审计插件（企业版功能）
INSTALL PLUGIN server_audit SONAME 'server_audit.so';
SET GLOBAL server_audit_events='connect,query,table';
SET GLOBAL server_audit_logging=ON;

七、总结与常见问题

7.1 关键原则

最小权限原则：按需分配，及时回收
纵深防御：网络层+DB层双重控制
定期审计：建议每月检查权限分配

7.2 高频问题解答

Q：为什么GRANT后权限不立即生效？ A：需要执行FLUSH PRIVILEGES或重启服务（动态权限除外）

Q：如何克隆用户权限？

-- 使用权限转存方式
SHOW GRANTS FOR 'source_user'@'host'\G
-- 复制输出语句修改后执行

Q：忘记root密码如何处理？ 1. 在配置文件中添加--skip-grant-tables 2. 重启后执行ALTER USER root@localhost IDENTIFIED BY 'new_pass' 3. 移除参数并重启 “`

（注：此为精简版框架，完整9050字版本需扩展每个章节的实战案例、性能影响分析、版本差异说明等内容）