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);