UML图

1561626104773-d6bd0964-326a-48a0-81ad-989de7a0ac53.png

权限类型

operator_rule 功能操作权限
menu_rule 菜单权限
page_item_rule 页面元素
file_rule 文件权限

表设计

ruleId 权限ID
operatorId 操作ID
menuId 菜单ID
pageItemId 页面元素ID
fileId 文件ID

权限表

  1. /** model/Permissions.js */
  2. const Sequelize = require('sequelize');
  3. const defaultInit = require('./config/default')
  4. const Model = Sequelize.Model;
  5. const { Op } = Sequelize;
  7. class Permissions extends Model { }
  8. /**
  9.  * 权限表
  10. */
  11. module.exports = async (sequelize, force = false) => {
  12.   Permissions.init({
  13.     ruleName: {
  14.       type: Sequelize.STRING,
  15.       // 设置为 false 表示 not null
  16.       allowNull: false,
  17.       field: 'rule_name',
  18.     },
  19.     // attributes
  20.     ruleId: {
  21.       type: Sequelize.INTEGER,
  22.       // 设置为 false 表示 not null
  23.       allowNull: false,
  24.       field: 'rule_id',
  25.       unique: true,
  26.     },
  27.     ruleType: {
  28.       type: Sequelize.STRING,
  29.       // 设置为 false 表示 not null
  30.       allowNull: false,
  31.       field: 'rule_type',
  32.       unique: true,
  33.     },
  34.     ...defaultInit
  35.   }, {
  36.     charset: 'utf8',
  37.     sequelize,
  38.     /** 表名称 */
  39.     tableName: 'permissions',
  40.     /** 模型名称 */
  41.     modelName: 'permissions',
  42.     // options
  43.     // 此属性约定是否自动创建，createdAt与updatedAt
  44.     timestamps: false
  45.   });
  47.   return Permissions.sync({ force: true }).then(async () => {
  48.     return {
  49.       getAll (params) {
  50.         const where = {};
  51.         if (params && params.ruleType) {
  52.           where.ruleType = params.ruleType
  53.         }
  55.         return Permissions.findAll({ where: { isDelete: { [Op.ne]: 1 }, ...where } })
  56.       },
  57.       createOperatorRule ({
  58.         ruleId,
  59.         ruleName,
  60.         createdBy
  61.       }) {
  62.         return Permissions.create({
  63.           ruleId,
  64.           ruleName,
  65.           ruleType: 'operator_rule',
  66.           createdBy: createdBy,
  67.           createdAt: new Date()
  68.         })
  69.       },
  70.       createMenuRule ({
  71.         ruleId,
  72.         ruleName,
  73.         createdBy
  74.       }) {
  75.         return Permissions.create({
  76.           ruleId,
  77.           ruleName,
  78.           ruleType: 'menu_rule',
  79.           createdBy: createdBy,
  80.           createdAt: new Date()
  81.         })
  82.       },
  83.       createPageItemRule ({
  84.         ruleId,
  85.         ruleName,
  86.         createdBy
  87.       }) {
  88.         return Permissions.create({
  89.           ruleId,
  90.           ruleName,
  91.           ruleType: 'page_item_rule',
  92.           createdBy: createdBy,
  93.           createdAt: new Date()
  94.         })
  95.       },
  96.       createFileRule ({
  97.         ruleId,
  98.         ruleName,
  99.         createdBy
  100.       }) {
  101.         return Permissions.create({
  102.           ruleId,
  103.           ruleName,
  104.           ruleType: 'file_rule',
  105.           createdBy: createdBy,
  106.           createdAt: new Date()
  107.         })
  108.       }
  109.     }
  110.   })
  111. }

