©2020-2024 ioDraw All rights reserved,
Privacy Policy
一、数据库表设计
1.权限相关表
CREATE TABLE `t_sys_action` ( `id` varchar(32) NOT NULL COMMENT '主键', `name`
varchar(32) DEFAULT NULL COMMENT '操作名称', `code` varchar(32) DEFAULT NULL
COMMENT '编码', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE
TABLE `t_sys_resource` ( `id` varchar(32) NOT NULL COMMENT '主键', `name`
varchar(64) DEFAULT NULL COMMENT '资源名称', `code` varchar(64) DEFAULT NULL
COMMENT '资源编码', `level` int(11) DEFAULT NULL COMMENT '资源级别', `parent_id`
varchar(32) DEFAULT NULL COMMENT '父级id', `rank` int(11) DEFAULT NULL COMMENT
'排序', `img` varchar(64) DEFAULT NULL COMMENT '资源图片', `url` varchar(126) DEFAULT
NULL COMMENT '路由', `description` varchar(255) DEFAULT NULL COMMENT '描述', `type`
varchar(255) DEFAULT NULL COMMENT '类型', `isleaf` int(1) DEFAULT NULL COMMENT
'是否叶子节点 1 是', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE
TABLE `t_sys_privilege` ( `id` varchar(32) NOT NULL COMMENT '主键', `name`
varchar(64) DEFAULT NULL COMMENT '权限名称', `code` varchar(64) DEFAULT NULL
COMMENT '编码', `resource_id` varchar(32) DEFAULT NULL COMMENT '资源编号',
`action_id` varchar(32) DEFAULT NULL COMMENT '操作编号', PRIMARY KEY (`id`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `t_sys_role` ( `id`
varchar(32) NOT NULL COMMENT '主键', `name` varchar(32) DEFAULT NULL COMMENT
'角色名称', `code` varchar(32) DEFAULT NULL COMMENT '编码', `rank` varchar(32)
DEFAULT NULL COMMENT '排序', `description` varchar(128) DEFAULT NULL COMMENT
'描述', `enabled` int(1) DEFAULT NULL COMMENT '状态 0 删除 1启用 2禁用', PRIMARY KEY
(`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE
`t_sys_role_privilege` ( `id` varchar(32) NOT NULL COMMENT '主键', `role_id`
varchar(32) DEFAULT NULL COMMENT '角色编号', `privilege_id` varchar(32) DEFAULT
NULL COMMENT '权限编号', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_sys_user` ( `id` varchar(32) NOT NULL COMMENT '主键', `user_name`
varchar(32) DEFAULT NULL COMMENT '姓名', `password` varchar(32) DEFAULT NULL
COMMENT '密码', `nick_name` varchar(64) DEFAULT NULL, `true_name` varchar(64)
DEFAULT NULL COMMENT '真实姓名', `salt` varchar(64) DEFAULT NULL COMMENT '盐',
`gender` int(11) DEFAULT NULL COMMENT '0 保密 1 男 2女', `age` int(11) DEFAULT NULL
COMMENT '年龄', `mobile` varchar(20) DEFAULT NULL COMMENT '电话号码', `email`
varchar(64) DEFAULT NULL COMMENT '邮箱', `country` varchar(32) DEFAULT NULL
COMMENT '国籍', `province` varchar(32) DEFAULT NULL COMMENT '省', `type` int(1)
DEFAULT NULL COMMENT '1，厂家，2机构', `city` varchar(126) DEFAULT NULL COMMENT '城市',
`avatar_url` varchar(256) DEFAULT NULL COMMENT '头像', `language` varchar(64)
DEFAULT NULL, `last_login_ip` varchar(32) DEFAULT NULL, `last_login_time`
varchar(32) DEFAULT NULL, `create_time` varchar(32) DEFAULT NULL, `update_time`
varchar(32) DEFAULT NULL, `create_by` varchar(32) DEFAULT NULL, `update_by`
varchar(32) DEFAULT NULL, `enabled` int(1) DEFAULT NULL COMMENT '状态 0 删除 1启用
2禁用', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' 用户表';
CREATE TABLE `t_sys_user_role` ( `id` varchar(32) NOT NULL COMMENT '主键',
`user_id` varchar(32) DEFAULT NULL COMMENT '用户编号', `role_id` varchar(32)
DEFAULT NULL COMMENT '角色编号', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT
CHARSET=utf8;
其他省略。。。
2. 相关视图
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW
`v_privilege` AS SELECT `t_sys_privilege`.`id` AS `id`,
`t_sys_privilege`.`resource_id` AS `resourceid`, `t_sys_privilege`.`action_id`
AS `actionid`, `t_sys_resource`.`code` AS `resourcecode`,
`t_sys_resource`.`name` AS `resourcename`, `t_sys_resource`.`type` AS
`resourcetype`, `t_sys_resource`.`parent_id` AS `parent_id`,
`t_sys_action`.`name` AS `actionname`, concat( `t_sys_resource`.`code`, ':',
`t_sys_action`.`code` ) AS `privilegecode` FROM ( ( `t_sys_resource` LEFT JOIN
`t_sys_privilege` ON ( ( `t_sys_privilege`.`resource_id` =
`t_sys_resource`.`id` ) ) ) LEFT JOIN `t_sys_action` ON ( (
`t_sys_privilege`.`action_id` = `t_sys_action`.`id` ) ) ) CREATE ALGORITHM =
UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_user_role` AS
SELECT DISTINCT `usr`.`id` AS `id`, `usrr`.`user_id` AS `userid`,
`usrr`.`role_id` AS `roleid`, `usr`.`user_name` AS `username` FROM (
`t_sys_user` `usr` LEFT JOIN `t_sys_user_role` `usrr` ON ( ( `usrr`.`user_id` =
`usr`.`id` ) ) ) WHERE (`usrr`.`role_id` IS NOT NULL) CREATE ALGORITHM =
UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_user_privilege` AS
SELECT `rl`.`userid` AS `USERID`, `rl`.`username` AS `USERNAME`, `rl`.`roleid`
AS `ROLEID`, `url`.`name` AS `ROLENAME`, `privilge`.`id` AS `PRIVILEGEID`,
`sr`.`id` AS `RESOURCEID`, `sr`.`code` AS `RESOURCECODE`, `sr`.`name` AS
`RESOURCENAME`, `sr`.`type` AS `RESOURCETYPE`, `sr`.`parent_id` AS `PARENTID`,
`sa`.`id` AS `ACTIONID`, `sa`.`code` AS `ACTIONCODE`, `sa`.`name` AS
`ACTIONNAME`, concat( `sr`.`code`, ':', `sa`.`code` ) AS `PRIVILEGECODE` FROM (
( ( ( ( `v_user_role` `rl` LEFT JOIN `t_sys_role` `url` ON ((`rl`.`roleid` =
`url`.`id`)) ) LEFT JOIN `t_sys_role_privilege` `urp` ON ( (`url`.`id` =
`urp`.`role_id`) ) ) LEFT JOIN `t_sys_privilege` `privilge` ON ( (
`urp`.`privilege_id` = `privilge`.`id` ) ) ) LEFT JOIN `t_sys_resource` `sr` ON
( ( `privilge`.`resource_id` = `sr`.`id` ) ) ) LEFT JOIN `t_sys_action` `sa` ON
( ( `privilge`.`action_id` = `sa`.`id` ) ) )
二、菜单栏，根据用户权限查询
1.查询与用户相关的权限资源（最底层的子节点，权限直接挂载在功能点上）
<select id="getResourceByUserName" resultMap="BaseResultMap"> SELECT * FROM
t_sys_resource WHERE id IN ( SELECT DISTINCT RESOURCEID FROM `v_user_privilege`
WHERE USERName = #{userName, jdbcType=VARCHAR} ) </select>
2.根据子节点递归查找所有父节点，组装成树结构
service层：
/** * 根据用户名称获取与权限相关的所有资源（父子级） * * @return */ public
ServiceResult<List<ResourceAO>> getResource(String userName) { //1.查询所有资源
ServiceResult<List<ResourceAO>> listServiceResult = selectByCriteria(null);
List<ResourceAO> allResourceList = null; if (listServiceResult != null &&
listServiceResult.isSucceed() &&
!CollectionUtils.isEmpty(listServiceResult.getData())) { allResourceList =
listServiceResult.getData(); } //超级管理员
if(Constant.SYSTEM_SUPER_ADMIN.equals(userName)){ return
listTreeNodes(allResourceList); } //2.查询用户权限关联的资源 List<ResourceAO>
resourceChildren = getResourceByUserName(userName); //3.根据权限关联的资源（子级）查询所有父级
List<ResourceAO> parentResourceList = new ArrayList<>(); if
(!CollectionUtils.isEmpty(resourceChildren)) { for (ResourceAO child :
resourceChildren) { getParentResourceList(allResourceList, child.getId(),
parentResourceList); } } //查询到的资源组装成树节点 return
listTreeNodes(parentResourceList); } /** * 根据子级查询所有父级资源 * * @param
allResourceList * @param childId * @param parentResourceList * @return */
private List<ResourceAO> getParentResourceList(List<ResourceAO>
allResourceList, String childId, List<ResourceAO> parentResourceList) { if
(!CollectionUtils.isEmpty(allResourceList)) { for (ResourceAO resource :
allResourceList) { // 判断是否存在父节点 if (resource.getId().equals(childId)) { //
递归遍历上一级 getParentResourceList(allResourceList, resource.getParentId(),
parentResourceList); if (!parentResourceList.contains(resource)) {
parentResourceList.add(resource); } } } return parentResourceList; } return
null; } /** * 获取树结构数据 * * @return */ public ServiceResult<List<ResourceAO>>
listTreeNodes(List<ResourceAO> allList) { ServiceResult<List<ResourceAO>> ret =
new ServiceResult<>(); List<ResourceAO> parentList = new ArrayList<>();//根节点
List<ResourceAO> allResourceList = null; if (!CollectionUtils.isEmpty(allList))
{ allResourceList = allList; for (ResourceAO resource : allList) { if
(StringUtils.isEmpty(resource.getParentId())) { parentList.add(resource); } } }
//返回的树形节点数据 List<ResourceAO> treeNodeList = new ArrayList<>(); if
(!CollectionUtils.isEmpty(parentList)) { for (ResourceAO parent : parentList) {
//递归查询所有子节点 treeNodeList.add(recursiveTree(parent, allResourceList)); } }
ret.setData(treeNodeList); ret.setSucceed(true); return ret; } /** *
递归算法解析成树形结构 */ public ResourceAO recursiveTree(ResourceAO parentNode,
List<ResourceAO> allResourceList) { List<ResourceAO> childTreeNodes =
getChildTree(parentNode.getId(), allResourceList); if
(!CollectionUtils.isEmpty(childTreeNodes)) { for (ResourceAO child :
childTreeNodes) { ResourceAO n = recursiveTree(child, allResourceList);
parentNode.getChildren().add(n); } } return parentNode; } /** * 根据父节点ID获取所有子节点
*/ public List<ResourceAO> getChildTree(String parentId, List<ResourceAO>
allResourceList) { List<ResourceAO> childNodes = new ArrayList<>(); if
(!CollectionUtils.isEmpty(allResourceList)) { for (ResourceAO resource :
allResourceList) { if (parentId.equals(resource.getParentId())) {
childNodes.add(resource); } } } return childNodes; }
controller层：
/** * 获取资源. * * @return */ @RequestMapping(value = "/getResource", method =
{RequestMethod.GET, RequestMethod.POST}) @LogOperation(action = "获取资源") public
Object getResource(@RequestParam String userName) { return
resourceService.getResource(userName); }
此时就完成了首页菜单根据权限进行树结构后端接口的组装。
3.vue前端从后端接口获取菜单树并在页面展示
（1）MenuTree.vue 菜单树组件
<template> <el-submenu v-if="menu.children && menu.children.length >= 1"
:key="menu.id" :index="menu.name"> <template slot="title"> <i :class="menu.img"
:style="{ marginLeft: asideContainerW }"></i> <span slot="title">{
{menu.name}}</span> </template> <MenuTree v-for="item in menu.children"
:menu="item" :key="item.id"></MenuTree> </el-submenu> <el-menu-item v-else
:index="menu.url" :key="menu.id" @click="handleRoute(menu)"> <i
:class="menu.img" :style="{ marginLeft: asideContainerChildW }"></i> <span
slot="title" style="font-size: 12px">{{menu.name}}</span> </el-menu-item>
</template> <script> export default { name: 'MenuTree', data() { return {
asideContainerW: '2rem', asideContainerChildW: '4rem' } }, props: { menu: {
type: Object, required: true } }, methods: { handleRoute(menu) { //
通过菜单URL跳转至指定路由 this.$router.push(menu.url) } } } </script>
（2）vuex和axios结合查询权限菜单数据：
（3）取出上面后台查出的菜单数据并展示：
三、角色授权
1.资源权限树结构的拼装：
(1) 权限和资源表整合成总的资源
<select id="getResourceWithPrivilege" resultMap="BaseResultMap"> select
id,name,code,parent_id from t_sys_resource UNION all SELECT privilege.id AS id,
action.name AS name, concat(resource.code,':',action.code) AS code, resource.id
as parent_id FROM t_sys_resource resource LEFT JOIN t_sys_privilege privilege
ON privilege.resource_id = resource.id LEFT JOIN t_sys_action action ON
privilege.action_id = action.id where privilege.id is not null </select>
2. 获取资源权限树
service层：
/** * 获取资源树 * * @return */ public ServiceResult<List<ResourceAO>>
getResourceTreeNode() { //查询所有资源 List<ResourceAO> allResource =
getResourceWithPrivilege(); return listTreeNodes(allResource); } /** *
权限和资源表整合成总的资源 * * @return */ @Override public List<ResourceAO>
getResourceWithPrivilege() { return
resourceCustomizedMapper.getResourceWithPrivilege(); }
这里的listTreeNodes方法上边已经列出过。
controller层：
/** * 获取资源树结构. * * @return */ @RequestMapping(value = "/getResourceTreeNode",
method = {RequestMethod.GET, RequestMethod.POST}) @LogOperation(action =
"获取资源树结构") public Object getResourceTreeNode() { return
resourceService.getResourceTreeNode(); }
3.vue前端：
<template v-if="privilegeShow"> <el-dialog title="角色授权功能菜单"
:visible.sync="privilegeShow" width="40%"> <el-form :model="privilegeForm"
:inline="true" ref="form" class="demo-form-inline" style="margin-bottom:
5px;text-align: center;margin-top: 0px"> <el-form-item label="角色名称：" required
prop="name"> <el-input v-model="privilegeForm.name"
disabled="disabled"></el-input> </el-form-item> <el-form-item label="角色编码：">
<el-input v-model="privilegeForm.code" disabled="disabled"></el-input>
</el-form-item> </el-form> <el-tag size="small">授权功能菜单</el-tag>
<el-divider></el-divider> <template> <el-row> <el-col :span="2" :offset="20">
<div class="grid-content bg-purple-dark"> <el-checkbox v-model="checked"
@change="checkedAll">全选</el-checkbox> </div> </el-col> </el-row> </template>
<ul class="infinite-list" style="height:350px;overflow:auto"> <el-tree
:data="resourceTree" show-checkbox node-key="id" ref="privilegeTree"
default-expand-all :default-checked-keys="rolePrivilegeData"
:props="defaultProps" style="margin-left: 10px;"> </el-tree> </ul> <div
slot="footer" class="dialog-footer"> <el-button size="mini"
@click="privilegeShow = false">取消</el-button> <el-button size="mini"
type="primary" @click="submitPrivilege" :loading="editLoading">确定</el-button>
</div> </el-dialog> </template> computed: { ...mapState('role', [ 'dataSource',
'loading', 'editLoading', 'resourceTree', 'rolePrivilegeData' ]), }
四、权限控制按钮的隐藏和显示
permission.js：
import Vue from 'vue' /**权限指令**/ Vue.directive('has', { inserted: function
(el, binding) { if (!Vue.prototype.$_has(binding.value)) {
el.parentNode.removeChild(el); } } }); //权限检查方法 Vue.prototype.$_has = function
(value) { let isExist = false; let user = localStorage.getItem("user"); if
("admin" == user) { return true; } let premissionsStr =
localStorage.getItem("permissions"); if (premissionsStr == undefined ||
premissionsStr == null) { return false; } if (premissionsStr.indexOf(value) >
-1) { isExist = true; } return isExist; };
main.js中引入：
import has from './public/permission.js';
页面：
<el-button size="mini" @click="addDialog" type="primary"
v-has="'roleManage:manage'">新增</el-button>