1. Introduction

In a microservices and front-end/back-end separation architecture, permission management is one of the fundamental pieces of system security. If every application implements its own authentication logic from scratch, it not only wastes effort by reinventing the wheel but also risks overlooking edge cases, leading to security vulnerabilities. RBAC (Role-Based Access Control) introduces an intermediate layer called “role” to decouple users from permissions, and it is currently the most widely adopted permission model in the industry. This article walks you through the core concepts of RBAC, database table design, and implements a minimal but functional permission center using Spring Boot + JPA.

After reading this article, you will understand the principles of the RBAC model, be able to independently design many-to-many table structures for users, roles, and permissions, and write runnable CRUD and authentication interfaces.

2. RBAC Core Concepts and Model Explanation

2.1 Basic Elements: Users, Roles, and Permissions

The core of the RBAC model consists of three entities: User, Role, and Permission. The user is the subject of operations, the permission is the capability to perform an operation, and the role serves as the bridge between them.

The relationships are as follows:

  • User and Role: Many-to-many. A user can have multiple roles (e.g., both an “admin” and a “reviewer”), and a role can be assigned to multiple users.
  • Role and Permission: Many-to-many. A role can contain multiple permissions (e.g., “admin” has “read,” “write,” and “delete” permissions), and a permission can belong to multiple roles (e.g., the “read” permission belongs to both “admin” and “regular user”).

This design makes permission changes flexible: when business rules change, you only need to adjust the role-permission associations without modifying each user individually. For example, if a company changes its attendance policy and needs to give “attendance administrators” a new “export report” permission, the administrator simply adds that permission to the “attendance administrator” role, and all users assigned that role automatically gain the new capability.

2.2 Permission Granularity: Operation Level and Resource Level

In practice, permissions often need to distinguish between the object of the operation and the type of operation. We introduce the combination of Resource and Action to define permission granularity.

  • Action: Refers to the operation performed on a resource. Common actions include: create, read, update, delete (abbreviated as CRUD). Sometimes business actions like export or approve are added.
  • Resource: Refers to the object of the operation, such as order, user, product, report, etc.

Therefore, a complete permission record can be expressed as: “Allow the ‘read’ operation on the ‘order’ resource.” At the database level, we design the permission table as permission(resource, action) or permission(code) like order:read. This combination is clear and extensible; when new resources or actions are added, you simply add new records to the permission table.

The RBAC model is divided into multiple levels. The most basic is RBAC0, which includes the triple of users, roles, and permissions described above. RBAC1 introduces role inheritance (e.g., “super admin” inherits all permissions of “admin”), and RBAC2 adds separation of duty constraints (e.g., a user cannot simultaneously have “cashier” and “accountant” roles). This article focuses on RBAC0, which is the starting point for most business systems.

3. Database Table Design: Users, Roles, Permissions, and Their Relationships

3.1 Core Table Structure

Based on the RBAC0 model, the database requires at least five tables:

  1. user: Stores login accounts, passwords, and personal information.
  2. role: Stores role names and descriptions.
  3. permission: Stores combinations of resources and actions; typically includes resource and action columns.
  4. user_role: The user-role association table, establishing a many-to-many relationship between users and roles.
  5. role_permission: The role-permission association table, establishing a many-to-many relationship between roles and permissions.

3.2 Sample Table Creation SQL (PostgreSQL)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- User table
CREATE TABLE "user" (
    id          BIGSERIAL PRIMARY KEY,
    username    VARCHAR(50) NOT NULL UNIQUE,
    password    VARCHAR(255) NOT NULL,
    enabled     BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Role table
CREATE TABLE role (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255)
);

-- Permission table
CREATE TABLE permission (
    id          BIGSERIAL PRIMARY KEY,
    resource    VARCHAR(50) NOT NULL,
    action      VARCHAR(50) NOT NULL,
    UNIQUE (resource, action)
);

-- User-role association table
CREATE TABLE user_role (
    user_id     BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
    role_id     BIGINT NOT NULL REFERENCES role(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, role_id)
);

