spring boot集成mybatis和springsecurity实现权限控制功能

上一篇已经实现了登录认证功能，这一篇继续实现权限控制功能，文中代码只贴出来和上一篇不一样的修改的地方，完整代码可结合上一篇一起整理spring boot集成mybatis和springsecurity实现登录认证功能-CSDN博客

数据库建表

权限控制的意思就是根据用户角色的不同，赋予不同的访问权限，比如管理员可以对数据进行增删改查操作，而普通用户只能查询数据。根据网上大部分文章的说法，最好的授权方式叫RABC，意思是基于资源的访问控制，详见SpringSecurity授权-CSDN博客

这种方式会创建5个数据库表，分别为用户表，角色表，权限表，用户角色关系表，角色权限关系表，他们之间存在一些对应关系，每次根据用户名可以查询到这个用户的角色和权限信息，我也不懂为啥要把授权相关的表拆成5个，按照我自己的理解，就是把所有用户、角色、权限信息放在一张表里也不是不行，但是既然大家都建议这么做，肯定是有道理的，所以我就按照主流方式来实现权限控制的数据库表创建

数据库建表和数据SQL如下：

-- test.permission definition权限信息表 
CREATE TABLE `permission` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `permission` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='权限信息表'; 
INSERT INTO test.permission (name,permission) VALUES ('selectinfo','url1'), ('insertinfo','url2'), ('updateinfo','url3'), ('deleteinfo','url4'); 
-- test.`role` definition角色信息表 
CREATE TABLE `role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; 
INSERT INTO test.`role` (name,description) VALUES ('admin','admin'), ('user','user');
-- test.`user` definition用户信息表 
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(255) DEFAULT NULL, `password` varchar(255) DEFAULT NULL, `permission` varchar(255) DEFAULT NULL, `role` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; 
INSERT INTO test.`user` (username,password,permission,`role`) VALUES ('aaa','$2a$10$m3tlFCB8IiZzm6dj7Q58KuD9FnkZI8M/1scJ2dZBZwiToFpgRMZCe',NULL,NULL), ('bbb','$2a$10$Wwfn31iTecY0n66LHXNSeeiPTcr.ZJsDHVuvnHR150rMqgJXugio2',NULL,NULL), ('ccc','1234',NULL,NULL); 
-- test.role_permission definition角色权限关系表 
CREATE TABLE `role_permission` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_id` varchar(255) DEFAULT NULL, `permission_id` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; 
INSERT INTO test.role_permission (role_id,permission_id) VALUES ('1','1'), ('1','2'), ('1','3'), ('1','4'), ('2','1'), ('2','3'); 
-- test.user_role definition用户角色关系表 
CREATE TABLE `user_role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` varchar(255) DEFAULT NULL, `role_id` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; 
INSERT INTO test.user_role (user_id,role_id) VALUES ('1','1'), ('1','2'), ('2','2');

所有和5个数据库表相关的dao、dto、service、mapper文件可参考上一篇user表编写，这里不在贴出源码

代码修改

1，springsecurity配置类

WebSecurityConfig添加支持权限控制的注解如下：

其中第一个配置项perPostEnable表示开启@PreAuthroize注解，是方法或类级别的注解，只需要在方法上添加@PreAuthroize注解即可，可以在资源被访问之前判断用户权限，是比较常用的方法，比如

图中注解就表示只有具备url1权限的用户才能执行方法queryName()。

第二个配置项securedEnable是专门用于判断是否具有角色的，可以在方法或者类上使用，参数需要以ROLE_开头，本文未使用所以忽略

2，UserDetailsServiceImpl

该方法实现了框架的UserDetailsService接口，并复写了loadUserByUsername方法，用于查询数据库的用户信息，添加权限控制功能后，需要同时查询用户的权限信息，代码如下：

package com.sgp.ss.security;

import com.sgp.ss.dao.IUserMapper;
import com.sgp.ss.domain.entity.PermissionEntity;
import com.sgp.ss.domain.entity.UserEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

/**
 * @author shanguangpu
 * @date 2023/11/30 15:50
 */
@Component
public class UserDetailsServiceImpl implements UserDetailsService {

    @Autowired
    IUserMapper userMapper;

    @Override
    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        try {
            UserEntity userEntityByName = userMapper.getUserEntityByName(username);
            if (userEntityByName == null){
                throw new UsernameNotFoundException("用户"+username+"不存在");
            }

            List<PermissionEntity> permissionList = userMapper.findPermissionByUsername(username);//查询用户权限信息
            List<GrantedAuthority> grantedAuthorities=new ArrayList<>();
            for (PermissionEntity p : permissionList){
                grantedAuthorities.add(new SimpleGrantedAuthority(p.getPermission()));
            }
            return new LoginUser(userEntityByName,grantedAuthorities);

            //测试用
//            List<String> list = new ArrayList<>(Arrays.asList("test"));
//            List<GrantedAuthority> grantedAuthorities=new ArrayList<>();
//            for(String s:list){
//                grantedAuthorities.add(new SimpleGrantedAuthority(s));
//            }
//            return new LoginUser(userEntityByName,list);

        } catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
}

