权限管理系统数据库设计

系统结构

  • domain 业务逻辑层
  • common 通用逻辑层
  • entity 数据库

domain 业务说明

auth

  • 用户登录
  • token 鉴定

user

  • 用户列表查询
  • 用户详情查询
  • 用户信息更新

role

  • 查询角色列表
  • 查询角色详情
  • 创建角色
  • 更新角色信息

group

  • 查询组列表
  • 创建组

assignment

  • 查询用户下角色
  • 为用户分配角色
  • 查询角色下权限
  • 为角色分配权限
  • 查询组下角色
  • 为角色分配组

endpoint

  • 查询权限列表
  • 查询权限详情
  • 创建权限
#

2021 年

  • 创建数据库
    CREATE DATABASE IF NOT EXISTS rebecca DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
  • 进入数据库
    show databases;
    use rebecca;
    show tables;

用户表

  • 创建用户表
    drop table if exists users;

    CREATE TABLE IF NOT EXISTS `users`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(40) NOT NULL,
   `pwd` VARCHAR(40) DEFAULT '',
   `status` TINYINT NOT NULL DEFAULT 0,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • 插入一个用户
    INSERT INTO users ( name )
                       VALUES
                       ( 'sunny' );
  • 查看可用用户
    select * from users where status <>1;
  • 更新用户
    UPDATE users SET name='用户A' where id=1;
  • 删除用户
    UPDATE users SET status=1 where id=1;

角色表

  • 创建角色表
    drop table if exists roles;

    CREATE TABLE IF NOT EXISTS `roles`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(40) NOT NULL,
   `status` TINYINT NOT NULL DEFAULT 0,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • 插入一个角色
    INSERT INTO roles ( name )
                       VALUES
                       ( 'admin' );
  • 查看可用角色
    select * from roles where status <>1;
  • 更新用户
    UPDATE roles SET name='超级管理员' where id=1;
  • 删除用户
    UPDATE roles SET status=1 where id=1;

权限表

  • 创建权限表
    drop table if exists authorities;

    CREATE TABLE IF NOT EXISTS `authorities`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(40) NOT NULL,
   `status` TINYINT NOT NULL DEFAULT 0,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • 插入权限
    INSERT INTO authorities ( name )
                       VALUES
                       ( '管理员' );
    INSERT INTO authorities ( name )
                       VALUES
                       ( '开发者' );
    INSERT INTO authorities ( name )
                       VALUES
                       ( '游客' );
  • 查看可用权限
    select * from authorities where status <>1;
  • 更新权限
    UPDATE authorities SET name='管理员' where id=1;
  • 删除权限
    UPDATE authorities SET status=1 where id=1;

用户角色关系表

  • 创建关系表
    drop table if exists user_role_relations;

    CREATE TABLE IF NOT EXISTS `user_role_relations`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `user_id` INT UNSIGNED NOT NULL,
   `role_id`  INT UNSIGNED NOT NULL,
    `status` TINYINT NOT NULL DEFAULT 0,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • 创建用户 - 角色关系
    INSERT INTO user_role_relations ( user_id,role_id )
                       VALUES
                       ( 1, 1 );
  • 查看用户 - 角色关系
    select * from  user_role_relations where status <> 1
  • 查看详细的用户角色关系
    select ur.id id, u.name user_name, r.name role_name from users u,roles r,user_role_relations ur where ur.user_id = u.id and ur.role_id = r.id;

角色权限关系表

  • 创建关系表
    drop table if exists role_authority_relations;

    CREATE TABLE IF NOT EXISTS `role_authority_relations`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `role_id` INT UNSIGNED NOT NULL,
   `authority_id`  INT UNSIGNED NOT NULL,
    `status` TINYINT NOT NULL DEFAULT 0,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • 创建用户 - 角色关系
    INSERT INTO role_authority_relations ( role_id,authority_id  )
                       VALUES
                       ( 1, 1 );
  INSERT INTO role_authority_relations ( role_id,authority_id  )
                       VALUES
                       ( 2, 2 );
  • 查看用户 - 角色关系
    select * from  role_authority_relations where status <> 1
  • 查看详细的用户角色关系
    select ra.id id, r.name role_name, a.name authority_name from roles r, authorities a,role_authority_relations ra where ra.role_id = r.id and ra.authority_id = a.id;