-- Role-permission association table
CREATE TABLE role_permission (
    role_id       BIGINT NOT NULL REFERENCES role(id) ON DELETE CASCADE,
    permission_id BIGINT NOT NULL REFERENCES permission(id) ON DELETE CASCADE,
    PRIMARY KEY (role_id, permission_id)
);

3.3 Design Considerations

  • Primary Keys and Unique Constraints: user_role and role_permission use composite primary keys to ensure unique records and avoid duplicate assignments. The permission table uses UNIQUE (resource, action) to prevent duplicate permission definitions.
  • Foreign Keys and Cascade Deletion: The foreign keys in association tables use ON DELETE CASCADE. When a user or role is deleted, the associated records are automatically removed.

However, do not use ON DELETE CASCADE directly on parent table records in production permission tables; typically, soft deletion is used in business logic, and physical deletion is only used during testing or cleanup.

  • Indexes: It is recommended to create separate indexes on foreign key columns in association tables to speed up queries. For example:
1
2
CREATE INDEX idx_user_role_user_id ON user_role(user_id);
CREATE INDEX idx_role_permission_role_id ON role_permission(role_id);

4. Project Setup and Dependency Configuration (Spring Boot in Practice)

4.1 Maven Dependencies (pom.xml)

Create a Spring Boot project and introduce the following core dependencies:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>3.2.0</version>
</parent>

<dependencies>
    <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>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

If using MySQL, replace postgresql with mysql-connector-j.

4.2 Configuration File (application.yml)

1
2
3
4
5
6
7
8
9
10
11
12
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/rbac_demo
    username: your_username
    password: your_password
  jpa:
    hibernate:
      ddl-auto: update  # Use 'update' for development; for production, use 'validate' and manage DDL manually
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect

Note: ddl-auto: update automatically updates the table structure based on entity classes, suitable for development and debugging. Disable this in production and use Flyway or Liquibase for database version management.

4.3 Directory Structure

1
2
3
4
5
6
7
8
9
src/main/java/com/example/rbac/
├── config/           # Configuration classes (e.g., Redis, security config)
├── controller/       # REST interface layer
├── entity/           # JPA entity classes
├── repository/       # Data access layer interfaces
├── service/          # Business logic layer
├── dto/              # Data transfer objects
├── exception/        # Exception definitions
└── RbacApplication.java

5. Entity Classes and Repository Implementation

5.1 Entity Class Code

User Entity:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
@Entity
@Table(name = "\"user\"")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true, length = 50)
    private String username;

    @Column(nullable = false, length = 255)
    private String password;

    private Boolean enabled = true;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(
        name = "user_role",
        joinColumns = @JoinColumn(name = "user_id"),
        inverseJoinColumns = @JoinColumn(name = "role_id")
    )
    @JsonIgnoreProperties("users")  // Prevent infinite serialization loops in bidirectional relationships
    private Set<Role> roles = new HashSet<>();
}

Role Entity:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
@Entity
@Table(name = "role")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Role {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true, length = 50)
    private String name;

    private String description;

    @ManyToMany(mappedBy = "roles", fetch = FetchType.LAZY)
    @JsonIgnoreProperties("roles")
    private Set<User> users = new HashSet<>();

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(
        name = "role_permission",
        joinColumns = @JoinColumn(name = "role_id"),
        inverseJoinColumns = @JoinColumn(name = "permission_id")
    )
    @JsonIgnoreProperties("roles")
    private Set<Permission> permissions = new HashSet<>();
}

Permission Entity:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@Entity
@Table(name = "permission", uniqueConstraints = {
    @UniqueConstraint(columnNames = {"resource", "action"})
})
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Permission {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, length = 50)
    private String resource;

    @Column(nullable = false, length = 50)
    private String action;

    @ManyToMany(mappedBy = "permissions", fetch = FetchType.LAZY)
    @JsonIgnoreProperties("permissions")
    private Set<Role> roles = new HashSet<>();
}

5.2 Repository Interfaces

1
2
3
4
5
6
7
8
9
10
11
public interface UserRepository extends JpaRepository<User, Long> {
    Optional<User> findByUsername(String username);
}

