Spring Security授权_权限表设计

用户和权限的关系为多对多，即用户拥有多个权限，权限也属于多个用户，所以建表方式如下：

这种方式需要指定用户有哪些权限，如：张三有查询工资的权限，即在用户权限中间表中添加一条数据，分别记录张三和查询工资权限ID。但在系统中权限数量可能非常庞大，如果一条一条添加维护数据较为繁琐。所以我们通常的做法是再加一张角色表：

用户角色，角色权限都是多对多关系，即一个用户拥有多个角色，一个角色属于多个用户；一个角色拥有多个权限，一个权限属于多个角色。这种方式需要指定用户有哪些角色，而角色又有哪些权限。

如：张三拥有总经理的角色，而总经理拥有查询工资、查询报表的权限，这样张三就拥有了查询工资、查询报表的权限。这样管理用户时只需管理少量角色，而管理角色时也只需要管理少量权限即可。接下来我们创建五张表：

xxxxxxxxxx
CREATE TABLE `users`  (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`uid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `users` VALUES (1, 'baizhan', '$2a$10$Eqv9PRMl6bPt5BiwgPr2eucgyl.E.xLENt4bvfDvv7DyS5AVPT.U6', '13812345678');

CREATE TABLE `role`  (
  `rid` int(11) NOT NULL AUTO_INCREMENT,
  `roleName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `roleDesc` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`rid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `role` VALUES (1, '总经理', '管理整个公司');
INSERT INTO `role` VALUES (2, '股东', '参与公司决策');
INSERT INTO `role` VALUES (3, '财务', '管理公司资产');

CREATE TABLE `permission`  (
  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `permissionName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`pid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `permission` VALUES (1, '查询报表', '/reportform/find');
INSERT INTO `permission` VALUES (2, '查询工资', '/salary/find');
INSERT INTO `permission` VALUES (3, '查询税务', '/tax/find');

CREATE TABLE `users_role`  (
  `uid` int(255) NOT NULL,
  `rid` int(11) NOT NULL,
  PRIMARY KEY (`uid`, `rid`) USING BTREE,
  INDEX `rid`(`rid`) USING BTREE,
  CONSTRAINT `users_role_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `users_role_ibfk_2` FOREIGN KEY (`rid`) REFERENCES `role` (`rid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `users_role` VALUES (1, 2);
INSERT INTO `users_role` VALUES (1, 3);

CREATE TABLE `role_permission`  (
  `rid` int(11) NOT NULL,
  `pid` int(11) NOT NULL,
  PRIMARY KEY (`rid`, `pid`) USING BTREE,
  INDEX `pid`(`pid`) USING BTREE,
  CONSTRAINT `role_permission_ibfk_1` FOREIGN KEY (`rid`) REFERENCES `role` (`rid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `role_permission_ibfk_2` FOREIGN KEY (`pid`) REFERENCES `permission` (`pid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `role_permission` VALUES (1, 1);
INSERT INTO `role_permission` VALUES (2, 1);
INSERT INTO `role_permission` VALUES (1, 2);
INSERT INTO `role_permission` VALUES (3, 2);
INSERT INTO `role_permission` VALUES (1, 3);
INSERT INTO `role_permission` VALUES (2, 3);

实时效果反馈

1. 在Spring Security的权限表设计中，角色和权限的关系为

A 一对一

B 一对多

C 多对一

D 多对多

答案

1=>D