Problem with user validation by httpBasic and jdbcAuthentication in WebSecurityConfigurerAdapter

by PaweĊ‚   Last Updated September 12, 2019 02:26 AM

I checked the most of solutions from stackoverflow and also few official articles like: spring.io i have finished small webapi based on SpringBoot and i want change security level from inMemoryAuthentication to jdbcAuthentication and i think my code is correct and i have no idea why still i have 401 response code in Postman.

Here is my code:

1.POM DEPENDENCIES

<dependencies>
    <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.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </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>
</dependencies>
  1. DOMAIN CLASS API CLIENT


    @Entity
    @Table(name = "apiclient")
    public class ApiClient {

        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        private Long id;

        @NotNull
        @Column(name = "username")
        @Size(max = 50)
        private String username;

        @NotNull
        @Column(name = "password")
        @Size(max = 65)
        private String password;

        @NotNull
        @Column(name = "role")
        @Size(max = 15)
        private String role;

        public Long getId() {
            return id;
        }

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

        public String getUsername() {
            return username;
        }

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

        public String getPassword() {
            return password;
        }

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

        public String getRole() {
            return role;
        }

        public void setRole(String role) {
            this.role = role;
        }

        @Override
        public String toString() {
            return "ApiClient{" +
                    "id=" + id +
                    ", username='" + username + '\'' +
                    ", password='" + password + '\'' +
                    ", role='" + role + '\'' +
                    '}';
        }
    }


  1. REST CONTROLLER


@RestController
public class HelloController {

    @RequestMapping(path = "/user")
    public String showUserMsg() {
        return "User has logged in!!!";

    }

    @RequestMapping(path = "/admin")
    public String showAdminMsg() {
        return "Admin has logged in!!!";
    }
}


  1. SECURITY CLASS



     @Configuration
        @EnableWebSecurity
        public class SpringSecurityConfig extends WebSecurityConfigurerAdapter {

            @Autowired
            private DataSource dataSource;

            @Override
            protected void configure(HttpSecurity http) throws Exception {
                http.httpBasic();
                http.authorizeRequests()
                        .antMatchers("/admin/**").hasRole("ADMIN")
                        .antMatchers("/user/**").hasRole("USER")
                        .antMatchers("/h2-console/**").permitAll()
                        .anyRequest().authenticated();

                http.csrf().ignoringAntMatchers("/h2-console/**");
                http.headers().frameOptions().disable();
            }

            @Autowired
            public void configureGlobal(AuthenticationManagerBuilder auth) throws Exception {
                auth.jdbcAuthentication().dataSource(dataSource)
                        .usersByUsernameQuery("select username, password, true"
                                + " from apiclient where username='?'")
                        .authoritiesByUsernameQuery("select username, role"
                                + " from apiclient where username='?'");
            }

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


  1. COMMAND LINE RUNER FO AUTO USER REGISTRATION


    public class SampleDataLoader implements CommandLineRunner {

        @PersistenceContext
        EntityManager entityManager;

        @Autowired
        private PasswordEncoder passwordEncoder;

        @Override
        @Transactional
        public void run(String... args) throws Exception {

            ApiClient user = new ApiClient();
            user.setRole("USER");
            user.setPassword(passwordEncoder.encode("TESTPAS123"));
            user.setUsername("USER");
            entityManager.persist(user);
            entityManager.flush();

            System.out.println(user);

            ApiClient admin = new ApiClient();
            admin.setRole("ADMIN");
            admin.setPassword(passwordEncoder.encode("TESTPAS123"));
            admin.setUsername("ADMIN");
            entityManager.persist(admin);
            entityManager.flush();

            System.out.println(admin);


        }
    }


After start springboot application h2 database look like: enter image description here



Answers 1


Query with single quote username='?' causing problem.

Modify

 @Autowired
 public void configureGlobal(AuthenticationManagerBuilder auth) throws Exception 
 {
     auth.jdbcAuthentication().dataSource(dataSource)
             .usersByUsernameQuery("select username, password, true"
                 + " from apiclient where username='?'")
         .authoritiesByUsernameQuery("select username, role"
                 + " from apiclient where username='?'");
}

to

@Autowired
public void configureGlobal(AuthenticationManagerBuilder auth) throws Exception {
    auth.jdbcAuthentication()
            .dataSource(dataSource)
            .usersByUsernameQuery("select username, password, true"
                    + " from apiclient where username=?")
            .authoritiesByUsernameQuery("select username, role"
                    + " from apiclient where username=?");
}

It will work.(Tested)

PraveenKumar Lalasangi
PraveenKumar Lalasangi
September 11, 2019 19:36 PM

Related Questions


Spring boot basic authentication

Updated September 06, 2017 01:26 AM