public interface RoleRepository extends JpaRepository<Role, Long> {
    Optional<Role> findByName(String name);
}

public interface PermissionRepository extends JpaRepository<Permission, Long> {
    Optional<Permission> findByResourceAndAction(String resource, String action);
}

Explanation: In a bidirectional @ManyToMany relationship, you need to specify one side as the owning side (the one that “holds” the foreign key). Here, the owning side for the User-Role relationship is User (via @JoinTable), while Role uses mappedBy. For the Role-Permission relationship, the owning side is Role (via @JoinTable). The value of mappedBy must match the field name in the other entity.

6. Core Business Logic: Role Assignment and Permission Verification

6.1 Role Assignment Service

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@Service
@RequiredArgsConstructor
public class UserRoleService {
    private final UserRepository userRepository;
    private final RoleRepository roleRepository;

    @Transactional
    public void assignRolesToUser(Long userId, List<Long> roleIds) {
        User user = userRepository.findById(userId)
                .orElseThrow(() -> new RuntimeException("User not found"));
        List<Role> roles = roleRepository.findAllById(roleIds);
        if (roles.size() != roleIds.size()) {
            throw new RuntimeException("Some roles do not exist");
        }
        user.getRoles().addAll(roles);
        userRepository.save(user);
    }
}

The implementation of RoleService.assignPermissions is similar: use role.getPermissions().addAll(...) and call roleRepository.save(role).

6.2 Permission Verification Service

The core logic of permission verification is to check, via a join query, whether a given user has the permission for a specific resource-action combination.

1
2
3
4
5
6
7
8
9
10
@Service
@RequiredArgsConstructor
public class PermissionCheckService {
    private final UserRepository userRepository;

    public boolean hasPermission(Long userId, String resource, String action) {
        // Use JPQL or native SQL for join queries to avoid loading all data in memory
        return userRepository.existsByUserIdAndResourceAndAction(userId, resource, action);
    }
}

Add a custom query method in UserRepository:

1
2
3
4
5
6
@Query("SELECT COUNT(u) > 0 FROM User u " +
       "JOIN u.roles r JOIN r.permissions p " +
       "WHERE u.id = :userId AND p.resource = :resource AND p.action = :action")
boolean existsByUserIdAndResourceAndAction(@Param("userId") Long userId,
                                           @Param("resource") String resource,
                                           @Param("action") String action);

This query uses JPA JOIN operations to perform permission checks at the database level, which is efficient. For finer-grained control, you can extend this method, e.g., by adding a resource instance ID (data permission).

7. Exposing Interfaces and Testing (Controller Layer)

7.1 RESTful Interface Design

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
@RestController
@RequestMapping("/api")
@RequiredArgsConstructor
public class PermissionController {
    private final UserRoleService userRoleService;
    private final PermissionCheckService permissionCheckService;

    // Assign roles to a user
    @PostMapping("/users/roles")
    public Result<Void> assignRoles(@RequestParam Long userId, @RequestBody List<Long> roleIds) {
        userRoleService.assignRolesToUser(userId, roleIds);
        return Result.success();
    }

    // Assign permissions to a role
    @PostMapping("/roles/permissions")
    public Result<Void> assignPermissions(@RequestParam Long roleId, @RequestBody List<Long> permissionIds) {
        // Implementation omitted for brevity; similar to assignRolesToUser
        return Result.success();
    }

    // Check if a user has a specific permission
    @GetMapping("/users/{userId}/check")
    public Result<Boolean> checkPermission(@PathVariable Long userId,
                                           @RequestParam String resource,
                                           @RequestParam String action) {
        boolean has = permissionCheckService.hasPermission(userId, resource, action);
        return Result.success(has);
    }
}

7.2 Unified Response Format

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Result<T> {
    private int code;      // 200 indicates success
    private String message;
    private T data;

    public static <T> Result<T> success() {
        return new Result<>(200, "success", null);
    }

    public static <T> Result<T> success(T data) {
        return new Result<>(200, "success", data);
    }

    public static <T> Result<T> error(int code, String message) {
        return new Result<>(code, message, null);
    }
}

