Realization of multi-tenant saas database table

Tenant model

  • Three multi-tenant models
Insert picture description here
The first example: use each tenant's independent application and its own database.
The second example: using a multi-tenant application, and each tenant has a database.
The third example: Use a multi-tenant application and have a sharded multi-tenant database.
The three models, from left to right, the degree of resource sharing becomes higher in turn, and of course the cost gradually decreases, but the technical difficulty is also greatly increased.

The realization of the database table


-- 租户表:租用系统的客户
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='职位表';

  • Glossary
System user: software developer user;
tenant user: software renter user;
tenant customer user: tenant customer user;
if the tenant is the software developer customer, it can be simplified to the first two users, because the tenant is the customer .
All tables must be added with tenant ID [, customer ID] and department ID to indicate which resource belongs to: which tenant's [which customer's] and which department's resources.
How to judge the operation authority?
You can customize AccessDecisionManager through springSecurity, obtain user permissions based on the logged-in user, and then use this user permission to match the url of this visit.
How to judge data permissions?
The original sql can be obtained through the interceptor of mybaits / JPA and rewritten (that is, the tenant_id / customer_id / dept_id conditions are added to the sql basis).
A user can fully manage their own resources, and can manage the scope of authority (department) granted under the system (that is, which tenant or customer the person belongs to) within the scope of authority (read, write, read and write).