权限操作关联表

  1. /** model/PermissionsRelative.js */
  2. const Sequelize = require('sequelize');
  3. const defaultInit = require('./config/default')
  4. const Model = Sequelize.Model;
  5. const { Op } = Sequelize;
  7. class PermissionsRelative extends Model { }
  8. /**
  9.  * 权限表与操作表的关联表
  10. */
  11. module.exports = async (sequelize, force = false) => {
  12.   PermissionsRelative.init({
  13.     // attributes
  14.     ruleId: {
  15.       type: Sequelize.INTEGER,
  16.       // 设置为 false 表示 not null
  17.       allowNull: false,
  18.       field: 'rule_id',
  19.     },
  20.     operatorCode: { // 功能操作编码
  21.       type: Sequelize.STRING,
  22.       // 设置为 false 表示 not null
  23.       allowNull: false,
  24.       field: 'operator_code',
  25.     },
  26.     ...defaultInit
  27.   }, {
  28.     charset: 'utf8',
  29.     sequelize,
  30.     /** 表名称 */
  31.     tableName: 'PermissionsRelative_operator_relation',
  32.     /** 模型名称 */
  33.     modelName: 'PermissionsRelative_operator_relation',
  34.     // options
  35.     // 此属性约定是否自动创建，createdAt与updatedAt
  36.     timestamps: false
  37.   });
  39.   return PermissionsRelative.sync({ force: true }).then(async () => {
  40.     return {
  41.       getAll (params) {
  42.         const where = {};
  44.         if (params && params.ids) {
  45.           where.ruleId = { [Op.in]: params.ids }
  46.         }
  48.         if (params && params.ruleType) {
  49.           where.ruleType = params.ruleType
  50.         }
  52.         return PermissionsRelative.findAll({ where: { isDelete: { [Op.ne]: 1 }, ...where } })
  53.       },
  54.       create ({
  55.         id,
  56.         code,
  57.         createdBy
  58.       }) {
  59.         return PermissionsRelative.create({
  60.           ruleId: id,
  61.           operatorCode: code,
  62.           createdBy: createdBy,
  63.           createdAt: new Date()
  64.         })
  65.       }
  66.     }
  67.   })
  68. }

