微服务 教程
1085个小节阅读:196k
C语言快速入门
JAVA全系列 教程
面向对象的程序设计语言
Python全系列 教程
Python3.x版本,未来主流的版本
人工智能 教程
顺势而为,AI创新未来
大厂算法 教程
算法,程序员自我提升必经之路
C++ 教程
一门通用计算机编程语言
微服务 教程
目前业界流行的框架组合
web前端全系列 教程
通向WEB技术世界的钥匙
大数据全系列 教程
站在云端操控万千数据
AIGC全能工具班
A A
White Night
授权即认证通过后,系统给用户赋予一定的权限,用户只能根据权限访问系统中的某些资源。所以在数据库中,用户需要和权限关联。除了用户表和权限表,还需要创建角色表,他们之间的关系如下:
用户角色,角色权限都是多对多关系,即一个用户拥有多个角色,一个角色拥有多个权限。如:张三拥有总经理和股东的角色,而总经理拥有查询工资、查询报表的权限;股东拥有查寻股权的权限,这样张三就拥有了查询工资、查询报表、查询股权的权限。
接下来我们创建除users外的其余表:
xxxxxxxxxx
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. 在Shiro的权限表设计中,角色和权限的关系为
A 一对一
B 一对多
C 多对一
D 多对多
答案
1=>D