多租户saas数据库表的实现

租户模式

• 三种多租户模式
  

第一个示例 : 使用每租户的独立应用程序和其自己的数据库。

第二个示例 : 使用多租户应用，并且每个租户都具有一个数据库。

第三个示例 : 使用多租户应用，并且具有分片式多租户数据库。

三种模型，从左向右，资源共享程度依次变高，当然成本也就逐步下降，但与之带来的就是技术难度也在大幅增加。

数据库表的实现

-- 租户表：租用系统的客户
CREATE TABLE IF NOT EXISTS `sys_tenant` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '租户id',
  `name` varchar(255) DEFAULT NULL COMMENT '租户名称',
  `code` varchar(64) DEFAULT NULL COMMENT '租户编号',
  `start_time` timestamp NULL DEFAULT NULL COMMENT '开始时间',
  `end_time` timestamp NULL DEFAULT NULL COMMENT '结束时间',
  `status` char(1) NOT NULL DEFAULT '0' COMMENT '0正常 9-冻结',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='租户表';

-- 租户客户表：
-- tenant_id ： 表示属于哪一个租户
CREATE TABLE IF NOT EXISTS `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(255) NOT NULL COMMENT '名称',
  `tenant_id` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='租户客户表';

-- 用户表（3 种类型 ：系统用户，租户用户，租户客户用户）：
-- tenant_id ： 表示属于哪一个租户
-- customer_id： 表示属于哪一个客户
-- 哪个租户的哪个客户的用户信息
CREATE TABLE IF NOT EXISTS `sys_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '用户名',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `salt` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '随机盐',
  `dept_id` int(11) DEFAULT NULL COMMENT '部门ID',
  `mini_openid` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '小程序openid',
  `gitee_login` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '码云登录',
  `osc_id` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '开源中国',
  `wx_openid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '微信openid',
  `qq_openid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'QQ openid',
  `user_stype` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户类型（0：系统用户，1：租户用户，2：租户客户用户）',
  `tenant_id` int(11) DEFAULT NULL DEFAULT '1' COMMENT '所属租户',
  `customer_id` int(11) DEFAULT NULL DEFAULT '1' COMMENT '所属客户',
  PRIMARY KEY (`user_id`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=412 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='用户表';

-- 用户与角色关联表
CREATE TABLE IF NOT EXISTS `sys_user_role` (
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `role_id` int(11) NOT NULL COMMENT '角色ID',
  PRIMARY KEY (`user_id`,`role_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户角色表';

-- 系统角色表（3 种类型 ：系统角色，租户角色，租户客户角色）：
-- tenant_id ： 表示属于哪一个租户 ;
-- customer_id： 表示属于哪一个租户客户 ;
-- dept_id： 表示属于哪一个部门;
-- ds_scope： 数据权限范围（为部门ID集合），即此角色可操作范围
-- ds_type： 数据权限类型（读，写，读写）
-- 哪个租户的哪个客户的角色信息
CREATE TABLE IF NOT EXISTS `sys_role` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `role_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `role_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `ds_type` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '2' COMMENT '数据权限类型',
  `ds_scope` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '数据权限范围',
  `tenant_id` int(11) NOT NULL COMMENT '所属租户',
  `customer_id` int(11) DEFAULT NULL COMMENT '所属客户',
  `dept_id` int(11) DEFAULT NULL COMMENT '所属部门',
  PRIMARY KEY (`role_id`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='系统角色表';

-- 角色菜单表
CREATE TABLE IF NOT EXISTS `sys_role_menu` (
  `role_id` int(11) NOT NULL COMMENT '角色ID',
  `menu_id` int(11) NOT NULL COMMENT '菜单ID',
  PRIMARY KEY (`role_id`,`menu_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='角色菜单表';

-- 菜单权限表（3 种类型 ：系统菜单，租户菜单，租户客户菜单）：
-- tenant_id ： 表示属于哪一个租户，可实现定制化菜单；
-- customer_id： 表示属于哪一个租户客户 ;
CREATE TABLE IF NOT EXISTS `sys_menu` (
  `menu_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单ID',
  `name` varchar(32) NOT NULL COMMENT '菜单名称',
  `permission` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '菜单权限标识',
  `path` varchar(128) DEFAULT NULL COMMENT '前端URL',
  `parent_id` int(11) DEFAULT NULL COMMENT '父菜单ID',
  `icon` varchar(32) DEFAULT NULL COMMENT '图标',
  `component` varchar(64) DEFAULT NULL COMMENT 'VUE页面',
  `sort` int(11) DEFAULT '1' COMMENT '排序值',
  `keep_alive` char(1) DEFAULT '0' COMMENT '0-开启，1- 关闭',
  `type` char(1) DEFAULT NULL COMMENT '菜单类型 （0菜单 1按钮）',
  `tenant_id` int(11) DEFAULT NULL DEFAULT '1' COMMENT '租户',
  `customer_id` int(11) DEFAULT NULL COMMENT '所属客户',
  PRIMARY KEY (`menu_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100362 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='菜单权限表';


-- 租户数据库的信息：
-- tenant_id ： 表示属于哪一个租户，可实现 上述三种模式中的后两种
CREATE TABLE IF NOT EXISTS `sys_datasource_conf` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '名称',
  `url` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL COMMENT '租户ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='数据源表';


-- 部门表（3 种类型 ：系统部门，租户部门，租户客户部门）：
-- tenant_id ： 表示属于哪一个租户，每一个租户都有自己的部门数；
-- customer_id： 表示属于哪一个租户客户 ;
-- 哪个租户的哪个客户的部门信息
CREATE TABLE IF NOT EXISTS `sys_dept` (
  `dept_id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL COMMENT '部门名称',
  `parent_id` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL COMMENT '所属租户',
  `customer_id` int(11) DEFAULT NULL COMMENT '所属客户',
  PRIMARY KEY (`dept_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='部门管理';

-- 职位表（3 种类型 ：系统职位，租户职位，租户客户职位）：
-- tenant_id ： 表示属于哪一个租户
-- customer_id： 表示属于哪一个租户客户 ;
-- dept_id ： 表示属于哪一个部门
-- 哪个租户的哪个客户的哪个部门的职位信息
CREATE TABLE IF NOT EXISTS `sys_job` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(255) NOT NULL COMMENT '职位名称',
  `tenant_id` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL COMMENT '所属客户',
  `dept_id` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='职位表';

• 名词解释

系统用户 ：就是软件开发商用户；
租户用户：就是软件租用方用户；
租户客户用户：就是租户的客户用户；
如果租户就是软件开发商的客户的话，可以简化为前两种用户，因为租户就是客户。

所有的表都要加上 租户ID [ 、客户ID ]、部门ID，以此表明资源所属者是：哪个租户的 [ 哪个客户的 ] 哪个部门的资源 。

操作权限如何判断？

可通过 springSecurity 自定义 AccessDecisionManager，根据登录用户获取用户权限，再此用户权限来匹配此次访问的url。

数据权限如何判断？

可通过 mybaits / JPA 的拦截器，获取原始sql并改写（就是在sql基础上加上 tenant_id / customer_id / dept_id 条件）。

一个用户可以完全管理自己的资源，并可以管理职权范围内( 读，写，读写 )的本人所在体系(就是这个人属于哪个租户或者哪个客户)下授予的权限范围（部门）。