一）6张表

1用户表 SMSE_USER

2角色表 SMSE_ROLE

3菜单表 SMSE_MENU

4用户角色关联表 SMSE_USER_ROLE_LINK

5角色菜单关联表 SMSE_ROLE_MENU_LINK

6进入权限表 SMSE_ACCESS_PERMISSION

 

sql：

/*==============================================================*/
/* Table: SMSE_USER                                             */
/*==============================================================*/
create table SMSE_USER 
(
   ID                   varchar2(32)         not null,
   NAME                 varchar2(200 char),
   USERNAME             varchar2(200),
   PASSWORD             varchar2(200),
   MOBILE               varchar2(200),
   EMAIL                varchar2(200),
   constraint PK_SMSE_USER primary key (ID)
);

comment on column SMSE_USER.ID is
'主键 sys_guid()';

comment on column SMSE_USER.NAME is
'名称';

comment on column SMSE_USER.USERNAME is
'用户名';

comment on column SMSE_USER.PASSWORD is
'用户密码';

comment on column SMSE_USER.MOBILE is
'手机号';

comment on column SMSE_USER.EMAIL is
'电子邮箱';

/*==============================================================*/
/* Table: SMSE_ROLE                                             */
/*==============================================================*/
create table SMSE_ROLE 
(
   ID                   varchar2(32)         not null,
   NAME                 varchar2(200 char),
   ROLE                 varchar2(200),
   constraint PK_SMSE_ROLE primary key (ID)
);

comment on column SMSE_ROLE.ID is
'主键sys_guid()';

comment on column SMSE_ROLE.NAME is
'名称';

comment on column SMSE_ROLE.ROLE is
'角色';

/*==============================================================*/
/* Table: SMSE_MENU                                             */
/*==============================================================*/
create table SMSE_MENU 
(
   ID                   varchar2(32)         not null,
   NAME                 varchar2(200 char),
   URL                  varchar2(200),
   ICON                 varchar2(200),
   SHOW                 varchar2(200),
   PID                  CHAR(32),
   SORT                 INTEGER,
   constraint PK_SMSE_MENU primary key (ID)
);

comment on column SMSE_MENU.ID is
'主键 sys_guid()';

comment on column SMSE_MENU.NAME is
'菜单名称';

comment on column SMSE_MENU.URL is
'Action 访问地址';

comment on column SMSE_MENU.ICON is
'图标名 如: sys.png';

comment on column SMSE_MENU.SHOW is
'是否显示 0：不显示，1：显示';

comment on column SMSE_MENU.PID is
'父菜单ID';

comment on column SMSE_MENU.SORT is
'排序';

/*==============================================================*/
/* Table: SMSE_USER_ROLE_LINK                                   */
/*==============================================================*/
create table SMSE_USER_ROLE_LINK 
(
   USER_ID              varchar2(32),
   ROLE_ID              varchar2(32)
);

comment on column SMSE_USER_ROLE_LINK.USER_ID is
'用户ID';

comment on column SMSE_USER_ROLE_LINK.ROLE_ID is
'角色ID';

/*==============================================================*/
/* Table: SMSE_ROLE_MENU_LINK                                   */
/*==============================================================*/
create table SMSE_ROLE_MENU_LINK 
(
   ROLE_ID              varchar2(32),
   MENU_ID              varchar2(32)
);

comment on column SMSE_ROLE_MENU_LINK.ROLE_ID is
'角色ID';

comment on column SMSE_ROLE_MENU_LINK.MENU_ID is
'菜单ID';

/*==============================================================*/
/* Table: SMSE_ACCESS_PERMISSION                                */
/*==============================================================*/
create table SMSE_ACCESS_PERMISSION 
(
   ID                   varchar2(32)         not null,
   NAME                 varchar2(200 char),
   URL                  varchar2(200),
   MENU_ID              varchar2(32),
   constraint PK_SMSE_ACCESS_PERMISSION primary key (ID)
);

comment on column SMSE_ACCESS_PERMISSION.ID is
'主键sys_guid()';

comment on column SMSE_ACCESS_PERMISSION.NAME is
'资源名称';

comment on column SMSE_ACCESS_PERMISSION.URL is
'Action 访问地址';

comment on column SMSE_ACCESS_PERMISSION.MENU_ID is
'菜单ID';

 

转载于:https://www.cnblogs.com/zno2/p/4861448.html