欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

spring security数据库表结构实例代码

程序员文章站 2024-02-29 08:37:46
pd建模图 建模语句 alter table sys_authorities_resources drop constraint fk_sys_au...

pd建模图

spring security数据库表结构实例代码

建模语句

alter table sys_authorities_resources 
  drop constraint fk_sys_auth_reference_sys_auth; 
alter table sys_authorities_resources 
  drop constraint fk_sys_auth_reference_sys_reso; 
alter table sys_resources 
  drop constraint fk_sys_reso_reference_sys_modu; 
alter table sys_roles_authorities 
  drop constraint fk_sys_role_reference_sys_role; 
alter table sys_roles_authorities 
  drop constraint fk_sys_role_reference_sys_auth; 
alter table sys_roles_moudles 
  drop constraint fk_sys_role_reference_sys_modu; 
alter table sys_roles_moudles 
  drop constraint fk_s_role_reference_sys_role; 
alter table sys_users_roles 
  drop constraint fk_sys_user_reference_sys_user; 
alter table sys_users_roles 
  drop constraint fk_sys_user_reference_sys_role; 
drop table persistent_logins cascade constraints; 
drop table sys_authorities cascade constraints; 
drop table sys_authorities_resources cascade constraints; 
drop table sys_modules cascade constraints; 
drop table sys_resources cascade constraints; 
drop table sys_roles cascade constraints; 
drop table sys_roles_authorities cascade constraints; 
drop table sys_roles_moudles cascade constraints; 
drop table sys_users cascade constraints; 
drop table sys_users_roles cascade constraints; 
/*==============================================================*/ 
/* table: persistent_logins                   */ 
/*==============================================================*/ 
create table persistent_logins ( 
  username       varchar2(64), 
  series        varchar2(64)          not null, 
  token        varchar2(64), 
  last_used      timestamp, 
  constraint pk_persistent_logins primary key (series) 
); 
comment on table persistent_logins is 
'spring remember me 持久化'; 
/*==============================================================*/ 
/* table: sys_authorities                    */ 
/*==============================================================*/ 
create table sys_authorities ( 
  authority_id     varchar2(100)          not null, 
  authority_mark    varchar2(100), 
  authority_name    varchar2(100)          not null, 
  authority_desc    varchar2(200), 
  message       varchar2(100), 
  enable        number, 
  issys        number, 
  module_id      varchar2(100), 
  constraint pk_sys_authorities primary key (authority_id) 
); 
/*==============================================================*/ 
/* table: sys_authorities_resources               */ 
/*==============================================================*/ 
create table sys_authorities_resources ( 
  id          varchar2(100)          not null, 
  resource_id     varchar2(100)          not null, 
  authority_id     varchar2(100)          not null, 
  constraint pk_sys_authorities_resources primary key (id) 
); 
/*==============================================================*/ 
/* table: sys_modules                      */ 
/*==============================================================*/ 
create table sys_modules ( 
  module_id      varchar2(100)          not null, 
  module_name     varchar2(100)          not null, 
  module_desc     varchar2(200), 
  module_type     varchar2(100), 
  parent        varchar2(100), 
  module_url      varchar2(100), 
  i_level       number, 
  leaf         number, 
  application     varchar2(100), 
  controller      varchar2(100), 
  enable        number(1), 
  priority       number, 
  constraint pk_sys_modules primary key (module_id) 
); 
comment on column sys_modules.i_level is 
'1'; 
/*==============================================================*/ 
/* table: sys_resources                     */ 
/*==============================================================*/ 
create table sys_resources ( 
  resource_id     varchar2(100)          not null, 
  resource_type    varchar2(100), 
  resource_name    varchar2(100), 
  resource_desc    varchar2(200), 
  resource_path    varchar2(200), 
  priority       varchar2(100), 
  enable        number, 
  issys        number, 
  module_id      varchar2(100), 
  constraint pk_sys_resources primary key (resource_id) 
); 
comment on column sys_resources.resource_type is 
'url,method'; 
/*==============================================================*/ 
/* table: sys_roles                       */ 
/*==============================================================*/ 
create table sys_roles ( 
  role_id       varchar2(100)          not null, 
  role_name      varchar2(100), 
  role_desc      varchar2(200), 
  enable        number, 
  issys        number, 
  module_id      varchar2(100), 
  constraint pk_sys_roles primary key (role_id) 
); 
/*==============================================================*/ 
/* table: sys_roles_authorities                 */ 
/*==============================================================*/ 
create table sys_roles_authorities ( 
  id          varchar2(100)          not null, 
  authority_id     varchar2(100)          not null, 
  role_id       varchar2(100)          not null, 
  constraint pk_sys_roles_authorities primary key (id) 
); 
/*==============================================================*/ 
/* table: sys_roles_moudles                   */ 
/*==============================================================*/ 
create table sys_roles_moudles ( 
  id          varchar2(100)          not null, 
  module_id      varchar2(100)          not null, 
  role_id       varchar2(100)          not null, 
  constraint pk_sys_roles_moudles primary key (id) 
); 
comment on table sys_roles_moudles is 
'控制角色对模块的访问权,主要用于生成菜单'; 
/*==============================================================*/ 
/* table: sys_users                       */ 
/*==============================================================*/ 
create table sys_users ( 
  user_id       varchar2(100)          not null, 
  username       varchar2(100)          not null, 
  name         varchar2(100), 
  password       varchar2(100)          not null, 
  dt_create      date              default sysdate, 
  last_login      date, 
  deadline       date, 
  login_ip       varchar2(100), 
  v_qzjgid       varchar2(100), 
  v_qzjgmc       varchar2(100), 
  dep_id        varchar2(100), 
  dep_name       varchar2(100), 
  enabled       number, 
  account_non_expired number, 
  account_non_locked  number, 
  credentials_non_expired number, 
  constraint pk_sys_users primary key (user_id) 
); 
/*==============================================================*/ 
/* table: sys_users_roles                    */ 
/*==============================================================*/ 
create table sys_users_roles ( 
  id          varchar2(100)          not null, 
  role_id       varchar2(100)          not null, 
  user_id       varchar2(100)          not null, 
  constraint pk_sys_users_roles primary key (id) 
); 
alter table sys_authorities_resources 
  add constraint fk_sys_auth_reference_sys_auth foreign key (authority_id) 
   references sys_authorities (authority_id); 
alter table sys_authorities_resources 
  add constraint fk_sys_auth_reference_sys_reso foreign key (resource_id) 
   references sys_resources (resource_id); 
alter table sys_resources 
  add constraint fk_sys_reso_reference_sys_modu foreign key (module_id) 
   references sys_modules (module_id); 
alter table sys_roles_authorities 
  add constraint fk_sys_role_reference_sys_role foreign key (role_id) 
   references sys_roles (role_id); 
alter table sys_roles_authorities 
  add constraint fk_sys_role_reference_sys_auth foreign key (authority_id) 
   references sys_authorities (authority_id); 
alter table sys_roles_moudles 
  add constraint fk_sys_role_reference_sys_modu foreign key (module_id) 
   references sys_modules (module_id); 
alter table sys_roles_moudles 
  add constraint fk_s_role_reference_sys_role foreign key (role_id) 
   references sys_roles (role_id); 
alter table sys_users_roles 
  add constraint fk_sys_user_reference_sys_user foreign key (user_id) 
   references sys_users (user_id); 
alter table sys_users_roles 
  add constraint fk_sys_user_reference_sys_role foreign key (role_id) 
   references sys_roles (role_id); 

总结

以上所述是小编给大家介绍的spring security数据库表结构实例代码,希望对大家有所帮助