Build a Scalable RBAC System with Sa-Token and MySQL – From Tables to API

In this article we discuss how to apply RBAC permission design in practice using the Sa-Token framework, providing a complete set of MySQL table definitions for users, roles, permissions and their relationships, along with sample data and Java Spring Boot API implementations for login, logout, and permission checks.

Table Structure

The core tables include user, role, permission, user_role, role_permission, and role_permission. Each table contains fields such as IDs, names, timestamps, and a soft‑delete flag.

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `enterprise_id` int(11) DEFAULT NULL COMMENT '企业ID',
  `enterprise_name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '企业名称',
  `username` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
  `password` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码',
  `create_user` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(4) DEFAULT '0' COMMENT '是否删除：0.不删除 1.删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_username` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户';

CREATE TABLE `role` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '角色ID',
  `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '角色名称',
  `parent_id` int(11) DEFAULT NULL COMMENT '父级id',
  `remark` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '描述',
  `create_user` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(4) DEFAULT '0' COMMENT '是否删除：0.不删除 1.删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_role_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1005 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色';

CREATE TABLE `permission` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '权限ID',
  `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '权限名称',
  `permission` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '权限标识',
  `path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '接口路径',
  `type` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '类型：dir目录 ，menu 菜单，button 按纽',
  `parent_id` int(11) DEFAULT NULL COMMENT '父级',
  `remark` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '描述',
  `create_user` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(4) DEFAULT '0' COMMENT '是否删除：0.不删除 1.删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_permission` (`permission`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1062 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='权限';

CREATE TABLE `user_role` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户-角色ID',
  `user_id` int(11) DEFAULT NULL COMMENT '用户ID',
  `role_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '角色ID',
  `create_user` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(4) DEFAULT '0' COMMENT '是否删除：0.不删除 1.删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `inx_user_id` (`user_id`) USING BTREE,
  UNIQUE KEY `inx_role_id` (`role_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1009 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户-角色';

CREATE TABLE `role_permission` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '角色-权限ID',
  `role_id` int(11) DEFAULT NULL COMMENT '角色ID',
  `permission_id` int(11) DEFAULT NULL COMMENT '权限id',
  `create_user` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(4) DEFAULT '0' COMMENT '是否删除：0.不删除 1.删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `inx_role_id` (`role_id`) USING BTREE,
  UNIQUE KEY `inx_permission_id` (`permission_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1021 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色-权限';

Permission Data

Sample permission records are inserted to illustrate root, query, pagination, update, and delete operations.

INSERT INTO permission (`id`,`name`,`permission`,`path`,`type`,`parent_id`,`remark`,`create_user`,`create_time`,`update_time`,`is_deleted`) VALUES
(0,'root','*','*','button',NULL,NULL,NULL,'2024-11-01 09:28:56','2024-11-01 14:51:01',0),
(1005,'channelaccount:query','channelaccount:query','/web/channelaccount/query','button',NULL,NULL,NULL,'2024-10-31 19:01:55','2024-10-31 19:01:55',0),
(1006,'channelaccount:queryPageList','channelaccount:queryPageList','/web/channelaccount/queryPageList','button',NULL,NULL,NULL,'2024-10-31 19:02:04','2024-10-31 19:02:04',0),
(1007,'channelaccount:queryList','channelaccount:queryList','/web/channelaccount/queryList','button',NULL,NULL,NULL,'2024-10-31 19:02:10','2024-10-31 19:02:10',0),
(1021,'enterpriseuser:update','enterpriseuser:update','/web/enterpriseuser/update','button',NULL,NULL,NULL,'2024-10-31 19:04:20','2024-10-31 19:04:20',0),
(1022,'enterpriseuser:delete','enterpriseuser:delete','/web/enterpriseuser/delete','button',NULL,NULL,NULL,'2024-10-31 19:04:42','2024-10-31 19:04:42',0);

API Development

Two categories of endpoints are shown: authentication (login) and regular CRUD APIs. The login method validates credentials, loads roles, stores the user in Sa‑Token session, and returns a token containing role and permission lists.

@SaIgnore
@ApiOperation(value = "企业用户登录", notes = "企业用户登录", httpMethod = "POST")
@PostMapping(value = "/login")
public ResUtil<GlobalPushToken> login(@RequestBody @Valid EnterpriseUserCreateForm form) throws ArgumentException {
    EnterpriseUserPO enterpriseUser = enterpriseUserService.queryOne(
        EnterpriseUserCondition.builder().username(form.getUsername()).build());
    if (enterpriseUser == null) {
        return ResUtil.fail(ExceptionCode.JWT_USER_INVALID);
    }
    BCryptPasswordEncoder encoder = new BCryptPasswordEncoder();
    if (!encoder.matches(form.getPassword(), enterpriseUser.getPassword())) {
        return ResUtil.fail(ExceptionCode.JWT_USER_INVALID);
    }
    List<CurrentRole> currentRoles = userRoleService.queryCurrentRoleList(enterpriseUser.getId().intValue());
    List<Integer> roleIdList = currentRoles.stream().map(CurrentRole::getRoleId).collect(Collectors.toList());
    // 登录到 Sa‑Token
    StpUtil.login(enterpriseUser.getId());
    CurrentUser currentUser = BeanUtil.copyProperties(enterpriseUser, CurrentUser.class);
    currentUser.setCurrentRoles(currentRoles);
    // 保存授权信息返回 token
    StpUtil.getSession().set("loginUser", currentUser);
    GlobalPushToken token = BeanUtil.copyProperties(StpUtil.getTokenInfo(), GlobalPushToken.class);
    token.setCurrentRoles(currentRoles);
    token.setPermissions(rolePermissionService.queryPermissionIfAbsent(roleIdList));
    return ResUtil.success(token);
}

@ApiOperation(value = "企业用户注销", notes = "企业用户注销", httpMethod = "POST")
@PostMapping(value = "/loginOut")
public ResUtil<Boolean> loginOut() throws ArgumentException {
    StpUtil.logout();
    return ResUtil.success(true);
}

Unified Permission Return

A custom StpInterface implementation provides the permission and role lists required by Sa‑Token during authorization checks.

@Component
public class PermissionInterfaceImpl implements StpInterface {
    @Resource
    private UserRoleService userRoleService;
    @Resource
    private RolePermissionService rolePermissionService;

    /** Return permission codes for a given login ID */
    @Override
    public List<String> getPermissionList(Object loginId, String loginType) {
        List<String> roleList = getRoleList(loginId, loginType);
        if (CollectionUtils.isEmpty(roleList)) {
            return new ArrayList<>();
        }
        List<Integer> roleIds = roleList.stream().map(Integer::parseInt).collect(Collectors.toList());
        List<RoleOfPermission> roleOfPermissions = rolePermissionService.queryPermissionIfAbsent(roleIds);
        return roleOfPermissions.stream().map(RoleOfPermission::getPermission).collect(Collectors.toList());
    }

    /** Return role identifiers for a given login ID */
    @Override
    public List<String> getRoleList(Object loginId, String loginType) {
        // Simulated; replace with real query logic
        List<CurrentRole> currentRoles = userRoleService.queryCurrentRoleList(Integer.parseInt((String) loginId));
        return currentRoles.stream().map(r -> r.getRoleId().toString()).collect(Collectors.toList());
    }
}

Get Logged‑in User

A method argument resolver extracts the CurrentUser object from the Sa‑Token session, allowing controller methods to receive the authenticated user directly.

public class LoginUserArgumentResolver implements HandlerMethodArgumentResolver {
    @Override
    public boolean supportsParameter(MethodParameter parameter) {
        // Check for @LoginUser annotation and CurrentUser type
        return parameter.hasParameterAnnotation(LoginUser.class) &&
               parameter.getParameterType().equals(CurrentUser.class);
    }
    @Override
    public Object resolveArgument(@NotNull MethodParameter parameter, ModelAndViewContainer mavContainer,
                                 @NotNull NativeWebRequest webRequest, WebDataBinderFactory binderFactory) throws Exception {
        SaSession session = StpUtil.getSession();
        return session.get("loginUser");
    }
}

@Data
public class CurrentUser {
    /** User ID */
    private Long id;
    /** Enterprise ID */
    private Integer enterpriseId;
    /** Enterprise name */
    private String enterpriseName;
    /** Username */
    private String username;
    /** Current roles */
    private List<CurrentRole> currentRoles;
}

By importing the provided SQL statements and integrating the sample Java code, you can quickly set up a functional RBAC permission system for any enterprise‑level application.