CODEWITHSUNDEEP
javaspringspring-securityspring-boot
Dany Y
Dany Y

Reputation: 7031

spring boot authenticating with custom tables

I'm still a beginner in Spring boot coming from a Grails background.

There are already many docs about configuration. But still nothing worked with me until now, I guess it's because i still don't get the whole concept of configuration on spring boot.

I want my application to authenticate using my own database tables.

my current tables are : 

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(512) DEFAULT '',
  `password` varchar(512) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `role` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `role_name` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

I've tried to configure my security with this class : 

@Configuration
@EnableWebSecurity
@ComponentScan
public class SecurityConfiguration  extends WebSecurityConfigurerAdapter {

        @Autowired
        private DataSource dataSource;

        @Override
        protected void configure(HttpSecurity http) throws Exception {
            http.authorizeRequests().anyRequest().authenticated();
        }

        @Autowired
        public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
            auth.jdbcAuthentication()
                    .dataSource(this.dataSource)
                    .authoritiesByUsernameQuery("select u.username,r.role_name from role r,users u where u.username = ? and u.role_id = r.id")
                    .usersByUsernameQuery("SELECT username, password FROM users where username = ?")
                    .passwordEncoder(new BCryptPasswordEncoder());
        }

}

But i still get a 403 when i try to access a page with the correct credentials.

Is there anything missing I should add? do i need to override userDetailsService

I can't find any clear documentation that says use the column "users.username" as username, "users.password" as password or a way to override the query of authentication.

Thanks

Upvotes: 2

Views: 4276

Answers (2)

Ali Dehghani
Ali Dehghani

Reputation: 48123

The standard JDBC implementation of the UserDetailsService (JdbcDaoImpl) requires tables to load the password, account status (enabled or disabled) and a list of authorities (roles) for the user. That schema looks like this:

create table users(
    username varchar_ignorecase(50) not null primary key,
    password varchar_ignorecase(50) not null,
    enabled boolean not null
);

create table authorities (
    username varchar_ignorecase(50) not null,
    authority varchar_ignorecase(50) not null,
    constraint fk_authorities_users foreign key(username) references users(username)
);
create unique index ix_auth_username on authorities (username,authority);

So you should use this for loading users:

usersByUsernameQuery("SELECT username, password, enabled FROM users where username = ?")

and this for loading all the authorities for one specific user:

select r.role_name from role r,users u where u.username = ? and u.role_id = r.id"

And based on .passwordEncoder(new BCryptPasswordEncoder()); your saved passwords in db should be encoded with BCrypt. See spring security doc to gain more insight about how spring security works (not spring boot's one).

Update: For enabling HTTP Basic security:

@Override
protected void configure(HttpSecurity http) throws Exception {
        http
            .httpBasic().and()
            .authorizeRequests().anyRequest().authenticated();
}

To authenticate add a Authorization header like this:

Authorization: Basic <username>:<password>

Or you can include the user and password as part of the URL:

http://user:passwd@localhost:8080/protected/service

Upvotes: 5

Kartoch
Kartoch

Reputation: 7779

Three solutions:

  1. Create a new class implementing the interface 'UsersDetailsService'
  2. You can provide our own 'DaoAuthenticationProvider' instead of the default one to deserialize 'UserDetails' instances for authentication.
  3. Redefine constant strings 'DEF_AUTHORITIES_BY_USERNAME_QUERY', 'DEF_GROUP_AUTHORITIES_BY_USERNAME_QUERY' and 'DEF_USERS_BY_USERNAME_QUERY' of 'JdbcDaoImpl' (as shown in the javadoc)

Upvotes: 3

Related Questions