I started screwing up the spring-boot-security authorization to the database, and took the construction common for many resources to work with it through JdbcUserDetailsManagerConfigurer . But faced with the fact that he does not have a method of checking for a pair of username - login .

 @Autowired public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception { auth.jdbcAuthentication().dataSource(dataSource) .usersByUsernameQuery( "select username,password, enabled from users where username=?") .authoritiesByUsernameQuery( "select username, role from user_roles where username=?"); }

It turns out that I must have a username field in the user_roles table and my current implementation of these tables is not appropriate. Or can it somehow be reused? Just with the methods in JdbcUserDetailsManagerConfigurer as it is not thick or I do not understand. Explain, please, using spring-security, am I tied tightly to the table structure prepared by the spring?

I just have this:

 CREATE TABLE IF NOT EXISTS users ( id SERIAL PRIMARY KEY, username VARCHAR(20) UNIQUE NOT NULL, password VARCHAR(20) UNIQUE NOT NULL, role INTEGER NOT NULL, FOREIGN KEY (role) REFERENCES user_role (id) ); CREATE TABLE IF NOT EXISTS user_role ( id SERIAL PRIMARY KEY, role VARCHAR(10) );

    1 answer 1

    Yes, you can use your own table structure in Spring Securty. The usersByUsernameQuery should return two string fields and one boolean, and the authoritiesByUsernameQuery query should return two string fields. How this data will be obtained from fields with what specific names - Spring Security does not care. In your case, simply use the connection :

     select u.username, r.role from users inner join user_role on u.role = r.id where username = ?

    Those who do not use the boot, the easiest way to set up non-standard requests in the configuration jdbc-user-service :

     <security:authentication-manager> <security:authentication-provider> <security:jdbc-user-service data-source-ref="dataSource" users-by-username-query="select username, password, enabled from users where username = ?" authorities-by-username-query="select m2m.username, a.authority from group_members as m2m inner join group_authorities as a using(group_id) where m2m.username = ?" /> </security:authentication-provider> </security:authentication-manager>

    If the security system should get more data from the database, you can go a little further and write your UserDetailsService and your UserDetals :

     @Service("userDetailsService") public class MyUserDetailsService extends JdbcDaoImpl { private final String usersByUsernameQuery = "select login, password, enabled, superuser from users where login = ?"; private final String authoritiesByUsernameQuery = "select u.login, a.authority_name from groups_users as m2m " + "inner join users as u on u.id = m2m.members_id " + "inner join groups as g on g.id = m2m.groups_id " + "where u.login = ?"; ... public UserDetails loadUserByUsernameAndRealm(String username) { List<MyUserDetails> users = loadUsersByUsername(username); if (users.size() == 0) { throw new UsernameNotFoundException("Username not found"); } UserDetails user = users.get(0); List<GrantedAuthority> authorities = loadAuthorities(user.getUsername()); return createUserDetails(username, user, authorities); } protected List<UserDetails> loadUsersByUsername(String username) { return getJdbcTemplate().query(usersByUsernameQuery, new String[] { username }, new RowMapper<UserDetails>() { public UserDetails mapRow(ResultSet rs, int rowNum) throws SQLException { String username = rs.getString(1); String password = rs.getString(2); boolean enabled = rs.getBoolean(3); boolean superuser = rs.getBoolean(4); // Новое свойство! boolean accountNonExpired = true; boolean credentialsNonExpired = true; boolean accountNonLocked = true; return new MyUser(username, password, enabled, superuser, accountNonExpired, credentialsNonExpired, accountNonLocked, AuthorityUtils.NO_AUTHORITIES); } } ); } } public class MyUser extends User { private final boolean superuser; public MyUser(String username, String password, boolean enabled, boolean superuser, boolean accountNonExpired, boolean credentialsNonExpired, boolean accountNonLocked, List<GrantedAuthority> authorities) { super(username, password, enabled, accountNonExpired, credentialsNonExpired, accountNonLocked, authorities); this.superuser = superuser; } public boolean isSuperuser() { return superuser; } }