7.3 Testing Verification

After starting the Spring Boot project, verify using Postman or curl:

  1. Assign roles to a user:

    1
    2
    3
    POST /api/users/roles?userId=1
    Content-Type: application/json
    Body: [1, 2]

    If user ID is 1 and role IDs are 1 (admin) and 2 (viewer), the user will have both roles.

  2. Check permission:

    1
    GET /api/users/1/check?resource=order&action=delete

    Returns {"code":200,"message":"success","data":true}, indicating user 1 has permission to delete orders.

Tip: During development, you can enable Swagger (springdoc-openapi) to call interfaces directly from the browser for easy debugging.

8. Advanced Tips: Fine-Grained Permission Control and Cache Optimization

8.1 Data Permission Extensions

The above solution only controls “whether a user can perform an action on a resource” but does not involve specific data rows. For example, a regular salesperson can only view “their own” orders, while a department manager can view “their department’s” orders. This is called data permission or row-level permission.

Extension approach: Add a scope field to the permission table to indicate the scope (e.g., ALL, DEPARTMENT, SELF), and then filter based on the current user context during authorization. For example:

1
2
3
4
public boolean hasDataPermission(Long userId, String resource, String action, Long dataOwnerId) {
    // First check if the user has the permission
    // Then check the scope: ALL allows any row, DEPARTMENT requires user and dataOwner to be in the same department, SELF requires dataOwner to be the current user
}

8.2 Method-Level Authorization Using AOP

Manually calling hasPermission in every interface method results in repetitive code. You can simplify this by using custom annotations + Spring AOP.

Define the annotation:

1
2
3
4
5
6
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface RequirePermission {
    String resource();
    String action();
}

Intercept in the aspect:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@Aspect
@Component
@RequiredArgsConstructor
public class PermissionAspect {
    private final HttpServletRequest request;
    private final PermissionCheckService permissionCheckService;

    @Around("@annotation(requirePermission)")
    public Object check(ProceedingJoinPoint joinPoint, RequirePermission requirePermission) throws Throwable {
        // Retrieve the current user (e.g., from request header or token)
        Long currentUserId = getCurrentUserId();
        if (!permissionCheckService.hasPermission(currentUserId, requirePermission.resource(), requirePermission.action())) {
            throw new AccessDeniedException("No permission");
        }
        return joinPoint.proceed();
    }
}

Then simply add the annotation on controller methods:

1
2
3
4
5
@GetMapping("/orders")
@RequirePermission(resource = "order", action = "read")
public Result<List<Order>> listOrders() {
    // ...
}

8.3 Introducing Redis Cache

Every authorization check requiring a database join query can be inefficient under high concurrency. You can cache the user’s permission set.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
@Service
@RequiredArgsConstructor
public class PermissionCacheService {
    private final StringRedisTemplate redisTemplate;
    private static final String CACHE_KEY_PREFIX = "user_permissions:";

    public Set<String> getCachedPermissions(Long userId) {
        Set<String> permissions = redisTemplate.opsForSet().members(CACHE_KEY_PREFIX + userId);
        if (permissions == null || permissions.isEmpty()) {
            // Load from database, format like "order:read, order:delete"
            permissions = loadPermissionsFromDB(userId);
            redisTemplate.opsForSet().add(CACHE_KEY_PREFIX + userId, permissions.toArray(new String[0]));
            redisTemplate.expire(CACHE_KEY_PREFIX + userId, Duration.ofMinutes(30));
        }
        return permissions;
    }

    private Set<String> loadPermissionsFromDB(Long userId) {
        // Call repository's join query, return Set<String> like ["order:read", "order:delete", "user:create"]
    }
}

Modify hasPermission to check the cache:

1
2
3
4
public boolean hasPermission(Long userId, String resource, String action) {
    Set<String> cache = permissionCacheService.getCachedPermissions(userId);
    return cache.contains(resource + ":" + action);
}

