PowerDesigner_数据库设计_通用的RBAC权限系统

通用的RBAC权限系统

  本文只介绍权限系统的设计,暂不涉及PowerDesigner工具的使用,网上有很多这里不再多做介绍.

1. 简单的介绍下RBAC

    基于角色的权限访问控制（Role-Based Access Control）作为传统访问控制（自主访问，强制访问）的有前景的代替受到广泛的关注。在RBAC中，权限与角色相关联，用户通过成为适当角色的成员而得到这些角色的权限。这就极大地简化了权限的管理。

  a. 最简单的RBAC五张表

表名	建表名称	描述
人员表	s_user_t	存储系统人员信息
角色表	s_role_t	存储系统角色信息
资源表	s_resource_t	存储系统资源信息
人员角色关系表	s_userrole_t	存储系统人员和角色之间的关系
角色资源关系表	s_roleresource_t	存储系统角色和系统资源的关系

  b. 如果你还想要更多

    仔细观察一下上面的图片也许你就明白为什么叫基于角色的权限访问控制了,因为把用户和权限关联起来的唯一基础表就是角色表,角色表通过关系表分别与人员表和资源表进行了关联.如果你有了这5张表基本可以满足一个不太复杂的权限系统了,可以用一句话表示:麻雀虽小五脏俱全.
    当然如果你有更复杂的需求,也可以以此为基础继续扩展,如:
     1, 需要组织机构,系统需要展现出用户的组织关系,可以在人员表旁边新建一个组织结构表和组织结构人员关系维护表.如果你要做的权限系统非常庞大.
     2,市下有多个区县,总公司下有多个分公司,分公司下还有多个群组,可以在角色上面继续加建:用户组,区县组,分公司组等,总之万变不离之宗,有这五张表为基础就可包罗万象满足一般系统的绝大部分需求.

2. 建表语句(本例使用数据库为oracle)

s_user_t

/*==============================================================*/
/* Table: s_user_t                                              */
/*==============================================================*/
create table s_user_t 
(
   id                   INTEGER              not null,
   code                 VARCHAR2(50)         not null,
   name                 VARCHAR2(50)         not null,
   loginname            VARCHAR2(50)         not null,
   password             VARCHAR2(200)        not null,
   phonenumber          INTEGER,
   phone                VARCHAR2(20),
   email                VARCHAR2(50),
   headimage            VARCHAR2(200),
   idcard               VARCHAR2(30),
   sex                  INTEGER,
   address              VARCHAR2(100),
   weixinid             VARCHAR2(100),
   validflag            INTEGER              not null,
   usermemo             VARCHAR2(100),
   sysmemo              VARCHAR2(100),
   sn                   INTEGER,
   createname           VARCHAR2(50),
   createtime           DATE,
   updatename           VARCHAR2(50),
   updatetime           TIMESTAMP,
   validname            VARCHAR2(50),
   validtime            DATE,
   memo1                VARCHAR2(200),
   memo2                VARCHAR2(200),
   memo3                VARCHAR2(200),
   memo4                VARCHAR2(200),
   memo5                VARCHAR2(200),
   constraint PK_S_USER_T primary key (id)
)

comment on table s_user_t is
'人员表'
/

comment on column s_user_t.id is
'id'
/

comment on column s_user_t.code is
'编码name'
/

comment on column s_user_t.name is
'姓名'
/

comment on column s_user_t.loginname is
'登陆名'
/

comment on column s_user_t.password is
'密码'
/

comment on column s_user_t.phonenumber is
'手机号(数字)'
/

comment on column s_user_t.phone is
'固定电话'
/

comment on column s_user_t.email is
'邮箱'
/

comment on column s_user_t.headimage is
'头像'
/

comment on column s_user_t.idcard is
'身份证号'
/

comment on column s_user_t.sex is
'性别'
/

comment on column s_user_t.address is
'家庭住址'
/

comment on column s_user_t.weixinid is
'微信id'
/

comment on column s_user_t.validflag is
'有效标记'
/

comment on column s_user_t.usermemo is
'用户备注'
/

comment on column s_user_t.sysmemo is
'系统备注'
/

comment on column s_user_t.sn is
'排序号'
/

comment on column s_user_t.createname is
'创建人'
/

comment on column s_user_t.createtime is
'创建时间'
/

comment on column s_user_t.updatename is
'更新人'
/

comment on column s_user_t.updatetime is
'更新时间'
/

comment on column s_user_t.validname is
'作废人'
/

comment on column s_user_t.validtime is
'作废时间'
/

comment on column s_user_t.memo1 is
'memo1'
/

comment on column s_user_t.memo2 is
'memo2'
/

comment on column s_user_t.memo3 is
'memo3'
/

comment on column s_user_t.memo4 is
'memo4'
/

comment on column s_user_t.memo5 is
'memo5'
/

s_role_t

/*==============================================================*/
/* Table: s_role_t                                              */
/*==============================================================*/
create table s_role_t 
(
   id                   INTEGER              not null,
   code                 VARCHAR2(50),
   name                 VARCHAR2(50),
   describe             VARCHAR2(200),
   validflag            INTEGER,
   usermemo             VARCHAR2(100),
   sysmemo              VARCHAR2(100),
   sn                   INTEGER,
   createname           VARCHAR2(100),
   createtime           DATE,
   updatename           VARCHAR2(100),
   updatetime           DATE,
   validname            VARCHAR2(100),
   validtime            DATE,
   memo1                VARCHAR2(200),
   memo2                VARCHAR2(200),
   memo3                VARCHAR2(200),
   memo4                VARCHAR2(200),
   memo5                VARCHAR2(200),
   constraint PK_S_ROLE_T primary key (id)
);

