mysql多租户系统如何设计权限_mysql架构思路解析

租户数据隔离必须通过字段级过滤实现，核心是在所有表中添加tenant_id字段并强制sql查询带上where tenant_id = ?条件，辅以dao层自动注入和mysql 8.0+的check约束；避免视图、存储过程等不可靠方式，跨租户操作应使用独立admin schema，分库分表需考虑二级分片与全局id设计。

租户数据隔离必须靠字段级过滤，不能只依赖数据库用户权限

MySQL 本身不提供原生多租户支持，靠 CREATE USER + GRANT 只能控制库/表级访问，无法防止租户 A 查到租户 B 的 order 行。真实业务中，绝大多数 SaaS 系统采用「共享数据库、共享表结构、字段标识租户」模式，核心是所有关键表都带 tenant_id 字段，并在每条 SQL 查询中强制带上 WHERE tenant_id = ? 条件。

常见错误是把权限校验全扔给应用层中间件或 ORM，结果漏写、缓存穿透、动态 SQL 拼接绕过导致越权。正确做法是：在 DAO 层封装统一的 withTenantId() 查询构造器，对所有读写操作自动注入租户上下文；同时在 MySQL 层加 CHECK CONSTRAINT（8.0.16+）防止非法 INSERT：

ALTER TABLE orders ADD CONSTRAINT chk_tenant_id CHECK (tenant_id = CURRENT_USER());

注意：该约束仅限 MySQL 8.0.16+，且 CURRENT_USER() 需配合代理用户或 application user 映射使用。

避免用视图或存储过程实现租户隔离

有人试图用 CREATE VIEW orders_v AS select * FROM orders WHERE tenant_id = @current_tenant，再让应用 SET @current_tenant。这看似简洁，但实际问题很多：视图无法被优化器下推索引、参数化视图不支持预编译、并发下 @current_tenant 容易污染、无法用于 INSERT/UPDATE。更糟的是，存储过程里拼 CONCAT('SELECT * FROM orders WHERE tenant_id = ', tenant_id)，直接触发 SQL 注入和执行计划失效。

可行替代方案：

• 用连接池绑定租户上下文（如 HikariCP 的 connectionInitSql 执行 SET @tenant_id = ?），再在查询中用 WHERE tenant_id = @tenant_id —— 但需确保每次连接只服务单租户
• 用 MySQL 8.0 的角色（ROLE）配合行级安全策略（ROW access POLICY），不过目前仅企业版支持，社区版不可用
• 最稳妥仍是应用层硬编码过滤 + 单元测试全覆盖验证所有 DAO 方法是否含 tenant_id 条件

跨租户管理操作要单独建 admin schema

运营后台需要查所有租户的统计报表、手动清理异常数据、重置密码等，这些操作天然跨租户。如果把 admin 功能塞进同一套租户表逻辑里，要么要临时关掉 tenant_id 过滤（危险），要么反复切库（低效）。正确做法是建独立 admin schema，里面放汇总视图或定时同步的宽表，例如：

CREATE VIEW admin_order_summary AS SELECT tenant_id, COUNT(*) cnt, SUM(amount) total FROM production.orders GROUP BY tenant_id;

注意：该视图底层仍走原表，所以必须配只读账号，且禁止在 admin 接口暴露原始租户表的 UPDATE 权限。另外，敏感操作（如删除租户）必须走异步任务 + 二次确认 + 操作日志落库（日志表也需带 tenant_id 或标记为系统级）。

分库分表后 tenant_id 不再是唯一路由键

当单库撑不住时，会按 tenant_id 分库（如 hash(tenant_id) % 4），此时 tenant_id 是路由依据，但要注意：一旦租户规模增长，扩库成本高；若某租户数据量爆炸（如大客户），反而造成热点库。更灵活的是「二级分片」：先按 tenant_id 分库，再按 order_id 或时间范围分表。此时应用层路由逻辑变复杂，必须保证 JOIN 不跨库（比如 orders 和 order_items 必须同库），否则只能拆成多次查询。

容易被忽略的点：

• 全局 ID 生成器（如雪花算法）必须包含 tenant_id 或分片位，避免不同库出现重复主键
• 跨租户事务（如平台向多个租户发通知）不能用本地事务，得用 Saga 或消息最终一致
• 备份恢复必须按库粒度，不能只 dump 一个表——因为 tenant_id = 123 的数据可能分散在多个物理库中