功能操作表

  1. /** model/FunctionOperator.js */
  2. const Sequelize = require('sequelize');
  3. const defaultInit = require('./config/default')
  4. const Model = Sequelize.Model;
  5. const { Op } = Sequelize;
  7. class FunctionOperator extends Model { }
  8. /**
  9.   * 功能操作表
  10.   *
  11.   * 查询路径的规则，表示在开始位置命中，或在中间位置命中
  12.   * `${operatorId}-%` || `%-${operatorId}-%`
  13. */
  15. const rules = {
  16.   operatorName: 'root',
  17.   operatorCode: 'root',
  18.   parentCode: '-',
  19.   treePath: '-',
  20.   children: [
  21.     {
  22.       operatorName: '应用管理 all',
  23.       operatorCode: 'application',
  24.       children: [
  25.         {
  26.           operatorName: '应用管理 查看',
  27.           operatorCode: 'applicationSelect',
  28.         },
  29.         {
  30.           operatorName: '应用管理 新增',
  31.           operatorCode: 'applicationCreate',
  32.         },
  33.         {
  34.           operatorName: '应用管理 编辑',
  35.           operatorCode: 'applicationUpdate',
  36.         },
  37.         {
  38.           operatorName: '应用管理 删除',
  39.           operatorCode: 'applicationDelete',
  40.         }
  41.       ]
  42.     },
  43.     {
  44.       operatorName: '页面管理 all',
  45.       operatorCode: 'pages',
  46.       children: [
  47.         {
  48.           operatorName: '页面管理 查看',
  49.           operatorCode: 'pagesSelect',
  50.         },
  51.         {
  52.           operatorName: '页面管理 新增',
  53.           operatorCode: 'pagesCreate',
  54.         },
  55.         {
  56.           operatorName: '页面管理 编辑',
  57.           operatorCode: 'pagesUpdate',
  58.         },
  59.         {
  60.           operatorName: '页面管理 删除',
  61.           operatorCode: 'pagesDelete',
  62.         }
  63.       ]
  64.     },
  65.     {
  66.       operatorName: '发布 all',
  67.       operatorCode: 'yidaWebPublish',
  68.       children: [
  69.         {
  70.           operatorName: '日常发布',
  71.           operatorCode: 'yidaWebPublishDev',
  72.         },
  73.         {
  74.           operatorName: '预发发布',
  75.           operatorCode: 'yidaWebPublishStaging',
  76.         },
  77.         {
  78.           operatorName: '线上发布',
  79.           operatorCode: 'yidaWebPublishProd',
  80.         },
  81.       ]
  82.     },
  83.   ]
  84. }
  86. module.exports = async (sequelize, force = false) => {
  87.   FunctionOperator.init({
  88.     // attributes
  89.     operatorName: { // 功能操作名称
  90.       type: Sequelize.STRING,
  91.       // 设置为 false 表示 not null
  92.       allowNull: false,
  93.       field: 'operator_name',
  94.       unique: true,
  95.     },
  96.     operatorCode: { // 功能操作编码
  97.       type: Sequelize.STRING,
  98.       // 设置为 false 表示 not null
  99.       allowNull: false,
  100.       field: 'operator_code',
  101.       unique: true,
  102.     },
  103.     parentCode: {
  104.       type: Sequelize.STRING,
  105.       // 设置为 false 表示 not null
  106.       allowNull: false,
  107.       field: 'parent_code',
  108.     },
  109.     treePath: {
  110.       type: Sequelize.STRING,
  111.       // 设置为 false 表示 not null
  112.       allowNull: false,
  113.       field: 'tree_path',
  114.     },
  115.     filterPath: { // 拦截Url前缀
  116.       type: Sequelize.STRING,
  117.       // 设置为 false 表示 not null
  118.       field: 'filter_path',
  119.     },
  120.     method: { // 拦截 url 请求类型
  121.       type: Sequelize.STRING,
  122.       // 设置为 false 表示 not null
  123.       field: 'filter_path',
  124.     },
  125.     ...defaultInit
  126.   }, {
  127.     charset: 'utf8',
  128.     sequelize,
  129.     /** 表名称 */
  130.     tableName: 'function_operator',
  131.     /** 模型名称 */
  132.     modelName: 'function_operator',
  133.     // options
  134.     // 此属性约定是否自动创建，createdAt与updatedAt
  135.     timestamps: false
  136.   });
  138.   return FunctionOperator.sync({ force: true }).then(async () => {
  139.     const data = await FunctionOperator.findAll({});
  140.     const list = [];
  141.     if (data.length === 0) { // 初始化数据
  142.       const root = rules;
  143.       root.createdAt = new Date();
  144.       root.createdBy = '__system',
  145.       list.push(root);
  146.       const loop = (arr, parentCode, treePath) => {
  147.         arr.forEach((item) => {
  148.           item.createdAt = root.createdAt;
  149.           item.createdBy = root.createdBy;
  151.           item.parentCode = parentCode
  152.           item.treePath = treePath + `-${item.operatorCode}`
  153.           list.push(item)
  154.           if (item.children) {
  155.             loop(
  156.               item.children,
  157.               item.operatorCode,
  158.               item.treePath
  159.             )
  160.           }
  161.           delete item.children
  162.         })
  163.       }
  164.       loop(root.children, root.operatorCode, root.operatorCode)
  165.       delete root.children;
  166.       await FunctionOperator.bulkCreate(list)
  167.     }
  169.     return {
  170.       destroy (operatorCodes) {
  171.         return FunctionOperator
  172.           .destroy({ where: { operatorCode: { [Op.in]: operatorCodes } } })
  173.           .then(res => {
  174.             if (Array.isArray(res) && res.length) {
  175.               return true
  176.             }
  178.             return false
  179.           });
  180.       },
  181.       delete (operatorCodes) {
  182.         const deletedAt = new Date()
  183.         return FunctionOperator
  184.           .update({
  185.             deletedAt: deletedAt,
  186.             isDelete: 1
  187.           }, { where: { operatorCode: { [Op.in]: operatorCodes } }, })
  188.           .then(res => {
  189.             if (Array.isArray(res) && res.length) {
  190.               return true
  191.             }
  193.             return false
  194.           })
  195.       },
  196.       getAll: () => {
  197.         return FunctionOperator.findAll({ where: { isDelete: { [Op.ne]: 1 } } })
  198.       },
  199.       update: ({
  200.         updatedAt,
  201.         updatedBy,
  202.         operatorName,
  203.         filterPath,
  204.         method,
  205.       }, operatorCode) => {
  206.         return FunctionOperator.update({
  207.           updatedAt,
  208.           updatedBy,
  209.           operatorName,
  210.           filterPath,
  211.           method,
  212.         },
  213.         { where: { operatorCode: operatorCode } })
  214.           .then(res => {
  215.             if (Array.isArray(res) && res.length) {
  216.               return true
  217.             }
  219.             return false
  220.           })
  221.       },
  222.       create: ({
  223.         createdBy,
  224.         createdAt,
  225.         operatorName,
  226.         operatorCode,
  227.         filterPath,
  228.         parentCode,
  229.         treePath,
  230.         method
  231.       }) => {
  232.         return FunctionOperator.create({
  233.           createdBy,
  234.           createdAt,
  235.           operatorName,
  236.           operatorCode,
  237.           filterPath,
  238.           parentCode,
  239.           treePath,
  240.           method
  241.         })
  242.       }
  243.     }
  244.   })
  245. }

