【SpringSecurity】六、基于数据库的认证与授权

今天药忘吃喽~ 2023-10-15 09:29 31阅读 0赞

#### 文章目录 ####

*  1、数据库表设计
 *  2、测试代码准备
 *  3、新建安全用户类
 *  4、实现UserDetailsService接口
 *  5、授权

## 1、数据库表设计 ##

接下来基于数据库里的用户信息进行登录认证，以RBAC设计表，分别为：

*  **用户表sys\_user** ：除了基本信息外，注意一些开关字段

![在这里插入图片描述][7c00ce903b0c4f10aeed6a456eafd56e.png]

*  **角色表sys\_role**：包括角色id，角色名称（存英文），以及备注（中文释义）

![在这里插入图片描述][cadc1f7fe60147a782f2f20e261c6f76.png]

*  **用户角色中间表sys\_role\_user** ：存两个多对多实体各自的主键

![在这里插入图片描述][d12abfb9d9b64a58bb9052f529a8fb47.png]

*  **菜单权限表sys\_menu**：存权限，一个权限对应一个功能

![在这里插入图片描述][746561d5637f4ad09333239ce0285462.png]

*  **角色权限关系表sys\_role\_menu**：存角色和权限这两个多对多实体的主键

![在这里插入图片描述][f73a1c3e4aa147d49956173f7b75187f.png]

## 2、测试代码准备 ##

整合mybatis做数据库的增删改查：

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.2</version>
    </dependency>

配置数据源和mybatis相关信息：

spring:
      datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/testDBy?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
        username: root
        password: root123
    mybatis:
      type-aliases-package: com.llg.entity
      configuration:
        map-underscore-to-camel-case: true # 下划线转驼峰
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl   # 打印SQL
      mapper-locations: classpath:mapper/*.xml  # xml文件位置

sys\_user表的PO类：

@Data
    public class SysUser implements Serializable {
        
        private Integer userId;
        private String username;
        private String password;
        private String sex;
        private String address;
        private Integer enabled;
        private Integer accountNoExpired;
        private Integer credentialsNoExpired;
        private Integer accountNoLocked;
    
    }

mapper层新建一个查询接口：

public interface SysUserDao {
        
        /**
         * 根据用户名获取用户信息
         * @param username
         * @return
         */
        SysUser getByUserName(@Param("username") String username);
    }

mapper目录下新建映射文件SysUserMapper.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="com.llg.dao.SysUserDao">
        <select id="getByUserName" resultType="sysUser">
            select user_id,username,password,sex,address,enabled,account_no_expired,credentials_no_expired,account_no_locked
            from sys_user where username=#{username}
        </select>
    </mapper>

启动类加@MapperScan注解，指明下mapper层接口的位置：

@SpringBootApplication
    @MapperScan("com.llg.dao")
    public class Application {
        
        public static void main(String[] args) {
        
            SpringApplication.run(Application.class,args);
        }
    }

## 3、新建安全用户类 ##

新建安全用户类，继承UserDetails，并和库里的SysUser类做转换。（注意这种思路两个类之间做转换，一个类做为属性定义在另一个类中）

public class SecurityUser implements UserDetails {
        
    
        private  final SysUser sysUser;
    
        public SecurityUser(SysUser sysUser) {
        
            this.sysUser=sysUser;
        }
    
        @Override
        public Collection<? extends GrantedAuthority> getAuthorities() {
        
            return null;   //注意权限数据暂时还未做处理
        }
    
        @Override
        public String getPassword() {
        
            String userPassword=this.sysUser.getPassword();
    		//注意清除密码
    		this.sysUser.setPassword(null);
    		return userPassword;
    
        }
    
        @Override
        public String getUsername() {
        
            return sysUser.getUsername();
        }
    
        @Override
        public boolean isAccountNonExpired() {
        
            return sysUser.getAccountNoExpired().equals(1);
        }
    
        @Override
        public boolean isAccountNonLocked() {
        
            return sysUser.getAccountNoLocked().equals(1);
        }
    
        @Override
        public boolean isCredentialsNonExpired() {
        
            return sysUser.getCredentialsNoExpired().equals(1);
        }
    
        @Override
        public boolean isEnabled() {
        
            return sysUser.getEnabled().equals(1);
        }
    }

到此，实现了将基于数据库自定义的SysUser对象和返给框架的UserDetails对象做了关联。

## 4、实现UserDetailsService接口 ##

@Service
    public class UserServiceImpl implements UserDetailsService {
        
        @Resource
        private SysUserDao sysUserDao;
    
        @Override
        public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        
            SysUser sysUser = sysUserDao.getByUserName(username);
            if(null==sysUser){
        
                throw new UsernameNotFoundException("账号不存在");
            }
            return new SecurityUser(sysUser);
        }
    }

如果能在数据库查到这个用户，就将他包装成UserDetails的格式返给框架。接下来补个安全配置类：