其中方法findPermissionByUsername()是我们新加的，需要在mapper文件中编写SQL，实现根据用户名查询用户权限的功能，这个SQL语句可以按照下述格式直接写，如果数据库表名称不同，只需要修改表名就行

<select id="findPermissionByUsername" resultType="com.sgp.ss.domain.entity.PermissionEntity" parameterType="java.lang.String">
        <if test="_parameter != null">
            SELECT DISTINCT permission.id,permission.name,permission.permission FROM
            user
            LEFT JOIN user_role on user.id = user_role.user_id
            LEFT JOIN role on user_role.role_id = role.id
            LEFT JOIN role_permission on role.id = role_permission.role_id
            LEFT JOIN permission on role_permission.permission_id = permission.id
            where user.username = #{username} and 1 = 1
            <!--select <include refid="UserEntityColumns"/> from user-->
            <!--where username = #{username} and 1 = 1-->
        </if>
    </select>

3，LoginUser?

从上述代码中可以看出，当我们从数据库查询完用户权限信息后，框架会将它封装到用户对象UserDetails中，代码如下：

package com.sgp.ss.security;

import com.sgp.ss.domain.entity.UserEntity;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.security.core.userdetails.UserDetails;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @author shanguangpu
 * @date 2023/11/30 15:58
 */
public class LoginUser extends UserEntity implements UserDetails {

    Collection<? extends GrantedAuthority> authorities;

    private List<String> permissions = new ArrayList<>();

    public LoginUser(UserEntity userEntity){
        if (null != userEntity){
            this.setUsername(userEntity.getUsername());
            this.setPassword(userEntity.getPassword());
        }
    }

    public LoginUser(UserEntity userEntity, Collection<? extends GrantedAuthority> authorities){
        this.setId(userEntity.getId());
        this.setUsername(userEntity.getUsername());
        this.setPassword(userEntity.getPassword());
//        permissions.add(userEntity.getPermission());
        this.authorities = authorities;
    }

    public LoginUser(UserEntity userEntity, List<String> permissions){
        this.setId(userEntity.getId());
        this.setUsername(userEntity.getUsername());
        this.setPassword(userEntity.getPassword());
        this.permissions = permissions;
    }

    @Override
    public Collection<? extends GrantedAuthority> getAuthorities() {
        if (authorities != null){
            return authorities;
        }
        authorities = permissions.stream().map(SimpleGrantedAuthority::new).collect(Collectors.toList());
        return authorities;
    }

    /**
     * 账户是否过期
     * @return
     */
    @Override
    public boolean isAccountNonExpired() {
        return true;
    }

    /**
     * 账户是否被锁定
     * @return
     */
    @Override
    public boolean isAccountNonLocked() {
        return true;
    }

    /**
     * 证书是否过期
     * @return
     */
    @Override
    public boolean isCredentialsNonExpired() {
        return true;
    }

    /**
     * 账户是否有效
     * @return
     */
    @Override
    public boolean isEnabled() {
        return true;
    }
}

4，JwtAuthenticationTokenFilter

下次访问资源只需要从token中解析出权限信息即可，所以需要在JWT过滤器中添加token解析步骤，并交给框架进行权限比对，存在上下文中

package com.sgp.ss.security;

import com.sgp.ss.domain.entity.UserEntity;
import com.sgp.ss.service.UserEntityService;
import com.sgp.ss.util.JwtTokenUtil;
import io.jsonwebtoken.ExpiredJwtException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import org.springframework.web.filter.OncePerRequestFilter;

import javax.servlet.FilterChain;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

/**
 * @author shanguangpu
 * @date 2023/12/8 15:29
 */
@Component
public class JwtAuthenticationTokenFilter extends OncePerRequestFilter {

    @Autowired
    private UserEntityService userEntityService;

    @Autowired
    private JwtTokenUtil jwtTokenUtil;

//    @Autowired
//    private JwtProperties jwtProperties;

