系统权限管理DB设计

不念不忘少年蓝@ 2022-04-12 03:19 292阅读 0赞

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