系统权限管理DB设计

-- Create table
create table SEC_RESOURCE
(
  id          VARCHAR2(255) not null,
  type        VARCHAR2(255),
  value       VARCHAR2(255),
  key         VARCHAR2(255),
  description VARCHAR2(1000)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table SEC_RESOURCE
  add primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
comment on column SEC_RESOURCE.value
  is '资源url,如/api/divisions/**#GET';

  -- Create table
create table SEC_ROLE
(
  id       VARCHAR2(255) not null,
  disabled NUMBER(1) not null,
  name     VARCHAR2(255),
  resc     VARCHAR2(255),
  p_id     VARCHAR2(255)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table SEC_ROLE
  add primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table SEC_ROLE
  add constraint FK67C567C4EC81D10F foreign key (P_ID)
  references SEC_ROLE (ID);



  -- Create table
create table SEC_ROLE_RESOURCE
(
  role_id     VARCHAR2(255) not null,
  resource_id VARCHAR2(255) not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table SEC_ROLE_RESOURCE
  add primary key (ROLE_ID, RESOURCE_ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table SEC_ROLE_RESOURCE
  add constraint FK7AE8C64921B1D649 foreign key (RESOURCE_ID)
  references SEC_RESOURCE (ID);
alter table SEC_ROLE_RESOURCE
  add constraint FK7AE8C6493E5F0B49 foreign key (ROLE_ID)
  references SEC_ROLE (ID);


  -- Create table
create table SEC_USER
(
  id          VARCHAR2(255) not null,
  disabled    NUMBER(1) not null,
  name        VARCHAR2(255),
  password    VARCHAR2(255),
  username    VARCHAR2(255),
  divisionid  VARCHAR2(255),
  email       VARCHAR2(255),
  gender      VARCHAR2(255),
  mobilephone VARCHAR2(255),
  telephone   VARCHAR2(255),
  usertype    NUMBER(10) not null,
  valid       NUMBER(1) not null,
  spellname   VARCHAR2(255),
  theme       VARCHAR2(50)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table SEC_USER
  add primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table SEC_USER
  add constraint UNIQUE_USERNAME unique (USERNAME)
  using index 
  tablespace OMNIBDTBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


  -- Create table
create table SEC_USER_ROLE
(
  role_id VARCHAR2(255) not null,
  user_id VARCHAR2(255) not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table SEC_USER_ROLE
  add primary key (ROLE_ID, USER_ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table SEC_USER_ROLE
  add constraint FK7DE039FC3E5F0B49 foreign key (ROLE_ID)
  references SEC_ROLE (ID);
alter table SEC_USER_ROLE
  add constraint FK7DE039FCE8DC4DE9 foreign key (USER_ID)
  references SEC_USER (ID);