-- ======================================== Sa-Sso-Pro 系统表 ==================================== -- 系统角色表 drop table if exists sp_role; CREATE TABLE `sp_role` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色id，--主键、自增', `name` varchar(20) NOT NULL COMMENT '角色名称, 唯一约束', `info` varchar(200) DEFAULT NULL COMMENT '角色详细描述', `is_lock` int(11) NOT NULL DEFAULT '1' COMMENT '是否锁定(1=是,2=否), 锁定之后不可随意删除, 防止用户误操作', `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `name` (`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='系统角色表'; INSERT INTO `sp_role`(`id`, `name`, `info`, `is_lock`) VALUES (1, 'Root 超管', '拥有系统最高权限', 1); INSERT INTO `sp_role`(`id`, `name`, `info`, `is_lock`) VALUES (2, '管理员', '系统维护管理员', 2); INSERT INTO `sp_role`(`id`, `name`, `info`, `is_lock`) VALUES (11, '普通账号', '普通账号', 2); INSERT INTO `sp_role`(`id`, `name`, `info`, `is_lock`) VALUES (12, '测试角色', '测试角色', 2); -- 菜单表 drop table if exists sp_menu; CREATE TABLE `sp_menu` ( `aid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id [no]', `id` varchar(50) NOT NULL COMMENT '菜单id', `title` varchar(50) NOT NULL COMMENT '菜单名称', `icon` varchar(200) COMMENT '菜单图标', `info` varchar(500) COMMENT '菜单介绍', `type` varchar(20) default 'com' COMMENT '菜单类型(dir=目录, com=组件, btn=按钮, link=链接)', `path` varchar(500) COMMENT '菜单路由', `component_path` varchar(500) COMMENT '组件路径', `url` varchar(1024) COMMENT '菜单url (如果指定了此值，则通过 iframe 打开页面视图)', `is_blank` varchar(1024) COMMENT '是否属于外部链接 (如果为true, 则点击菜单时从新窗口打开url) [j switch=true]', `show` varchar(500) COMMENT '是否显示 (yes=永远显示,no=永远不显示,auth=根据权限决定是否显示) [j a-type=3]', `auth` varchar(500) COMMENT '是否鉴权 [j a-type=3]', `parent_id` varchar(50) COMMENT '父菜单id', `sort` bigint(20) COMMENT '排序索引 [num]', `create_time` datetime COMMENT '创建时间', `update_time` datetime COMMENT '更新时间', PRIMARY KEY (`aid`) USING BTREE, UNIQUE KEY `id` (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='菜单表'; INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1001, 'bas', '身份相关', '', '', 'dir', '', '', '', '0', 'no', '1', '-1', 1001, '2022-11-09 16:15:27', '2022-11-12 13:20:49'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1002, 'in-system', '允许进入后台管理', '', '', 'btn', '', '', '', '0', 'no', '1', 'bas', 1003, '2022-11-09 16:18:13', '2022-11-30 05:29:24'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1003, 'root', 'Root 权限（最高权限）', '', '当前系统的最高权限标识，请谨慎授权', 'btn', '', '', '', '0', 'no', '1', 'bas', 1002, '2022-11-09 16:16:50', '2022-11-30 03:32:28'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1004, 'auth', '权限控制', 'el-icon-Unlock', '控制 Admin 管理员对后台的访问规则', 'dir', '', '', '', '0', 'auth', '1', '-1', 1004, '2022-11-09 16:18:51', '2022-11-30 03:55:23'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1005, 'role-list', '角色管理', 'el-icon-Unlock', '', 'com', '', '@/sp-views/sp-role/role-list.vue', NULL, '0', 'auth', '1', 'auth', 1005, '2022-11-09 16:24:52', '2022-12-11 22:01:33'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1006, 'menu-list', '菜单管理', 'el-icon-CollectionTag', '', 'com', '', '/@/sp-views/sp-role/menu-list.vue', NULL, '0', 'auth', '1', 'auth', 1006, '2022-11-09 16:25:41', '2022-12-11 22:01:59'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1007, 'admin-list', '管理员列表', 'el-icon-Key', '管理所有可以登录后台的 Admin 账号', 'com', '', '@/sp-views/sp-admin/admin-list.vue', NULL, '0', 'auth', '1', 'auth', 1007, '2022-11-09 17:01:02', '2022-12-11 22:02:09'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1008, 'console', '监控中心', 'el-icon-View', '提供 Redis、SQL、API访问日志等在线监控能力', 'dir', '', '', '', '0', 'auth', '1', '-1', 1008, '2022-11-09 18:11:13', '2022-11-30 03:54:16'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1009, 'redis-console', 'Redis 监控台', 'el-icon-Search', '', 'com', '', '/@/sp-views/sp-console/redis-console.vue', NULL, '0', 'auth', '1', 'console', 1009, '2022-11-09 18:12:32', '2022-11-30 05:12:32'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1010, 'apilog-list', 'API 请求日志', 'el-icon-MostlyCloudy', '', 'com', '', '@/sp-views/sp-apilog/apilog-list.vue', NULL, '0', 'auth', '1', 'console', 1010, '2022-11-09 18:13:35', '2022-11-30 05:12:37'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1015, 'admin-add', '管理员添加', 'el-icon-Key', '按钮权限：决定管理员列表页是否显示 [ 管理员添加 ] 按钮', 'btn', '', '@/sp-views/sp-admin/admin-add.vue', '', '0', 'no', '1', 'auth', 1011, '2022-11-12 18:02:34', '2022-12-11 22:02:15'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1016, 'sp-admin-login', '管理员登录日志', 'el-icon-Mouse', '', 'com', '', '@/sp-views/sp-admin-login/sp-admin-login-list.vue', NULL, '0', 'auth', '1', 'auth', 1012, '2022-11-12 18:03:37', '2022-12-11 22:02:23'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1017, 'sql-console', 'SQL 监控台', 'el-icon-View', '', 'link', '', NULL, '${SERVER_URL}/druid/index.html', '0', 'auth', '1', 'console', 1013, '2022-11-12 18:04:46', '2022-11-30 03:53:46'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1018, 'form-generator', '在线表单构建', 'el-icon-View', '', 'link', '', NULL, 'https://mrhj.gitee.io/form-generator', '0', 'no', '1', 'console', 1014, '2022-11-12 18:05:25', NULL); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1019, 'sys-client', '应用管理', 'el-icon-Eleme', '管理所有可 SSO 授权的 url 地址', 'dir', '', '', '', '0', 'auth', '1', '-1', 1015, '2022-11-13 11:48:27', '2022-11-30 03:32:56'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1020, 'sys-client-list', '应用列表', '', '', 'com', '', '@/views/sys-client/sys-client-list.vue', NULL, '0', 'auth', '1', 'sys-client', 1016, '2022-11-13 11:48:52', '2022-11-13 11:49:04'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1021, 'sys-client-add', '应用添加', '', '', 'btn', '', '', '', '0', 'no', '1', 'sys-client', 1017, '2022-11-13 11:50:59', NULL); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1022, 'sys-user', '用户管理', 'el-icon-User', '管理 SSO 统一认证的 User 用户', 'dir', '', '', '', '0', 'auth', '1', '-1', 1018, '2022-11-13 11:51:39', '2022-11-30 03:33:42'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1023, 'sys-user-list', '用户列表', '', '', 'com', '', '@/views/sys-user/sys-user-list.vue', NULL, '0', 'auth', '1', 'sys-user', 1019, '2022-11-13 11:51:59', NULL); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1024, 'sys-login-log', '登录日志', '', '', 'com', '', '@/views/sys-login-log/sys-login-log-list.vue', NULL, '0', 'auth', '1', 'sys-user', 1021, '2022-11-13 11:52:51', NULL); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1025, 'console-plate', '数据走势', '', '无此权限的用户无法进入首页大屏', 'com', '', '@/views/sys-user-sta/console-plate.vue', NULL, '0', 'auth', '1', 'sys-user', 1034, '2022-11-13 11:53:15', '2022-11-30 06:18:50'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1026, 'sys-user-online', '在线用户', 'el-icon-CollectionTag', '查看所有正在登录的 User 用户，提供踢人下线操作', 'dir', '', '', '', '0', 'auth', '1', '-1', 1022, '2022-11-13 11:54:19', '2022-11-30 03:38:00'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1027, 'sys-user-online-list', '在线用户', '', '', 'com', '', '@/views/sys-user-online/sys-user-online-list.vue', NULL, '0', 'auth', '1', 'sys-user-online', 1023, '2022-11-13 11:54:52', NULL); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1028, 'sp-config', '系统配置', 'el-icon-Setting', '维护系统全局参数配置、User 用户同步 等', 'dir', '', '', '', '0', 'auth', '1', '-1', 1024, '2022-11-13 11:55:14', '2022-11-30 03:53:34'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1029, 'config-view-info', '系统信息', 'el-icon-Plus', '', 'com', '', '@/sp-views/sp-config/config-view-info.vue', NULL, '0', 'auth', '1', 'sp-config', 1025, '2022-11-13 11:56:02', NULL); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1030, 'config-view-server', '全局参数', 'el-icon-Plus', '', 'com', '', '@/sp-views/sp-config/config-view-server.vue', NULL, '0', 'auth', '1', 'sp-config', 1026, '2022-11-13 11:57:34', NULL); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1031, 'sp-config-list', '表格视图', 'el-icon-Postcard', '', 'com', '', '@/sp-views/sp-config/sp-config-list.vue', NULL, '0', 'auth', '1', 'sp-config', 1035, '2022-11-13 11:58:00', NULL); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1032, 'test', '组件测试', 'el-icon-Scissor', '提供 UI 表单增删改查的封装写法展示', 'dir', '', '@/sp-views/test/data-info.vue', '', '0', 'yes', '1', '-1', 1028, '2022-11-13 11:58:52', '2022-11-30 03:38:39'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1033, 'data-list', '简单列表', 'el-icon-DocumentRemove', '', 'com', '', '@/sp-views/test/data-list.vue', NULL, '0', 'yes', '1', 'test', 1029, '2022-11-13 12:04:10', NULL); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1034, 'data-list2', '复杂列表', 'el-icon-DocumentRemove', '', 'com', '', '@/sp-views/test/list-more/data-list2.vue', NULL, '0', 'yes', '1', 'test', 1030, '2022-11-13 12:04:49', NULL); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1035, 'data-add', '表单提交', 'el-icon-Edit', '', 'com', '', '@/sp-views/test/data-add.vue', NULL, '0', 'yes', '1', 'test', 1031, '2022-11-13 12:05:24', '2022-11-13 12:24:11'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1036, 'data-info', '信息展示', 'el-icon-Finished', '', 'com', '', '@/sp-views/test/data-info.vue', NULL, '0', 'yes', '1', 'test', 1032, '2022-11-13 12:06:00', '2022-11-13 12:26:18'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1050, 'sys-user-list-gc', '用户回收站', '', '', 'com', '', '@/views/sys-user/sys-user-list-gc.vue', NULL, '0', 'auth', '1', 'sys-user', 1020, '2022-11-18 02:48:47', '2022-11-30 03:52:59'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1051, 'config-view-sync-user', '用户同步', 'el-icon-Plus', '', 'com', '', '@/sp-views/sp-config/config-view-sync-user.vue', NULL, '0', 'auth', '1', 'sp-config', 1027, '2022-11-18 10:19:48', '2022-11-30 03:53:21'); INSERT INTO `sp_menu`(`aid`, `id`, `title`, `icon`, `info`, `type`, `path`, `component_path`, `url`, `is_blank`, `show`, `auth`, `parent_id`, `sort`, `create_time`, `update_time`) VALUES (1052, 'sys-client-visit', '应用访问关系', '', '', 'com', '', '@/views/sys-client-visit/sys-client-visit-list.vue', NULL, '0', 'auth', '1', 'sys-client', 1036, '2022-12-03 11:58:17', NULL); -- 角色权限对应表 drop table if exists sp_role_permission; CREATE TABLE `sp_role_permission` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id号', `role_id` bigint(20) DEFAULT NULL COMMENT '角色ID ', `permission_code` varchar(50) DEFAULT NULL COMMENT '菜单项ID', `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='角色权限中间表'; INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'bas', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'root', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'in-system', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'auth', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'role-list', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'menu-list', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'admin-list', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'admin-add', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sp-admin-login', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'console', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'redis-console', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'apilog-list', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sql-console', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'form-generator', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-client', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-client-list', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-client-add', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-client-visit', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-user', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-user-list', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-user-list-gc', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-login-log', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'console-plate', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-user-online', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sys-user-online-list', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sp-config', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'config-view-info', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'config-view-server', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'config-view-sync-user', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (1, 'sp-config-list', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'in-system', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-client', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-client-list', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-client-add', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-client-visit', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-user', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-user-list', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-user-list-gc', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-login-log', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'console-plate', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-user-online', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sys-user-online-list', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sp-config', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'config-view-info', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'config-view-server', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'config-view-sync-user', now()); INSERT INTO `sp_role_permission`(`role_id`, `permission_code`, `create_time`) VALUES (2, 'sp-config-list', now()); -- 系统管理员表 drop table if exists sp_admin; CREATE TABLE `sp_admin` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id，--主键、自增', `name` varchar(100) NOT NULL COMMENT 'admin名称', `avatar` varchar(500) DEFAULT NULL COMMENT '头像地址', `password` varchar(100) DEFAULT NULL COMMENT '密码', `pw` varchar(50) DEFAULT NULL COMMENT '明文密码', `phone` varchar(20) DEFAULT NULL COMMENT '手机号', `role_id` int(11) DEFAULT '11' COMMENT '所属角色id', `status` int(11) DEFAULT '1' COMMENT '账号状态(1=正常, 2=禁用)', `create_by_aid` bigint(20) DEFAULT '-1' COMMENT '创建自哪个管理员', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `login_time` datetime DEFAULT NULL COMMENT '上次登陆时间', `login_ip` varchar(50) DEFAULT NULL COMMENT '上次登陆IP', `login_count` int(11) DEFAULT '0' COMMENT '登陆次数', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `name` (`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='系统管理员表'; INSERT INTO `sp_admin`(`id`, `name`, `avatar`, `password`, `pw`, `role_id`, create_time) VALUES (10001, 'sa', 'http://file.dev33.cn/ssp/avatar1.png', 'E4EF2A290589A23EFE1565BB698437F5', '123456', 1, now()); INSERT INTO `sp_admin`(`id`, `name`, `avatar`, `password`, `pw`, `role_id`, create_time) VALUES (10002, 'admin', 'http://file.dev33.cn/ssp/avatar2.png', '1DE197572C0B23B82BB2F54202E8E00B', 'admin', 2, now()); INSERT INTO `sp_admin`(`id`, `name`, `avatar`, `password`, `pw`, `role_id`, create_time) VALUES (10003, 'uper', 'http://file.dev33.cn/ssp/avatar3.png', '276AE2077ADA0ACEDA51B9D3432E4764', '123123', 11, now()); INSERT INTO `sp_admin`(`id`, `name`, `avatar`, `password`, `pw`, `role_id`, create_time) VALUES (10004, 'sky', 'http://file.dev33.cn/ssp/avatar4.png', 'D8E4064CBDDC70E9B54ED85155160F6F', '123123', 11, now()); -- 管理员登录日志表 drop table if exists sp_admin_login; CREATE TABLE `sp_admin_login` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id号', `acc_id` bigint(20) NOT NULL COMMENT '管理员账号id', `acc_token` varchar(300) DEFAULT NULL COMMENT '本次登录Token', `login_ip` varchar(50) DEFAULT NULL COMMENT '登陆IP', `login_address` varchar(127) DEFAULT NULL COMMENT '登录地点', `login_device` varchar(127) DEFAULT NULL COMMENT '客户端设备标识', `login_system` varchar(127) DEFAULT NULL COMMENT '客户端系统标识', `create_time` datetime NOT NULL COMMENT '创建时间', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='管理员登录日志表'; -- 配置信息表 drop table if exists sp_config; CREATE TABLE `sp_config` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id号', `group_name` varchar(100) NOT NULL COMMENT '配置分组', `name` varchar(100) NOT NULL COMMENT '配置名称', `value` text COMMENT '配置值', `remarks` varchar(255) DEFAULT NULL COMMENT '备注', `create_time` datetime COMMENT '创建时间', `update_time` datetime COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY (`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='配置信息表'; INSERT INTO `sp_config`() VALUES (0, 'server', 'isAllowRegister', 'true', '是否开放注册', '2022-11-08 08:13:15', NULL); INSERT INTO `sp_config`() VALUES (0, 'server', 'userDefaultAvatar', 'http://file.dev33.cn/ssp/user-avatar/1.jpg,http://file.dev33.cn/ssp/user-avatar/2.png,http://file.dev33.cn/ssp/user-avatar/3.jpg,http://file.dev33.cn/ssp/user-avatar/4.jpg,http://file.dev33.cn/ssp/user-avatar/5.jpg,http://file.dev33.cn/ssp/user-avatar/6.jpg,http://file.dev33.cn/ssp/user-avatar/7.jpeg', '用户默认头像', '2022-11-08 08:14:21', '2022-11-08 04:18:59'); INSERT INTO `sp_config`() VALUES (0, 'server', 'reserveInfo', '预留信息', '预留信息', '2022-11-08 08:14:58', '2022-11-08 04:19:09'); INSERT INTO `sp_config`() VALUES (0, 'info', 'appName', 'Sa-Sso-Pro 后台', '', '2022-11-08 08:39:40', NULL); INSERT INTO `sp_config`() VALUES (0, 'info', 'appLogo', 'http://file.dev33.cn/ssp/ssp-logo-480.png', '', '2022-11-08 08:39:40', '2022-12-02 08:43:00'); INSERT INTO `sp_config`() VALUES (0, 'info', 'appVersion', 'v1.6.0', '', '2022-11-08 08:39:40', '2022-12-02 08:30:47'); INSERT INTO `sp_config`() VALUES (0, 'info', 'appIntro', 'Sa-Sso-Pro 后台管理', '', '2022-11-08 08:39:40', '2022-12-02 03:51:52'); INSERT INTO `sp_config`() VALUES (0, 'info', 'isDynamicInfo', 'false', '', '2022-12-02 08:52:04', NULL); INSERT INTO `sp_config`() VALUES (0, 'info', 'appUpdateTime', '2023-1-13', '', '2022-12-02 08:33:16', NULL); INSERT INTO `sp_config`() VALUES (0, 'sync-user', 'isListen', 'true', '', '2022-11-20 23:25:51', '2022-11-24 03:46:34'); INSERT INTO `sp_config`() VALUES (0, 'sync-user', 'isBrd', 'false', '', '2022-11-20 23:25:58', '2022-11-24 03:46:28'); INSERT INTO `sp_config`() VALUES (0, 'sync-user', 'brdUrlSync', '', '', '2022-11-20 23:27:02', '2022-11-24 03:46:17'); INSERT INTO `sp_config`() VALUES (0, 'sync-user', 'brdUrl', 'http://localhost:9012', '', '2022-11-20 23:27:04', NULL); INSERT INTO `sp_config`() VALUES (0, 'sync-user', 'isListenDecrypt', 'true', '', '2022-11-22 20:56:46', '2022-11-24 03:45:32'); INSERT INTO `sp_config`() VALUES (0, 'sync-user', 'isBrdEncrypt', 'true', '', '2022-11-22 20:56:53', '2022-11-24 03:45:11'); INSERT INTO `sp_config`() VALUES (0, 'sync-user', 'listenIpList', '127.0.0.1,192.198.1.102', '', '2022-11-22 21:13:52', '2022-12-17 03:46:17'); -- 系统api请求记录表 -- 如果此段脚本执行报错，请将 datetime(3) 改为 datetime 再次执行 drop table if exists sp_apilog; CREATE TABLE `sp_apilog` ( `id` bigint(50) NOT NULL AUTO_INCREMENT COMMENT '请求id', `req_ip` varchar(100) DEFAULT NULL COMMENT '客户端ip', `req_api` varchar(512) DEFAULT NULL COMMENT '请求api', `req_parame` text COMMENT '请求参数', `req_type` varchar(50) DEFAULT NULL COMMENT '请求类型（GET、POST...）', `req_token` varchar(50) DEFAULT NULL COMMENT '请求token', `req_header` text DEFAULT NULL COMMENT '请求header', `res_code` varchar(50) DEFAULT NULL COMMENT '返回-状态码', `res_msg` text COMMENT '返回-信息描述', `res_string` text COMMENT '返回-整个信息字符串形式', `user_id` bigint(20) DEFAULT NULL COMMENT 'user_id', `admin_id` bigint(20) DEFAULT NULL COMMENT 'admin_id', `start_time` datetime(3) DEFAULT NULL COMMENT '请求开始时间', `end_time` datetime(3) DEFAULT NULL COMMENT '请求结束时间', `cost_time` bigint(20) DEFAULT NULL COMMENT '花费时间，单位ms', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='api请求记录表'; -- ======================================== 登录相关表 ==================================== -- 用户表 drop table if exists sys_user; CREATE TABLE `sys_user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id号 [no]', `username` varchar(30) DEFAULT NULL COMMENT '用户昵称 [t j=like]', `password` varchar(50) DEFAULT NULL COMMENT '账号密码', `pw` varchar(50) DEFAULT NULL COMMENT '明文密码', `avatar` varchar(512) DEFAULT NULL COMMENT '用户头像 [img]', `intro` varchar(1024) DEFAULT NULL COMMENT '个人介绍(签名) [textarea]', `age` int(11) DEFAULT 0 COMMENT '用户年龄 [num]', `sex` int(11) DEFAULT '3' COMMENT '用户性别 (1=男,2=女,3=未知) [j]', `phone` varchar(20) DEFAULT NULL COMMENT '手机号 [num]', `email` varchar(50) DEFAULT NULL COMMENT '用户邮箱', `status` int(11) DEFAULT '1' COMMENT '账号状态(1=正常,2=禁用) [j]', `create_time` datetime NOT NULL COMMENT '创建时间 [date-create]', `login_time` datetime DEFAULT NULL COMMENT '上次登陆时间 [date]', `login_ip` varchar(50) DEFAULT NULL COMMENT '上次登陆IP', `login_count` int(11) DEFAULT '0' COMMENT '登陆次数 [num]', `is_del` int(11) not null DEFAULT 1 COMMENT '是否删除(1=否,2=是) [num]', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='用户表'; INSERT INTO `sys_user`(`id`, `username`, `password`, `pw`, `avatar`, `age`, `sex`, `phone`, `create_time`, `is_del`) VALUES (1000001, 'zhang', 'AEDBE3D6D827FE5624FEF050CA9BD429', '123456', 'http://file.dev33.cn/ssp/user-avatar/1.jpg', 10, 1, '15688889999', now(), 1); INSERT INTO `sys_user`(`id`, `username`, `password`, `pw`, `avatar`, `age`, `sex`, `phone`, `create_time`, `is_del`) VALUES (1000002, 'wangwu', 'F493A5C66A04B4BC7E31718D24B95049', '123456', 'http://file.dev33.cn/ssp/user-avatar/2.png', 10, 1, '13644445555', now(), 1); INSERT INTO `sys_user`(`id`, `username`, `password`, `pw`, `avatar`, `age`, `sex`, `phone`, `create_time`, `is_del`) VALUES (1000003, 'zhaoliu', '0B0261D9F8A649A8B566D2C2633D2224', '123456', 'http://file.dev33.cn/ssp/user-avatar/3.jpg', 10, 1, '13644446666', now(), 2); -- 单点登录日志表 drop table if exists sys_login_log; CREATE TABLE `sys_login_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id号 [no]', `user_id` bigint(20) NOT NULL COMMENT '用户id [num click=sys_user.id]', `acc_token` varchar(300) DEFAULT NULL COMMENT '本次登录Token', `login_ip` varchar(50) DEFAULT NULL COMMENT '登陆IP', `login_address` varchar(127) DEFAULT NULL COMMENT '登录地点', `login_device` varchar(127) DEFAULT NULL COMMENT '客户端设备标识', `login_system` varchar(127) DEFAULT NULL COMMENT '客户端系统标识', `client_id` bigint(20) DEFAULT NULL COMMENT '所属应用id', `client_domain` varchar(300) DEFAULT NULL COMMENT 'Client端域名', `create_time` datetime NOT NULL COMMENT '创建时间 [date-create]', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='用户登录日志表 [fk-s js=(user_id=sys_user.id), show=username.账号昵称.avatar.头像]'; -- 应用表 drop table if exists sys_client; CREATE TABLE `sys_client` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id号 [no]', `name` varchar(50) NOT NULL COMMENT '应用名称', `logo` varchar(512) COMMENT '应用图标 [img]', `intro` varchar(512) COMMENT '应用介绍 [textarea]', `allow_url` varchar(5120) COMMENT '允许授权的url (多个用逗号隔开)', `is_public` int(11) DEFAULT '1' COMMENT '是否公开(1=公开应用, 2=私有应用) [j]', `status` int(11) DEFAULT '1' COMMENT '应用状态(1=启用, 2=禁用) [j switch=true]', `create_time` datetime NOT NULL COMMENT '创建时间 [date-create]', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='应用表'; INSERT INTO `sys_client`(`id`, `name`, `logo`, `intro`, `allow_url`, `status`, `create_time`) VALUES (1001, '凉云商城', 'http://file.dev33.cn/ssp/client1.png', '凉云商城，在线百货，生鲜超市', 'http://sa-sso-client.dev33.cn/sso/login', 1, '2022-01-26 22:42:11'); INSERT INTO `sys_client`(`id`, `name`, `logo`, `intro`, `allow_url`, `status`, `create_time`) VALUES (1002, '凉云商城（前后台分离版）', 'http://file.dev33.cn/ssp/client2.png', '凉云商城，在线百货', 'http://sa-sso-client-h5.dev33.cn/sso-login.html,http://sa-sso-client-vue2.dev33.cn/*,http://sa-sso-client-vue3.dev33.cn/*', 1, '2022-01-28 20:45:05'); INSERT INTO `sys_client`(`id`, `name`, `logo`, `intro`, `allow_url`, `status`, `create_time`) VALUES (1003, '凉云短视频', 'http://file.dev33.cn/ssp/client3.png', '凉云短视频，分享美好生活', 'http://sa-sso-client1.com:9001/*,http://sa-sso-client2.com:9001/*,http://sa-sso-client3.com:9001/*', 1, '2022-01-26 22:44:19'); INSERT INTO `sys_client`(`id`, `name`, `logo`, `intro`, `allow_url`, `status`, `create_time`) VALUES (1004, '测试应用', 'http://file.dev33.cn/ssp/client4.png', '测试应用，本地测试', 'http://127.0.0.1*,http://localhost*', 1, '2022-02-02 06:00:39'); INSERT INTO `sys_client`(`id`, `name`, `logo`, `intro`, `allow_url`, `is_public`, `status`, `create_time`) VALUES (1005, '测试应用2', 'http://file.dev33.cn/ssp/client5.png', '测试应用，本地测试2', 'http://192.168.1.*', 2, 1, '2022-02-02 06:00:39'); -- 应用访问关系表，决定一个用户是否可以访问一个应用 drop table if exists sys_client_visit; CREATE TABLE `sys_client_visit` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '记录id', `client_id` bigint(20) DEFAULT NULL COMMENT '应用ID', `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID', `visit` int(12) DEFAULT 1 COMMENT '所属关系 (1=允许访问,2=禁止访问, 3=跟随应用) [j]', `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='应用访问关系表 [fk-s js=(client_id=sys_client.id), show=name.应用名称.logo.应用Logo][fk-s js=(user_id=sys_user.id), show=username.用户昵称.avatar.用户头像]'; insert into sys_client_visit() values (0, 1001, 1000001, 1, now()); insert into sys_client_visit() values (0, 1005, 1000001, 1, now()); insert into sys_client_visit() values (0, 1001, 1000002, 2, now()); insert into sys_client_visit() values (0, 1002, 1000002, 2, now()); insert into sys_client_visit() values (0, 1003, 1000002, 3, now());