Chapter 04- Customizing THE RBAC table for Authentication

1. Create user-defined user tables, role tables, and user role relationship tables

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rolename` varchar(255) NOT NULL,
  `rolememo` varchar(255) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `realname` varchar(255) DEFAULT NULL,
  `isenable` varchar(255) NOT NULL,
  `islock` varchar(255) NOT NULL,
  `iscredentials` varchar(255) DEFAULT NULL,
  `createtime` datetime DEFAULT NULL,
  `logintime` datetime DEFAULT NULL,
  `isexpire` varchar(255) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
  `userid` int(11) DEFAULT NULL,
  `roleid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;
Copy the code

2. Create a Maven project and add dependencies

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.2.1. RELEASE</version>
    <relativePath/> <! -- lookup parent from repository -->
</parent>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <exclusions>
            <exclusion>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-logging</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>1.3.2</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-log4j2</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-security</artifactId>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>
Copy the code

3. Configure application.properties to configure database connection information

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test? useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=root
Copy the code

4. Define the SysUser class to replace the UserDetails class in Spring Security, implement the method in UserDetails, put it in the Entity package, and add the entity class SysRole

public class SysUser implements UserDetails {

    private Integer id;
    private String username;
    private String password;
    private String realname;
    private boolean isExpired;
    private boolean isLocked;
    private boolean isCredentials;
    private boolean isEnabled;

    private Date createTime;
    private Date loginTime;

    private List<GrantedAuthority> authorities;


    public SysUser(a){}public SysUser(String username, String password, String realname, boolean isExpired, boolean isLocked, boolean isCredentials, boolean isEnabled, Date createTime, Date loginTime,List<GrantedAuthority> authorities) {
        this.username = username;
        this.password = password;
        this.realname = realname;
        this.isExpired = isExpired;
        this.isLocked = isLocked;
        this.isCredentials = isCredentials;
        this.isEnabled = isEnabled;
        this.createTime = createTime;
        this.loginTime = loginTime;
        this.authorities = authorities;
    }
    
    @Override
    public boolean isAccountNonExpired(a) {
        return isExpired;
    }

    @Override
    public boolean isAccountNonLocked(a) {
        return isLocked;
    }

    @Override
    public boolean isCredentialsNonExpired(a) {
        return isCredentials;
    }

    @Override
    public boolean isEnabled(a) {
        return isEnabled;
    }
    // omit getter/setter/toString()
}    
Copy the code

public class SysRole {

    private Integer id;
    private String name;
    private String memo;
    
    // Omit getter/setter/toString methods here
}    
Copy the code

5. Create the mapper package, create the SysUserMapper interface, and add insertSysUser() and selectByUser() methods

@Repository
public interface SysUserMapper {

    int insertSysUser(SysUser user);
    // Obtain user information based on the account name
    SysUser selectSysUser(String username);
}
Copy the code

