generated from youfool-project/youfool-prj-springboot3-template
165 lines
7.5 KiB
SQL
165 lines
7.5 KiB
SQL
-- ============================================================================
|
||
-- OARMS - 系统管理模块 DDL
|
||
-- Database: DM8 (达梦)
|
||
-- Schema: OARMS
|
||
-- Version: V11.0.0
|
||
-- Date: 2026-05-24
|
||
-- Description: 用户/角色/权限管理（RBAC）
|
||
-- ============================================================================
|
||
|
||
-- ----------------------------------------------------------------------------
|
||
-- 1. sys_user - 系统用户表
|
||
-- ----------------------------------------------------------------------------
|
||
CREATE TABLE OARMS.SYS_USER (
|
||
id VARCHAR(50) NOT NULL,
|
||
username VARCHAR(50) NOT NULL,
|
||
password VARCHAR(100) NOT NULL,
|
||
real_name VARCHAR(50),
|
||
phone VARCHAR(20),
|
||
org_name VARCHAR(100),
|
||
district_code VARCHAR(20),
|
||
status TINYINT NOT NULL DEFAULT 1,
|
||
create_by VARCHAR(50),
|
||
create_time TIMESTAMP,
|
||
create_name VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
update_time TIMESTAMP,
|
||
update_name VARCHAR(50),
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE UNIQUE INDEX IDX_SYS_USER_USERNAME ON OARMS.SYS_USER (USERNAME);
|
||
CREATE INDEX IDX_SYS_USER_STATUS ON OARMS.SYS_USER (STATUS);
|
||
CREATE INDEX IDX_SYS_USER_DISTRICT ON OARMS.SYS_USER (DISTRICT_CODE);
|
||
|
||
COMMENT ON TABLE OARMS.SYS_USER IS '系统用户';
|
||
COMMENT ON COLUMN OARMS.SYS_USER.ID IS '主键ID';
|
||
COMMENT ON COLUMN OARMS.SYS_USER.USERNAME IS '登录用户名';
|
||
COMMENT ON COLUMN OARMS.SYS_USER.PASSWORD IS '密码(BCrypt)';
|
||
COMMENT ON COLUMN OARMS.SYS_USER.REAL_NAME IS '真实姓名';
|
||
COMMENT ON COLUMN OARMS.SYS_USER.PHONE IS '联系电话';
|
||
COMMENT ON COLUMN OARMS.SYS_USER.ORG_NAME IS '所属单位';
|
||
COMMENT ON COLUMN OARMS.SYS_USER.DISTRICT_CODE IS '所属区域编码';
|
||
COMMENT ON COLUMN OARMS.SYS_USER.STATUS IS '状态(1=正常,0=停用)';
|
||
COMMENT ON COLUMN OARMS.SYS_USER.CREATE_BY IS '创建人ID';
|
||
COMMENT ON COLUMN OARMS.SYS_USER.CREATE_TIME IS '创建时间';
|
||
COMMENT ON COLUMN OARMS.SYS_USER.CREATE_NAME IS '创建人姓名';
|
||
COMMENT ON COLUMN OARMS.SYS_USER.UPDATE_BY IS '更新人ID';
|
||
COMMENT ON COLUMN OARMS.SYS_USER.UPDATE_TIME IS '更新时间';
|
||
COMMENT ON COLUMN OARMS.SYS_USER.UPDATE_NAME IS '更新人姓名';
|
||
|
||
-- ----------------------------------------------------------------------------
|
||
-- 2. sys_role - 系统角色表
|
||
-- ----------------------------------------------------------------------------
|
||
CREATE TABLE OARMS.SYS_ROLE (
|
||
id VARCHAR(50) NOT NULL,
|
||
role_code VARCHAR(30) NOT NULL,
|
||
role_name VARCHAR(50) NOT NULL,
|
||
description VARCHAR(200),
|
||
status TINYINT NOT NULL DEFAULT 1,
|
||
create_by VARCHAR(50),
|
||
create_time TIMESTAMP,
|
||
create_name VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
update_time TIMESTAMP,
|
||
update_name VARCHAR(50),
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE UNIQUE INDEX IDX_SYS_ROLE_CODE ON OARMS.SYS_ROLE (ROLE_CODE);
|
||
|
||
COMMENT ON TABLE OARMS.SYS_ROLE IS '系统角色';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE.ID IS '主键ID';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE.ROLE_CODE IS '角色编码';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE.ROLE_NAME IS '角色名称';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE.DESCRIPTION IS '角色描述';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE.STATUS IS '状态(1=正常,0=停用)';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE.CREATE_BY IS '创建人ID';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE.CREATE_TIME IS '创建时间';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE.CREATE_NAME IS '创建人姓名';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE.UPDATE_BY IS '更新人ID';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE.UPDATE_TIME IS '更新时间';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE.UPDATE_NAME IS '更新人姓名';
|
||
|
||
-- ----------------------------------------------------------------------------
|
||
-- 3. sys_user_role - 用户角色关联表
|
||
-- ----------------------------------------------------------------------------
|
||
CREATE TABLE OARMS.SYS_USER_ROLE (
|
||
id VARCHAR(50) NOT NULL,
|
||
user_id VARCHAR(50) NOT NULL,
|
||
role_id VARCHAR(50) NOT NULL,
|
||
create_by VARCHAR(50),
|
||
create_time TIMESTAMP,
|
||
create_name VARCHAR(50),
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE INDEX IDX_SYS_USER_ROLE_UID ON OARMS.SYS_USER_ROLE (USER_ID);
|
||
CREATE INDEX IDX_SYS_USER_ROLE_RID ON OARMS.SYS_USER_ROLE (ROLE_ID);
|
||
CREATE UNIQUE INDEX IDX_SYS_USER_ROLE_UK ON OARMS.SYS_USER_ROLE (USER_ID, ROLE_ID);
|
||
|
||
COMMENT ON TABLE OARMS.SYS_USER_ROLE IS '用户角色关联';
|
||
COMMENT ON COLUMN OARMS.SYS_USER_ROLE.ID IS '主键ID';
|
||
COMMENT ON COLUMN OARMS.SYS_USER_ROLE.USER_ID IS '用户ID';
|
||
COMMENT ON COLUMN OARMS.SYS_USER_ROLE.ROLE_ID IS '角色ID';
|
||
COMMENT ON COLUMN OARMS.SYS_USER_ROLE.CREATE_BY IS '创建人ID';
|
||
COMMENT ON COLUMN OARMS.SYS_USER_ROLE.CREATE_TIME IS '创建时间';
|
||
COMMENT ON COLUMN OARMS.SYS_USER_ROLE.CREATE_NAME IS '创建人姓名';
|
||
|
||
-- ----------------------------------------------------------------------------
|
||
-- 4. sys_permission - 系统权限表
|
||
-- ----------------------------------------------------------------------------
|
||
CREATE TABLE OARMS.SYS_PERMISSION (
|
||
id VARCHAR(50) NOT NULL,
|
||
perm_code VARCHAR(50) NOT NULL,
|
||
perm_name VARCHAR(100) NOT NULL,
|
||
module VARCHAR(30),
|
||
create_by VARCHAR(50),
|
||
create_time TIMESTAMP,
|
||
create_name VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
update_time TIMESTAMP,
|
||
update_name VARCHAR(50),
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE UNIQUE INDEX IDX_SYS_PERM_CODE ON OARMS.SYS_PERMISSION (PERM_CODE);
|
||
CREATE INDEX IDX_SYS_PERM_MODULE ON OARMS.SYS_PERMISSION (MODULE);
|
||
|
||
COMMENT ON TABLE OARMS.SYS_PERMISSION IS '系统权限';
|
||
COMMENT ON COLUMN OARMS.SYS_PERMISSION.ID IS '主键ID';
|
||
COMMENT ON COLUMN OARMS.SYS_PERMISSION.PERM_CODE IS '权限编码';
|
||
COMMENT ON COLUMN OARMS.SYS_PERMISSION.PERM_NAME IS '权限名称';
|
||
COMMENT ON COLUMN OARMS.SYS_PERMISSION.MODULE IS '所属模块';
|
||
COMMENT ON COLUMN OARMS.SYS_PERMISSION.CREATE_BY IS '创建人ID';
|
||
COMMENT ON COLUMN OARMS.SYS_PERMISSION.CREATE_TIME IS '创建时间';
|
||
COMMENT ON COLUMN OARMS.SYS_PERMISSION.CREATE_NAME IS '创建人姓名';
|
||
COMMENT ON COLUMN OARMS.SYS_PERMISSION.UPDATE_BY IS '更新人ID';
|
||
COMMENT ON COLUMN OARMS.SYS_PERMISSION.UPDATE_TIME IS '更新时间';
|
||
COMMENT ON COLUMN OARMS.SYS_PERMISSION.UPDATE_NAME IS '更新人姓名';
|
||
|
||
-- ----------------------------------------------------------------------------
|
||
-- 5. sys_role_permission - 角色权限关联表
|
||
-- ----------------------------------------------------------------------------
|
||
CREATE TABLE OARMS.SYS_ROLE_PERMISSION (
|
||
id VARCHAR(50) NOT NULL,
|
||
role_id VARCHAR(50) NOT NULL,
|
||
perm_id VARCHAR(50) NOT NULL,
|
||
create_by VARCHAR(50),
|
||
create_time TIMESTAMP,
|
||
create_name VARCHAR(50),
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE INDEX IDX_SYS_ROLE_PERM_RID ON OARMS.SYS_ROLE_PERMISSION (ROLE_ID);
|
||
CREATE INDEX IDX_SYS_ROLE_PERM_PID ON OARMS.SYS_ROLE_PERMISSION (PERM_ID);
|
||
CREATE UNIQUE INDEX IDX_SYS_ROLE_PERM_UK ON OARMS.SYS_ROLE_PERMISSION (ROLE_ID, PERM_ID);
|
||
|
||
COMMENT ON TABLE OARMS.SYS_ROLE_PERMISSION IS '角色权限关联';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE_PERMISSION.ID IS '主键ID';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE_PERMISSION.ROLE_ID IS '角色ID';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE_PERMISSION.PERM_ID IS '权限ID';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE_PERMISSION.CREATE_BY IS '创建人ID';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE_PERMISSION.CREATE_TIME IS '创建时间';
|
||
COMMENT ON COLUMN OARMS.SYS_ROLE_PERMISSION.CREATE_NAME IS '创建人姓名';
|