    @Override
    protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response, FilterChain filterChain) throws ServletException, IOException {

        //获取token信息
        final String authorization = request.getHeader("token");

        String name = null;
        String authToken = null;

        if (!StringUtils.isEmpty(authorization)) {
            authToken = authorization.replace("Authorization", "");
            try {
                name = jwtTokenUtil.getUsernameFromToken(authToken);
            } catch (ExpiredJwtException e){
                e.printStackTrace();
            }
        }
        if (name != null && SecurityContextHolder.getContext().getAuthentication() == null){
//            if (jwtTokenUtil.isTokenValid(name, authToken)){
            UserEntity userEntityByName = userEntityService.getUserEntityByName(name);
            List<GrantedAuthority> authorityFromToken = jwtTokenUtil.getAuthorityFromToken(authToken);
            UsernamePasswordAuthenticationToken authentication = new UsernamePasswordAuthenticationToken(userEntityByName, null, authorityFromToken);
            SecurityContextHolder.getContext().setAuthentication(authentication);
//            }
        }
        filterChain.doFilter(request, response);

    }

    public JwtAuthenticationTokenFilter(){
        super();
    }
}

5，JWT工具类

然后在JWT工具类中实现权限获取方法，完整代码如下：

package com.sgp.ss.util;

import com.sgp.ss.security.LoginUser;
import io.jsonwebtoken.*;
import io.jsonwebtoken.impl.DefaultClock;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.io.Serializable;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @author shanguangpu
 * @date 2023/2/22 10:42
 */
@Component
public class JwtTokenUtil implements Serializable {

    private static final Logger LOG = LoggerFactory.getLogger(JwtTokenUtil.class);

    private static final long serialVersionUID = -1264536648286114018L;

    private Clock clock = DefaultClock.INSTANCE;

    private Map<String, String> tokenMap = new ConcurrentHashMap<>(32);

    public String generateToken(LoginUser loginUser){
        Map<String, Object> claims = new HashMap<>();
        claims.put("username", loginUser.getUsername());
        claims.put("authority", loginUser.getAuthorities());
        String token = generateToken(claims, loginUser.getId() + "");
        if (! StringUtils.isEmpty(token)){

        }
        return token;
    }

    /**
     * 生成token
     * @param claims
     * @param subject
     * @return
     */
    private String generateToken(Map<String, Object> claims, String subject) {
        final Date createdDate = clock.now();
        final Date expirationDate = generateExpirationDate(createdDate);
        return Jwts.builder()
                // 自定义属性
                .setClaims(claims)
                .setSubject(subject)
                // 创建时间
                .setIssuedAt(createdDate)
                // 过期时间
                .setExpiration(expirationDate)
                // 签名算法及秘钥
                .signWith(SignatureAlgorithm.HS512, "sgpsgp")
                .compact();
    }

    /**
     * 设置过期时间，以当前时间加上毫秒数计算，该方法中设置的过期时间是6小时
     * @param createdDate
     * @return
     */
    private Date generateExpirationDate(Date createdDate) {
        return new Date(createdDate.getTime() + 21600 * 1000);
    }

    // 从得到的令牌里面获取用户ID
    public Integer getUserIdFromToken(String token) {
        Integer id = null;
        try {
            final Claims claims = getClaimsFromToken(token);
            id = Integer.parseInt(claims.getSubject());
            return id;
        } catch (Exception e) {
        }
        return id;
    }

    // 从得到的令牌里面获取用户名
    public String getUsernameFromToken(String token) {
        String username;
        try {
            final Claims claims = getClaimsFromToken(token);
            username = (String) claims.get("username");
        } catch (Exception e) {
            username = null;
        }
        return username;
    }

    // 从得到的令牌里面获取权限
    public List<GrantedAuthority> getAuthorityFromToken(String token) {
        List<GrantedAuthority> list = new ArrayList<>();
        try {
            final Claims claims = getClaimsFromToken(token);
            StringBuffer sb = new StringBuffer();
            List<LinkedHashMap> authority2 = (List<LinkedHashMap>) claims.get("authority");
            for (LinkedHashMap s : authority2){
                String authority = (String) s.get("authority");
                sb.append(authority+",");
                list.add(new SimpleGrantedAuthority(authority));
            }
            LOG.info("当前用户名称是：{}，权限url为：{}",claims.get("username"),sb.subSequence(0,sb.length()-1));
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    // 从得到的令牌里面获取创建时间
    public Date getCreatedDateFromToken(String token) {
        Date created;
        try {
            final Claims claims = getClaimsFromToken(token);
            created = claims.getIssuedAt();
        } catch (Exception e) {
            created = null;
        }
        return created;
    }
    // 从得到的令牌里面获取过期时间
    public Date getExpirationDateFromToken(String token) {
        Date expiration;
        try {
            final Claims claims = getClaimsFromToken(token);
            expiration = claims.getExpiration();
        } catch (Exception e) {
            expiration = null;
        }
        return expiration;
    }

    private Claims getClaimsFromToken(String token) {
        Claims claims;
        try {
            claims = Jwts.parser()
                    .setSigningKey("sgpsgp")
                    .parseClaimsJws(token)
                    .getBody();
        } catch (Exception e) {
            claims = null;
        }
        return claims;
    }


    /**
     * 检查token是否过期
     * @param token
     * @return
     */
    public Boolean isTokenExpired(String token) {
        final Date expiration = getExpirationDateFromToken(token);
        boolean flag = expiration.before(new Date());
        if(flag) {
            String username = getUsernameFromToken(token);
            if(username != null) {
                tokenMap.remove(username);
            }
        }
        return flag;
    }

    public Boolean isTokenValid(String username, String token) {
        if(tokenMap.get(username) != null && tokenMap.get(username).equals(token.trim())) {
            return !isTokenExpired(token);
        }
        return false;
    }

    public Boolean isTokenValid(int id, String token) {
        if (tokenMap.get(id) != null && tokenMap.get(id).equals(token.trim())) {
            return !isTokenExpired(token);
        }
        return false;
    }

    public void deleteToken(String token) {
        String username = getUsernameFromToken(token);
        if(username != null) {
            tokenMap.remove(username);
        }
    }


    }

}

6，controller

controller文件中可以多写几个方法，分别给与不同的权限，代码如下：

package com.sgp.ss.controller;

import com.sgp.ss.dao.*;
import com.sgp.ss.domain.dto.data.*;
import com.sgp.ss.domain.entity.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * @author shanguangpu
 * @date 2023/12/12 14:59
 */
@RestController
public class index {