查询三者的关系

  • 方案一
    select  u.name user_name, r.name role_name, a.name authority_name from users u,roles r,authorities a, user_role_relations ur,role_authority_relations ra where ra.role_id = r.id and ra.authority_id = a.id and  ur.user_id = u.id and ur.role_id = r.id;
  • 方案二

功能表

    drop table if exists endpoints;

    CREATE TABLE IF NOT EXISTS `endpoints`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(50) NOT NULL,
   `methods` VARCHAR(50) NOT NULL,
   `description` VARCHAR(100) ,
   `url` VARCHAR(100) NOT NULL,
   `parent_id` INT UNSIGNED,
    `status` TINYINT NOT NULL DEFAULT 0,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询所有功能

select * from endpoints;

插入功能

    INSERT INTO endpoints ( name,methods,url ,description) VALUES ( '推广页','get','/promote2', '访问推广页权限');

    INSERT INTO endpoints ( name,methods,url ,description) VALUES ( '工具箱所有者','get,post,put,patch','/tools', '访问推广页权限');

    INSERT INTO endpoints ( name,methods,url ,description, parent_id) VALUES ( '推广页广告计划','get','/promote2/advertisements', '访问广告计划权限', 1);

      INSERT INTO endpoints ( name,methods,url ,description, parent_id) VALUES ( '推广页广告创意','get','/promote2/creatives', '访问广告创意权限', 1);

    INSERT INTO endpoints ( name,methods,url ,description, parent_id) VALUES ( '推广页广告系列','get','/promote2/campaigns', '访问广告系列权限', 1);

        INSERT INTO endpoints ( name,methods,url ,description, parent_id) VALUES ( '工具页应用管理','get','/tools/launch/appcenter', '访问应用管理页面', 2);

踩坑：描述 description 千万不要用简写 desc，因为 mysql 中的 desc 是排序的意思，属于系统关键字。

更新功能

  UPDATE endpoints SET name='推广页广告系列' where id=5;

权限操作关联表

    drop table if exists authority_endpoint_relations;

    CREATE TABLE IF NOT EXISTS `authority_endpoint_relations`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `endpoint_id` INT UNSIGNED NOT NULL,
   `authority_id`  INT UNSIGNED NOT NULL,
    `status` TINYINT NOT NULL DEFAULT 0,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询

    select * from authority_endpoint_relations;

插入权限操作

    insert into authority_endpoint_relations (authority_id, endpoint_id) values (1, 1)
     insert into authority_endpoint_relations (authority_id, endpoint_id) values (1, 2)
     insert into authority_endpoint_relations (authority_id, endpoint_id) values (2, 2)
     insert into authority_endpoint_relations (authority_id, endpoint_id) values (3, 4)

连续详细情况

select a.name authority_name, e.name endpoint_name,e.description, e.url from authority_endpoint_relations ae,authorities a,endpoints e where ae.authority_id=a.id and ae.endpoint_id = e.id;

select a.name authority_name, e.name endpoint_name, e.description, e.url ,ae.authority_id from authority_endpoint_relations ae,authorities a,endpoints e where ae.authority_id = 1 and a.id=1;

select ae.authority_id id , a.name authority_name, e.name endpoint_name, e.description, e.url  from authority_endpoint_relations ae,authorities a,endpoints e where ae.authority_id = 1 and a.id=1 and ae.endpoint_id = e.id;


// 查询某角色下权限
select a.name authority_name ,e.name endpoint_name, e.description description from authorities a, endpoints e where a.id=3 and (e.id in (select endpoint_id from authority_endpoint_relations where authority_id=3) or e.parent_id in  (select endpoint_id from authority_endpoint_relations where authority_id=3)  );


