spring security jdbc authentication example

In the previous article, we saw a custom authentication provider which had the hard coded user name and passwords. But this kind of authentication is not safe and encouraged. It is very vulnerable.

So it is encouraged to use a kind of JDBC or LDAP authentication in our system which would not involve hard coding any username or passwords.

Below are the pre-requisites to enable JDBC auth:

Include the corresponding spring-boot-starter-data-jpa and h2 dependencies.
Configure the database connection with application properties.
Enable the H2 console.

@Autowired
private DataSource dataSource;

@Autowired
public void configure(AuthenticationManagerBuilder auth)
  throws Exception {
    auth.jdbcAuthentication()
      .dataSource(dataSource);
}

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

Here we passed a data source isntance which would have the db details in which the user information would be maintained along with their role and password details.

Coming back to the types of JDBC authentication, there are 2 ways to achieve this.

1)Hard coding user names:



@Autowired
private DataSource dataSource;

@Autowired
public void configure(AuthenticationManagerBuilder auth)
  throws Exception {
    auth.jdbcAuthentication()
      .dataSource(dataSource)
      .withDefaultSchema()
      .withUser(User.withUsername("admin")
        .password(passwordEncoder().encode("pass"))
        .roles("ADMIN"));
}

This is not encouraged.

2)Connect to a particular schema and database.

We will tell spring sec to create tables and insert queries in the IN Memory DB.

It would be in the default User Schema and spring supports the default table names.

1)User Table

2) Authorities Table.

To configure, when the application starts those create table would be executed once.

Let’s create the tables and insert the data into it.


CREATE TABLE users (
  username VARCHAR(50) NOT NULL,
  password VARCHAR(100) NOT NULL,
  enabled TINYINT NOT NULL DEFAULT 1,
  PRIMARY KEY (username)
);
  
CREATE TABLE authorities (
  username VARCHAR(50) NOT NULL,
  authority VARCHAR(50) NOT NULL,
  FOREIGN KEY (username) REFERENCES users(username)
);


INSERT INTO users (username, password, enabled)
  values ('user1',
    '^ssd#DSd45sdffrhhh',
    1);

INSERT INTO authorities (username, authority)
  values ('user1', 'ADMIN');

Now when we access the spring login form in browser once the app starts, we insert the id and password as configured in table.The login should be successful if we pass the same used id and password.

Now what if the table name is different and not the default ones?

Above we implemented security based on the default table schemas that Spring Security issues requests.

Now we would use some custom queries to implement JDBC Auth.

@EnableWebSecurity
public class CustomQuerySecConfigExample extends WebSecurityConfigurerAdapter {

    @Autowired
    private DataSource dataSource;

    @Autowired
    public void configure(AuthenticationManagerBuilder auth) throws Exception {
        auth.jdbcAuthentication().dataSource(dataSource)
                .usersByUsernameQuery("SELECT username,password,1 FROM App_Users where username=?")
                .authoritiesByUsernameQuery("SELECT username,authority FROM App_Roles where username=?");
    }

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

        http.authorizeRequests()
                .antMatchers("/login").permitAll()
                .anyRequest().authenticated()
                .and()
                .formLogin()
                .permitAll()
                .and()
                .logout()
                .permitAll();
    }

}

We used 2 custom tables App_Users and App_Roles for the authentication. Those tables would have credential as well as the role information.

Now what if DB is diff than the default H2 DB? 🙂

Do this in application.properties.

spring.datasource.url = ……
spring.datasource.username = ……
spring.datasource.password = ……

What if we write our own custom service for authentication?

We would make use of UserDetailsService Interface.

@Entity
public class User {

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

    @Column
    private String username;

    private String password;

    // many getters and setters
}

public class MyUserPrincipal implements UserDetails {
    private User user;

    public MyUserPrincipal(User user) {
        this.user = user;
    }

    //getters and setters if required
}

@Service
public class MyUserDetailsService implements UserDetailsService {

    @Autowired
    private UserRepository userRepository;

    @Override
    public UserDetails loadUserByUsername(String userName) {
        System.out.println("Load user details using Spring JPA repository");
        User usr = userRepository.findByUsername(userName);
        if (usr == null) {
            throw new UsernameNotFoundException(userName);
        }
        return new MyUserPrincipal(usr);
    }
}

@Configuration
@EnableWebMvcSecurity
public class UserDetailsSecurityConfig extends WebSecurityConfigurerAdapter {

@Autowired
private MyUserDetailsService userService;

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception    {
        auth.userDetailsService(userService);
    }

}

So now here what we have done is written a separate logic to do the authentication. We have implemented a separate class called MyUserDetailsService which implements the UserDetailsService interface. The method loadUserByUsername was overriden when doing this and it returned the user details wrapped inside a MyPrincipal object. It has to be wrapped or else we cannot return the user object directly. We used Spring JPA to query the DB and get the results which is our custom logic.

In the main security config class, we passed the MyUserDetailsService instance so that our custom logic is executed for auth.

Hope it was clear!!!!

Tag: spring security jdbc authentication example

Spring Security – JDBC Authentication