6. Add the mappers folder in the resource directory to add the sysusermapper. XML configuration file

      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.citi.mapper.SysUserMapper">

    <resultMap id="userMapper" type="com.citi.entity.SysUser">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password" />
        <result column="realname" property="realname" />
        <result column="isenable" property="isEnabled" />
        <result column="islock" property="isLocked" />
        <result column="iscredentials" property="isCredentials" />
        <result column="createtime" property="createTime" />
        <result column="logintime" property="loginTime" />
        <result column="isexpire" property="isExpired" />
    </resultMap>

    <insert id="insertSysUser">
        insert into sys_user(username,password,realname,
           isenable,islock,iscredentials,createtime,logintime)

         values(#{username},#{password},#{realname},#{isEnabled},
           #{isLocked},#{isCredentials},#{createTime},#{loginTime})
    </insert>


    <select id="selectSysUser" resultMap="userMapper">
        select id, username,password,realname,isexpire,
           isenable,islock,iscredentials,createtime,logintime
           from sys_user where username=#{username}
    </select>
</mapper>
Copy the code

7. Add the Mybatis configuration to the application.properties configuration file

# set MyBatis
mybatis.mapper-locations=classpath:/mappers/*.xml
mybatis.type-aliases-package=com.citi.entity
Copy the code

8. Create startup class MainApplication under Citi package, add jdbcInit() method, initialize database when start program, that is, add USER to sys_USER table, create three users Peter, Thor and Stark belong to three roles ADMIN, USER, READ, the container creates a user each time it is started. Create a user on the first start. Annotate @PostConstruct once the user is created

@MapperScan("com.citi.mapper")
@SpringBootApplication
public class MainApplication {

    @Resource
    private SysUserMapper sysUserMapper;

    @PostConstruct
    public void jdbcInit(a){
        List<GrantedAuthority> authorityList = new ArrayList<>();
        // The role name must start with ROLE_, followed by the custom role name
        GrantedAuthority authority = new SimpleGrantedAuthority("ROLE_" + "ADMIN");
        authorityList.add(authority);
        // Password encryption
        PasswordEncoder passwordEncoder = new BCryptPasswordEncoder();
        SysUser user = new SysUser("Peter",passwordEncoder.encode("12345"),"Peter Parker".true.true.true.true.new Date(),new Date(),authorityList);
        sysUserMapper.insertSysUser(user);
    }

    public static void main(String[] args) { SpringApplication.run(MainApplication.class,args); }}Copy the code

9. Add data to sys_ROLE and sys_user_role

10. Add the SysRoleMapper interface to the mapper package, and add the user role query method

@Repository
public interface SysRoleMapper {

    List<SysRole> selectRoleByUser(Integer userId);
}
Copy the code

11. Add an XML configuration file to the Mappers folder under the Resources directory to implement the query method in the Mapper interface

      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.citi.mapper.SysRoleMapper">

    <resultMap id="roleMapper" type="com.citi.entity.SysRole">
        <id column="id" property="id" />
        <result column="rolename" property="name" />
        <result column="rolememo" property="demo" />
    </resultMap>

    <select id="selectRoleByUser" parameterType="integer" resultMap="roleMapper">
        SELECT r.id, r.rolename, rolememo
        FROM sys_user_role ur, sys_role r
        WHERE ur.roleid = r.id
        AND ur.userid = #{userId}
    </select>
</mapper>
Copy the code

12. Create a user-defined UserDetailsService implementation class, rewrite the method to query the database to obtain user information, obtain role data, and build the UserDetails implementation class object

@Service
public class JdbcUserDetailsService implements UserDetailsService {

    @Resource
    private SysUserMapper userMapper;

    @Resource
    private SysRoleMapper roleMapper;

    @Override
    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        // 1. Obtain SysUser according to username
        SysUser user = userMapper.selectSysUser(username);
        // 2. Obtain the user role according to userId
        if(user ! =null){
            List<SysRole> sysRoles = roleMapper.selectRoleByUser(user.getId());
            String roleName = "";
            List<GrantedAuthority> authorities = new ArrayList<>();

            for (SysRole sysRole : sysRoles) {
                roleName = sysRole.getName();
                SimpleGrantedAuthority grantedAuthority = new SimpleGrantedAuthority("ROLE_" + roleName);
                authorities.add(grantedAuthority);
            }

            return user;
        }

        returnuser; }}Copy the code

13. New CustSecurityConfig under config package, custom WebSecurityConfigurerAdapter, custom security configuration

@Configuration
@EnableWebSecurity
public class CustSecurityConfig extends WebSecurityConfigurerAdapter {

    @Resource
    private UserDetailsService userDetailsService;

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {

        auth.userDetailsService(userDetailsService).passwordEncoder(newBCryptPasswordEncoder()); }}Copy the code

14. Added controller package, added UserController and index.html page

@RestController
public class UserController {

    @GetMapping(value = "/access/user", produces = "text/html; charset=utf-8")
    public String user(a){
        return "USER";
    }

    @GetMapping(value = "/access/read", produces = "text/html; charset=utf-8")
    public String read(a){
        return "READ";
    }

    @GetMapping(value = "/access/admin", produces = "text/html; charset=utf-8")
    public String admin(a){
        return "ADMIN"; }}Copy the code

<! DOCTYPEhtml>
<html lang="en">
<head>
    <meta charset="UTF-8"/>
    <title>Title</title>
</head>
<body>
<p>Verify access</p>
<a href="/access/user">USER</a>
<a href="/access/read">READ</a>
<a href="/access/admin">ADMIN</a>
</body>
</html>
Copy the code

15, Start MainApplication, open localhost:8080, automatically jump to localhost:8080/login

Enter the user name password Thor / 12345, Peter / 12345, Stark / 12345, can be successful jump to the index page

Remove permission control for index page, and add overloaded configure method in CustSecurityConfig class, which can be accessed directly from index page, while other pages require permission verification

@Override
protected void configure(HttpSecurity http) throws Exception {

    http.authorizeRequests().antMatchers("/index.html").permitAll()
            .anyRequest().authenticated()
            .and()
            .formLogin();
}
Copy the code

Restart the application to clear the browser cache, enter localhost:8080 in the browser, and the page is displayed directly. You do not need to log in again. Click a link on the Index page to display the authentication page

16. Configure role access permissions for urls and modify the configure(HttpSecurity HTTP) method

@Override
protected void configure(HttpSecurity http) throws Exception {

    http.authorizeRequests().antMatchers("/index.html").permitAll()
            // Assign role access rights to the URL
            .antMatchers("/access/user").hasRole("USER")
            .antMatchers("/access/read").hasRole("READ")
            .antMatchers("/access/admin").hasRole("ADMIN")
            .anyRequest().authenticated()
            .and()
            .formLogin();
}
Copy the code

This code assigns /access/ USER to the USER role, /access/ READ to the READ role, and /access/ ADMIN to the ADMIN role

17. Verify permissions, restart the application, and open the home page as follows. To ensure correctness, first clear the browser cache

First verify the USER role. Click the USER link and enter Thor/123456

Other pages cannot be accessed

Since Thor account also has an ADMIN role, the ADMIN page can be accessed normally

Clear browser cache, use the Stark account of the READ role to access the READ page

Try another page, access user page and admin page error

It is also possible to assign multiple page access rights to a role

@Override
protected void configure(HttpSecurity http) throws Exception {

    http.authorizeRequests().antMatchers("/index.html").permitAll()
            // Assign role access rights to the URL
            .antMatchers("/access/user"."/access/read").hasRole("USER")
            .antMatchers("/access/read").hasRole("READ")
            .antMatchers("/access/admin"."/access/read"."/access/user").hasRole("ADMIN")
            .anyRequest().authenticated()
            .and()
            .formLogin();
}
Copy the code

AntMatchers () takes the String… AntPatterns, without limiting the number of urls.

So far, we have realized user authentication and authorization authentication in the case of customized database tables, and passed the test.