// 查询某权限有多少角色拥有
select a.name authority_name ,e.name endpoint_name, e.description description, e.parent_id from authorities a, endpoints e where e.id=4 and (a.id in (select authority_id from authority_endpoint_relations where endpoint_id=4)  or a.id in ( select authority_id from authority_endpoint_relations where endpoint_id in (select parent_id from endpoints where id=4))   );

Group

创建组

     drop table if exists groups;

    CREATE TABLE IF NOT EXISTS `groups`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(50) NOT NULL,
   `parent_id` INT UNSIGNED,
    `status` TINYINT NOT NULL DEFAULT 0,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入组信息

    insert into groups (name) values ('超级粉丝通前端');
    insert into groups (name, parent_id) values ('应用中心开发组', 1);
     insert into groups (name, parent_id) values ('转化归因开发组', 1);

用户和组的关系表

创建

     drop table if exists user_group_relations;

    CREATE TABLE IF NOT EXISTS `user_group_relations`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `user_id` INT UNSIGNED NOT NULL,
   `group_id`  INT UNSIGNED NOT NULL,
    `status` TINYINT NOT NULL DEFAULT 0,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入

    insert into user_group_relations (user_id, group_id) values (1, 1);
    insert into user_group_relations (user_id, group_id) values (3, 2);

查询

    select g.name group_name, u.name user_name from user_group_relations ug,users u,groups g where ug.user_id=u.id and ug.group_id = g.id;

组与角色关系表


  drop table if exists group_role_relations;

    CREATE TABLE IF NOT EXISTS `group_role_relations`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `group_id` INT UNSIGNED NOT NULL,
   `role_id`  INT UNSIGNED NOT NULL,
    `status` TINYINT NOT NULL DEFAULT 0,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建一条数据

    insert into group_role_relations (group_id, role_id) values (1,2);

查询组和角色

       select g.name group_name, r.name role_name from group_role_relations gr,groups g,roles r where gr.group_id=g.id and gr.role_id = r.id;

问题来了，如何标记 1 个人所有的权限？

  1. 查询该人名下的角色
  2. 查询该人所在的组
  3. 查询组下所拥有的角色
  4. 查询角色下所有权限
  5. 查询权限对应的具体 url
    用户 用户A id

    // 1  uid 1
    select r.id role_id, u.name user_name, r.name role_name from users u,roles r,user_role_relations ur where ur.user_id = u.id and ur.role_id = r.id and u.id = 1;

    // 2 uid 1
    select g.id group_id, g.name group_name, u.name user_name from user_group_relations ug,users u,groups g where ug.user_id=u.id and ug.group_id = g.id and u.id = 1;

    // 3 grou
    select r.id role_id, g.name group_name, r.name role_name from group_role_relations gr,groups g,roles r where gr.group_id=g.id and gr.role_id = r.id and g.id=1;

    // 4
     select a.id athority_id, r.name role_name, a.name authority_name from roles r, authorities a,role_authority_relations ra where ra.role_id = r.id and ra.authority_id = a.id and r.id in (1,2);

    // 5 
    select a.name authority_name, e.name endpoint_name,e.description, e.url from authority_endpoint_relations ae,authorities a,endpoints e where ae.authority_id=a.id and ae.endpoint_id = e.id and  a.id in (1,2);
  • 分配表
drop table if exists assignments;

    CREATE TABLE IF NOT EXISTS `assignments`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `x_name` VARCHAR(20) NOT NULL,
   `x_id` INT UNSIGNED NOT NULL,
   `y_name` VARCHAR(20) NOT NULL,
   `y_id` INT UNSIGNED NOT NULL,
   `type` VARCHAR(40) NOT NULL,
    `status` TINYINT NOT NULL DEFAULT 0,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

results matching ""

    No results matching ""