@EnableGlobalMethodSecurity(prePostEnabled = true)
    @Slf4j
    public class WebSecurityConfig extends WebSecurityConfigurerAdapter {
        
    
        @Bean
        public PasswordEncoder passwordEncoder() {
        
            return new BCryptPasswordEncoder();
        }
    
        @Override
        protected void configure(HttpSecurity http) throws Exception {
        
            http.authorizeRequests().anyRequest().authenticated();
            http.formLogin();
        }
    }

此时用户是可以正常登录的。但没有权限信息，写个接口查看权限信息：

![在这里插入图片描述][2cd67364d60b4bab8afc82d8951ed0c3.png]  
返回数据没权限信息：  
![在这里插入图片描述][7d3dcc806e05406bb90f22ffa88ea0c8.png]

## 5、授权 ##

再来处理权限部分，要实现根据用户名查询权限，RBAC下需要联结三张表来查，新建Mapper接口：

List<String> queryPermissionByUserId(@Param("userId") Integer userId);

映射文件SysMenuMapper.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="com.llg.dao.SysUserDao">
        <select id="queryPermissionByUserId" resultType="string">
            SELECT distinct sm.`code` 
            FROM `sys_role_user` sru 
            inner join sys_role_menu srm on sru.rid=srm.rid 
            inner join sys_menu sm on srm.mid=sm.id  
            where sru.uid=#{userId}  and sm.delete_flag=0
        </select>
    </mapper>

修改上面定义的SecurityUser类，加入权限。新增属性并加Set方法

private List<SimpleGrantedAuthority> simpleGrantedAuthorities;

@Override
    public Collection<? extends GrantedAuthority> getAuthorities() {
        
        return simpleGrantedAuthorities;
    }

整体：

![在这里插入图片描述][82be631cd6e84a66af7ebab6d5dd59aa.png]

修改UserServiceImpl实现类，加入权限信息：

@Service
    @Slf4j
    public class UserServiceImpl implements UserDetailsService {
        
        @Resource
        private SysUserDao sysUserDao;
        @Resource
        private SysMenuDao sysMenuDao;
    
        @Override
        public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        
            SysUser sysUser = sysUserDao.getByUserName(username);
            if(null==sysUser){
        
                throw new UsernameNotFoundException("账号不存在");
            }
            //查到权限
            List<String> strList=sysMenuDao.queryPermissionByUserId(sysUser.getUserId());
    		//使用stream流来转换
            List<SimpleGrantedAuthority> grantedAuthorities=strList.stream().map(SimpleGrantedAuthority::new).collect(toList());
            SecurityUser securityUser = new SecurityUser(sysUser);
            securityUser.setSimpleGrantedAuthorities(grantedAuthorities);
            return securityUser;
        }
    }

以上使用Stream流做了个转换，把一个个String对象转为了SimpleGrantedAuthority对象，再赋值给对象的权限属性，也可for循环：

//查到权限
     List<String> strList=sysMenuDao.queryPermissionByUserId(sysUser.getUserId());
     List<SimpleGrantedAuthority> grantedAuthorities = new ArrayList<>();
     for(String s:strList){
        
    	SimpleGrantedAuthority simpleGrantedAuthority = new SimpleGrantedAuthority(s);
    	grantedAuthorities.add(simpleGrantedAuthority);
     }
     securityUser.setSimpleGrantedAuthorities(grantedAuthorities);
     .....

再查看权限：

![在这里插入图片描述][34f639e12a1b4f5baaeb99a1e117f129.png]

[7c00ce903b0c4f10aeed6a456eafd56e.png]: https://img-blog.csdnimg.cn/7c00ce903b0c4f10aeed6a456eafd56e.png
[cadc1f7fe60147a782f2f20e261c6f76.png]: https://img-blog.csdnimg.cn/cadc1f7fe60147a782f2f20e261c6f76.png
[d12abfb9d9b64a58bb9052f529a8fb47.png]: https://img-blog.csdnimg.cn/d12abfb9d9b64a58bb9052f529a8fb47.png
[746561d5637f4ad09333239ce0285462.png]: https://img-blog.csdnimg.cn/746561d5637f4ad09333239ce0285462.png
[f73a1c3e4aa147d49956173f7b75187f.png]: https://img-blog.csdnimg.cn/f73a1c3e4aa147d49956173f7b75187f.png
[2cd67364d60b4bab8afc82d8951ed0c3.png]: https://img-blog.csdnimg.cn/2cd67364d60b4bab8afc82d8951ed0c3.png
[7d3dcc806e05406bb90f22ffa88ea0c8.png]: https://img-blog.csdnimg.cn/7d3dcc806e05406bb90f22ffa88ea0c8.png
[82be631cd6e84a66af7ebab6d5dd59aa.png]: https://img-blog.csdnimg.cn/82be631cd6e84a66af7ebab6d5dd59aa.png
[34f639e12a1b4f5baaeb99a1e117f129.png]: https://img-blog.csdnimg.cn/34f639e12a1b4f5baaeb99a1e117f129.png