rbac权限管理表mysql

-- 用户表
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

-- 角色表
CREATE TABLE `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);

-- 权限表
CREATE TABLE `permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);

-- 用户角色关联表
CREATE TABLE `user_roles` (
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`, `role_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
);

-- 角色权限关联表
CREATE TABLE `role_permissions` (
  `role_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`),
  FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`),
  FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`)
);

-- 假设用户ID为1，角色ID为2
INSERT INTO `user_roles` (`user_id`, `role_id`) VALUES (1, 2);

SELECT COUNT(*) > 0 AS has_permission
FROM `users`
JOIN `user_roles` ON `users.id` = `user_roles.user_id`
JOIN `roles` ON `user_roles.role_id` = `roles.id`
JOIN `role_permissions` ON `roles.id` = `role_permissions.role_id`
JOIN `permissions` ON `role_permissions.permission_id` = `permissions.id`
WHERE `users.id` = 1 AND `permissions.name` = 'edit_article';