Spring Security入门基础

此篇文章为上一篇 Spring Security入门基础 的续作，了解这里的内容需要对上一篇有了解

1.5 使用数据库鉴权

使用数据库鉴权，需要先提前建立好关于权限的表（用户表 sys_user，角色表 sys_permission，权限表 sys_menu，用户角色表 sys_user_permission，角色权限表 sys_permission_menu 等等），我这边使用MySQL+Druid进行数据库连接，工具使用Mybatis+mybatis-plus，前端使用element-ui的方式，前端密码框内容到请求参数中，使用了md5加密，即数据库密码=new BCryptPasswordEncoder().encode(md5(前端输入))。（我在数据库鉴权使用了BCryptPasswordEncoder方式）

1.5.1 建表

-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (`id` int(11) NOT NULL AUTO_INCREMENT,`username` varchar(63) NOT NULL,`password` varchar(63) NOT NULL,`created` datetime NOT NULL,`updated` datetime DEFAULT NULL,`enabled` int(11) NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `index_user` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES ('1', 'default', '$2a$10$szvYfsb71jUUQL8h8kDk8eFfXVweob2wuYD5qDaIVjMrf/D7MeTjO', '2021-11-01 21:45:41', null, '1');
INSERT INTO `sys_user` VALUES ('2', 'lmc', '$2a$10$A9vUkwciu7oVeNZrUQB3KuXv9g4/85WPhShbI0Sxx73HWzy.hDJpC', '2021-11-01 21:50:44', null, '1');-- ----------------------------
-- Table structure for sys_permission
-- ----------------------------
DROP TABLE IF EXISTS `sys_permission`;
CREATE TABLE `sys_permission` (`id` int(11) NOT NULL AUTO_INCREMENT,`authority` varchar(63) NOT NULL,`permission_desc` varchar(127) DEFAULT NULL,`created` datetime NOT NULL,`updated` datetime DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `index_permission_name` (`authority`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of sys_permission
-- ----------------------------
INSERT INTO `sys_permission` VALUES ('1', 'DEFAULT', '默认角色', '2021-11-01 21:52:30', null);
INSERT INTO `sys_permission` VALUES ('2', 'ROLE_USER', '普通用户', '2021-11-01 21:54:27', null);
INSERT INTO `sys_permission` VALUES ('3', 'ROLE_ADMIN', '管理员', '2021-11-01 21:54:45', null);-- ----------------------------
-- Table structure for sys_menu
-- ----------------------------
DROP TABLE IF EXISTS `sys_menu`;
CREATE TABLE `sys_menu` (`id` int(11) NOT NULL AUTO_INCREMENT,`menu_name` varchar(63) NOT NULL,`menu_path` varchar(127) NOT NULL,`parent_id` int(11) DEFAULT NULL,`image_url` varchar(255) DEFAULT NULL,`created` datetime NOT NULL,`creator` int(11) NOT NULL,`updated` datetime DEFAULT NULL,`updator` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Table structure for sys_user_permission
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_permission`;
CREATE TABLE `sys_user_permission` (`id` int(11) NOT NULL AUTO_INCREMENT,`user_id` int(11) NOT NULL,`permission_id` int(11) NOT NULL,`created` datetime NOT NULL,`creator` varchar(63) DEFAULT NULL,`updated` datetime DEFAULT NULL,`updator` varchar(63) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of sys_user_permission
-- ----------------------------
INSERT INTO `sys_user_permission` VALUES ('1', '2', '3', '2021-11-01 21:55:55', '2', null, null);
INSERT INTO `sys_user_permission` VALUES ('2', '2', '1', '2021-11-03 22:38:32', '2', null, null);-- ----------------------------
-- Table structure for sys_permission_menu
-- ----------------------------
DROP TABLE IF EXISTS `sys_permission_menu`;
CREATE TABLE `sys_permission_menu` (`id` int(11) NOT NULL AUTO_INCREMENT,`permission_id` int(11) NOT NULL,`menu_id` int(11) NOT NULL,`created` datetime NOT NULL,`creator` varchar(63) NOT NULL,`updated` datetime DEFAULT NULL,`updator` varchar(63) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在上面这些表中，为了方便，外键没有设置，最好还是要设置一下；权限表和角色权限表还没有数据（暂时不用）。

注意：sys_user表的password使用了加密方式，用户default登录时密码应输入default，用户lmc登录时密码应输入 123

1.5.2 引入依赖

由于使用了数据库鉴权，因此我需要在pom.xml添加MySQL和mybatis等相关的依赖配置。

		<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.21</version></dependency><!-- https://mvnrepository.com/artifact/com.alibaba/druid --><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.2.6</version></dependency><!-- mybatis相关 --><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.2.0</version></dependency><!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter --><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.4.2</version></dependency>

1.5.3 配置文件

引入依赖之后，在配置文件application.yml中也添加相应数据

spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/lmc-tools?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTCusername: rootpassword: roottype: com.alibaba.druid.pool.DruidDataSource# 初始化，最小，最大连接数initialSize: 3minidle: 3maxActive: 18# 获取数据库连接等待的超时时间maxWait: 60000# 配置多久进行一次检测，检测需要关闭的空闲连接 单位毫秒timeBetweenEvictionRunsMillis: 60000validationQuery: SELECT 1 FROM dualmybatis:type-aliases-package: com.lmc.security.entitymapper-locations: classpath*:mapper/*.xmlmybatis-plus:global-config:db-config:id-type: autofield-strategy: not_empty#驼峰下划线转换column-underline: true#逻辑删除配置logic-delete-value: 0logic-not-delete-value: 1db-type: mysqlrefresh: falseconfiguration:map-underscore-to-camel-case: truecache-enabled: false

同时在启动类SecurityApplication上添加注解@MapperScan("com.lmc.security.mapper")

注意，上一篇博客忘记的 1.4自定义用户认证 忘记写上application.yml添加的配置了

spring:thymeleaf:prefix: classpath:/templates/suffix: .html

1.5.4 修改用户认证类前

前面1.4.2的时候我们使用内存方式进行用户登录，对应的是inMemoryAuthentication()方法，如果使用数据库鉴权的话，就改成使用userDetailsService(T userDetailsService)方法。同时，我们需要创建接口UserDetailsService的实现类，实现其loadUserByUsername(String s)方法：通过用户名获取用户信息（要包括角色信息）。

不过在修改WebSecurityConfiguration.java前，我们还需要重新创建一些用户，角色表的相应实体类，数据映射类，服务类等，用户类需要实现UserDetails接口，角色类需要实现GrantedAuthority接口，如下所示：

用户类 UserEntity：

package com.lmc.security.entity;import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import org.springframework.security.core.userdetails.UserDetails;import java.util.Date;
import java.util.List;/*** @author lmc* @Description: TODO 用户实体类* @Create 2021-11-01 22:11* @version: 1.0*/
@Data
@TableName(value = "sys_user")
public class UserEntity implements UserDetails {private Integer id;private String username;private String password;private Date created;private Date updated;private Boolean enabled;@TableField(exist = false)private List<PermissionEntity> authorities;/*** 用户是否不过期* @return*/@Overridepublic boolean isAccountNonExpired() {return true;}/*** 账号是否不被锁* @return*/@Overridepublic boolean isAccountNonLocked() {return true;}/*** 该凭证是否可用* @return*/@Overridepublic boolean isCredentialsNonExpired() {return true;}/*** 该用户是否可用* @return*/@Overridepublic boolean isEnabled() {return true;}
}

注意，默认实现的接口中，isAccountNonExpired()，isAccountNonLocked()，isCredentialsNonExpired()和isEnabled()都是返回false，我们需要改成true

角色类 PermissionEntity：

package com.lmc.security.entity;import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import org.springframework.security.core.GrantedAuthority;import java.util.Date;/*** @author lmc* @Description: TODO 角色实体类* @Create 2021-11-01 22:15* @version: 1.0*/
@Data
@TableName(value = "sys_permission")
public class PermissionEntity implements GrantedAuthority {private Integer id;private String authority;private String permissionDesc;private Date created;private Date updated;}

UserEntityMapper.java

package com.lmc.security.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lmc.security.entity.UserEntity;
import org.apache.ibatis.annotations.Mapper;/*** @author lmc* @Description: TODO* @Create 2021-11-01 22:52* @version: 1.0*/
@Mapper
public interface UserEntityMapper extends BaseMapper<UserEntity> {
}

PermissionEntityMapper.java

package com.lmc.security.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lmc.security.entity.PermissionEntity;
import org.apache.ibatis.annotations.Mapper;import java.util.List;/*** @author lmc* @Description: TODO* @Create 2021-11-02 23:07* @version: 1.0*/
@Mapper
public interface PermissionEntityMapper extends BaseMapper<PermissionEntity> {List<PermissionEntity> getAuthoritiesByUserId(Integer userId);}

resources/mapper目录下创建文件PermissionEntityMapper.xml，UserEntityMapper.xml暂时不需要，就不创建

PermissionEntityMapper.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.lmc.security.mapper.PermissionEntityMapper"><select id="getAuthoritiesByUserId" parameterType="int" resultType="com.lmc.security.entity.PermissionEntity">select id, authority, permission_desc, created, updated from sys_permissionwhere id in (select permission_id from sys_user_permission where user_id = #{userId})</select></mapper>

再继续创建UserEntity实体类的数据服务类UserEntityService和UserEntityServiceImpl

package com.lmc.security.service;/*** @author lmc* @Description: TODO* @Create 2021-11-03 20:46* @version: 1.0*/
public interface UserEntityService{/*** 获取该用户名是否存在* @param username* @return*/Boolean isExistName(String username);}

和

package com.lmc.security.service.impl;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.lmc.security.entity.UserEntity;
import com.lmc.security.mapper.UserEntityMapper;
import com.lmc.security.service.UserEntityService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.Objects;/*** @author lmc* @Description: TODO* @Create 2021-11-03 20:48* @version: 1.0*/
@Service
public class UserEntityServiceImpl implements UserEntityService {@Autowiredprivate UserEntityMapper userEntityMapper;@Overridepublic Boolean isExistName(String username) {UserEntity userEntity = userEntityMapper.selectOne(new QueryWrapper<UserEntity>().eq("username", username).last("limit 1"));if (Objects.nonNull(userEntity)) {return true;}return false;}
}

都准备好后，就可以创建UserDetailsService.java的实现类 UserService.java,重写loadUserByUsername方法

package com.lmc.security.service;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.lmc.security.entity.PermissionEntity;
import com.lmc.security.entity.UserEntity;
import com.lmc.security.mapper.PermissionEntityMapper;
import com.lmc.security.mapper.UserEntityMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
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.Service;import java.util.List;
import java.util.Objects;/*** @author lmc* @Description: TODO* @Create 2021-11-01 23:12* @version: 1.0*/
@Service
public class UserService implements UserDetailsService {private final static Logger logger = LoggerFactory.getLogger(UserService.class);@Autowiredprivate UserEntityMapper userEntityMapper;@Autowiredprivate PermissionEntityMapper permissionEntityMapper;@Overridepublic UserDetails loadUserByUsername(String s) throws UsernameNotFoundException {UserEntity userEntity = userEntityMapper.selectOne(new QueryWrapper<UserEntity>().eq("username", s).last("limit 1"));if (Objects.nonNull(userEntity)) {List<PermissionEntity> permissionEntityList = permissionEntityMapper.getAuthoritiesByUserId(userEntity.getId());userEntity.setAuthorities(permissionEntityList);}logger.info("userEntity:{}", userEntity);return userEntity;}
}

1.5.5 修改用户认证类WebSecurityConfiguration

前面操作完成后，就可以修改用户认证类了，主要修改configure(AuthenticationManagerBuilder auth)方法

	@Overrideprotected void configure(AuthenticationManagerBuilder auth) throws Exception {
//        auth
//                .inMemoryAuthentication()   // 在内存存储用户信息
                .withUser("lmc").password("{noop}123") // 用户名为lmc,密码为123，{noop}表示密码不加密
//                .passwordEncoder(new BCryptPasswordEncoder()) // 使用BC加密方式
//                .withUser("lmc").password(new BCryptPasswordEncoder().encode("123"))
//                .roles("USER");  // 给用户lmc赋予USER角色auth.userDetailsService(userService).passwordEncoder(new BCryptPasswordEncoder());}

同时configure(HttpSecurity http)也添加

	.antMatchers("/home.html").hasRole("DEFAULT")

让home.html需要角色DEFAULT才能访问

1.5.6 登录页面

对于前面的登录页面，我新加了输入用户后的验证操作（该用户是否存在），修改后的login.html如下所示

<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><title>Login Page</title><!-- 引入样式 --><link rel="stylesheet" href="/assets/element-ui/index.css">
</head>
<style>.el-header {background-color: #1B73E8;color: white;text-align: left;line-height: 60px;}.el-footer {background-color: #F5F5F5;color: black;text-align: center;line-height: 60px;font-size: 12px;}.el-main {background-color: white;color: #333;text-align: center;line-height: 160px;}body > .el-container {margin-bottom: 40px;}.el-row {margin-bottom: 20px;}.el-col {border-radius: 4px;}.bg-purple-dark {background: #99a9bf;}.bg-purple {background: #d3dce6;}.bg-purple-light {/*background: #e5e9f2;*/}.grid-content {border-radius: 4px;min-height: 36px;height: 800px;}.row-bg {padding: 10px 0;background-color: #f9fafc;}.el-card {padding: 80px;height: 638px;}
</style>
<body><div id="app"><el-container><el-header>lmc-tools</el-header><el-main><el-row :gutter="20"><el-col :span="7"><div class="grid-content bg-purple"></div></el-col><el-col :span="10"><div class="grid-content bg-purple-light"><el-card class="box-card"><el-form ref="form" action="/login" method="post" :model="form" :rules="rules" label-width="100px" name="loginform" class="login-form"><el-form-item label="用户名" prop="username"><el-input v-model="form.username" name="username" @blur="onUsernameChange()" clearable></el-input><div style="visibility: hidden" id="user-tip" class="el-form-item__error">该用户不存在</div></el-form-item><el-form-item label="密码" prop="password"><el-input v-model="form.password" name="password" show-password type="password" clearable></el-input><div th:if="${message} == 'error'" id="password-tip" class="el-form-item__error">密码错误</div></el-form-item><el-form-item><el-button type="primary" @click="submitForm('form')">登录</el-button></el-form-item></el-form></el-card></div></el-col><el-col :span="7"><div class="grid-content bg-purple"></div></el-col></el-row></el-main><el-footer>Copyright © 2021 lmc</el-footer></el-container></div>
</body>
<!-- 引入vue -->
<script src="/assets/vue/vue.js"></script>
<script src="/assets/vue/dict/axios.min.js"></script>
<!-- 引入Element-UI组件库 -->
<script src="/assets/element-ui/index.js"></script>
<!-- 引入md5 -->
<script src="/assets/md5/md5.js"></script>
<script>new Vue({el: "#app",data: {form: {username: '',password: ''},rules: {username: [{required: true, message: '用户名不能为空', trigger: 'blur'}],password: [{required: true, message: '密码不能为空', trigger: 'blur'}]}},methods: {// 登录按钮触发事件submitForm: function(formName) {// 验证输入框是否为空this.$refs[formName].validate((valid) => {if (valid && document.getElementById("user-tip").style.visibility == 'hidden') {document.loginform.password.value = hex_md5(document.loginform.password.value)document.loginform.submit()}else if (document.getElementById("user-tip").style.visibility != 'hidden') {document.getElementById("user-tip").style.fontSize = '14px'setTimeout(function () {document.getElementById("user-tip").style.fontSize = '12px'}, 1000)}})},onUsernameChange: function () {// 判断该用户是否存在axios.get('/user/isExistUser/' + this.form.username).then(res => {if (res.data.code == 200) {if (res.data.data != null && res.data.data.isExist) {document.getElementById("user-tip").style.visibility = 'hidden'}else {document.getElementById("user-tip").style.visibility = 'visible'}}else {document.getElementById("user-tip").innerHTML = "获取用户名失败，请联系管理员处理"document.getElementById("user-tip").style.visibility = 'visible'}}).catch(error => {document.getElementById("user-tip").innerHTML = "获取用户名失败，请联系管理员处理：" + errordocument.getElementById("user-tip").style.visibility = 'visible'})}}})</script>
</html>

可以看到，我使用 /user/isExistUser/{username} 来访问后台接口，因此要在用户认证类WebSecurityConfiguration中添加忽略，如下所示：

	@Overridepublic void configure(WebSecurity web) throws Exception {web.ignoring().antMatchers("/assets/**").antMatchers("/403", "/404", "/500").antMatchers("/login.html", "/user/isExistUser/*");}

接口如下：

package com.lmc.security.controller;import com.lmc.common.entity.WebResult;
import com.lmc.security.service.UserEntityService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;/*** @author lmc* @Description: TODO* @Create 2021-11-03 20:55* @version: 1.0*/
@RestController
@RequestMapping("/user")
public class UserEntityController {@Autowiredprivate UserEntityService userEntityService;/*** 判断该用户是否存在* @param username* @return*/@RequestMapping("/isExistUser/{username}")public WebResult isExistUser(@PathVariable("username") String username) {if (userEntityService.isExistName(username)) {return WebResult.ok("该用户存在").withKeyValue("isExist", true);}else {return WebResult.ok("该用户名尚未注册").withKeyValue("isExist", false);}}}

1.5.7 测试

操作完之后，就可以运行项目，访问 http://localhost:9040/home.html ，会跳转到http://localhost:9040/login.html，输入账号/密码：lmc/123，将会跳转到 http://localhost:9040/home.html页面。

然后注销： http://localhost:9040/logout，重新用default/default登录，访问 http://localhost:9040/home.html，会出现403异常，因为用户default没有DEFAULT角色权限，不能访问home.html