    @Autowired
    private IUserMapper userMapper;

    @Autowired
    private IRoleMapper roleMapper;

    @Autowired
    private IPermissionMapper permissionMapper;

    @Autowired
    private IUserRoleMapper userRoleMapper;

    @Autowired
    private IRolePermissionMapper rolePermissionMapper;

    @GetMapping(value = "hello")
    public String request(){
        System.out.println("hello word");
        return "hello success";
    }

    @PostMapping(value = "select")
    @PreAuthorize("hasAuthority('url2')")
    public String selectInfo(){
        List<UserEntity> userEntities = userMapper.queryUserEntityList(new UserDto());
        return userEntities.toString();
    }


    @PostMapping(value = "queryName")
    @PreAuthorize("hasAuthority('url1')")
    public String queryName(){

        List<UserEntity> userEntities = userMapper.queryUserEntityList(new UserDto());
        int i = 1;
        for (UserEntity userEntity : userEntities){
            String username = userEntity.getUsername();
            System.out.println("name"+i+" is : "+username);
            i++;
        }
        return "query finish";

    }

    @PostMapping(value = "queryTable")
    public String queryRoleAndPermission(){
        List<RoleEntity> roleEntities = roleMapper.queryRoleEntityList(new RoleDto());
        for (RoleEntity roleEntity : roleEntities){
            System.out.println(roleEntity.getId()+","+roleEntity.getName()+","+roleEntity.getDescription());
        }
        List<PermissionEntity> permissionEntities = permissionMapper.queryPermissionEntityList(new PermissionDto());
        for (PermissionEntity permissionEntity : permissionEntities){
            System.out.println(permissionEntity.getId()+","+permissionEntity.getName()+","+permissionEntity.getPermission());
        }
        List<RolePermissionEntity> rolePermissionEntities = rolePermissionMapper.queryRolePermissionEntityList(new RolePermissionDto());
        for (RolePermissionEntity rolePermissionEntity : rolePermissionEntities){
            System.out.println(rolePermissionEntity.getId()+","+rolePermissionEntity.getPermission_id()+","+rolePermissionEntity.getRole_id());
        }
        List<UserRoleEntity> userRoleEntities = userRoleMapper.queryUserRoleEntityList(new UserRoleDto());
        for (UserRoleEntity userRoleEntity: userRoleEntities){
            System.out.println(userRoleEntity.getId()+","+userRoleEntity.getRole_id()+","+userRoleEntity.getUser_id());
        }

        List<PermissionEntity> bbb = userMapper.findPermissionByUsername("bbb");
        for (PermissionEntity p : bbb){
            System.out.println(p.getId()+","+p.getName()+","+p.getPermission());
        }

        return "success";

    }

}

postman测试

1，获取token

首先我们通过用户名获取到token

2，具备权限测试

然后携带这个token，访问接口/queryTable，这个接口可以查询出数据库里所有相关的数据，以及用户bbb的权限列表打印在IDEA控制台

可以看到，用户bbb具备访问url1和url3的权限，那么我们继续测试接口/queryName，这个接口上方注解标注了需要具备url1权限才能访问，如下

3，不具备权限测试

然后测试接口/select，该接口上方注解表示只有具备url2的权限才能访问，用户bbb不具备这个权限，所以访问会失败，如下

至此，权限控制功能测试完毕