1.说明
之前的几个示例都是从内存中获取的数据，这里改写为从数据库获取数据实现用户登录访问功能
从内存读取数据的代码可参看
地址：https://blog.csdn.net/qq_32224047/article/details/108604598
2.代码示例
数据库建表语句
CREATE DATABASE /*!32312 IF NOT EXISTS*/`security` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `security`;
DROP TABLE IF EXISTS `tb_permission`;
CREATE TABLE `tb_permission` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT,`user_id` BIGINT(20) DEFAULT NULL COMMENT '父权限',`name` VARCHAR(64) NOT NULL COMMENT '权限名称',`authority` VARCHAR(64) NOT NULL COMMENT '权限英文名称',`created` DATETIME NOT NULL,`updated` DATETIME NOT NULL,PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8 COMMENT='权限表';
/*Data for the table `tb_permission` */ INSERTINTO `tb_permission`(`id`,`user_id`,`name`,`authority`,`created`,`updated`) VALUES (1,1,'写入','write','2019-06-07 15:00:00','2019-06-07 15:00:00'),(2,1,'更新','update','2019-06-07 15:00:00','2019-06-07 15:00:00'),(3,1,'删除','delete','2019-06-07 15:00:00','2019-06-07 15:00:00'),(4,1,'读取','read','2019-06-07 15:00:00','2019-06-07 15:00:00'),(5,2,'读取','read','2019-06-07 15:00:00','2019-06-07 15:00:00');
/*Table structure for table `tb_user` */ DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT,`username` VARCHAR(50) NOT NULL COMMENT '用户名',`password` VARCHAR(64) NOT NULL COMMENT '密码，加密存储',`phone` VARCHAR(20) DEFAULT NULL COMMENT '注册手机号',`created` DATETIME NOT NULL,`updated` DATETIME NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `username` (`username`) USING BTREE,UNIQUE KEY `phone` (`phone`) USING BTREE) ENGINE=INNODB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8 COMMENT='用户表';
/*Data for the table `tb_user` */ INSERTINTO `tb_user`(`id`,`username`,`password`,`phone`,`created`,`updated`) VALUES (1,'admin','123456','18510270606','2019-06-15 10:00:58','2019-06-15 10:00:58'),(2,'user','123456','18610270607','2019-06-15 10:00:58','2019-06-15 10:00:58');
表的机构信息如下图
文章图片
文章图片
项目结构如下
文章图片
启动类StarterSecurity1 添加了Mapper包的扫描
@SpringBootApplication
@MapperScan("cn.wy.mapper")
public class StarterSecurity1 {
public static void main(String[] args) {
SpringApplication.run(StarterSecurity1.class,args);
}
}配置文件数据
#datasource配置
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.url=jdbc:mysql:///security?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.driver-class-name=com.mysql.jdbc.Driver#mybatis
mybatis.type-aliases-package=cn.wy.domain
#将带有下划线的表字段映射为驼峰格式的实体类属性
#添加了该项配置后，在开发中只需要根据查询返回的字段，创建好实体类就可以了
mybatis.configuration.map-underscore-to-camel-case=true控制器类AdminController
@RestController
public class AdminController {
@RequestMapping("/admin/write")
public String write() {
return "写入数据";
}@RequestMapping("/admin/update")
public String update() {
return "更新数据";
}@RequestMapping("/admin/delete")
public String delete() {
return "删除数据";
}@RequestMapping("/user/read")
public String read() {
return "读取数据，不需要权限";
}
@RequestMapping("/other")
public String other() {
return "其他权限";
}
}配置类MyWebSecurityConfig
import cn.wy.service.UserService;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.config.annotation.web.configurers.ExpressionUrlAuthorizationConfigurer;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.security.crypto.password.NoOpPasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;
@Configuration
@EnableWebSecurity
public class MyWebSecurityConfig
extends WebSecurityConfigurerAdapter {
//明文加密器,只需要在内存中有这个管理对象,如果不添加，从前端登录时会抛出异常
@Bean
public PasswordEncoder passwordEncoder(){
return NoOpPasswordEncoder.getInstance();
}
//构造一个内存框架对象，获取数据库中的数据
@Bean
public UserDetailsService myUserDetailsService(){
return new UserService();
}
@Override
protected void configure(AuthenticationManagerBuilder auth) throws Exception {
//获取数据库中的数据
auth.userDetailsService(myUserDetailsService());
}@Override
protected void configure(HttpSecurity http) throws Exception {
http.authorizeRequests()// 开启权限控制,通过ANT规范，自定义逻辑//当访问/admin/write时必须有write权限
.antMatchers("/admin/write").hasAuthority("write")
.antMatchers("/admin/update").hasAuthority("update")
.antMatchers("/admin/delete").hasAuthority("delete")
.antMatchers("/user/read").hasAuthority("read")
.anyRequest()//任意请求
.authenticated();
//必须经过认证 只要登陆就能访问
http.formLogin();
//开启表单认证
http.httpBasic();
//开启http 基本认证
}}实体类User和Permission类
package cn.wy.domain;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.userdetails.UserDetails;
import java.io.Serializable;
import java.util.Collection;
import java.util.Date;
import java.util.List;
public class User implements Serializable {
private Long id;
private String username;
private String password;
private String phone;
private Date created;
private Date updated;
private List authorities;
public Long getId() {
return id;
}public void setId(Long id) {
this.id = id;
}
@Override
public String getUsername() {
return username;
}public void setUsername(String username) {
this.username = username;
}
public void setAuthorities(List authorities) {
this.authorities = authorities;
}@Override
public String getPassword() {
return password;
}public void setPassword(String password) {
this.password = password;
}public String getPhone() {
return phone;
}public void setPhone(String phone) {
this.phone = phone;
}public Date getCreated() {
return created;
}public void setCreated(Date created) {
this.created = created;
}public Date getUpdated() {
return updated;
}public void setUpdated(Date updated) {
this.updated = updated;
}}
package cn.wy.domain;
import java.io.Serializable;
import java.util.Date;
public class Permission implements Serializable {
private Long id;
private Long userId;
private String name;
private String authority;
private Date created;
private Date updated;
public Long getId() {
return id;
}public void setId(Long id) {
this.id = id;
}public Long getUserId() {
return userId;
}public void setUserId(Long userId) {
this.userId = userId;
}public String getName() {
return name;
}public void setName(String name) {
this.name = name;
}public String getAuthority() {
return authority;
}public void setAuthority(String authority) {
this.authority = authority;
}public Date getCreated() {
return created;
}public void setCreated(Date created) {
this.created = created;
}public Date getUpdated() {
return updated;
}public void setUpdated(Date updated) {
this.updated = updated;
}
}
接口类UserMapper、PermissionMapper
直接在接口类上编写sql语句，不在创建对应的映射文件，注意sql 语句中换行后要添加一个空格，防止代码粘粘在一起，导致sql语句错误
package cn.wy.mapper;
import cn.wy.domain.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
public interface UserMapper {
//利用username读取user对象
@Select("select * from tb_user " +
"where username=#{username}")
public User selectUserByUsername(
@Param("username") String username);
}
package cn.wy.mapper;
import cn.wy.domain.Permission;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface PermissionMapper {
//利用user_id搜索的对应permission list
//注意 下面的sql中表名和 where 之间需要加一个空格，否则会粘粘在一起，导致sql语句错误
@Select("select * from tb_permission" +
" where user_id=#{userId}")
public List selectPermissionsByUserid(
@Param("userId") Long userId);
}
UserService类用于读取Mapper数据库数据，要和Security整合，需要实现接口UserDetailsService，注意自定义的User和Security提供的User
package cn.wy.service;
import cn.wy.domain.Permission;
import cn.wy.mapper.PermissionMapper;
import cn.wy.mapper.UserMapper;
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.User;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import java.util.ArrayList;
import java.util.List;
/**
* 用于读取Mapper数据库数据，要和Security整合，需要
* 实现接口UserDetailsService
*
*/
public class UserService implements UserDetailsService {
@Autowired
private UserMapper userMapper;
@Autowired
private PermissionMapper permissionMapper;
@Override
public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
//username参数,是在登陆时,用户传递的表单数据username
//主要读取数据库3个值 username password authorities
//这里自定义的User和security中的User重名，且那个包名很长所以这里写自定义User的包路径
cn.wy.domain.User user = userMapper.selectUserByUsername(username);
Long userId=user.getId();
List permissions = permissionMapper.selectPermissionsByUserid(userId);
//为了返回一个UserDetails 使用User
List authorities=new ArrayList<>();
for (Permission p:permissions) {
//循环一次,就拿到了表格权限,使用authority字段
String authorityName = p.getAuthority();
GrantedAuthority authority=
new SimpleGrantedAuthority(authorityName);
authorities.add(authority);
}//这里的User 是这个包下的 org.springframework.security.core.userdetails.User;
User userDetails=new User(
user.getUsername(),
user.getPassword(),
authorities);
user.setAuthorities(authorities);
return userDetails;
}}
测试验证：
登录user访问write功能
文章图片
【security|spring security入门--从数据库读取数据实现用户登录访问简单示例四】测试正常，无权限
文章图片
更换为admin用户
文章图片
测试正确
文章图片
其他测试均正常，这里不再列出
推荐阅读
- Spring|Spring更简单的读取和存储对象
- JAVAEE进阶|Lesson2（Spring更简单的读取和存储对象(使用注解)）
- MybatisPlus|Mybatis-Plus实现存储以及读取Java对象
- 四|Spring使用——读取properties配置文件
- spring|Spring 读取和存储对象
- 面试|【Spring----对象的读取和存储】
- JavaEE|Spring 创建和使用
- Spring|【Spring】Spring 更简单的读取和存储对象
- Spring|Spring~Spring的创建和使用（如何将对象储存到Spring、如何将对象从Spring中读取出来）