springboot security数据库 spring security基于数据库

学习本篇文章之前一定 要具备spring security的基础知识，可以参考：security基础学习

上篇文章我们spring security登录的用户与密码是手动进行设置的，有两种方式：一种是application.properies文件中设置，还有一种是java配置类里进行设置。但是我们在实际的项目开发里一般都会利用数据库进行用户权限认证，这样也有利于用户权限的动态更改。

本篇文章虽然篇幅较长，但是请大家耐心读下，最好可以动下手把这个demo给实现。

实战

（1）创建web项目，引入security、web 、mysql、mybatis与druid依赖即可（druid手动进行配置），

pom.xml配置如下：

<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-security</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.4</version>
		</dependency>

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.1.10</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.security</groupId>
			<artifactId>spring-security-test</artifactId>
			<scope>test</scope>
		</dependency>

（2）在navicate执行sql脚本

CREATE DATABASE /*!32312 IF NOT EXISTS*/`security` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `security`;

/*Table structure for table `menu` */

DROP TABLE IF EXISTS `menu`;

CREATE TABLE `menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pattern` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `menu` */

insert  into `menu`(`id`,`pattern`) values 
(1,'/db/**'),
(2,'/admin/**'),
(3,'/user/**');

/*Table structure for table `menu_role` */

DROP TABLE IF EXISTS `menu_role`;