comment on table s_role_t is
'角色表';

comment on column s_role_t.id is
'id';

comment on column s_role_t.code is
'角色编码';

comment on column s_role_t.name is
'角色名称';

comment on column s_role_t.describe is
'角色描述';

comment on column s_role_t.validflag is
'有效标记';

comment on column s_role_t.usermemo is
'用户备注';

comment on column s_role_t.sysmemo is
'系统备注';

comment on column s_role_t.sn is
'排序号';

comment on column s_role_t.createname is
'创建人';

comment on column s_role_t.createtime is
'创建时间';

comment on column s_role_t.updatename is
'更新人';

comment on column s_role_t.updatetime is
'更新时间';

comment on column s_role_t.validname is
'作废人';

comment on column s_role_t.validtime is
'作废时间';

comment on column s_role_t.memo1 is
'memo1';

comment on column s_role_t.memo2 is
'memo2';

comment on column s_role_t.memo3 is
'memo3';

comment on column s_role_t.memo4 is
'memo4';

comment on column s_role_t.memo5 is
'memo5';

s_resource_t

/*==============================================================*/
/* Table: s_resource_t                                          */
/*==============================================================*/
create table s_resource_t 
(
   id                   INTEGER              not null,
   resourcecode         VARCHAR2(50),
   resourcename         VARCHAR2(50),
   resourceurl          VARCHAR2(200),
   resourcetype         INTEGER,
   fid                  INTEGER,
   display              INTEGER,
   resourceicon         VARCHAR2(100),
   validflag            INTEGER,
   usermemo             VARCHAR2(100),
   sysmemo              VARCHAR2(100),
   sn                   INTEGER,
   createname           VARCHAR2(100),
   createtime           DATE,
   updatename           VARCHAR2(100),
   updatetime           DATE,
   validname            VARCHAR2(100),
   validtime            DATE,
   memo1                VARCHAR2(200),
   memo2                VARCHAR2(200),
   memo3                VARCHAR2(200),
   memo4                VARCHAR2(200),
   memo5                VARCHAR2(200),
   constraint PK_S_RESOURCE_T primary key (id)
);

comment on table s_resource_t is
'资源表';

comment on column s_resource_t.id is
'id';

comment on column s_resource_t.resourcecode is
'资源编码';

comment on column s_resource_t.resourcename is
'资源名称';

comment on column s_resource_t.resourceurl is
'资源路径';

comment on column s_resource_t.resourcetype is
'资源类型(模块,子菜单)';

comment on column s_resource_t.fid is
'父id';

comment on column s_resource_t.display is
'是否显示';

comment on column s_resource_t.resourceicon is
'资源图标';

comment on column s_resource_t.validflag is
'作废标记';

comment on column s_resource_t.usermemo is
'用户备注';

comment on column s_resource_t.sysmemo is
'系统备注';

comment on column s_resource_t.sn is
'排序号';

comment on column s_resource_t.createname is
'创建人';

comment on column s_resource_t.createtime is
'创建时间';

comment on column s_resource_t.updatename is
'更新人';

comment on column s_resource_t.updatetime is
'更新时间';

comment on column s_resource_t.validname is
'作废人';

comment on column s_resource_t.validtime is
'作废时间';

comment on column s_resource_t.memo1 is
'memo1';

comment on column s_resource_t.memo2 is
'memo2';

comment on column s_resource_t.memo3 is
'memo3';

comment on column s_resource_t.memo4 is
'memo4';

comment on column s_resource_t.memo5 is
'memo5';

s_userrole_t

/*==============================================================*/
/* Table: s_userrole_t                                          */
/*==============================================================*/
create table s_userrole_t 
(
   id                   INTEGER              not null,
   userid               INTEGER,
   roleid               INTEGER,
   createname           VARCHAR2(50)         default '1',
   createtime           DATE                 default '1',
   constraint PK_S_USERROLE_T primary key (id)
);

comment on table s_userrole_t is
'人员和角色关系表';

comment on column s_userrole_t.id is
'id';

comment on column s_userrole_t.userid is
'用户id';

comment on column s_userrole_t.roleid is
'角色id';

comment on column s_userrole_t.createname is
'创建人';

comment on column s_userrole_t.createtime is
'创建时间';

s_roleresource_t

/*==============================================================*/
/* Table: s_roleresource_t                                      */
/*==============================================================*/
create table s_roleresource_t 
(
   id                   INTEGER              not null,
   roleid               INTEGER,
   resourceid           INTEGER,
   createname           VARCHAR2(50),
   createtime           DATE,
   constraint PK_S_ROLERESOURCE_T primary key (id)
);

comment on table s_roleresource_t is
'角色和资源关系表';

comment on column s_roleresource_t.id is
'id';

comment on column s_roleresource_t.roleid is
'角色id';

comment on column s_roleresource_t.resourceid is
'资源id';

comment on column s_roleresource_t.createname is
'创建人';

comment on column s_roleresource_t.createtime is
'创建时间';

你也可以上码云直接下载PowerDesigner源文件: https://gitee.com/gaoshaomin/opensystem.git