Cache Invalidation Strategy: When a role’s permissions change (e.g., a permission is removed), you need to clear the cache for affected users. You can call redisTemplate.delete(CACHE_KEY_PREFIX + userId) after assignPermissions or removePermissions. For a large number of users, you can use a message queue to perform batch cleanup.

9. Pitfalls and Common Issues

9.1 N+1 Query Problem

@ManyToMany defaults to lazy loading (FetchType.LAZY). If you iterate over a role’s user collection in code, each access triggers an additional SQL query, leading to the N+1 problem. Solutions:

  • Use @EntityGraph to explicitly load associations:
    1
    2
    3
    @EntityGraph(attributePaths = {"roles", "roles.permissions"})
    @Query("SELECT u FROM User u WHERE u.id = :id")
    Optional<User> findWithRoles(@Param("id") Long id);
  • Or use JOIN FETCH in JPQL.

9.2 Circular Dependency and JSON Serialization Infinite Loop

Bidirectional @ManyToMany relationships can cause stack overflow when serialized to JSON due to mutual references. Solutions:

  • Add @JsonIgnoreProperties("users") to the roles field in User;
  • Add @JsonIgnoreProperties("roles") to the users field in Role;
  • Or use @JsonIgnore on the association field on one side to omit it from JSON serialization.

9.3 Immediate Effect of Permission Changes

Cached permission data may become stale. When designing the cache, in addition to setting an expiration time, you should actively invalidate the cache after modifying a role’s permissions. Recommendations:

  • In RoleService‘s assignPermissions and removePermissions methods, call permissionCacheService.evictCache(roleId);
  • If needed, iterate over all users of that role and clear their caches one by one.

9.4 Accidental Cascade Deletion

JPA’s CascadeType.REMOVE will cascade delete records in associated tables. For example, if the Role entity has cascade = CascadeType.REMOVE configured on @ManyToMany, deleting a role will attempt to delete the associated Permission records, which is usually not the intended behavior. Use the database’s own ON DELETE CASCADE only on the foreign keys of the user_role and role_permission association tables, and avoid configuring cascade in the entities as much as possible.

10. Summary and Extensions

This article walked through the design and implementation of a minimal permission center based on the RBAC model, from concepts to code. The key points are summarized as follows:

  1. Model Value: RBAC decouples users and permissions through roles, reducing the complexity of permission management and supporting flexible batch authorization and revocation.
  2. Table Structure Design: Five tables (user, role, permission, user-role association, role-permission association) express all semantics of RBAC0; the permission table uses the resource + action combination to support fine-grained operation-level control.
  3. Implementation Path: Spring Boot + JPA effectively reduces development costs. Entity classes use @ManyToMany with @JoinTable, taking care to specify the owning side and mappedBy. Permission checks are done via JOIN queries or caching.
  4. Common Pitfalls: N+1 queries, circular serialization, cache consistency issues, and unintended cascade deletions.

Directions for extension include:

  • Role Inheritance (RBAC1): Establish hierarchical relationships between roles, where child roles automatically inherit parent role permissions. Add a parent_id field to the role table.

  • Separation of Duties (RBAC2): Use constraint rules to prevent a user from having conflicting roles simultaneously (e.g., “cashier” and “accountant”), verifying during role assignment.

  • Attribute-Based Access Control (ABAC): When permission rules depend on environmental attributes (e.g., IP, time, user context), ABAC is more flexible than RBAC, but the model is more complex.

  • Integration with Spring Security: Embed the hasPermission logic into a PermissionEvaluator, and use expressions in @PreAuthorize to further reduce boilerplate code.

Recommended reading: The original “RBAC96 Model” paper and Apache Shiro’s permission design source code will help you understand permission engineering practices in large-scale systems. Subsequent topics could include “Spring Security Integration with RBAC in Practice” and “Comparison of Permission Center Cache Solutions.”

Keywords: RBAC permission model tutorial, build RBAC permission center from scratch, RBAC model design steps, Java implementation of RBAC permission management, many-to-many user-role-permission.

Summary

Through this article, you should now have a deeper understanding of “getting started with the RBAC permission model.” It is recommended to practice more in real projects. If you have any questions, feel free to discuss!