CREATE TABLE `menu_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mid` int(11) DEFAULT NULL,
  `rid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `menu_role` */

insert  into `menu_role`(`id`,`mid`,`rid`) values 
(1,1,1),
(2,2,2),
(3,3,3);

/*Table structure for table `role` */

DROP TABLE IF EXISTS `role`;

CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `nameZh` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `role` */

insert  into `role`(`id`,`name`,`nameZh`) values 
(1,'ROLE_dba','数据库管理员'),
(2,'ROLE_admin','系统管理员'),
(3,'ROLE_user','用户');

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `enabled` tinyint(1) DEFAULT NULL,
  `locked` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `user` */

insert  into `user`(`id`,`username`,`password`,`enabled`,`locked`) values 
(1,'root','$2a$10$RMuFXGQ5AtH4wOvkUqyvuecpqUSeoxZYqilXzbz50dceRsga.WYiq',1,0),
(2,'admin','$2a$10$RMuFXGQ5AtH4wOvkUqyvuecpqUSeoxZYqilXzbz50dceRsga.WYiq',1,0),
(3,'sang','$2a$10$RMuFXGQ5AtH4wOvkUqyvuecpqUSeoxZYqilXzbz50dceRsga.WYiq',1,0);

/*Table structure for table `user_role` */

DROP TABLE IF EXISTS `user_role`;

CREATE TABLE `user_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) DEFAULT NULL,
  `rid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `user_role` */

insert  into `user_role`(`id`,`uid`,`rid`) values 
(1,1,1),
(2,1,2),
(3,2,2),
(4,3,3);

执行完成后，如图

menu表：访问的路径种类

role表：角色表

user表：用户表（密码的明文都是123）

user_role表：一个用户具有哪些角色（关联表）

menu_role表：访问一个路径需要哪些角色（关联表）

（3）application.properies进行数据库配置

spring.datasource.username=root
spring.datasource.password=1234
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/security?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

(4)创建实体类，get set tostring方法自己生成

menu：

public class Menu {
    private Integer id;
    private String pattern;
    private List<Role> roles;

role

public class Role {
    private Integer id;
    private String name;
    private String nameZh;

user：（创建此类要继承自org.springframework.security.core.userdetails.UserDetails）

public class User implements UserDetails{
    private Integer id;
    private String username;
    private String password;
    private Boolean enabled;
    private Boolean locked;
    private List<Role> roles;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setPassword(String password) {
        this.password = password;
    }


    public void setEnabled(Boolean enabled) {
        this.enabled = enabled;
    }


    public void setLocked(Boolean locked) {
        this.locked = locked;
    }

    public List<Role> getRoles() {
        return roles;
    }

    public void setRoles(List<Role> roles) {
        this.roles = roles;
    }

    @Override
    public Collection<? extends GrantedAuthority> getAuthorities() {
    	//相当于告诉spring security该用户具有哪些角色
        List<SimpleGrantedAuthority> authorities = new ArrayList<>();
        for (Role role : roles) {
            authorities.add(new SimpleGrantedAuthority(role.getName()));
        }
        return authorities;
    }

    @Override
    public String getPassword() {
        return password;
    }

    @Override
    public String getUsername() {
        return username;
    }

    @Override
    public boolean isAccountNonExpired() {
    	//设置账户是否不过期 直接返回true
        return true;
    }

    //注意 isxxx的方法存在时，需要删除对应属性的get方法
    @Override
    public boolean isAccountNonLocked() {
        //设置账户是否被锁定 这边返回的!locked 因为数据库里locked = 0表示没被锁定，locked = 1表示被锁定
        return !locked;
    }

    @Override
    public boolean isCredentialsNonExpired() {
        //设置账户凭证是否不过期 直接返回true
        return true;
    }

    @Override
    public boolean isEnabled() {
        //账户是否可用
        return enabled;
    }
}

（5）访问数据库用到的是mybatis 所以要创建对应的mapper接口与mapper.xml

对应的xml

<mapper namespace="com.example.securitydatabase.mapper.UserMapper">
    <select id="loadUserByUsername" resultType="com.example.securitydatabase.Bean.User">
        SELECT * FROM  user WHERE username = #{username}
    </select>

    <select id="getRolesById" resultType="com.example.securitydatabase.Bean.Role">
        SELECT * FROM role WHERE id in (SELECT rid FROM user_role WHERE uid = #{id} )
    </select>
</mapper>

对应的xml（如果不知道mybatis如何返回嵌套list请看：参考

<mapper namespace="com.example.securitydatabase.mapper.MenuMapper">

    <resultMap id="BaseResultMap" type="com.example.securitydatabase.Bean.Menu">
        <id property="id" column="id"/>
        <result property="pattern" column="pattern"/>
        <collection property="roles" ofType="com.example.securitydatabase.Bean.Role">
            <id column="rid" property="id"/>
            <result column="rname" property="name"/>
            <result column="rnameZh" property="nameZh"/>
        </collection>
    </resultMap>


    <select id="getAllMenus" resultMap="BaseResultMap">
        select m.*,r.`id` as rid,r.`name` as rname,r.`nameZh` as rnameZh from menu m left join menu_role mr on m.`id`=mr.`mid` left join role r on mr.`rid`=r.`id`
    </select>
</mapper>

最后记得在SpringBootApplication中配置mapper的扫描路径（因为我们在mapper接口里没配置@mapper注解）

@SpringBootApplication
@MapperScan(basePackages = "com.example.securitydatabase.mapper")
public class SecuritydatabaseApplication {

在pom.xml里告诉系统编译时不要过滤.xml文件（因为我们的mapper.xml放在src/main/java文件夹下）

<build>
		<resources>
			<resource>
				<directory>src/main/java</directory>
				<includes>
					<include>**/*.xml</include>
				</includes>
			</resource>
			<resource>
				<directory>src/main/resources</directory>
			</resource>
		</resources>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

（6）然后创建userservice与menuservice，userservice有两个功能：查询该用户是否存在、查询该用户所具有的角色；menuservice的功能是获取所有路径种类。

@Service
public class UserService implements UserDetailsService {
	// UserDetailsService 是spring security提供的接口
    @Autowired
    UserMapper userMapper;

    @Override
    public UserDetails loadUserByUsername(String s) throws UsernameNotFoundException {
        User user = userMapper.loadUserByUsername(s);
        if(user == null){
            throw new UsernameNotFoundException("用户不存在");
        }
        user.setRoles(userMapper.getRolesById(user.getId()));
        //直接返回即可 spring security会帮助我们判断登录的账户密码是否正确
        return user;
    }
}

@Service
public class MenuService {

    @Autowired
    MenuMapper menuMapper;

    public List<Menu> getAllMenus(){
       return menuMapper.getAllMenus();
    }
}

（7）接下来我们要进行spring security的配置了

创建Myfilter，这类似与一个过滤器，当访问路径时会先走这个过滤器，不管状态是登录还是未登录

@Component
public class MyFilter implements FilterInvocationSecurityMetadataSource {
    //进行路径匹配的工具
    AntPathMatcher pathMatcher = new AntPathMatcher();

    @Autowired
    MenuService menuService;


    @Override
    public Collection<ConfigAttribute> getAttributes(Object o) throws IllegalArgumentException {
        //获取访问的路径
        String requestUrl = ((FilterInvocation) o).getRequestUrl();
        List<Menu> allMenus = menuService.getAllMenus();
        for (Menu menu : allMenus) {
            //判断该路径属于menu里的哪一种
            if (pathMatcher.match(menu.getPattern(), requestUrl)) {
                List<Role> roles = menu.getRoles();
                String[] rolesStr = new String[roles.size()];
                for (int i = 0; i < roles.size(); i++) {
                    rolesStr[i] = roles.get(i).getName();
                }
                //将访问该路径所需要的roles返回
                return SecurityConfig.createList(rolesStr);
            }
        }
        //若为其他路径则直接返回ROLE_login，ROLE_login作为一个标识而已
        return SecurityConfig.createList("ROLE_login");
    }

    @Override
    public Collection<ConfigAttribute> getAllConfigAttributes() {
        return null;
    }

    @Override
    public boolean supports(Class<?> aClass) {
        return true;
    }
}

创建MyAccessDecisionManager。继承自org.springframework.security.access.AccessDecisionManager;

@Component
public class MyAccessDecisionManager implements AccessDecisionManager {
    @Override
    public void decide(Authentication authentication, Object o, Collection<ConfigAttribute> collection) throws AccessDeniedException, InsufficientAuthenticationException {
        //authentication当前用户的一个登陆信息 collection当前所访问的路径需要哪些角色
        for (ConfigAttribute attribute : collection) {
            if ("ROLE_login".equals(attribute.getAttribute())) {
                //如果是ROLE_login则表示为其他访问路径，不存在于menu中
                //下面判断 用户是登陆了还是未登录
                if (authentication instanceof AnonymousAuthenticationToken) {
                    throw new AccessDeniedException("非法请求!");
                } else {
                    return;
                }
            }
            Collection<? extends GrantedAuthority> authorities = authentication.getAuthorities();
            for (GrantedAuthority authority : authorities) {
                //当前所登录的用户满足 访问的url所对应的多个角色中的一个即可访问
                if (authority.getAuthority().equals(attribute.getAttribute())) {
                    return;
                }
            }
        }
        throw new AccessDeniedException("非法请求!");
    }

    @Override
    public boolean supports(ConfigAttribute configAttribute) {
        return true;
    }

    @Override
    public boolean supports(Class<?> aClass) {
        return true;
    }
}

最后创建securityConfig

@Configuration
public class SecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    UserService userService;
    @Autowired
    MyFilter myFilter;
    @Autowired
    MyAccessDecisionManager myAccessDecisionManager;


    @Bean
    PasswordEncoder passwordEncoder(){
        return  new BCryptPasswordEncoder();
    }

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        //验证登录的账号与密码
        auth.userDetailsService(userService);

    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        //获取请求的路径 并对登录的用户进行判断 该用户是否具有 可以访问此路径的角色中的一个
        http.authorizeRequests()
                .withObjectPostProcessor(new ObjectPostProcessor<FilterSecurityInterceptor>() {
                    @Override
                    public <O extends FilterSecurityInterceptor> O postProcess(O o) {
                    //设置filter 与manager
                        o.setAccessDecisionManager(myAccessDecisionManager);
                        o.setSecurityMetadataSource(myFilter);
                        return o;
                    }
                })
                .and()
                .formLogin()
                .permitAll()
                .and()
                .csrf().disable();
    }
}

到此spring security配置完成了。访问的具体过程 经过 MyFilter --------》 MyAccessDecisionManager
（8）编写 controller 测试

@RestController
public class HelloController {
    @GetMapping("/hello")
    public String hello(){
        return "hello";
    }

    @GetMapping("/db/hello")
    public String db() {
        return "hello db";
    }

    @GetMapping("/admin/hello")
    public String admin() {
        return "hello admin";
    }

    @GetMapping("/user/hello")
    public String user() {
        return "hello user";
    }
}

整个项目文件结构图如下：

（9）测试

以登录root用户为例。先访问/hello，访问该路径的条件是只要登录即可

访问/db/hello和/admin/hello，访问该路径的条件是需要ROLE_dba角色

最后访问/user/hello，访问该路径的条件是需要ROLE_user角色，root不具有此角色

当然，大家也可以测试下admin和sang用户

总结：

基于spring security数据库认证的主要难点还是在设计数据库的用户、角色和访问路径之间的关系，只有理清对应的关系后再在spring boot中配置spring security就会变得相对轻松。