229 lines
15 KiB
SQL
229 lines
15 KiB
SQL
-- ============================================================================
|
||
-- 用户企业认证、身份认证和员工CA二维码认证功能数据库初始化脚本
|
||
-- 创建日期: 2026-03-04
|
||
-- 描述: 创建认证相关表、角色、菜单和权限
|
||
-- ============================================================================
|
||
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- ============================================================================
|
||
-- 1. 创建认证相关表
|
||
-- ============================================================================
|
||
|
||
-- ----------------------------
|
||
-- Table structure for sys_user_enterprise_verification
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `sys_user_enterprise_verification`;
|
||
CREATE TABLE `sys_user_enterprise_verification` (
|
||
`verification_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '认证ID',
|
||
`user_id` BIGINT(20) NOT NULL COMMENT '用户ID',
|
||
`enterprise_name` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '企业名称',
|
||
`enterprise_code` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '企业统一社会信用代码',
|
||
`legal_person` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '法人代表',
|
||
`business_license` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '营业执照URL',
|
||
`verification_status` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'PENDING' COMMENT '认证状态: PENDING-未认证, APPROVED-已认证, REJECTED-认证失败',
|
||
`audit_remark` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '审核备注',
|
||
`auditor_id` BIGINT(20) NULL DEFAULT NULL COMMENT '审核人ID',
|
||
`audit_time` DATETIME NULL DEFAULT NULL COMMENT '审核时间',
|
||
`create_by` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '创建者',
|
||
`create_time` DATETIME NULL DEFAULT NULL COMMENT '创建时间',
|
||
`update_by` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '更新者',
|
||
`update_time` DATETIME NULL DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`verification_id`) USING BTREE,
|
||
INDEX `idx_user_id` (`user_id`) USING BTREE,
|
||
INDEX `idx_status` (`verification_status`) USING BTREE,
|
||
INDEX `idx_enterprise_code` (`enterprise_code`) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户企业认证表' ROW_FORMAT = DYNAMIC;
|
||
|
||
-- ----------------------------
|
||
-- Table structure for sys_user_identity_verification
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `sys_user_identity_verification`;
|
||
CREATE TABLE `sys_user_identity_verification` (
|
||
`verification_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '认证ID',
|
||
`user_id` BIGINT(20) NOT NULL COMMENT '用户ID',
|
||
`real_name` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '真实姓名',
|
||
`id_card_number` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '身份证号(加密)',
|
||
`verification_status` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'PENDING' COMMENT '认证状态: PENDING-未认证, APPROVED-已认证, REJECTED-认证失败',
|
||
`ca_verification_id` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'CA服务返回的认证ID',
|
||
`reject_reason` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'CA服务拒绝原因',
|
||
`verification_time` DATETIME NULL DEFAULT NULL COMMENT 'CA认证时间',
|
||
`create_by` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '创建者',
|
||
`create_time` DATETIME NULL DEFAULT NULL COMMENT '创建时间',
|
||
`update_by` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '更新者',
|
||
`update_time` DATETIME NULL DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`verification_id`) USING BTREE,
|
||
INDEX `idx_user_id` (`user_id`) USING BTREE,
|
||
INDEX `idx_status` (`verification_status`) USING BTREE,
|
||
INDEX `idx_ca_verification_id` (`ca_verification_id`) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户身份认证表' ROW_FORMAT = DYNAMIC;
|
||
|
||
-- ----------------------------
|
||
-- Table structure for sys_verification_audit_log
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `sys_verification_audit_log`;
|
||
CREATE TABLE `sys_verification_audit_log` (
|
||
`log_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
|
||
`verification_type` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '认证类型: ENTERPRISE-企业认证, IDENTITY-身份认证',
|
||
`verification_id` BIGINT(20) NOT NULL COMMENT '认证记录ID',
|
||
`user_id` BIGINT(20) NOT NULL COMMENT '用户ID',
|
||
`old_status` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '原状态',
|
||
`new_status` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '新状态',
|
||
`audit_remark` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '审核备注',
|
||
`operator_id` BIGINT(20) NOT NULL COMMENT '操作人ID',
|
||
`operator_name` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作人姓名',
|
||
`operator_roles` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作人角色列表（逗号分隔）',
|
||
`operation_time` DATETIME NOT NULL COMMENT '操作时间',
|
||
PRIMARY KEY (`log_id`) USING BTREE,
|
||
INDEX `idx_verification` (`verification_type`, `verification_id`) USING BTREE,
|
||
INDEX `idx_user_id` (`user_id`) USING BTREE,
|
||
INDEX `idx_operation_time` (`operation_time`) USING BTREE,
|
||
INDEX `idx_operator_id` (`operator_id`) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '认证审核日志表' ROW_FORMAT = DYNAMIC;
|
||
|
||
-- ----------------------------
|
||
-- Table structure for dc_employee_qr_code
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `dc_employee_qr_code`;
|
||
CREATE TABLE `dc_employee_qr_code` (
|
||
`qr_code_id` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '二维码ID(UUID)',
|
||
`employee_id` BIGINT(20) NOT NULL COMMENT '员工ID',
|
||
`real_name` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '真实姓名',
|
||
`id_card_number` VARCHAR(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '身份证号',
|
||
`generate_time` DATETIME NOT NULL COMMENT '生成时间',
|
||
`expiry_time` DATETIME NOT NULL COMMENT '过期时间',
|
||
`qr_code_status` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'ACTIVE' COMMENT '二维码状态: ACTIVE-有效, EXPIRED-已过期, USED-已使用',
|
||
`verification_status` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '认证状态: PENDING-待认证, APPROVED-已认证, REJECTED-认证失败',
|
||
`ca_verification_id` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'CA服务返回的认证ID',
|
||
`verification_time` DATETIME NULL DEFAULT NULL COMMENT '认证时间',
|
||
PRIMARY KEY (`qr_code_id`) USING BTREE,
|
||
INDEX `idx_employee_id` (`employee_id`) USING BTREE,
|
||
INDEX `idx_status` (`qr_code_status`) USING BTREE,
|
||
INDEX `idx_expiry_time` (`expiry_time`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '员工实名认证二维码表' ROW_FORMAT = DYNAMIC;
|
||
|
||
-- ----------------------------
|
||
-- Table structure for sys_ca_service_config
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `sys_ca_service_config`;
|
||
CREATE TABLE `sys_ca_service_config` (
|
||
`config_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '配置ID',
|
||
`config_key` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '配置键',
|
||
`config_value` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '配置值',
|
||
`config_desc` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '配置描述',
|
||
`is_encrypted` TINYINT(1) NULL DEFAULT 0 COMMENT '是否加密存储',
|
||
`create_time` DATETIME NULL DEFAULT NULL COMMENT '创建时间',
|
||
`update_time` DATETIME NULL DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`config_id`) USING BTREE,
|
||
UNIQUE INDEX `uk_config_key` (`config_key`) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'CA服务配置表' ROW_FORMAT = DYNAMIC;
|
||
|
||
-- ----------------------------
|
||
-- Records of sys_ca_service_config
|
||
-- ----------------------------
|
||
INSERT INTO `sys_ca_service_config` (`config_key`, `config_value`, `config_desc`, `is_encrypted`, `create_time`, `update_time`) VALUES
|
||
('ca.service.url', 'http://localhost:8080/mock-ca', 'CA服务接口地址', 0, NOW(), NOW()),
|
||
('ca.service.timeout', '30000', 'CA服务超时时间(毫秒)', 0, NOW(), NOW()),
|
||
('ca.service.app_id', 'ruoyi-system', 'CA服务应用ID', 0, NOW(), NOW()),
|
||
('ca.service.app_secret', 'mock-secret-key', 'CA服务应用密钥', 0, NOW(), NOW()),
|
||
('ca.service.enabled', 'true', 'CA服务是否启用', 0, NOW(), NOW()),
|
||
('ca.qrcode.validity_hours', '24', '二维码默认有效期(小时)', 0, NOW(), NOW());
|
||
|
||
-- ============================================================================
|
||
-- 2. 创建认证相关角色
|
||
-- ============================================================================
|
||
|
||
-- 插入认证审核员角色
|
||
INSERT INTO `sys_role` (`role_name`, `role_key`, `role_sort`, `data_scope`, `menu_check_strictly`, `dept_check_strictly`, `status`, `del_flag`, `create_by`, `create_time`, `remark`) VALUES
|
||
('认证审核员', 'verification_auditor', 50, '1', 1, 1, '0', '0', 'admin', NOW(), '负责审核企业认证申请');
|
||
|
||
-- 插入认证管理员角色
|
||
INSERT INTO `sys_role` (`role_name`, `role_key`, `role_sort`, `data_scope`, `menu_check_strictly`, `dept_check_strictly`, `status`, `del_flag`, `create_by`, `create_time`, `remark`) VALUES
|
||
('认证管理员', 'verification_manager', 51, '1', 1, 1, '0', '0', 'admin', NOW(), '负责管理所有认证信息');
|
||
|
||
-- ============================================================================
|
||
-- 3. 创建认证相关菜单和权限
|
||
-- ============================================================================
|
||
|
||
-- 插入认证管理目录菜单
|
||
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`) VALUES
|
||
('认证管理', 0, 5, 'verification', NULL, NULL, '', 1, 0, 'M', '0', '0', NULL, 'shield', 'admin', NOW(), '认证管理目录');
|
||
|
||
-- 获取刚插入的认证管理目录的menu_id
|
||
SET @verification_menu_id = LAST_INSERT_ID();
|
||
|
||
-- 插入企业认证审核菜单
|
||
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`) VALUES
|
||
('企业认证审核', @verification_menu_id, 1, 'enterprise', 'system/verification/enterprise/index', NULL, '', 1, 0, 'C', '0', '0', 'system:verification:audit:enterprise', 'building', 'admin', NOW(), '企业认证审核菜单');
|
||
|
||
-- 获取企业认证审核菜单的menu_id
|
||
SET @enterprise_menu_id = LAST_INSERT_ID();
|
||
|
||
-- 插入认证信息管理菜单
|
||
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`) VALUES
|
||
('认证信息管理', @verification_menu_id, 2, 'manage', 'system/verification/manage/index', NULL, '', 1, 0, 'C', '0', '0', 'system:verification:manage', 'list', 'admin', NOW(), '认证信息管理菜单');
|
||
|
||
-- 获取认证信息管理菜单的menu_id
|
||
SET @manage_menu_id = LAST_INSERT_ID();
|
||
|
||
-- 插入企业认证审核的按钮权限
|
||
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`) VALUES
|
||
('企业认证查询', @enterprise_menu_id, 1, '', NULL, NULL, '', 1, 0, 'F', '0', '0', 'system:verification:enterprise:query', '#', 'admin', NOW(), ''),
|
||
('企业认证审核', @enterprise_menu_id, 2, '', NULL, NULL, '', 1, 0, 'F', '0', '0', 'system:verification:enterprise:audit', '#', 'admin', NOW(), ''),
|
||
('企业认证详情', @enterprise_menu_id, 3, '', NULL, NULL, '', 1, 0, 'F', '0', '0', 'system:verification:enterprise:detail', '#', 'admin', NOW(), '');
|
||
|
||
-- 插入认证信息管理的按钮权限
|
||
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`) VALUES
|
||
('认证信息查询', @manage_menu_id, 1, '', NULL, NULL, '', 1, 0, 'F', '0', '0', 'system:verification:view:all', '#', 'admin', NOW(), ''),
|
||
('认证信息导出', @manage_menu_id, 2, '', NULL, NULL, '', 1, 0, 'F', '0', '0', 'system:verification:export', '#', 'admin', NOW(), ''),
|
||
('批量审核', @manage_menu_id, 3, '', NULL, NULL, '', 1, 0, 'F', '0', '0', 'system:verification:batch:audit', '#', 'admin', NOW(), '');
|
||
|
||
-- ============================================================================
|
||
-- 4. 为角色分配菜单权限
|
||
-- ============================================================================
|
||
|
||
-- 为认证审核员角色分配权限
|
||
-- 获取认证审核员角色ID
|
||
SET @auditor_role_id = (SELECT role_id FROM sys_role WHERE role_key = 'verification_auditor');
|
||
|
||
-- 分配企业认证审核菜单权限
|
||
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
|
||
SELECT @auditor_role_id, menu_id
|
||
FROM sys_menu
|
||
WHERE perms IN (
|
||
'system:verification:audit:enterprise',
|
||
'system:verification:enterprise:query',
|
||
'system:verification:enterprise:audit',
|
||
'system:verification:enterprise:detail',
|
||
'system:verification:view:all'
|
||
);
|
||
|
||
-- 为认证管理员角色分配权限
|
||
-- 获取认证管理员角色ID
|
||
SET @manager_role_id = (SELECT role_id FROM sys_role WHERE role_key = 'verification_manager');
|
||
|
||
-- 分配所有认证相关菜单权限
|
||
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
|
||
SELECT @manager_role_id, menu_id
|
||
FROM sys_menu
|
||
WHERE perms IN (
|
||
'system:verification:audit:enterprise',
|
||
'system:verification:manage',
|
||
'system:verification:enterprise:query',
|
||
'system:verification:enterprise:audit',
|
||
'system:verification:enterprise:detail',
|
||
'system:verification:view:all',
|
||
'system:verification:export',
|
||
'system:verification:batch:audit'
|
||
) OR menu_id = @verification_menu_id;
|
||
|
||
-- ============================================================================
|
||
-- 完成
|
||
-- ============================================================================
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|
||
|
||
-- 输出完成信息
|
||
SELECT '用户企业认证、身份认证和员工CA二维码认证功能数据库初始化完成！' AS message;
|