RBAC权限模型
Role-Based Access Control，中文意思是：基于角色（Role）的访问控制。这是一种广泛应用于计算机系统和网络安全领域的访问控制模型。
简单来说，就是通过将权限分配给➡角色，再将角色分配给➡用户，来实现对系统资源的访问控制。一个用户拥有若干角色，每一个角色拥有若干权限。这样，就构造成“用户-角色-权限”的授权模型。在这种模型中，用户与角色之间，角色与权限之间，一般者是多对多的关系
具体概念可以查看RBAC——基于角色权限的模型
这里我只演示如何在Mybatis中实现RBAC权限模型的查询
现在有四张表
CREATE TABLE `user`
(
`username` varchar(50) NOT NULL,
`password` varchar(200) NOT NULL,
`name` varchar(50),
PRIMARY KEY (`username`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;
-- 创建角色表
CREATE TABLE `role`
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50),
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;
-- 创建用户角色表
CREATE TABLE `user_role`
(
`username` varchar(50),
`role_id` int(11),
PRIMARY KEY (`username`, `role_id`),
FOREIGN KEY (`role_id`) REFERENCES `role` (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;
-- 创建菜单表
CREATE TABLE `menu`
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50),
`parent_id` int(11),
PRIMARY KEY (`id`),
FOREIGN KEY (`parent_id`) REFERENCES `menu` (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;
-- 创建角色菜单表
CREATE TABLE `role_menu`
(
`role_id` int(11),
`menu_id` int(11),
PRIMARY KEY (`role_id`, `menu_id`),
FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
FOREIGN KEY (`menu_id`) REFERENCES `menu` (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;
# -----------------------------------------------------------------------
-- 插入用户数据
INSERT INTO user (username, password, name) VALUES ('user1', 'password1', 'User 1');
INSERT INTO user (username, password, name) VALUES ('user2', 'password2', 'User 2');
-- 插入角色数据
INSERT INTO role (name) VALUES ('file_role');
INSERT INTO role (name) VALUES ('db_role');
-- 插入用户角色数据
INSERT INTO user_role (username, role_id) VALUES ('user1', 1);
INSERT INTO user_role (username, role_id) VALUES ('user2', 2);
-- 插入菜单数据
INSERT INTO menu (name, parent_id) VALUES ('File', NULL);
INSERT INTO menu (name, parent_id) VALUES ('Database', NULL);
INSERT INTO menu (name, parent_id) VALUES ('File Access', 1);
INSERT INTO menu (name, parent_id) VALUES ('Database Access', 2);
-- 插入角色菜单数据
INSERT INTO role_menu (role_id, menu_id) VALUES (1, 3);
INSERT INTO role_menu (role_id, menu_id) VALUES (2, 4);
根据表分析，其实具有对应实体类的表只有user用户表和menu菜单表，其他的表都是用来关联和描述关系的，所以实体类只需要User和Menu
准备实体类User、Menu
@Data
public class User {
private String username;
private String password;
private String name;
private Menu menu;//一个用户只有一个菜单
}
//-------------------------------------------------
@Data
public class Menu {
private Integer id;
private String name;
private Integer parentId;
private List<Menu> sonMenus;//一个父级菜单下可能有多个子菜单
}
准备userMapper接口
public interface UserMapper {
// 使用mybatis完成任意用户拥有的菜单查询
List<User> getUsers();
//使用mybatis的级联查询完成菜单的查询（包含子菜单）
List<User> getUsersInclude();
}
准备userMapper.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.cnmd.mapper.UserMapper">
<resultMap id="map1" type="user">
<result property="username" column="username"/>
<result property="name" column="name"/>
<association property="menu" javaType="menu">
<id property="id" column="menuId"/>
<result property="name" column="menuName"/>
<result property="parentId" column="menuParentId"/>
</association>
</resultMap>
<!--任意用户拥有的菜单查询-->
<select id="getUsers" resultMap="map1">
SELECT u.username, u.name, m.name menuName, m.id menuId, m.parent_id menuParentId
FROM rbac.user u
JOIN rbac.user_role ur ON u.username = ur.username
JOIN rbac.role r ON ur.role_id = r.id
JOIN rbac.role_menu rm ON r.id = rm.role_id
JOIN rbac.menu m ON rm.menu_id = m.id;
</select>
<!-- ================================================================ -->
<resultMap id="map2" type="user">
<result property="username" column="username"/>
<result property="name" column="name"/>
<association property="menu" javaType="menu">
<result property="name" column="parent_menu_name"/>
<collection property="sonMenus" ofType="menu">
<result property="name" column="son_menu_name"/>
</collection>
</association>
</resultMap>
<!--完成菜单的查询（包含子菜单）-->
<select id="getUsersInclude" resultMap="map2">
SELECT u.username, u.name, mp.name as parent_menu_name, m.name as son_menu_name
FROM rbac.user u
JOIN rbac.user_role ur ON u.username = ur.username
JOIN rbac.role r ON ur.role_id = r.id
JOIN rbac.role_menu rm ON r.id = rm.role_id
JOIN rbac.menu m ON rm.menu_id = m.id
JOIN rbac.menu mp ON m.parent_id = mp.id;
</select>
</mapper>
Java代码
SqlSession session = MybatisUtil.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> users = mapper.getUsers();
users.forEach(System.out::println);
System.out.println("====================================");
List<User> usersInclude = mapper.getUsersInclude();
usersInclude.forEach(System.out::println);
查询结果
User(username=user1, password=null, name=User 1, menu=Menu(id=3, name=File Access, parentId=1, sonMenus=null))
User(username=user2, password=null, name=User 2, menu=Menu(id=4, name=Database Access, parentId=2, sonMenus=null))
====================================
User(username=user1, password=null, name=User 1, menu=Menu(id=null, name=File, parentId=null, sonMenus=[Menu(id=null, name=File Access, parentId=null, sonMenus=null)]))
User(username=user2, password=null, name=User 2, menu=Menu(id=null, name=Database, parentId=null, sonMenus=[Menu(id=null, name=Database Access, parentId=null, sonMenus=null)]))
如果需要单独完成菜单的查询（包含子菜单）
只需要重新创建MenuMapper接口
public interface MenuMapper {
List<Menu> getMenus();
}
创建MenuMapper.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.cnmd.mapper.MenuMapper">
<resultMap id="map" type="menu">
<id property="id" column="parent_menu_id"/>
<result property="name" column="parent_menu_name"/>
<collection property="sonMenus" ofType="menu">
<id property="id" column="son_menu_id"/>
<result property="name" column="son_menu_name"/>
</collection>
</resultMap>
<select id="getMenus" resultMap="map">
SELECT m.id parent_menu_id, m.name as parent_menu_name, ms.id son_menu_id, ms.name as son_menu_name
FROM rbac.menu m
JOIN rbac.menu ms
ON m.id = ms.parent_id;
</select>
</mapper>
查询结果
Menu(id=1, name=File, parentId=null, sonMenus=[Menu(id=3, name=File Access, parentId=null, sonMenus=null)])
Menu(id=2, name=Database, parentId=null, sonMenus=[Menu(id=4, name=Database Access, parentId=null, sonMenus=null)])
