In real apps, user data and their roles (and permissions) are rarely kept in static config files. A database makes user management way more flexible and convenient. For example:
- Dynamic user management — the ability to add and remove users "on the fly".
- Security — user passwords can be securely hashed right in the database.
- Scalability — support for a large number of users.
- Integration — easy to sync users from other systems (for example, CRM).
What we'll do
Here's the plan:
- We'll create tables to store users and roles.
- Set up the relationship between the tables (many-to-many).
- Create the user (
User) and role (Role) entities. - Implement database access via Spring Data JPA repositories.
- Configure
UserDetailsServiceto integrate the database with Spring Security.
Step 1. Creating tables for users and roles
Let's start with the DB design. We need two tables: one for user info and one for roles. Besides that, we need a join table to model the many-to-many relationship between them. Here's an example SQL for creating the tables:
CREATE TABLE role (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE user (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
enabled BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE user_roles (
user_id BIGINT,
role_id BIGINT,
PRIMARY KEY (user_id, role_id),
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES user(id),
CONSTRAINT fk_role FOREIGN KEY (role_id) REFERENCES role(id)
);
Here's what these fields mean:
- Table
user:username— the user's login name, used to sign in.password— we store the hashed password (more on this later).enabled— a flag indicating whether the user is active.
- Table
role:- Each role has a unique name (for example,
ROLE_USER,ROLE_ADMIN).
- Each role has a unique name (for example,
- Table
user_roles:- Represents the link between users and their roles.
Step 2. Creating User and Role entities
First we'll create the Java classes that represent our tables. We'll use JPA annotations to map entity fields to database table columns.
Class Role
import jakarta.persistence.*;
import java.util.Set;
@Entity
public class Role {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(unique = true, nullable = false)
private String name;
public Role() {}
public Role(String name) {
this.name = name;
}
// Getters and Setters
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
Class User
import jakarta.persistence.*;
import java.util.Set;
@Entity
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(unique = true, nullable = false)
private String username;
@Column(nullable = false)
private String password;
private boolean enabled = true;
@ManyToMany(fetch = FetchType.EAGER)
@JoinTable(
name = "user_roles",
joinColumns = @JoinColumn(name = "user_id"),
inverseJoinColumns = @JoinColumn(name = "role_id")
)
private Set<Role> roles;
public User() {}
public User(String username, String password, Set<Role> roles) {
this.username = username;
this.password = password;
this.roles = roles;
}
// Getters and Setters
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 boolean isEnabled() {
return enabled;
}
public void setEnabled(boolean enabled) {
this.enabled = enabled;
}
public Set<Role> getRoles() {
return roles;
}
public void setRoles(Set<Role> roles) {
this.roles = roles;
}
}
Step 3. Spring Data JPA repositories
Now let's create repositories to interact with the database.
Interface RoleRepository
import org.springframework.data.jpa.repository.JpaRepository;
public interface RoleRepository extends JpaRepository<Role, Long> {
Role findByName(String name);
}
Interface UserRepository
import org.springframework.data.jpa.repository.JpaRepository;
public interface UserRepository extends JpaRepository<User, Long> {
User findByUsername(String username);
}
These repositories make common operations easier: finding a user by name, adding new roles, etc.
Step 4. Configuring UserDetailsService
To hook the database into Spring Security you need to provide an implementation of UserDetailsService. That lets the framework load user info during authentication.
UserDetailsService implementation
import org.springframework.security.core.userdetails.*;
import org.springframework.security.core.userdetails.User;
import org.springframework.stereotype.Service;
import java.util.stream.Collectors;
@Service
public class CustomUserDetailsService implements UserDetailsService {
private final UserRepository userRepository;
public CustomUserDetailsService(UserRepository userRepository) {
this.userRepository = userRepository;
}
@Override
public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
User user = userRepository.findByUsername(username);
if (user == null) {
throw new UsernameNotFoundException("User not found");
}
return User.builder()
.username(user.getUsername())
.password(user.getPassword())
.authorities(user.getRoles().stream()
.map(Role::getName)
.collect(Collectors.toList()))
.build();
}
}
Step 5. Adding password hashing
Password hashing is a must. We'll use BCryptPasswordEncoder:
import org.springframework.context.annotation.*;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;
@Configuration
public class SecurityConfig {
@Bean
public PasswordEncoder passwordEncoder() {
return new BCryptPasswordEncoder();
}
}
When you create new users in the DB, always encode their passwords via PasswordEncoder.
Results
Now your app is ready to securely work with users and roles stored in a database. You can add, update, and manage them without changing the code. This approach is common in enterprise systems, e-commerce sites, and any system that needs flexibility in user management. Don't forget to remind users to pick really strong passwords so they don't fall victim to password123!
GO TO FULL VERSION