权限菜单关联表

权限页面元素关联表

权限文件关联表

查询设计

  1. 查询用户有哪些角色，查询出 roles (角色List)
  2. 利用 roles 查询出当前用户的所有权限 rules (权限List)
  3. 根据具体场景，利用 rules 去查对应 “权限类型”（【菜单，页面元素，文件，功能操作】）的对应权限

题目一：单独的功能权限设计

一、当前我有一个应用表，页面表，要设计出，控制应用表的，查看，编辑，新增，删除，发布的权限。
应用表 : appId，appName
页面表：appId，pageId，pageName

首先，先有应用 【查看，编辑，新增，删除，发布日常，发布线上】六个权限。

根据 应用表 和 功能操作表 设计出一张 “应用功能操作权限表”，
应用功能操作权限表：appId，operatorId，operatorName

用户查询角色，角色查询权限，权限根据“权限类型”去查相关操作（operatorList）。

再去查 “应用功能操作权限表” 看当前应用有没有操作权限。

题目二： 新增功能操作

一、新增一个“功能操作”时，要关联 “权限操作关联表”数据，并新增“权限表”一条数据。
插入一条“功能操作”的数据，插入一条“权限操作关联”的数据，插入一条“权限表”的数据

index.js 入口文件

  1. // 应用管理
  2. const ApplicationInit = require('./application');
  3. // 页面管理
  4. const PagesInit = require('./page');
  5. // 发布管理
  6. const ApplicationVersionInit = require('./applicationVersion');
  7. // 全局 数据源 管理
  8. const ApplicationDataSource = require('./applicationDataSource');
  9. // 权限表
  10. const Permissions = require('./permissions');
  11. const PermissionsRelation = require('./permissionsRelation')
  12. // 功能表
  13. const FunctionOperator = require('./functionOperation');
  14. // page历史记录表
  15. const pagesSave = require('./pageSave')
  17. module.exports = async (sequelize) => {
  18.   const model = {}
  19.   const force = false;
  20.   model.pagesSave = await pagesSave(sequelize, force)
  21.   model.permissions = await Permissions(sequelize, force)
  22.   model.PermissionsRelation = await PermissionsRelation(sequelize, force)
  23.   model.functionOperator = await FunctionOperator(sequelize, force)
  24.   model.applicationDataSource = await ApplicationDataSource(sequelize, force);
  25.   model.application = await ApplicationInit(sequelize, force);
  26.   model.pages = await PagesInit(sequelize, force);
  27.   model.applicationVersion = await ApplicationVersionInit(sequelize, force);
  28.   return model
  29. }

defaultInit

  1. /** model/config/default.js */
  2. const Sequelize = require('sequelize');
  4. module.exports = {
  5.   createdBy: {
  6.     type: Sequelize.STRING,
  7.     allowNull: false,
  8.     field: 'created_by',
  9.   },
  10.   updatedBy: {
  11.     type: Sequelize.STRING,
  12.     field: 'updated_by'
  13.   },
  14.   deletedBy: {
  15.     type: Sequelize.STRING,
  16.     field: 'deleted_by'
  17.   },
  18.   createdAt: {
  19.     type: Sequelize.DATE,
  20.     allowNull: false,
  21.     field: 'created_at'
  22.     // allowNull defaults to true
  23.   },
  24.   updatedAt: {
  25.     type: Sequelize.DATE,
  26.     field: 'updated_at'
  27.     // allowNull defaults to true
  28.   },
  29.   /** 删除时间 */
  30.   deletedAt: {
  31.     type: Sequelize.DATE,
  32.     field: 'deleted_at'
  33.   },
  34.   /** 伪删除字段 伪删除字段 1 表示删除 */
  35.   isDelete: {
  36.     type: Sequelize.TINYINT,
  37.     field: 'is_delete',
  38.     defaultValue: 0
  39.   }
  40. }